LINQ to SQL * compiled * queries and when they are executed

Question

I have the following compiled request.

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 runs directly in the database 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 request was not compiled, as in

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

then it executes in the database after execution

  var result = query.SingleOrDefault(); 

Is this the expected behavior?

Note. This is a duplicate. When is a compiled query being executed that returns IQueryable? but all the answers to them do not seem to be consistent with my conclusions. I wrote my answer there, but I do not know how to attract people's attention to him, since he is older than two years.

Answer 1

Interest Ask. Taking it to decompiled sources, when compiling the request, this 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 as follows:

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

When evaluating the expression that you defined, this value is false, so the else case is used.

The call is as follows:

 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 }); } 

ExecuteQuery is similar:

 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, SqlProvider.CompiledQuery is the class that implements ICompiledQuery. This class is being executed:

  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 will post the main points:

 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); } } 

Between acquiring and releasing a connection, it runs sql commands. Therefore, I would say that you are right. Contrary to popular belief, compiled requests do not behave the same as uncompiled requests when it comes to deferred execution.

I'm sure you can download the actual source code from MS, but I don’t have it at hand, and Resharper 6 has a terrific transition to a decompiled function, so I just used that.

Answer 2

I have nothing to add to Andrew Barrett's answer, except for this:

• This is true (i.e. a query database) when you call the delegate returned by CompiledQuery.Compile () for LINQ to SQL only.
• If you use LINQ to Entities, this is NOT true. Query does not get into the database when the delegate is called, it only does this when you start receiving data. Behavior compatible with uncompiled queries.

Answer 3

Yes, that's right. He will not go and receive nothing until you ask him.

Check MSDN for Delayed and Immediate Download . In particular, you can enable / disable lazy loading .

Look at the most accurate answer to this question, where the final list <T> is created. It has a select statement that sends it and requests the result. LINQ will wait as long as possible to send the request to the database.

By the way, you can easily learn this if you set the DataContext.Log property:

 db.Log = Console.Out; 

Then you can see the SQL statements in the console. Through your program, you can see exactly when the SQL statement gets into the database.