c# - LINQ to SQL *compiled* queries and when they execute

Question

I have the following compiled query.

private static Func<Db, int, IQueryable<Item>> func =
        CompiledQuery.Compile((Db db, int id) => 
            from i in db.Items
            where i.ID == id
            select i
            );

This executes on the database immediately when I do

var db = new Db()
var query = func(db, 5);  // Query hits the database here

As in before doing

var result = query.SingleOrDefault(); // Happens in memory

But if this query wasn't compiled, as in

var query = from i in db.Items
            where i.ID == id
            select i

then it executes on the database after doing

   var result = query.SingleOrDefault();

Is this the expected behaviour?

Note: This is a duplicate of When does a compiled query that returns an IQueryable execute?, but all the answers on there seem to disagree with my findings. I have posted my answer there, but I don't know how to get peoples' attention to it as it's over 2 years old.

Answer 1

Interesting question. Taking it to decompiled sources, when you compile a query, this is what happens:

public static Func<TArg0, TArg1, TResult> Compile<TArg0, TArg1, TResult>(Expression<Func<TArg0, TArg1, TResult>> query) where TArg0 : DataContext
{
  if (query == null)
    System.Data.Linq.Error.ArgumentNull("query");
  if (CompiledQuery.UseExpressionCompile((LambdaExpression) query))
    return query.Compile();
  else
    return new Func<TArg0, TArg1, TResult>(new CompiledQuery((LambdaExpression) query).Invoke<TArg0, TArg1, TResult>);
}

The UseExpressionCompile method is defined like this:

private static bool UseExpressionCompile(LambdaExpression query)
{
  return typeof (ITable).IsAssignableFrom(query.Body.Type);
}

This evaluates to false for the expression you've defined, so the else case is used.

The Invoke is like this:

private TResult Invoke<TArg0, TArg1, TResult>(TArg0 arg0, TArg1 arg1) where TArg0 : DataContext
{
  return (TResult) this.ExecuteQuery((DataContext) arg0, new object[2]
  {
    (object) arg0,
    (object) arg1
  });
}

The ExecuteQuery is like:

private object ExecuteQuery(DataContext context, object[] args)
{
  if (context == null)
    throw System.Data.Linq.Error.ArgumentNull("context");
  if (this.compiled == null)
  {
    lock (this)
    {
      if (this.compiled == null)
        this.compiled = context.Provider.Compile((Expression) this.query);
    }
  }
  return this.compiled.Execute(context.Provider, args).ReturnValue;
}

In this case our provider is the SqlProvider class, the SqlProvider.CompiledQuery is the class that implements ICompiledQuery. Execute on that class is implemented:

  public IExecuteResult Execute(IProvider provider, object[] arguments)
  {
    if (provider == null)
      throw System.Data.Linq.SqlClient.Error.ArgumentNull("provider");
    SqlProvider sqlProvider = provider as SqlProvider;
    if (sqlProvider == null)
      throw System.Data.Linq.SqlClient.Error.ArgumentTypeMismatch((object) "provider");
    if (!SqlProvider.CompiledQuery.AreEquivalentShapes(this.originalShape, sqlProvider.services.Context.LoadOptions))
      throw System.Data.Linq.SqlClient.Error.CompiledQueryAgainstMultipleShapesNotSupported();
    else
      return sqlProvider.ExecuteAll(this.query, this.queryInfos, this.factory, arguments, this.subQueries);
  }

SqlProvider.ExecuteAll calls SqlProvider.Execute, which is a pretty big method, so I'll post the highlights:

private IExecuteResult Execute(Expression query, SqlProvider.QueryInfo queryInfo, IObjectReaderFactory factory, object[] parentArgs, object[] userArgs, ICompiledSubQuery[] subQueries, object lastResult)
{
  this.InitializeProviderMode();
  DbConnection dbConnection = this.conManager.UseConnection((IConnectionUser) this);
  try
  {
    DbCommand command = dbConnection.CreateCommand();
    command.CommandText = queryInfo.CommandText;
    command.Transaction = this.conManager.Transaction;
    command.CommandTimeout = this.commandTimeout;
    this.AssignParameters(command, queryInfo.Parameters, userArgs, lastResult);
    this.LogCommand(this.log, command);
    ++this.queryCount;
    switch (queryInfo.ResultShape)
    {
      case SqlProvider.ResultShape.Singleton:
        DbDataReader reader1 = command.ExecuteReader();
...
      case SqlProvider.ResultShape.Sequence:
        DbDataReader reader2 = command.ExecuteReader();
...
      default:
        return (IExecuteResult) new SqlProvider.ExecuteResult(command, queryInfo.Parameters, (IObjectReaderSession) null, (object) command.ExecuteNonQuery(), true);
    }
  }
  finally
  {
    this.conManager.ReleaseConnection((IConnectionUser) this);
  }
}

In between acquiring and releasing the connection it exceutes sql commands. So I'd say you're right. Contrary to popular belief, compiled queries don't behave the same as uncompiled queries when it comes to deferred execution.

I'm pretty sure you can download the actual source code from MS, but I don't have it handy and Resharper 6 has an awesome go to decompiled function, so I just used that.