Query Execution
After a LINQ query is created by a user, it is converted to a command tree. A command tree is a representation of a query that is compatible with the Entity Framework. The command tree is then executed against the data source. At query execution time, all query expressions (that is, all components of the query) are evaluated, including those expressions that are used in result materialization.
At what point query expressions are executed can vary. LINQ queries are always executed when the query variable is iterated over, not when the query variable is created. This is called deferred execution. You can also force a query to execute immediately, which is useful for caching query results. This is described later in this topic.
When a LINQ to Entities query is executed, some expressions in the query might be executed on the server and some parts might be executed locally on the client. Client-side evaluation of an expression takes place before the query is executed on the server. If an expression is evaluated on the client, the result of that evaluation is substituted for the expression in the query, and the query is then executed on the server. Because queries are executed on the data source, the data source configuration overrides the behavior specified in the client. For example, null value handling and numerical precision depend on the server settings. Any exceptions thrown during query execution on the server are passed directly up to the client.
Deferred Execution
The query variable itself only stores the query commands when the query is designed to return a sequence of values. If the query does not contain a method that will cause immediate execution, the actual execution of the query is deferred until you iterate over the query variable in a foreach or For Each loop. The query is executed against the server every time you iterate over the query variable in a foreach or For Each loop. This is called deferred execution. Deferred execution allows multiple queries to be combined or a query to be extended. When this occurs, the query is modified to include the new operations and the eventual execution will reflect the changes. The query variable itself never holds the query results. This means that you can execute a query as often as you want. For example, you might have a database that is being updated continually by a separate application. In your application, you could create one query that retrieves the latest data, and you could execute it repeatedly at some interval, retrieving different results each time.
LINQ to Entities queries are converted to command trees in the Entity Framework and executed against the data source when the results are iterated. At this point, conversion failures will cause exceptions to be thrown to the client.
Immediate Execution
In contrast to the deferred execution of queries that produce a sequence of values, queries that return a singleton value are executed immediately. Some examples of singleton queries are Average, Count, First, and Max. These execute immediately because the query must produce a sequence to calculate the singleton result. You can also force immediate execution. This is useful when you want to cache the results of a query. To force immediate execution of a query that does not produce a singleton value, you can call the ToList method, the ToDictionary method, or the ToArray method on a query or query variable. The following example uses the ToArray method to immediately evaluate a sequence into an array.
Using AWEntities As New AdventureWorksEntities
Dim products As ObjectQuery(Of Product) = AWEntities.Product
Dim prodArray As Product() = ( _
From product In products _
Order By product.ListPrice Descending _
Select product).ToArray()
Console.WriteLine("The list price from highest to lowest:")
For Each prod As Product In prodArray
Console.WriteLine(prod.ListPrice)
Next
End Using
using (AdventureWorksEntities AWEntities = new AdventureWorksEntities())
{
ObjectQuery<Product> products = AWEntities.Product;
Product[] prodArray = (
from product in products
orderby product.ListPrice descending
select product).ToArray();
Console.WriteLine("Every price from highest to lowest:");
foreach (Product product in prodArray)
{
Console.WriteLine(product.ListPrice);
}
}
You could also force execution by putting the foreach or For Each loop immediately after the query expression, but by calling ToList or ToArray you cache all the data in a single collection object.
Store Execution
In general, expressions in LINQ to Entities are evaluated on the server, and the behavior of the expression should not be expected to follow common language runtime (CLR) semantics, but those of the data source. There are exceptions to this, however, such as when the expression is executed on the client. This could cause unexpected results, for example when the server and client are in different time zones.
Some expressions in the query might be executed on the client. In general, most query execution is expected to occur on the server. Aside from methods executed against query elements mapped to the data source, there are often expressions in the query that can be executed locally. Local execution of a query expression yields a value that can be used in the query execution or result construction.
Certain operations are always executed on the client, such as binding of values, sub expressions, sub queries from closures, and materialization of objects into query results. The net effect of this is that these elements (for example, parameter values) cannot be updated during the execution. Anonymous types can be constructed inline on the data source, but should not be assumed to do so. Inline groupings can be constructed in the data source, as well, but this should not be assumed in every instance. In general, it is best not to make any assumptions about what is constructed on the server.
This section describes the scenarios in which code is executed locally on the client. For more information about which types of expressions are executed locally, see Expressions in LINQ to Entities Queries.
Literals and Parameters
Local variables, such as the orderID variable in the following example, are evaluated on the client.
Dim sales As ObjectQuery(Of SalesOrderHeader) = AWEntities.SalesOrderHeader
Dim orderID As Integer = 51987
Dim salesInfo = _
From s In sales _
Where s.SalesOrderID = orderID _
Select s
ObjectQuery<SalesOrderHeader> sales = AWEntities.SalesOrderHeader;
int orderID = 51987;
IQueryable<SalesOrderHeader> salesInfo =
from s in sales
where s.SalesOrderID == orderID
select s;
Method parameters are also evaluated on the client. The orderID parameter passed into the MethodParameterExample method, below, is an example.
Function MethodParameterExample(ByVal orderID As Integer)
Using AWEntities As New AdventureWorksEntities()
Dim sales As ObjectQuery(Of SalesOrderHeader) = AWEntities.SalesOrderHeader
Dim salesInfo = _
From s In sales _
Where s.SalesOrderID = orderID _
Select s
Console.WriteLine("Sales order info:")
For Each sale As SalesOrderHeader In salesInfo
Console.WriteLine("OrderID: {0}, Total due: {1}", sale.SalesOrderID, sale.TotalDue)
Next
End Using
End Function
public static void MethodParameterExample(int orderID)
{
using (AdventureWorksEntities AWEntities = new AdventureWorksEntities())
{
ObjectQuery<SalesOrderHeader> sales = AWEntities.SalesOrderHeader;
IQueryable<SalesOrderHeader> salesInfo =
from s in sales
where s.SalesOrderID == orderID
select s;
foreach (SalesOrderHeader sale in salesInfo)
{
Console.WriteLine("OrderID: {0}, Total due: {1}", sale.SalesOrderID, sale.TotalDue);
}
}
}
Casting Literals on the Client
Casting from null to a CLR type is executed on the client:
Dim contacts As ObjectQuery(Of Contact) = AWEntities.Contact
Dim query = _
From c In contacts _
Where c.EmailAddress = CType(Nothing, String) _
Select c
ObjectQuery<Contact> contacts = AWEntities.Contact;
IQueryable<Contact> query =
from c in contacts
where c.EmailAddress == (string)null
select c;
Casting to a type, such as a nullable Decimal, is executed on the client:
Dim products As ObjectQuery(Of Product) = AWEntities.Product
Dim query = _
From product In products _
Where product.Weight = CType(23.77, Decimal?) _
Select product
ObjectQuery<Product> products = AWEntities.Product;
IQueryable<Product> query =
from product in products
where product.Weight == (decimal?)23.77
select product;
Constructors for Literals
New CLR types that can be mapped to EDM types are executed on the client:
Dim products As ObjectQuery(Of Product) = AWEntities.Product
Dim query = _
From product In products _
Where product.Weight = New Decimal(23.77) _
Select product
ObjectQuery<Product> products = AWEntities.Product;
IQueryable<Product> query =
from product in products
where product.Weight == new decimal(23.77)
select product;
New arrays are also executed on the client.
Store Exceptions
Any store errors that are encountered during query execution are passed up to the client, and are not mapped or handled.
Store Configuration
When the query executes on the store, the store configuration overrides all client behaviors, and store semantics are expressed for all operations and expressions. This can result in a difference in behavior between CLR and store execution in areas such as null comparisons, GUID ordering, precision and accuracy of operations involving non-precise data types (such as floating point types or DateTime), and string operations. It is important to keep this in mind when examining query results.
For example, the following are some differences in behavior between the CLR and SQL Server:
SQL Server orders GUIDs differently than the CLR.
There can also be differences in result precision when dealing with the Decimal type on SQL Server. This is due to the fixed precision requirements of the SQL Server decimal type. For example, the average of Decimal values 0.0, 0.0, and 1.0 is 0.3333333333333333333333333333 in memory on the client, but 0.333333 in the store (based on the default precision for SQL Server’s decimal type).
Some string comparison operations are also handled differently in SQL Server than in the CLR. String comparison behavior depends on the collation settings on the server.
Function or method calls, when included in a LINQ to Entities query, are mapped to canonical functions in the Entity Framework, which are then translated to Transact-SQL and executed on the SQL Server database. There are cases when the behavior these mapped functions exhibit might differ from the implementation in the base class libraries. For example, calling the Contains, StartsWith, and EndsWith methods with an empty string as a parameter will return true when executed in the CLR, but will return false when executed in SQL Server. The EndsWith method can also return different results because SQL Server considers two strings to be equal if they only differ in trailing white space, whereas the CLR considers them to be not equal. This is illustrated by the following example:
Using AWEntities As New AdventureWorksEntities()
Dim products As ObjectQuery(Of Product) = AWEntities.Product
Dim query = _
From p In products _
Where p.Name = "Reflector" _
Select p.Name
Dim q = _
query.Select(Function(c) c.EndsWith("Reflector "))
Console.WriteLine("LINQ to Entities returns: " & q.First())
Console.WriteLine("CLR returns: " & "Reflector".EndsWith("Reflector "))
End Using
using (AdventureWorksEntities AWEntities = new AdventureWorksEntities())
{
ObjectQuery<Product> products = AWEntities.Product;
IQueryable<string> query = from p in products
where p.Name == "Reflector"
select p.Name;
IEnumerable<bool> q = query.Select(c => c.EndsWith("Reflector "));
Console.WriteLine("LINQ to Entities returns: " + q.First());
Console.WriteLine("CLR returns: " + "Reflector".EndsWith("Reflector "));
}