How To LINQ To SQL: Part III

Introduction

This article is the third 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 detailed how to implement a class used in this 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. Please consult the previous article for details, if you haven’t done so already.

The Executor Class

The purpose of the Executor class is, as its name suggests, executing a SQL statement and returning the results from the generated DbDataReader as an IEnumerable<T>. I shall employ the same motivating example used in the last article to explain exactly what that means:

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

The Executor: In Depth

The LINQ query above will translate to the following SQL statement:

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

The Executor will require the following in order to perform its function:

1. A DbConnection to the database being queried.
2. The SQL statement to be executed.
3. The expression that yielded the SQL statement above. (In our motivating example above, you will note that the expression contains a variable named city. We need the value of this variable in order to initialize the parameter @p0.)
4. A delegate that accepts a DbDataReader and returns an object of type T. In our motivating example, T is an anonymous type with two properties, Name and Phone. This delegate is obtained from the Binder discussed in the previous article.

The field declarations for the Executor class are as follows:

private class Executor<T> : ExpressionVisitor, IEnumerable<T> {

    private readonly DbConnection connection = null;
    private readonly SqlExpressionParser sqlExpressionParser = null;
    private readonly Func<DbDataReader, T>  binder = null;
    private readonly List<object> parameters = new List<object>();

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

The Executor class inherits from a class known as the ExpressionVisitor (see Parts 1 and 2 for more details) and implements IEnumerable<T>. sqlExpressionParser is responsible for supplying the SQL statement to be executed and the expression that yields that statement.

Query Parameterization

In order to retrieve the parameters (if any) required by the SQL statement, we must inspect the expression that yielded it. We then retrieve the constants embedded therein, and add them to our list of parameters. This action is performed as follows:

public Executor(DbConnection connection, 
                SqlExpressionParser sqlExpressionParser,
                Delegate binder) 
{

this.Visit(sqlExpressionParser.expression);
---------------------------------------------------------------------
}

protected override Expression VisitConstant(ConstantExpression c) 
{
      if (c.Value == null) {
          parameters.Add("NULL");
      }
      else {
          switch (Type.GetTypeCode(c.Value.GetType())) {
                case TypeCode.Boolean:
                      parameters.Add(((bool)c.Value) ? 1 : 0);
                        break;
                  case TypeCode.String:
                        parameters.Add(c.Value);
                        break;
                  case TypeCode.Object:
                        break;
                  default:
                      parameters.Add(c.Value.ToString());
                        break;
            }
      }
      return c;
}

There is one additional complication, that I will deal with in the next article for the sake of readability.

Query Execution

As stated earlier, the Executor implements IEnumerable<T>. We must, therefore, implement the method IEnumerator<T> GetEnumerator() and execute the query when GetEnumerator() is called. This is done as follows.

public IEnumerator<T> GetEnumerator() 
{

      DbCommand cmd = connection.CreateCommand();

      cmd.CommandText = sqlExpressionParser.GetSQLStatement();

      for (int i = 0; i < parameters.Count; i++) {
                
          var parameter = cmd.CreateParameter();

            parameter.ParameterName = "@p" + i;

            parameter.Value = parameters[i];
                    
            cmd.Parameters.Add(parameter);
      }

      DbDataReader reader = cmd.ExecuteReader();

      if (!reader.HasRows) 
      {
          reader.Close();
            yield break;
      }

      while (reader.Read()) 
      {
          yield return binder(reader);
      }

      reader.Close();
}

It’s almost that simple.

Complications

Suppose that instead of using our fairly simple motivating example, we were to do something more complex, such as the following LINQ query:

var x = from c in customers
    select new
    {
          Name = c.ContactName, 
          Orders = from o in orders
                   where o.CustomerID == c.CustomerID
                   select o
    };

This LINQ query would generate the following method calls:

.Select(c => new <>f__AnonymousType0`2(Name = c.ContactName, 
        Orders = c.orders.Where(o => (o.CustomerID = c.CustomerID))))

This would then be translated to the following SQL statement:

SELECT t0.ContactName, CustomerID
FROM dbo.Customers AS t0

That doesn’t look right, does it? We’re only retrieving the ContactName and CustomerID. What happened to the customer’s orders? I will attempt to explain, but you may want to consult the LINQ specification, and particularly the section on deferred execution, for comprehensive details.

A Tale of Two Queries

Suppose you used x from above in a loop, like so:

foreach (var customer in x) {
  foreach (var order in customer.Orders) {
  ----------
  }
}

The outer loop will generate the following query:

SELECT t0.ContactName, CustomerID
FROM dbo.Customers AS t0

And, every execution of the inner loop will generate a query that looks like so:

SELECT t0.CustomerID, t0.EmployeeID, t0.Freight, 
    t0.OrderDate, t0.OrderID, t0.RequiredDate, t0.ShipAddress, 
    t0.ShipCity, t0.ShipCountry, t0.ShipName, t0.ShippedDate, 
    t0.ShipPostalCode, t0.ShipRegion, t0.ShipVia
FROM dbo.Orders AS t0
WHERE (t0.CustomerID = @p0)

Why? The execution of the outer loop will generate a binder Lambda expression like so:

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

As you can see, the value of Orders is not a constant. It will be obtained from a method call that will be invoked “on demand”, hence the deferred execution. That’s something to chew on, so I’ll leave it there for now.

Note

For the next article, I will upload the sources for the complete LINQ to SQL IQueryable provider and provide examples for its use. Explanations of implementation details will resume after that.

Executor Source

private class Executor<T> : ExpressionVisitor, IEnumerable<T>
{
    private readonly DbConnection connection = null;
    private readonly SqlExpressionParser sqlExpressionParser = null;
    private readonly Func<DbDataReader, T> binder = null;
    private readonly List<object> parameters = new List<object>();

    public Executor(DbConnection connection, 
                    SqlExpressionParser sqlExpressionParser,
                    Delegate binder) {

        this.Visit(sqlExpressionParser.expression);
        this.connection = connection;
        this.sqlExpressionParser = sqlExpressionParser;
        this.binder = (Func<DbDataReader, T>)binder;
    }

    public IEnumerator<T> GetEnumerator() {

        DbCommand cmd = connection.CreateCommand();
        cmd.CommandText = sqlExpressionParser.GetSQLStatement();

        for (int i = 0; i < parameters.Count; i++) {
        
            var parameter = cmd.CreateParameter();
            parameter.ParameterName = "@p" + i;
            parameter.Value = parameters[i];
            cmd.Parameters.Add(parameter);
        }

        DbDataReader reader = cmd.ExecuteReader();

        if (!reader.HasRows) {
            reader.Close();
            yield break;
        }

        while (reader.Read()) {
            yield return binder(reader);
        }

        reader.Close();
    }

    System.Collections.IEnumerator System.Collections.IEnumerable.GetEnumerator()
    {
        return this.GetEnumerator(); // probably wrong
    }

    protected override Expression VisitConstant(ConstantExpression c) 
    {

        if (c.Value == null) {
            parameters.Add("NULL");
        }
        else {
            switch (Type.GetTypeCode(c.Value.GetType())) {
                case TypeCode.Boolean:
                    parameters.Add(((bool)c.Value) ? 1 : 0);
                    break;
                case TypeCode.String:
                    parameters.Add(c.Value);
                    break;
                case TypeCode.Object:
                    break;
                default:
                    parameters.Add(c.Value.ToString());
                    break;
            }
        }

        return c;
    }

    protected override Expression VisitConditional(ConditionalExpression c) 
    {

        Debug.Assert(c.Test as ConstantExpression != null);

        if ((bool)(c.Test as ConstantExpression).Value == true) 
        {
            return this.Visit(c.IfTrue);
        }

        return this.Visit(c.IfFalse);
    }
}