Databases Reference
In-Depth Information
In this example, the query returns
Customer
objects dynamically, based on the values of
two variables. The values are hard-coded here for simplicity, but in a real application they
would be entered by a user.
LINQ allows any
IQueryable
to appear in the
from
part of another query, and this
code takes advantage of this to augment the original query with additional operators.
Notice that the original query simply returns all customers. If the city parameter is speci-
fied, the code adds a
where
clause to filter customers based on the specified value and
replaces the original query. You can also sort the query results based on the value of the
sortByContactName
variable—by
ContactName
if it is
true
or by
CompanyName
if it is
false
.
Because of the delayed execution, the final query is sent to the database only once, when
the
foreach
statement enumerates it after all of the query modifications have been made.
Composing LINQ queries in this fashion allows you to create the most specific query
required for your scenario and helps to ensure its correctness, thanks to the thorough
validation performed by the C# compiler.
NOTE
It is also possible to create LINQ queries dynamically, from strings that contain prop-
erty names and operators. Chapter 5, “Filter Templates,” provides a detailed discus-
sion on this topic.
SQL Translation
The Entity Framework translates LINQ queries into actual SQL statements based on the
information in the data model and sends them for execution to the database server.
Because LINQ query syntax is significantly different from SQL syntax, you might find it
helpful to see the actual SQL statements your Entity Framework application executes.
The easiest and least obtrusive way to do this with Microsoft SQL Server is by using the
SQL Server Profiler, which is as part of the Management Tools—Complete feature you can
select during SQL client installation. Alternatively, you can change your application code
to do it as well.
var query = from c in context.Customers
where c.City == “London”
orderby c.CompanyName
select c;
ObjectQuery objectQuery = (ObjectQuery)query;
Console.WriteLine(
objectQuery.ToTraceString()
);
The
IQueryable
objects produced by the C# compiler when compiling LINQ to Entities
queries are actually of the type
ObjectQuery
. This class is provided by the Entity
Framework and offers a
ToTraceString
method that can be used to get the actual SQL
statement to which the LINQ query will be translated. The LINQ query in the example
just shown is translated into the SQL statement shown next. Although slightly more
