Customized SQL Features

A number of new hooks are available to provide more flexibility when creating and dropping your RDBMS tables.

CustomSelect Attribute​

The [CustomSelect] can be used to define properties you want populated from a Custom SQL Function or Expression instead of a normal persisted column, e.g:

public class Block
{
    public int Id { get; set; }
    public int Width { get; set; }
    public int Height { get; set; }

    [CustomSelect("Width * Height")]
    public int Area { get; set; }

    [Default(OrmLiteVariables.SystemUtc)]
    public DateTime CreatedDate { get; set; }

    [CustomSelect("FORMAT(CreatedDate, 'yyyy-MM-dd')")]
    public string DateFormat { get; set; }
}

db.Insert(new Block { Id = 1, Width = 10, Height = 5 });

var block = db.SingleById<Block>(1);

block.Area.Print(); //= 50

block.DateFormat.Print(); //= 2016-06-08 (SQL Server)

Order by dynamic expressions​

The [CustomSelect] attribute can be used to populate a property with a dynamic SQL Expression instead of an existing column, e.g:

public class FeatureRequest
{
    public int Id { get; set; }
    public int Up { get; set; }
    public int Down { get; set; }

    [CustomSelect("1 + Up - Down")]
    public int Points { get; set; }
}

You can also order by the SQL Expression by referencing the property as you would a normal column. By extension this feature now also works in AutoQuery where you can select it in a partial result set and order the results by using its property name, e.g:

/features?fields=id,points&orderBy=points

Custom SQL Fragments​

The Sql.Custom() API lets you use raw SQL Fragments in Custom .Select() expressions, e.g:

var q = db.From<Table>()
    .Select(x => new {
        FirstName = x.FirstName,
        LastName = x.LastName,
        Initials = Sql.Custom("CONCAT(LEFT(FirstName,1), LEFT(LastName,1))")
    });

Custom Field Declarations​

The [CustomField] attribute can be used for specifying custom field declarations in the generated Create table DDL statements, e.g:

public class PocoTable
{
    public int Id { get; set; }

    [CustomField("CHAR(20)")]
    public string CharColumn { get; set; }

    [CustomField("DECIMAL(18,4)")]
    public decimal? DecimalColumn { get; set; }

    [CustomField(OrmLiteVariables.MaxText)]        //= {MAX_TEXT}
    public string MaxText { get; set; }

    [CustomField(OrmLiteVariables.MaxTextUnicode)] //= {NMAX_TEXT}
    public string MaxUnicodeText { get; set; }
}

db.CreateTable<PocoTable>(); 

Generates and executes the following SQL in SQL Server:

CREATE TABLE "PocoTable" 
(
  "Id" INTEGER PRIMARY KEY, 
  "CharColumn" CHAR(20) NULL, 
  "DecimalColumn" DECIMAL(18,4) NULL, 
  "MaxText" VARCHAR(MAX) NULL, 
  "MaxUnicodeText" NVARCHAR(MAX) NULL 
); 

Custom Insert and Update Expressions​

The [CustomInsert] and [CustomUpdate] attributes can be used to override what values rows are inserted during INSERT's and UPDATE's.

We can use this to insert a salted and hashed password using PostgreSQL native functions:

public class CustomSqlUser
{
    [AutoIncrement]
    public int Id { get; set; }

    public string Email { get; set; }

    [CustomInsert("crypt({0}, gen_salt('bf'))"),
     CustomUpdate("crypt({0}, gen_salt('bf'))")]
    public string Password { get; set; }
}

var user = new CustomSqlUser {
    Email = "user@email.com", 
    Password = "secret"
};
db.Insert(user);

We can then use Sql.Custom() to create a partially typed custom query to match on the hashed password, e.g:

var quotedSecret = db.Dialect().GetQuotedValue("secret");
var q = db.From<CustomSqlUser>()
    .Where(x => x.Password == Sql.Custom($"crypt({quotedSecret}, password)"));
var row = db.Single(q);

Pre / Post Custom SQL Hooks when Creating and Dropping tables​

Pre / Post Custom SQL Hooks allow you to inject custom SQL before and after tables are created or dropped, e.g:

[PostCreateTable("INSERT INTO TableWithSeedData (Name) VALUES ('Foo');" +
                 "INSERT INTO TableWithSeedData (Name) VALUES ('Bar');")]
public class TableWithSeedData
{
    [AutoIncrement]
    public int Id { get; set; }
    public string Name { get; set; }
}

Which like other ServiceStack attributes, can also be added dynamically, e.g:

typeof(TableWithSeedData)
    .AddAttributes(new PostCreateTableAttribute(
        "INSERT INTO TableWithSeedData (Name) VALUES ('Foo');" +
        "INSERT INTO TableWithSeedData (Name) VALUES ('Bar');"));

Custom SQL Hooks also allow executing custom SQL before and after a table has been created or dropped, i.e:

[PreCreateTable(runSqlBeforeTableCreated)]
[PostCreateTable(runSqlAfterTableCreated)]
[PreDropTable(runSqlBeforeTableDropped)]
[PostDropTable(runSqlAfterTableDropped)]
public class Table {}

Custom SqlExpression Filter​

The generated SQL from a Typed SqlExpression can also be customized using .WithSqlFilter(), e.g:

var q = db.From<Table>()
    .Where(x => x.Age == 27)
    .WithSqlFilter(sql => sql + " option (recompile)");

var q = db.From<Table>()
    .Where(x => x.Age == 27)
    .WithSqlFilter(sql => sql + " WITH UPDLOCK");

var results = db.Select(q);

Ignoring DTO Properties​

You may use the [Ignore] attribute to denote DTO properties that are not fields in the table. This will force the SQL generation to ignore that property.