SqlSet Tutorial

Overview

SqlSet is a class designed to build and optionally execute SQL queries. It’s a LINQ-inspired query API that uses SQL instead of lambda expressions. The main design goals are:

• Execute the most common queries without having to build the complete SQL yourself.
• Abstract-away SQL dialect differences (e.g. pagination).

Let’s take a look at the first example:

var db = new Database("<connection string>", "<provider invariant name>");
SqlSet<Product> products = db.From<Product>("Products");

To get an instance call the Database.From method specifying the table name and the type you want to map to.

AsEnumerable, ToArray, ToList

IEnumerable<Product> r1 = products.AsEnumerable();
Product[] r2 = products.ToArray();
List<Product> r3 = products.ToList();

Because SqlSet is a connected query, you can directly call methods like AsEnumerable, ToArray or ToList to get back results. Note that in the above code only two queries are executed, because AsEnumerable is deferred-executed.

First, FirstOrDefault, Single, SingleOrDefault

Product p1 = products.First();
Product p2 = products.FirstOrDefault();
Product p3 = products.Single();
Product p4 = products.SingleOrDefault();

Call these methods to get a single result. If you know LINQ you should know the difference between them :)

All, Any

bool haveProducts = products.Any();
bool allHavePrice = products.All("NOT UnitPrice IS NULL");

…which executes:

SELECT (CASE WHEN EXISTS (
   SELECT * 
   FROM Products) THEN 1 ELSE 0 END)

SELECT (CASE WHEN EXISTS (
   SELECT *
   FROM Products
   WHERE NOT (NOT UnitPrice IS NULL)) THEN 1 ELSE 0 END)

Count, LongCount

int count1 = products.Count();
long count2 = products.LongCount();

Both execute the same query:

SELECT COUNT(*) FROM Products

SELECT COUNT(*) FROM Products

Where, OrderBy, Skip, Take, Select

These methods return a new SqlSet instance. Unlike SqlBuilder, SqlSet is immutable. Also unlike SqlBuilder, you can call methods in any order. For example:

Product[] topFiveWithLeastStock = products
   .OrderBy("UnitsInStock")
   .Take(5)
   .ToArray();

…which executes:

SELECT *
FROM Products
ORDER BY UnitsInStock
LIMIT @p0
-- @p0: Input Int32 (Size = 0) [5]
-- [-1] records affected.

SELECT TOP(@p0) * 
FROM Products
ORDER BY UnitsInStock
-- @p0: Input Int32 (Size = 0) [5]
-- [-1] records affected.

…but if we call Take first and then OrderBy:

Product[] firstFiveOrderedByStock = products
   .Take(5) 
   .OrderBy("UnitsInStock")
   .ToArray();

…it executes:

SELECT *
FROM (
   SELECT *
   FROM Products
   LIMIT @p0) dbex_set3
ORDER BY UnitsInStock
-- @p0: Input Int32 (Size = 0) [5]
-- [-1] records affected.

SELECT *
FROM (
   SELECT TOP(@p0) *
   FROM Products) dbex_set4
ORDER BY UnitsInStock
OFFSET 0 ROWS
-- @p0: Input Int32 (Size = 0) [5]
-- [-1] records affected.

Again, if you know LINQ this shouldn’t come as a surprise.

For more about Select, see Projections.

SqlSet vs. SqlSet<TResult>

So far we’ve been using the generic SqlSet<TResult>. There is also a non-generic SqlSet (inherited by SqlSet<TResult>) which is useful when you don’t know the type you are mapping to until runtime.

SqlSet<Product> products1 = db.From<Product>("Products");
SqlSet products2 = db.From("Products", typeof(Product));

A SqlSet can be promoted to a SqlSql<TResult> using the Cast method:

// The type parameter must be the same type used when products2 was created
SqlSet<Product> products3 = products2.Cast<Product>();

Typed vs. Untyped

You can also completely omit the result type:

// Untyped set
SqlSet products = db.From("Products");

When you execute queries with an untyped set you get dynamic objects:

dynamic p1 = products.First();
string name = p1.ProductName;

Untyped sets are also useful when you don’t need to execute and just want to build a query and get back a SqlBuilder representation of it.

SqlBuilder query = products.GetDefiningQuery();

Again, you can promote an untyped set to a typed set using the Cast method.

Projections

To change the result type of a set you must call the Select method.

SqlSet<Product> products = db.From<Product>("Products");
SqlSet<ProductStock> productStocks = products.Select<ProductStock>("ProductID, ProductName, UnitsInStock");

You can also provide a custom mapping function:

SqlSet<string> productNames = products.Select(r => r.GetString(0), "ProductName");

The function takes an IDataRecord and can return anything you want, which means you can also use SqlSet for simple values like string or int.

If you provide no result type or mapping function it turns into an untyped set.

SqlSet productNames = products.Select("ProductName");

Complex queries

SqlSet doesn’t support joins or grouping. After all, the goal is not to completely abstract the SQL language, but to provide a simple API for the most common queries. For complex queries use SqlBuilder instead, then you can pass the query to the From method and continue building using SqlSet:

