How To: LINQ To SQL Transformations - Part II

Introduction

This article is the second in a series outlining how to translate LINQ expression trees to SQL statements that can be executed against multiple RDBMS systems and not just Microsoft's SQL Server offerings. The articles will also illustrate how to:

1. Correctly and comprehensively translate binary and unary expressions that have valid translations into SQL.
2. Translate function calls (e.g., customer.FirstName.ToUpper()) that have SQL equivalents.
3. Implement GroupBy.
4. Implement the IQueryable methods ANY, ALL, COUNT, AVERAGE etc.
5. Parameterize queries instead of embedding constants in the SQL transformation.
6. Perform caching of previously translated expression trees.
7. Potentially not make use of MARS.

Background

In the last article in this series, I outlined how to implement a class used in the implementation, the Binder. In essence, the function of the binder is to assign values in a DbDataReader to a newly instantiated object of a given class.

The motivating example will be the following LINQ query:

var customers = from customer in customers
                where customer.City == city
                select new { Name = customer.ContactName, 
                             Phone = customer.Phone };

This will translate into the following SQL statement:

SELECT t0.ContactName, t0.Phone
FROM dbo.Customers AS t0
WHERE (t0.City = @p0)

and the following method calls:

.Where(customer => (customer.City = value(LinqTest.NorthwindLinq+<>c__DisplayClass1).city))
.Select(customer => new <>f__AnonymousType0`2(Name = customer.ContactName, 
                                              Phone = customer.Phone))

The Binder will translate the Lambda Expression:

customer => new <>f__AnonymousType0`2(Name = customer.ContactName,       
                                      Phone = customer.Phone)

to:

reader => new <>f__AnonymousType0`2(Name = IIF(Not(reader.IsDBNull(0)), 
                                                   reader.GetString(0), Convert(null)), 
                                    Phone = IIF(Not(reader.IsDBNull(1)),
                                                    reader.GetString(1), Convert(null)))

The Binder: In Depth

The Binder will need to perform the following tasks in order to perform the above translation:

1. Determine the position (or index, if you prefer) in the DbDataReader that corresponds to a given field.

In the motivating example, the field customer.ContactName will be located at index 0 of the DbDataReader.

2. Determine the type of the field we wish to retrieve from the DbDataReader and generate the appropriate expression.

In the motivating example, customer.ContactName is a string and is located at index 0. We, therefore, need to generate a call to reader.GetString(0).

3. Handle a few special cases not illustrated in our motivating example. These will be discussed at the end of the article, for the sake of readability.

Let's begin with the field declarations:

private class Binder : ExpressionVisitor {

    private readonly LambdaExpression selector = null;
    private readonly LambdaExpression binderLambda = null;
    private readonly Delegate binderMethod = null;
    private readonly Dictionary<string,> fieldPositions = new Dictionary<string,>();
    private readonly ParameterExpression reader = 
            Expression.Parameter(typeof(DbDataReader), "reader");

    private static readonly MethodInfo getBoolean = 
            typeof(DbDataReader).GetMethod("GetBoolean");
    private static readonly MethodInfo getByte = 
            typeof(DbDataReader).GetMethod("GetByte");
    private static readonly MethodInfo getChar = 
            typeof(DbDataReader).GetMethod("GetChar");
    private static readonly MethodInfo getDateTime = 
            typeof(DbDataReader).GetMethod("GetDateTime");
    private static readonly MethodInfo getDecimal = 
            typeof(DbDataReader).GetMethod("GetDecimal");
    private static readonly MethodInfo getDouble = 
            typeof(DbDataReader).GetMethod("GetDouble");
    private static readonly MethodInfo getGUID = 
            typeof(DbDataReader).GetMethod("GetGuid");
    private static readonly MethodInfo getInt16 = 
            typeof(DbDataReader).GetMethod("GetInt16");
    private static readonly MethodInfo getInt32 = 
            typeof(DbDataReader).GetMethod("GetInt32");
    private static readonly MethodInfo getInt64 = 
            typeof(DbDataReader).GetMethod("GetInt64");
    private static readonly MethodInfo getString = 
            typeof(DbDataReader).GetMethod("GetString");
    private static readonly MethodInfo getValue = 
            typeof(DbDataReader).GetMethod("GetValue");

------------------------------------------------------------------------------------------
}

1. The selector field will hold a reference to the Lambda Expression we are translating.

In the motivating example, that will be the expression:

customer => new <>f__AnonymousType0`2(Name = customer.ContactName,       
                                      Phone = customer.Phone)

2. The binderLambda field will hold a reference to the result of the translation.

In the motivating example, that will be the expression:

C#

reader => new <>f__AnonymousType0`2(Name = IIF(Not(reader.IsDBNull(0)), 
                                           reader.GetString(0), Convert(null)), 
                                    Phone = IIF(Not(reader.IsDBNull(1)), 
                                            reader.GetString(1), Convert(null)))

3. The binderMethod is a delegate that will be generated by a call to binderLambda.Compile().

Conceptually, it will be employed as follows:

C#

<>f__AnonymousType0`2 anyonymousType = 
              (<>f__AnonymousType0`2)binder.DynamicInvoke(reader)

The details of its use will be covered in the next article.

4. The fieldPositions field is a dictionary that will keep track of the fields we need to retrieve from the DbDataReader and their positions.
5. The reader is a parameter of type DbDataReader that will be referenced by the Lambda Expression.

In the motivating example, that will be the expression:

C#

reader => new <>f__AnonymousType0`2(Name = IIF(Not(reader.IsDBNull(0)), 
                                               reader.GetString(0), Convert(null)), 
                                    Phone = IIF(Not(reader.IsDBNull(1)), 
                                               reader.GetString(1), Convert(null)))

6. The fields of type MethodInfo are used to look up a field in the reader and return the value required as the required type.

I will not give a blow by blow account of what every method does; rather, I will give a more detailed conceptual overview.

Conceptual Overview

A LINQ Expression such as:

customer => new <>f__AnonymousType0`2(Name = customer.ContactName,       
                                      Phone = customer.Phone)

is a tree that consists of a series of nodes.

In the example above, the expression tree would conceptually look something like so:

Expression.Lambda(Expression.New(anonymousType2Constructor,
                    new Expression[]{
                        Expression.MakeMemberAccess(
                            Expression.Parameter(typeof(customer, "customer"),
                            typeof(Customer).GetProperty("ContactName")),
                        Expression.MakeMemberAccess(
                            Expression.Parameter(customer "customer"),
                            typeof(Customer).GetProperty("Phone"))
                    }));

You will recall from above that the reader we shall employ will be generated from the execution of a SQL statement that looks like so:

SELECT t0.ContactName, t0.Phone
FROM dbo.Customers AS t0
WHERE (t0.City = @p0)

The key thing to note is that the fields in the DbDataReader will be returned in the same order as they are accessed in the Lambda Expression, therefore ContactName will be in position 0 of the DbDataReader and Phone will be in position 1 of the DbDataReader. That, in essence, is how we know what fields are in what positions.

Now, we simply need to replace references to the customer parameter with references to the reader parameter and then replace references to properties/fields of the customer parameter with calls to the appropriate reader.GetXXX(fieldPosition) methods. E.g.:

Replace:

Expression.MakeMemberAccess(Expression.Parameter(typeof(Customer), "customer"),
                            typeof(Customer).GetProperty("ContactName"))

with:

Expression.Call(reader, getString, Expression.Constant(0));

In order to make these changes, we must be able to inspect every node in the expression tree and change that node if necessary. This is where the ExpressionVisitor discussed in Part 1 comes in. We subclass the ExpressionVisitor and override the following method:

protected override Expression VisitMemberAccess(MemberExpression m) {
    ------------
}

That, in essence, is it.

Notes:

1. Method calls:

Suppose we have a Lambda query such as:

var x = from customer in customers
        where customer.City == city
        select new { Name = customer.ContactName, 
                     OrderCount = customer.Orders.Count() };

We should expect to generate a SQL statement that looks like:

SQL

SELECT t0.ContactName, 
    (
        SELECT Count(*) 
        FROM dbo.Orders AS t2
        WHERE (t2.CustomerID = t0.CustomerID)
    )
     AS OrderCount
FROM dbo.Customers AS t0
WHERE (t0.City = @p0)

We need to intercept the call to:

C#

customer.Orders.Count()

Please see the source code for details.

2. Lambda queries that do not yield a projection:

Suppose we have a Lambda query such as:

var x = from customer in customers
        where customer.City == city
        select customer;

We need to instantiate an object of type customer, and then assign every property that has a corresponding column in the database with the appropriate value from the DbDataReader.

Please see the source code for details.

Binder Class Listing

private class Binder : ExpressionVisitor {

    private readonly LambdaExpression selector = null;
    private readonly LambdaExpression binderLambda = null;
    private readonly Delegate binderMethod = null;
    private readonly Dictionary<string,> fieldPositions = new Dictionary<string,>();

    private readonly ParameterExpression reader = 
            Expression.Parameter(typeof(DbDataReader), "reader");

    private static readonly MethodInfo getBoolean = 
            typeof(DbDataReader).GetMethod("GetBoolean");
    private static readonly MethodInfo getByte = 
            typeof(DbDataReader).GetMethod("GetByte");
    private static readonly MethodInfo getChar = 
            typeof(DbDataReader).GetMethod("GetChar");
    private static readonly MethodInfo getDateTime = 
            typeof(DbDataReader).GetMethod("GetDateTime");
    private static readonly MethodInfo getDecimal = 
            typeof(DbDataReader).GetMethod("GetDecimal");
    private static readonly MethodInfo getDouble = 
            typeof(DbDataReader).GetMethod("GetDouble");
    private static readonly MethodInfo getGUID = 
            typeof(DbDataReader).GetMethod("GetGuid");
    private static readonly MethodInfo getInt16 = 
            typeof(DbDataReader).GetMethod("GetInt16");
    private static readonly MethodInfo getInt32 = 
            typeof(DbDataReader).GetMethod("GetInt32");
    private static readonly MethodInfo getInt64 = 
            typeof(DbDataReader).GetMethod("GetInt64");
    private static readonly MethodInfo getString = 
            typeof(DbDataReader).GetMethod("GetString");
    private static readonly MethodInfo getValue = 
            typeof(DbDataReader).GetMethod("GetValue");

    public Delegate BinderMethod {
        get {
            return binderMethod;
        }
    }

    public Binder(LambdaExpression selector) {

        this.selector = selector;

        if (selector.Body.NodeType != ExpressionType.Parameter) {
            binderLambda = Expression.Lambda(((LambdaExpression)this.Visit(selector)).Body,
                                             reader);
        }
        else {
            binderLambda = GetBindingLambda(selector);
        }

        binderMethod = binderLambda.Compile();

    }

    protected override Expression VisitMethodCall(MethodCallExpression m) {

        switch (m.Method.Name) {

            case "Count":
            case "Average":
            case "Max":
            case "Min":
            case "Sum":
                break;
            default:
                return base.VisitMethodCall(m);

        }

        Debug.Assert(m.Arguments.Count > 0);
        Debug.Assert(m.Arguments[0].NodeType == ExpressionType.MemberAccess);

        if (GetAccessedType(m.Arguments[0] as MemberExpression) != 
                                   selector.Parameters[0].Type) {
            return m;
        }

        int fieldPosition = GetFieldPosition(m.ToString());
        return GetFieldReader(m, fieldPosition);
    }

    protected override Expression VisitMemberAccess(MemberExpression m) {

        Debug.Assert(selector.Parameters.Count == 1);

        if (GetAccessedType(m) != selector.Parameters[0].Type) {
            return m;
        }

        int fieldPosition = GetFieldPosition(m);
        return GetFieldReader(m, fieldPosition);

    }

    private Expression GetFieldReader(Expression m, int fieldPosition) {

        var field = Expression.Constant(fieldPosition, typeof(int));
        var readerExpression = GetReaderExpression(m, field);

        var isDbNull = Expression.Call(reader,
                                       typeof(DbDataReader).GetMethod("IsDBNull"),
                                       field);

        var conditionalExpression =
            Expression.Condition(Expression.Not(isDbNull),
                                 readerExpression,
                                 Expression.Convert(Expression.Constant(null),
                                                     readerExpression.Type));

        return conditionalExpression;

    }

    private Expression GetReaderExpression(Expression m, ConstantExpression field) {

        MethodInfo getReaderMethod = GetReaderMethod(m);

        var readerExpression = Expression.Call(reader, getReaderMethod, field);

        if (getReaderMethod.ReturnType == m.Type) {
            return readerExpression;
        }

        return Expression.Convert(readerExpression, m.Type);

    }

    private static MethodInfo GetReaderMethod(Expression m) {

        Type memberType = GetMemberType(m);

        MethodInfo getMethod = null;

        switch (Type.GetTypeCode(memberType)) {

            case TypeCode.Boolean:
                getMethod = getBoolean;
                break;

            case TypeCode.Byte:
                getMethod = getByte;
                break;

            case TypeCode.Char:
                getMethod = getChar;
                break;

            case TypeCode.DateTime:
                getMethod = getDateTime;
                break;

            case TypeCode.Decimal:
                getMethod = getDecimal;
                break;

            case TypeCode.Double:
                getMethod = getDouble;
                break;

            case TypeCode.Int16:
                getMethod = getInt16;
                break;

            case TypeCode.Int32:
                getMethod = getInt32;
                break;

            case TypeCode.Int64:
                getMethod = getInt64;
                break;

            case TypeCode.String:
                getMethod = getString;
                break;

            case TypeCode.Object:
                getMethod = getValue;
                break;

            default:
                if (m.Type == typeof(Guid)) {
                    getMethod = getGUID;
                }
                else {
                    getMethod = getValue;
                }
                break;
        }

        return getMethod;

    }

    private int GetFieldPosition(MemberExpression m) {

        return GetFieldPosition(m.Member.Name);
    }

    private int GetFieldPosition(string fieldName) {

        int fieldPosition = 0;

        if (fieldPositions.ContainsKey(fieldName)) {
            fieldPosition = fieldPositions[fieldName];
            return fieldPosition;
        }

        fieldPosition = fieldPositions.Count();
        fieldPositions.Add(fieldName, fieldPosition);

        return fieldPosition;
    }

    private static Type GetMemberType(Expression m) {

        Type memberType = null;

        if (m.Type.Name == "Nullable`1") {
            memberType = m.Type.GetGenericArguments()[0];
        }
        else {
            memberType = m.Type;
        }

        return memberType;
    }

    private static Type GetAccessedType(MemberExpression m) {

       if (m.Expression.NodeType == ExpressionType.MemberAccess) {
            return GetAccessedType((MemberExpression)m.Expression);
       }

       return m.Expression.Type;

    }

   private LambdaExpression GetBindingLambda(LambdaExpression selector) {

        var instanceType = selector.Body.Type;

        // this is a hack
        var properties = (from property in instanceType.GetProperties()
                          where property.PropertyType.IsValueType ||
                          property.PropertyType == typeof(string)
                          orderby property.Name
                          select instanceType.GetField("_" + property.Name,
                                                       BindingFlags.Instance |
                                                       BindingFlags.NonPublic))
                          .ToArray();

        var bindings = new MemberBinding[properties.Length];

        for (int i = 0; i < properties.Length; i++) {
            var callMethod = GetFieldReader(
                                Expression.MakeMemberAccess(
                                    Expression.Parameter(instanceType, "param"),
                                    properties[i]),
                                i);

            bindings[i] = Expression.Bind(properties[i], callMethod);
        }

        return Expression.Lambda(Expression.MemberInit(Expression.New(instanceType),
                                 bindings),
                                 reader);
    }
}