Writing SQL using lambda expressions (not Linq)

Jump to Comments

Some of the code in this post may be outdated, I suggest visiting the project site at http://dbex.googlecode.com/

Persistence ignorance and type safety, this is what Linq gives us. I can’t get enough of the latter, but in most cases a generic query API is not what I need, specially when I’m not querying in-memory objects.

You know what I’m talking about, you work with SQL, XPath, etc. You know how to write queries and get the results you want. For instance, there are some things in SQL that I don’t know how to translate to Linq, and if they are going to look the same when translated back to SQL, like the column IN (subquery) syntax. Although you can instead write a join and get the same results I like this better because is more readable. Another concern are functions, what if you want to use a function that has no CLR mapping?

To address this problems and keep the type safety I wrote a class called SqlBuilder. Think of it as a StringBuider for SQL. Let’s take a look at an example:

var query = new SqlBuilder()
   .Select<Product>(p => new { Description = p.ProductName, ID = p.ProductID })
   .From<Product>().Where<Product>(p => p.CategoryID == 1);         

Debug.WriteLine(query);         

// -- the output:
// SELECT t0.ProductName AS Description, t0.ProductID AS ID
// FROM Product AS t0
// WHERE (t0.CategoryID = 1)

This looks nice, but could be better. First of all, the name of the table we want to query is ‘Products’ not ‘Product’. The SqlBuilder has no way of knowing that, unless we provide a MetaModel instance, which lives in the System.Data.Linq.Mapping namespace. This is the mapping API that Linq to Sql uses. Secondly, It would be nice if we could have the identifiers quoted. For that all we need is to provide a DbProviderFactory instance. Let’s change the constructor and run the example again:

var query2 = new SqlBuilder(SqlClientFactory.Instance, new AttributeMappingSource().GetModel(typeof(Product)))
   .Select<Product>(p => new { Description = p.ProductName, ID = p.ProductID })
   .From<Product>().Where<Product>(p => p.CategoryID == 1);         

Debug.WriteLine(query2);         

// -- the output:
// SELECT [t0].[ProductName] AS [Description], [t0].[ProductID] AS [ID]
// FROM [Products] AS [t0]
// WHERE ([t0].[CategoryID] = 1)

Now it looks perfect. We get what we write, and that’s what we want. Let’s take a look at a more complex example, this is Northwind’s ‘Product Sales for 1997′ view:

var query3 = new SqlBuilder(MySqlClientFactory.Instance, new AttributeMappingSource().GetModel(typeof(Product)))
   .Select<Product>(p => new object[] { p.Category.CategoryName, p.ProductName })
   .Select<OrderDetail>(od => new { ProductSales = Sql.Sum(Sql.Round((od.UnitPrice * od.Quantity * Convert.ToDecimal(((1 - od.Discount) / 100))) * 100, 2)) })
   .From<Product>().Join<Product, Category>(JoinType.InnerJoin, (p, c) => p.CategoryID == c.CategoryID)
   .Join<Product, OrderDetail>(JoinType.InnerJoin, (p, od) => p.ProductID == od.ProductID)
   .Join<OrderDetail, Order>(JoinType.InnerJoin, (od, o) => od.OrderID == o.OrderID)
   .Where<Order>(o => Sql.Between(o.ShippedDate, DateTime.Parse("1997-01-01"), DateTime.Parse("1997-12-31")))
   .GroupBy<Category>(c => c.CategoryName)
   .GroupBy<Product>(p => p.ProductName);         

Debug.WriteLine(query3);         

// -- the output:
// SELECT `t0`.`CategoryName`, `t1`.`ProductName`, Sum(Round((((`t2`.`UnitPrice` * `t2`.`Quantity`) * ((1 - `t2`.`Discount`) / 100)) * 100), 2)) AS `ProductSales`
// FROM `Products` AS `t1`
// INNER JOIN `Categories` AS `t0` ON (`t1`.`CategoryID` = `t0`.`CategoryID`)
// INNER JOIN `Order Details` AS `t2` ON (`t1`.`ProductID` = `t2`.`ProductID`)
// INNER JOIN `Orders` AS `t3` ON (`t2`.`OrderID` = `t3`.`OrderID`)
// WHERE `t3`.`ShippedDate` BETWEEN {0} AND {1}
// GROUP BY `t0`.`CategoryName`, `t1`.`ProductName`

Now I’m using the MySqlClientFactory just to show off it should work with any provider. Notice I’m using SQL functions, like Sum, Round and Between. Also notice I’m calling Select and GroupBy twice, but the second call appends “, ” instead of the clause name, that’s something you can configure if you write your own methods. Also you can see that there are two placeholders for the DateTime values. The values are cached and the parameter names are generated when you call ToCommand() and inserted in those placeholders.

You can extend the SqlBuilder class using extension methods to add more clauses and statements, and static methods that represent server functions. This is a static class called MySql that encapsulates some functionality available in MySql Server.

public static class MySql { 

   [SqlFunction(Template = "MATCH ({0}) AGAINST ({1})")]
   public static bool Match(object[] members, string searchExpression) {
      throw new InvalidOperationException();
   } 

   [SqlFunction(Template = "MATCH ({0}) AGAINST ({1} IN BOOLEAN MODE)")]
   public static bool MatchBoolean(object[] members, string searchExpression) {
      throw new InvalidOperationException();
   } 

   public static SqlBuilder Limit(this SqlBuilder builder, int maxRecords) {
      return Limit(builder, maxRecords, 0);
   } 

   [SqlClause(Name = "LIMIT", IncludeInCountCommand = false)]
   public static SqlBuilder Limit(this SqlBuilder builder, int maxRecords, int startIndex) { 

      MethodBase method = MethodBase.GetCurrentMethod();
      builder.OnClauseBuilding(new ClauseBuildingEventArgs(method)); 

      StringBuilder sb = new StringBuilder();
      sb.Append(maxRecords); 

      if (startIndex > 0) {
         sb.Append(" OFFSET ");
         sb.Append(startIndex);
      } 

      builder.OnClauseBuilt(new ClauseBuiltEventArgs(method, sb.ToString())); 

      return builder;
   }
}

You can see there are 2 functions, called Match and MatchBoolean, denoted by the SqlFunction attribute (mandatory). The SqlFunction.Template property is optional and useful for those functions that don’t look like functions at all. The Limit clause uses the SqlClause attribute which is optional. SqlBuilder also builds a second command for retriving the total number of records the main command is supposed to return. This is specially useful when doing paging. The SqlClause.IncludeInCountCommand indicates if the clause should be appended to the count command, which is not the case for Limit and OrderBy.

I hope you’ll find this useful enough to give it a try, specially for those who don’t want to give up the power of writing SQL.

1 Comment

Filed under ado.net

Tags: dbextensions, dynamic sql, lambda expressions