public IEnumerable<Product> GetProductsByCategory(int categoryId, int skip = 0, int take = 20) {

   var query = SQL
      .SELECT("p.ProductID, p.ProductName, p.CategoryID")
      ._("c.CategoryID AS Category$CategoryID, c.CategoryName AS Category$CategoryName")
      .FROM("Products p")
      .LEFT_JOIN("Categories c ON p.CategoryID = c.CategoryID");

   return this.db
      .From<Product>(query)
      .Where("CategoryID = {0}", categoryId)
      .OrderBy("ProductID DESC")
      .Skip(skip)
      .Take(take)
      .AsEnumerable();
}

…which executes:

SELECT *
FROM (
   SELECT p.ProductID, p.ProductName, p.CategoryID, c.CategoryID AS Category$CategoryID, c.CategoryName AS Category$CategoryName
   FROM Products p
   LEFT JOIN Categories c ON p.CategoryID = c.CategoryID) dbex_set5
WHERE CategoryID = @p0
ORDER BY ProductID DESC
LIMIT @p1
OFFSET @p2
-- @p0: Input Int32 (Size = 0) [1]
-- @p1: Input Int32 (Size = 0) [20]
-- @p2: Input Int32 (Size = 0) [0]
-- [-1] records affected.

SELECT *
FROM (
   SELECT p.ProductID, p.ProductName, p.CategoryID, c.CategoryID AS Category$CategoryID, c.CategoryName AS Category$CategoryName
   FROM Products p
   LEFT JOIN Categories c ON p.CategoryID = c.CategoryID) dbex_set6
WHERE CategoryID = @p0
ORDER BY ProductID DESC
OFFSET @p1 ROWS
FETCH NEXT @p2 ROWS ONLY
-- @p0: Input Int32 (Size = 0) [1]
-- @p1: Input Int32 (Size = 0) [0]
-- @p2: Input Int32 (Size = 0) [20]
-- [-1] records affected.

Include

The previous example used query mapping syntax to eagerly-load the Category for each Product. The same can be acomplished using the Include method:

public IEnumerable<Product> GetProductsByCategory(int categoryId, int skip = 0, int take = 20) {

   return this.db
      .Table<Product>()
      .Include("Category")
      .Where("CategoryID = {0}", categoryId)
      .OrderBy("ProductID DESC")
      .Skip(skip)
      .Take(take)
      .AsEnumerable();
}

…which executes:

SELECT *
FROM (
   SELECT `dbex_l`.*, `dbex_r1`.`CategoryID` AS Category$CategoryID, `dbex_r1`.`CategoryName` AS Category$CategoryName, `dbex_r1`.`Description` AS Category$Description, `dbex_r1`.`Picture` AS Category$Picture
   FROM (
      SELECT `ProductID`, `ProductName`, `SupplierID`, `CategoryID`, `QuantityPerUnit`, `UnitPrice`, `UnitsInStock`, `UnitsOnOrder`, `ReorderLevel`, `Discontinued`
      FROM `Products`) `dbex_l`
   LEFT JOIN `Categories` `dbex_r1` ON (`dbex_l`.`CategoryID` = `dbex_r1`.`CategoryID`)) dbex_set6
WHERE CategoryID = @p0
ORDER BY ProductID DESC
LIMIT @p1
OFFSET @p2
-- @p0: Input Int32 (Size = 0) [1]
-- @p1: Input Int32 (Size = 0) [20]
-- @p2: Input Int32 (Size = 0) [0]
-- [-1] records affected.

SELECT *
FROM (
   SELECT [dbex_l].*, [dbex_r1].[CategoryID] AS Category$CategoryID, [dbex_r1].[CategoryName] AS Category$CategoryName, [dbex_r1].[Description] AS Category$Description, [dbex_r1].[Picture] AS Category$Picture
   FROM (
      SELECT [ProductID], [ProductName], [SupplierID], [CategoryID], [QuantityPerUnit], [UnitPrice], [UnitsInStock], [UnitsOnOrder], [ReorderLevel], [Discontinued]
      FROM [Products]) [dbex_l]
   LEFT JOIN [Categories] [dbex_r1] ON ([dbex_l].[CategoryID] = [dbex_r1].[CategoryID])) dbex_set6
WHERE CategoryID = @p0
ORDER BY ProductID DESC
OFFSET @p1 ROWS
FETCH NEXT @p2 ROWS ONLY
-- @p0: Input Int32 (Size = 0) [1]
-- @p1: Input Int32 (Size = 0) [0]
-- @p2: Input Int32 (Size = 0) [20]
-- [-1] records affected.

Note that I called the Database.Table method instead of Database.From. Although using Database.From would also work, with Database.Table you don’t need to specify the table name. Both Database.Table and Include only work for annotated types.

Include can be called many times:

return this.db
   .Table<Product>()
   .Include("Category")
   .Include("Supplier")
   .AsEnumerable();

And you can use a deep path:

return this.db
   .Table<EmployeeTerritory>()
   .Include("Territory.Region")
   .AsEnumerable();

And load one-to-many associations:

return this.db
   .Table<Order>()
   .Include("OrderDetails.Product")
   .AsEnumerable();

Find

Find is another method that only works for annotated types. You can use it to get a single result (or null) that matches the given primary key value.

Order order = db
   .Table<Order>()
   .Include("OrderDetails.Product")
   .Find(orderId);

Conclusions

Having the power to write your own SQL is great. Not having to write the same simple queries over and over is even better. SqlSet helps you compose and reuse SQL in a database independent way. While SqlBuilder is meant for private use, SqlSet can be shared, allowing the caller to further refine the query. LINQ lovers should feel right at home with SqlSet.