MySQL

 

What is it?

The DbExpressions  library is an abstract syntax tree implementation for SQL.

A SQL query is represented as an expression tree where each node represents a fragment in the query.

The query represented by this expression tree is completely independent upon the target database system.

The library has a lot in common with what we find in  the System.Linq.Expressions  namespace.

You might say that when Expressions compile into intermediate language code (IL), DbExpressions “compiles” into the vendor specific SQL dialect.

Currently MS SQL Server,MySql  and SQLite are supported out of the box.

Why do I need it?

You don’t want to maintain different versions of your queries for each DBMS.

You don’t want to restrict yourself to ANSI SQL.

You don’t want to manually build your queries using string concatenation.

You don’t want to manually deal with parameters and placeholders.

You are developing an application that makes heavy use of dynamic SQL generation.

 

How do I use it?

There are basically two ways to deal with DbExpression trees.

Constructing the expression tree manually

We can create new expressions using the DbExpressionFactory class.

The following example shows how to create a DbBinaryExpression

  1. var columnExpression = dbExpressionFactory.Column("SomeColumn");
  2. var constantExpression = dbExpressionFactory.Constant(1);
  3. var binaryExpression = dbExpressionFactory.MakeBinary(DbBinaryExpressionType.Equal, columnExpression, constantExpression);
  4. Console.WriteLine(binaryExpression);

The output from this will be “([SomeColumn] = @p0)”

The DbExpression class overrides the ToString() method so that it is easy to see the translated version of the expression during debugging.

Note that the text returned from the ToString() method will be generated using Sql Server syntax.

 

Using the fluent interface.

  1. var selectQuery = new DbSelectQuery();
  2. selectQuery.Select(f => f.Column("ProductID"))
  3.     .From(f => f.Table("Order Details"));

The ability to do paging is also a nice feature.

  1. selectQuery.Select(f => f.Column("ProductID"))
  2.     .From(f => f.Table("Products")).Skip(10).Take(10);

The fluent API builds up the expression tree so that we don’t need to manually create all expressions.

Take a look at the examples page for more details

 

 

Translating the query into SQL

In order to execute the query we need to translate the query into SQL, just in the same way that Expression<T>  delegates need to be compiled into IL before they can be invoked.

  1. using (IDbConnection connection = CreateConnection())
  2. {
  3.     IDbCommand command = query.Translate();
  4.     command.Connection = connection;
  5.     command.ExecuteNonQuery();
  6. }

Version history

v1.0.0.3

Added support for mathematical functions

Example:

  1. selectQuery.Select(f => f.Abs(f.Column("UnitPrice"))).From(f => f.Table("Products"));

Review the DbMathematicalFunctionExpressionType class for a list of supported mathematical functions.

Added the “As” extension method

This method allows expressions to be aliased without explicitly creating a DbAliasExpression.

Example:

  1. selectQuery.Select(f => f.Abs(f.Column("UnitPrice").As("up"))).From(f => f.Table("Products").As("p"));
Added support for null values
  1. selectQuery.Select(f => f.Column("OrderID")).From(f => f.Table("Orders")).Where(
  2.                 f => f.Column("OrderDate") == f.Constant(null));

This would translate into the following SQL (Sql Server):

  1. SELECT
  2.     [OrderID]
  3. FROM
  4.     [Orders]
  5. WHERE
  6.     ([OrderDate] IS NULL)

v.1.0.0.4

Added support for SQLite

NOTE: Some limitations exists due to the current implementation of the SQLite engine.

Take a look at SQLite- Unsupported SQL for a full list of unsupported features.

Still we are able to support most functions exposed by the DbExpressions library.

The most important limitations relevant here is:

  • Right outer joins
  • Operations working at the millisecond level will not work
  • Update statements containing a FROM clause.
  • Aliased UPDATE and DELETE target tables

Added support for DISTINCT

It is now possible to do distinct select queries using the new SelectDistinct method.

Last edited Jul 8, 2011 at 11:45 AM by seesharper, version 15