How and when are LINQ queries translated and evaluated?

Question

I have a LINQ to SQL query as shown below:

var carIds = from car in _db.Cars where car.Color == 'Blue' select car.Id; 

The above will be translated below in sql:

 select Id from Cars where Color = 'Blue' 

I read that the steps are:

• LINQ to Lambda Expression
• Label expression for expression trees
• Expression Trees for SQL Statements
• SQL statements are executed

So my questions are when and how can this be translated and implemented?

I know that phase 4 occurs at runtime when my carIds variable gets access in a foreach loop.

 foreach(var carId in carIds) // here? { Console.Writeline(carId) // or here? } 

What about the other steps? when do they occur? at compile time or runtime? on which line (by definition or after definition, upon access or before its receipt)?

Answer 1

What you're talking about is deferred execution - essentially, the linq to SQL query will be executed while trying to count the results (e.g. iterate around it, .ToArray () it, etc.).

In your example, the statement is executed on the following line:

 foreach(var carId in carIds) 

See the MSDN article in LINQ and Delayed Execution.

Answer 2

• performed either by you, or by the developer, or by the C # compiler; you can write a lambda expression manually or using LINQ syntax, which they can imply from LINQ (i.e. where x.Foo == 12 treated as if it were .Where(x => x.Foo == 12) , and then the compiler processes step 2 based on this)
• performed by the C # compiler; it translates the lambda expression into IL, which builds (or reuses where possible) the expression tree. You can also argue that runtime is what handles this step.
• and
• usually executed when the request is enumerated , especially when foreach calls GetEnumerator() (and maybe even delayed to the first .MoveNext )

deferred execution allows you to create queries; eg:

 var query = ...something complex... var items = query.Take(20).ToList(); 

here Take(20) is executed as part of the general request, so you do not return everything from the server, and then (at the caller) filter it to the first 20; only 20 rows are always retrieved from the server.

You can also use compiled queries to restrict the query to only step 4 (or steps 3 and 4 in some cases where the query requires a different TSQL depending on the parameters). Or you can skip all but 4 if you just start writing TSQL.

You should also add step "5": materialization; This is a rather complicated and important step. In my experience, the materialization step may actually be the most significant in terms of overall performance (which is why we wrote "dapper").