Custom SQL

OrmLite's Expression support satisfies the most common RDBMS queries with a strong-typed API. For more complex queries you can easily fall back to raw SQL where the Custom SQL APIs let you map custom SqlExpressions into different responses:

var q = db.From<Person>()
          .Where(x => x.Age < 50)
          .Select("*");
List<Person> results = db.SqlList<Person>(q);

List<Person> results = db.SqlList<Person>(
    "SELECT * FROM Person WHERE Age < @age", new { age=50});

List<string> results = db.SqlColumn<string>(db.From<Person>().Select(x => x.LastName));
List<string> results = db.SqlColumn<string>("SELECT LastName FROM Person");

HashSet<int> results = db.ColumnDistinct<int>(db.From<Person>().Select(x => x.Age));
HashSet<int> results = db.ColumnDistinct<int>("SELECT Age FROM Person");

var q = db.From<Person>()
          .Where(x => x.Age < 50)
          .Select(Sql.Count("*"));
int result = db.SqlScalar<int>(q);
int result = db.SqlScalar<int>("SELECT COUNT(*) FROM Person WHERE Age < 50");

Custom SQL with Typed SqlExpression​

Using a typed SQL Expression with a mix of typed an custom SQL Expressions:

var q = db.From<Person>();
q.Where("Age < {0}", 50);
List<Person> results = db.Select(q)

var q = db.From<Person>().Where("Age < {0}", 50);
string sql = q.ToSelectStatement();
List<Person> results = db.Select(sql, q.Params);

Custom Selects​

public record CustomPoco(int Year, string Max);

var upperNamesWithA = db.SqlColumn<string>(
    "SELECT upper(Name) FROM Track WHERE instr(Name,'a') > 0");

var meta = db.SqlList<CustomPoco>(
    "SELECT DISTINCT Year % 10 as Year, hex(Year % 10) as Hex FROM Track");

db.ExecuteSql("ALTER TABLE Track ADD Rand INT default 0");
db.ExecuteSql("UPDATE Track SET Rand = abs(random()) % 1000");

var trackRandValues = db.Dictionary<string,int>("SELECT Name, Rand FROM Track");

var maxRand = db.SqlScalar<int>("SELECT MAX(Rand) FROM Track");

Custom Insert and Updates​

Db.ExecuteSql("INSERT INTO page_stats (ref_id, fav_count) VALUES (@refId, @favCount)",
              new { refId, favCount })

//Async:
Db.ExecuteSqlAsync("UPDATE page_stats SET view_count = view_count + 1 WHERE id = @id", new { id })

INSERT INTO SELECT​

You can use OrmLite's Typed SqlExpression to create a subselect expression that you can use to create and execute a typed INSERT INTO SELECT SqlExpression with:

var q = db.From<User>()
    .Where(x => x.UserName == "UserName")
    .Select(x => new {
        x.UserName, 
        x.Email, 
        GivenName = x.FirstName, 
        Surname = x.LastName, 
        FullName = x.FirstName + " " + x.LastName
    });

var id = db.InsertIntoSelect<CustomUser>(q)

Foreign Key attribute for referential actions on Update/Deletes​

Creating a foreign key in OrmLite can be done by adding [References(typeof(ForeignKeyTable))] on the relation property, which will result in OrmLite creating the Foreign Key relationship when it creates the DB table with db.CreateTable<Poco>.

Additional fine-grain options and behaviour are available in the [ForeignKey] attribute which will let you specify the desired behaviour when deleting or updating related rows in Foreign Key tables.

An example of a table with the different available options:

public class TableWithAllCascadeOptions
{
	[AutoIncrement] public int Id { get; set; }
	
	[References(typeof(ForeignKeyTable1))]
	public int SimpleForeignKey { get; set; }
	
	[ForeignKey(typeof(ForeignKeyTable2), OnDelete = "CASCADE", OnUpdate = "CASCADE")]
	public int? CascadeOnUpdateOrDelete { get; set; }
	
	[ForeignKey(typeof(ForeignKeyTable3), OnDelete = "NO ACTION")]
	public int? NoActionOnCascade { get; set; }
	
	[Default(typeof(int), "17")]
	[ForeignKey(typeof(ForeignKeyTable4), OnDelete = "SET DEFAULT")]
	public int SetToDefaultValueOnDelete { get; set; }
	
	[ForeignKey(typeof(ForeignKeyTable5), OnDelete = "SET NULL")]
	public int? SetToNullOnDelete { get; set; }
}

System Variables and Default Values​

To provide richer support for non-standard default values, each RDBMS Dialect Provider contains a OrmLiteDialectProvider.Variables placeholder dictionary for storing common, but non-standard RDBMS functionality. We can use this to define non-standard default values, in a declarative way, that works across all supported RDBMS's like automatically populating a column with the RDBMS UTC Date when Inserted with a default(T) Value:

public class Poco
{
    [Default(OrmLiteVariables.SystemUtc)]  //= {SYSTEM_UTC}
    public DateTime CreatedTimeUtc { get; set; }
}

OrmLite variables need to be surrounded with {} braces to identify that it's a placeholder variable, e.g {SYSTEM_UTC}.

The ForeignKeyTests show the resulting behaviour with each of these configurations in more detail.

Custom SQL using PostgreSQL Arrays​

The PgSql.Array() provides a typed API for generating PostgreSQL Array Expressions, e.g:

PgSql.Array(1,2,3)     //= ARRAY[1,2,3]
var strings = new[]{ "A","B","C" };
PgSql.Array(strings)   //= ARRAY['A','B','C']

Which you can safely use in Custom SQL Expressions that use PostgreSQL's native ARRAY support:

q.And($"{PgSql.Array(anyTechnologyIds)} && technology_ids")
q.And($"{PgSql.Array(labelSlugs)} && labels");

If you want and empty collection to return null instead of an empty ARRAY[] you can use the nullIfEmpty overload:

PgSql.Array(new string[0], nullIfEmpty:true)      //= null
PgSql.Array(new[]{"A","B","C"}, nullIfEmpty:true) //= ARRAY['A','B','C']