In Chapter 3, you wrote the same basic query over and over and over again. Hopefully, you’ll agree that this was a great way to get exposure to the many different ways of writing queries against the Entity Framework.
There is a lot more to querying the Entity Data Model, however. You’ll need to learn about the flexibility you have for expressing complex queries, projecting data, composing and nesting queries, and writing parameterized queries. There are also nuances regarding what type of data is returned based on how you construct your queries. Sometimes you will get objects, as we saw in the examples in Chapter 3, but other times you will get unknown objects (anonymous types). It is also possible for Object Services queries to return rows and columns. You’ll need to know when to expect these varied forms of data to be returned.
Covering all of this exhaustively would require thousands of pages. Therefore, the goal of this chapter is to expose you to the critical features and some of the possibilities. You will learn how to project specific values (rather than entire objects) in queries, how to query across relationships, how to write nested queries and joins, and how to control when trips are made to the database. Along the way, I will introduce and explain additional new concepts to ensure that you truly understand the workings of the Entity Framework.
Throughout the rest of the book, you will see variations on queries that take advantage of even more interesting techniques.
A number of resources provide many specific examples of queries. Here you will learn some of the more common query tasks so that you will know enough to write queries without constantly having to search for a perfect example of what you are trying to accomplish. Don’t expect that you will never have to rely on the “query by example” technique, though! We all do at one time or another. It is also useful to check out resources such as the 101 LINQ Examples on MSDN (for VB and for C#) and the Entity Framework Samples, which provide a great variety of query examples with helpful commentary.
In this chapter, we’ll continue to use the same model, Model1, which you worked with in the previous
chapters. Writing Entity SQL queries with that model’s long EntityContainer name,
ProgrammingEFDB1Entities, is more
than a bit cumbersome. This was the default name that the ADO.NET Entity
Data Model Wizard gave to the entity container, which it based on the
database name.
Since you will be writing more Entity SQL expressions in this
chapter, you may want to modify the EntityContainer name so that you have
something simpler to type. The Designer makes this easy to do.
Open the model in the Designer and click the background of the
model. This will cause the model’s properties to show up in the
Properties window. Change the EntityContainerName to PEF.
This change will have an impact in three places in your application:
PEFwill be the newEntityContainername for the model.PEFwill be the new name of theEntityContainerclass that you will instantiate to perform queries.PEFwill be the new name of the connection string in the app.config file.
You should double-check that the change was made in all three locations.
Note
You will also need to change any existing code references that use this model from the longer name to the new short name. If you are using Find & Replace, you will be better off replacing instances in “Current Document”.
This new name is not a recommended naming convention, but a convenience for the sake of writing numerous Entity SQL queries in this chapter.
So far, the queries you have seen return an entire object,
comparable to writing SELECT * in a
SQL query. By returning an entire object in your query, you will get all
of the benefits associated with the entity classes—most importantly, the
ability to keep track of changes to an entity class for database
updates.
However, you may be getting much more data than you want to work with. Not only might this be a drag when you are programming, but it also means that you are forcing the database to do more work than necessary, causing more data to be transferred than necessary, and asking Object Services to materialize objects when that may not be necessary. If you are concerned about the performance of your applications, all of this extra effort will make your application processing inefficient.
Often in SQL, you will select particular columns to return from a
table (SELECT Firstname, Lastname FROM
Contact) or from a set of joined tables. This is referred to
as projection. With LINQ or Entity SQL queries you
can shape your results by picking particular properties or expressions
rather than entities. You can also select properties from related
data.
In the previous queries, you returned an entire object but used
only the Title, FirstName, and LastName properties. You can rewrite those
queries to return only these three properties.
In the QueryContacts method,
modify the query by replacing Select
con with the projected properties, as shown in Example 4-1.
Example 4-1. Simple LINQ to Entities query
VB
Dim contacts = From c In context.Contacts _
Where c.FirstName= "Robert" _
Select New With {c.Title, c.LastName, c.FirstName}C#
var contacts = from c in context.Contacts
where c.LastName.StartsWith("S")
select new { c.Title, c.FirstName, c.LastName };You may have noticed the syntax differences between VB and C# projections. This is not particular to LINQ to Entities, but it is common for all implementations of LINQ.
C# requires that you use select new
{…} when projecting. Visual Basic is more lenient. The
most explicit syntax for VB is Select New
With {…} as in Example 4-1, though you could write
the query in this simpler format:
From c In context.Contacts _
Where c.FirstName= "Robert" _
Select c.Title, c.LastName, c.FirstNameNote
There are plenty of other nuances to LINQ projections in both languages. For example, you can project into predefined types. In addition, C# projections always create immutable (read-only) results, whereas VB allows the creation of immutable and mutable results. You can learn more about projecting with LINQ in the MSDN Library and from the many great resources that focus on LINQ.
A number of new language features have made it easier for developers to implement LINQ projections. We’ll examine several of these in this section, including anonymous types and implicitly typed local variables.
If you hover your mouse pointer over the contacts variable, the DataTip will show you
what the query returns. It’s an IQueryable of an “anonymous type,” rather
than an IQueryable of contact
types. The DataTips and debuggers in Visual Basic and C# often show
different information. In this case, the difference is interesting, as
you can see in Figures 4-1 and 4-2.
What is this anonymous type that the LINQ to Entities projection is returning?
The anonymous type is a new language enhancement introduced in Visual Basic 9 and C# 3.0 that allows the compilers to work with types that were not previously defined. Anonymous types are generally used for on-the-fly types that won’t be used elsewhere in the application. They relieve you from having to define a class for every type, even if the type is to be used only briefly. Yet the returned anonymous type is still strongly typed, which means you can easily interact with it in the code following its creation.
The sidebar Wrapping Your Head Around Lambdas includes a link to an article by Anders Hejlsberg. The article also contains a great introduction to anonymous types. Anonymous types are a powerful new feature that you can use throughout .NET, but they have special importance for LINQ queries because of their ability to allow projections that can return anything a developer can dream up.
So, this query returned an anonymous type that doesn’t have a
name, but has the properties Title, FirstName, and LastName. If you are still modifying the
earlier query method, you can see a bit of this magic by removing
the Console.WriteLine method and
retyping it. The anonymous type is strongly typed and recognized by
IntelliSense. Pretty cool!
Another new compiler trick that you have been taking advantage
of in the code samples so far is the use of implicitly typed local
variables. In C# you use them with a new keyword, var, and in VB you use them with the
existing Dim keyword. It is
possible to declare variables without identifying their types. They
will infer the type based on the value that is being set.
Hasn’t it always seemed redundant to say something like
Dim str as String="this is some
text" or int MyInt=123?
With implicitly typed local variables, Dim
str="this is some text" and var
MyInt=123 are enough. In the case of replacing int with var the benefit is not very obvious. Had
that type been MyCustomType<Myothercustomtype<T>>,
suddenly var looks pretty
convenient.
Note
This shortcut is not always a good thing, as it removes some of the explicitness of your code. I wrote a blog post on DevSource.com titled “How Visual Studio 2008 made me even lazier” (http://blogs.devsource.com/devlife/content/net_general/how_visual_studio_2008_made_me_even_lazier.html/). There is an interesting discussion in the comments about the pros and cons of implicit typing.
Where implicitly typed local variables really shine, however,
is with LINQ query projections, because there’s no way to say “Dim
contacts as a thing with a Title, a FirstName, and a LastName.”
Instead, you can write “Dim contacts (and just look at the other
side of the equals sign to figure out what this is).” In this
context, Dim in VB and var in C# essentially translate to
“thing.”
Run the application and you’ll see that, once again, the results are the same as they were previously.
You can project into anonymous types in a number of ways. For
instance, it is possible to give a name to an anonymous type, such
as ContactName in Example 4-2.
Example 4-2. Naming a projected anonymous type in LINQ
VB
From c In context.Contacts _
Where c.FirstName = "Robert" _
Select ContactName = New With {c.Title, c.LastName, c.FirstName}C#
from c in context.Contacts
where c.FirstName == "Robert"
let ContactName = new {c.Title, c.LastName, c.FirstName}
select ContactNameC# does not allow naming in the SELECT statement; it has another operator,
LET, for this purpose.
Note
There are so many ways to do projection and use anonymous types in LINQ queries. Here you are seeing just a small slice of what you can achieve, so be sure to look to the dedicated LINQ resources to expand your understanding.
Naming the anonymous type is more useful if this new type is a property of the projected results. In Example 4-3, a projection is used to project much more than some strings. It creates a new type with another anonymous type as the first property and the addresses of the contact as the second.
Note
I’m projecting the Addresses property here to highlight the
projection. You’ll learn more about working with related data
later in this chapter.
When you name the anonymous type, the property that results
will have the name specified in the query. Notice that the property
name is used later in the query for the Order By operator and when working with
the results.
Example 4-3. Anonymous types as properties
VB
Dim contacts = From c In context.Contacts _
Where c.FirstName = "Robert" _
Select _
ContactName = New With { _
c.Title, c.LastName, c.FirstName}, _
c.Addresses _
Order By ContactName.LastName
For Each contact In contacts
Dim name = contact.ContactName
Console.WriteLine("{0} {1} {2}", _
name.Title.Trim, name.FirstName.Trim, _
name.LastName.Trim)
NextC#
var contacts =
from c in context.Contacts
where c.FirstName == "Robert"
let foo= new {
ContactName = new {c.Title, c.LastName, c.FirstName},
c.Addresses
}
orderby foo.ContactName.LastName
select foo;
foreach (var contact in contacts)
{
var name = contact.ContactName;
Console.WriteLine("{0} {1} {2}",
name.Title.Trim(), name.FirstName.Trim(),
name.LastName.Trim());
}To project using LINQ’s method-based query syntax, you would use
the Select method and then identify
the properties you want in its parameter. The method-based query
syntax requires the explicit syntax for creating an anonymous type in
the predicate. In VB, that definitely starts to get a bit clunky. (See
Example 4-4.)
Example 4-4. Projecting using LINQ’s method-based syntax
VB
context.Contacts _
.Where(Function(c) c.FirstName = "Robert") _
.Select(Function(c) New With {c.Title, c.LastName, c.FirstName})C#
context.Contacts
.Where((c) => c.FirstName == "Robert")
.Select((c) => new {c.Title, c.LastName, c.FirstName})You can use projections with Entity SQL queries in both Object
Services and EntityClient queries. Only LINQ queries
can return anonymous types. Object Services has its own way of resolving
projections when the resultant type doesn’t exist. Because EntityClient does not attempt to materialize
objects from the results, this is not a concern with EntityClient queries.
When projecting with Object Services, the query will return data
records. These are the same System.Data.Common.DbDataRecords returned by
EntityClient queries.
First let’s look at the code in Example 4-5 and then at the query results.
Here is where you will begin to benefit from having changed the name of
the EntityContainer to PEF.
Example 4-5. Projection with Entity SQL
VB
Dim queryStr = "SELECT c.FirstName, c.LastName, c.Title " & _
"FROM PEF.Contacts AS c " & _
"WHERE c.FirstName='Robert'"
Dim contacts = PEF.CreateQuery(Of DbDataRecord)(queryStr)C#
var queryStr = "SELECT c.FirstName,c.LastName, c.Title " +
"FROM PEF.Contacts AS c " +
"WHERE c.FirstName='Robert'";
var contacts = context.CreateQuery<DbDataRecord>(queryStr);Notice that in the Entity SQL string, the keyword VALUE is gone. That’s because the projection
is selecting multiple values. Also, note that the type being passed into
the CreateQuery method is now a
DbDataRecord.
In the introduction to EntityClient in Chapter 3, you learned that a DbDataRecord represents a record in a DbDataReader. Therefore, you will need to
interact with these results in the same way you did when using the
EntityClient example.
There is one very nice difference, however. The results are not
being streamed; they have been materialized into the DbDataRecord. Therefore, you can access the
column data that results in any order you want. That’s why the query
string selected FirstName, LastName, and then Title. When you build the code to display the
results, shown in Example 4-6, you’ll see that
it’s OK to use Title first.
Example 4-6. Enumerating through the DbDataRecord returned by a LINQ projection
VB
For Each c In contacts
Console.WriteLine("{0} {1} {2}", _
c.Item("Title").ToString.Trim, _
c.Item("FirstName").ToString.Trim, _
c.Item("LastName").ToString.Trim)
NextC#
foreach (var item in contacts)
{
Console.WriteLine("{0} {1} {2}",
item["Title"].ToString().Trim(),
item["FirstName"].ToString().Trim(),
item["LastName"].ToString().Trim());
}Note
In Example 4-6,
I used an alternative way of pulling data from a DbDataRecord. Item takes a string parameter (the column
name) or an integer (the column position), whereas the GetString, GetInt, and other related methods take only
an integer as a parameter.
Most of these examples project strings, though you saw one
example with LINQ for Entities where an address’s EntityCollection was projected. How would
you interact with a DbDataRecord
that contains an entity or another object in its columns? The Entity
SQL expression in Example 4-7 selects the entire
Contact entity as the first
property of the results and the contact’s addresses as the second
property.
Example 4-7. Projecting objects with Entity SQL
VB
Dim queryStr = "SELECT c,c.Addresses " & _
"FROM PEF.Contacts AS c " & _
"WHERE c.FirstName='Robert'"
Dim contacts = context.CreateQuery(Of DbDataRecord)(queryStr)
For Each c In contacts
Dim contact = CType(c.Item(0), Contact)
Console.WriteLine("{0} {1} {2}", _
contact.Title, _
contact.FirstName, _
contact.LastName)
NextC#
var queryStr = "SELECT c,c.Addresses " +
"FROM PEF.Contacts AS c " +
"WHERE c.FirstName='Robert'";
var contacts = context.CreateQuery<DbDataRecord>(queryStr);
foreach (var c in contacts)
{
var contact = (Contact)(c[0]);
Console.WriteLine("{0} {1} {2}",
contact.Title,
contact.FirstName,
contact.LastName);
}In Example 4-6,
you had to explicitly cast the items of the results to String types. In this case, because you know
the first item will contain a Contact type, you can cast the column to
Contact and then work directly with
that strongly typed object. You can do the same with the collection of
Address types in the second
column.
Example 4-8
shows an example of using a query builder method to do projection. In
the projection, you use the it
alias to access the properties.
Example 4-8. Using query builder methods to project data
VB
Dim contacts = context.Contacts _
.Where("it.FirstName='Robert'") _
.Select("it.Title, it.FirstName, it.LastName")C#
var contacts = context.Contacts
.Where("it.FirstName='Robert'")
.Select("it.Title, it.FirstName, it.LastName");Projection with query builder methods also returns DbDataRecords. You’ll need to access the
results through the data record’s items, as with the other
examples.
One of the big benefits that the EDM lends to querying is that the relationships are built into the model and you won’t have to construct joins very often to access related data. Additionally, when using LINQ for the queries, the related data is presented via IntelliSense, which makes it very discoverable.
Using the model, let’s take a look at some more queries, this time digging into the associations.
The model has only one association between Contact and Address. The association provides two
navigations—one from Contact to all
of its related addresses and one from Address to its related contact.
In many cases, you will not be returning a collection of a single
previously defined type. When the type doesn’t preexist, LINQ to
Entities will return an anonymous type. Because an ObjectQuery can’t return anonymous types, in
these cases it will return the DbDataRecord type that you learned about in
Chapter 3 in the EntityClient discussion.
You can easily do projection drilling into related entities,
although drilling into a collection is different from drilling into a
reference entity. For example, you can’t request Contact.Addresses.Street in a query. Contact to Addresses is a one-to-many relationship and
Addresses is a collection, not a
single entity. Street is not a
property of the Addresses
EntityCollection. However, you could select Address.Contact.LastName,
because you would be navigating to a single entity. There is only one
contact per address; therefore, there is no question regarding from
which entity the query should retrieve the LastName.
Recall that navigating to the “one” end of a one-to-one or
many-to-one relationship is referred to as a navigation reference. The
entity you are pointing to is referred to as an EntityReference or
EntityRef.
Note
Chapter 15
will drill further into EntityRefs and EntityCollections, and how they are
surfaced as navigation properties.
The LINQ query in Example 4-9 returns an anonymous type containing an address and its related contact.
Example 4-9. Projecting into an EntityRef with LINQ to Entities
VB
Dim newTypes = From a In context.Addresses _
Where a.CountryRegion = "UK" _
Select New With {a, a.Contact}C#
var newTypes = from a in context.Addresses
where a.CountryRegion == "UK"
select new { a, a.Contact };Figure 4-3 displays the complex type that results in the debugger, where you can see that one property is the address record and the other is the contact.
When working with the results, you’ll have to drill into the new
type’s properties (the Address and
the Contact) and from there you’ll
have to drill into their properties, as shown in Example 4-10.
Example 4-10. Accessing the properties of an anonymous type
VB
For Each newtype In newTypes
Console.WriteLine("{0} {1} {2}", _
newtype.Contact.LastName, _
newtype.a.Street1, newtype.a.City, )
NextC#
foreach (var newtype in newTypes)
{
Console.WriteLine("{0} {1} {2}",
newtype.Contact.LastName, newtype.a.Street1,
newtype.a.City);
}Note
The first property is named a because it is using the variable name
given in the query. If you want to be sure the property is called
Address you can use that instead
of the simpler a, or use a LINQ
feature to rename the property.
VB
Select New With {.Address = a, a.Contact}C#
C#: select new {Address= a, a.Contact };Then you can work with newtype.Address in the data that
results.
Example 4-11 demonstrates how to express this same query (from Example 4-9) using Entity SQL.
Example 4-11. Projecting into an EntityRef with Entity SQL
SELECT a,a.Contact FROM PEF.Addresses AS a WHERE a.CountryRegion='Canada'
When working with these results you can cast the data in the first position to an address and the data in the second position to a contact, as you did in Example 4-7.
Although this may suit many scenarios in your applications, you
may prefer to project individual properties to create a type that is
not complex. Example 4-12 shows such a
query using LINQ to Entities. This projection returns a new type with
three properties. The first is an Address entity; the second and third are
strings. Again, the property names are based on the query
defaults—a, FirstName, and LastName.
Example 4-12. Projecting into an EntityRef and returning unshaped data
VB
Dim newTypes = From a In context.Addresses _
Where a.CountryRegion = "UK" _
Select New With {a, a.Contact.FirstName, _
a.Contact.LastName}
For Each newtype In newTypes
Console.WriteLine("{0} {1} {2} {3}", _
newtype.a.Street1, newtype.a.City _
newtype.FirstName, newtype.LastName)
NextC#
var newTypes = from a in context.Addresses
where a.CountryRegion == "UK"
select new { a, a.Contact.FirstName,
a.Contact.LastName };
foreach (var newtype in newTypes)
{
Console.WriteLine("{0} {1} {2} {3}",
newtype.FirstName, newtype.LastName,
newtype.a.Street1, newtype.a.City);
}You can filter and sort based on a property of an EntityReference even if you are not
selecting the related data.
For example, you can select all addresses for contacts with a
particular last name. The LINQ to Entities query in Example 4-13 sorts by
Contact.LastName and filters on the
Contact.AddDate field even though
AddDate is not part of the
results.
Example 4-13. Filtering and sorting on reference properties
VB
From a In context.Addresses _
Where a.Contact.AddDate > New Date(2009, 1, 1) _
Order By a.Contact.LastName _
Select New With {a, a.Contact.FirstName, a.Contact.LastName}C#
from a in context.Addresses
where a.Contact.AddDate > new System.DateTime(2009, 1, 1)
orderby a.Contact.LastName
select new {a, a.Contact.FirstName, a.Contact.LastName};In Entity SQL, the same query would be represented as follows:
SELECT a,a.Contact.FirstName,a.Contact.LastName FROM PEF.Addresses AS a WHERE a.Contact.AddDate>DATETIME'2009-01-1 00:00' ORDER BY a.Contact.LastName
Querying with related data is straightforward when the related
data is a single entity, but what about when the navigation property
is an EntityCollection such as
Contact.Addresses?
Let’s start with a simple scenario that you have seen a few
times already in this chapter: returning a contact and its collection
of addresses. To highlight the difference between the original
properties and the results, the EntityCollection in the new type is given a
random name, as shown in Example 4-14.
Example 4-14. Projecting an EntityCollection with LINQ
VB
Dim contacts = From c In context.Contacts _
Select New With {c, .Foos = c.Addresses}C#
var contacts = from c in context.Contacts
select new {c, Foos = c.Addresses};This query creates a new anonymous type with two properties. The
first is the Contact and the second is the EntityCollection of Addresses related to that Contact.
You can then enumerate through the results, and then, for each
result, enumerate through the collection inside the Address property, as shown in Example 4-15.
Example 4-15. Enumerating over shaped data that includes an EntityCollection
VB
For Each contact In contacts
Console.WriteLine("{0}: Address Count {1} ", _
contact.c.LastName.Trim, contact.Foos.Count)
For Each address In contact.Foos
Console.WriteLine(" City= {0}", address.City)
Next
NextC#
foreach (var contact in contacts)
{
Console.WriteLine("{0}: Address Count {1} ",
contact.c.LastName.Trim(), contact.Foos.Count);
foreach (var foo in contact.Foos)
{
Console.WriteLine(" City= {0}", foo.City);
}
}You may not want the addresses to be represented as a separate property in the resulting data, but there are many ways to shape query results and right now the goal is to get a view of what these queries can achieve.
If you wanted to select particular properties such as Street and City from each Address of each Contact, the method you should use to do the
query depends on what shape you want the results to be.
You could shape the data similar to the previous example, but
instead of a set of complete address entities as the Foos property, you can project some of the
address properties. This would result in a set of anonymous types,
named StreetsCities instead of
Foos, in the second
property.
You can achieve this with a nested query, a feature we’ll look
at more closely later in the chapter. For now, you can see in the
query in Example 4-16
that the third property, StreetsCities, contains the results of
querying the Contact’s Addresses.
Example 4-16. Projecting values from an EntityCollection
VB
From c In context.Contacts _
Select New With {c.FirstName, c.LastName, _
.StreetsCities = From a In c.Addresses Select a.Street1, a.City
}C#
from c in context.Contacts
select new {c.FirstName, c.LastName,
StreetsCities = from a in c.Addresses select new { a.Street1, a.City }
}The anonymous type that is returned has the properties
FirstName and LastName, along with a collection of
anonymous types with Street and
City properties. The debugger
screenshot in Figure 4-4
displays the new type.
Another way to project into the addresses is to merely turn the query around. That is, query the addresses and their contact data to flatten the results, as shown in Example 4-17, so that the data is no longer shaped.
Example 4-17. Flattening the related data
VB
From a In context.Addresses _
order By a.Contact.LastName _
Select New With {a.Contact.FirstName, a.Contact.LastName, _
a.Street1, a.City}C#
var contacts =
from a in context.Addresses
orderby a.Contact.LastName
select new {a.Contact, a.Contact.LastName, a.Street1, a.City};This will result in a single type with four properties, but contacts with multiple addresses will appear multiple times, as you can see in this section of the results. For instance, Katherine Harding and Keith Harris each have two results:
Hanson, John: 825 W 500 S, Bountiful Harding, Katherine: 52560 Free Street, Toronto Harding, Katherine: 25 Flatiron Blvd, Vancouver Harrington, Lucy: 482505 Warm Springs Blvd., Fremont Harris, Keith: 3207 S Grady Way, Renton Harris, Keith: 7943 Walnut Ave, Renton Harui, Roger: 9927 N. Main St., Tooele Hass, Ann: Medford Outlet Center, Medford
Although you can easily use related data in projections or for
filtering, sorting, and other operations, it is important to keep in
mind that when the related data is in a collection, you need to
leverage operations that can be performed on a set of data. For
example, if you want to find contacts with addresses in the United
Kingdom (represented as UK in the database), you can use the ANY method in LINQ to Entities (see Example 4-18) or the EXISTS operator in Entity SQL to search the
contact’s addresses. The LINQ query uses a predicate to provide the
condition for ANY.
Example 4-18. Filter condition provided by an EntityCollection with LINQ
VB
From c In context.Contacts _
Where c.Addresses.Any(Function(a) a.CountryRegion = "UK") _
Select cC#
from c in context.Contacts
where c.Addresses.Any((a) => a.CountryRegion == "UK")
select c;Entity SQL’s EXISTS is not as
facile as the ANY method. You’ll
need to pass a subquery into EXISTS
so that it knows what to search. Look closely at the subquery in Example 4-19. It is querying
c.Addresses, which is the
collection of addresses that belongs to the value being returned in
the main query. The subquery is able to take advantage of the
navigation from a contact to its addresses.
Aggregates perform calculations on a series of data—count, sum, average, min, max, and so on. You may not want the entire collection of addresses, but rather some aggregated information about that collection.
It’s relatively simple to aggregate data with LINQ, using one
of the aggregate methods such as Count, which you can add as a method on a
collection. The Count method will
return the count of the items in the collection (see Example 4-20).
Example 4-20. Using the Count aggregate method in LINQ to Entities
VB
From c In context.Contacts _
Select New With {c.LastName, c.Addresses.Count}C#
from c in context.Contacts select new {c.LastName, c.Addresses.Count};Other types of aggregates, such as Max, require a specific value to
aggregate. You can supply that value using a lambda expression, as
shown in Example 4-21.
Example 4-21. Using an aggregate method with a lambda in LINQ
VB
From c In context.Contacts _
Select New With {c.LastName, _
.MaxPC = c.Addresses.Max(Function(a) a.PostalCode)}C#
from c in context.Contacts
select new { c.LastName, MaxPC = c.Addresses.Max((a) => a.PostalCode)};It’s important to name the property returned by the aggregate function because LINQ is unable to derive one based on the method. If you forget to do this, both VB and C# will give a compiler error explaining the problem.
Working with aggregates in Entity SQL is not as simple as it
is in LINQ to Entities. For example, LINQ is able to count the
elements in a collection and doesn’t care whether the collection
contains values or objects. But Entity SQL can perform aggregates on
only a collection of values, and even then on only certain types of
values. This actually mirrors how SQL Server uses aggregates.
Therefore, with Entity SQL you can’t write Count(c.Addresses), but rather you need to
pass a value, such as AddressID,
in to the Count function. To do
this, you can use a subquery against c.Addresses that returns a collection of
AddressIDs. You can then COUNT the results of that query, as shown
in Example 4-22.
Example 4-22. Using the Count aggregate function in Entity SQL
Select c, COUNT(Select VALUE a.AddressID FROM c.Addresses as a) FROM PEF.Contacts as c
The other aggregates work in the same way. Example 4-23 shows the
MAX query written with Entity
SQL.
Example 4-23. Using the MAX aggregate function in Entity SQL
SELECT c.LastName,
MAX(SELECT VALUE a.PostalCode FROM c.Addresses AS a)
FROM PEF.Contacts AS cYou can even use an aggregate in a subquery, as in Example 4-24.
Example 4-24. An aggregate in a subquery
SELECT c.LastName,
(SELECT VALUE MAX(a.PostalCode) FROM c.Addresses as a)
FROM PEF.Contacts AS cIn this example, the second column of the query results does
not contain the string value of the PostalCode. It contains the results of a
query, and therefore it is a collection of string values. If you
want to read the PostalCodes, you
can iterate through the collection using code similar to Example 4-15, or use a
SET operator.
Like aggregates, SET
operators work with a set of values. The ANYELEMENT operator is a SET operator that will randomly pick an
element from a collection. As shown in Example 4-25, you can even
use this with collections that contain only one element, such as the
MAX PostalCode column in Example 4-24.
Example 4-25. Using the ANYELEMENT operator against a set of data
SELECT c.LastName,
ANYELEMENT(SELECT VALUE MAX(a.PostalCode)
FROM c.Addresses AS a)
FROM PEF.Contacts AS cThe results of this query will now contain a string in the second position, not a collection.
The SET operators in Entity
SQL are ANYELEMENT, EXCEPT, FLATTEN, INTERSECT, EXISTS and NOT
EXISTS, IN and NOT IN, OVERLAPS, SET, and UNION. There is also an ELEMENT operator that has not yet been
implemented but is reserved. If you attempt to use it in the first
version of the Entity Framework, you will get an exception that
explains that ELEMENT cannot be
used yet.
The LINQ aggregates are methods, not query operators. Therefore,
they work very naturally with the LINQ query methods. Example 4-26 uses the
Max aggregate as one of two
projected values to be returned.
Example 4-26. A LINQ method syntax query using an aggregate
VB
context.Contacts _
.Select(Function(c) New With {c.LastName, _
.MaxCode = c.Addresses.Max(Function(a) a.PostalCode)})C#
context.Contacts
.Select((c) => new { c.LastName,
MaxCode = c.Addresses.Max((a) => a.PostalCode) });This query does two interesting things with the lambdas. First
it uses a lambda expression to specify what values should be
projected: LastName and MaxCode. Once the variable, c, has been declared, the function projects
an anonymous type consisting of LastName as the first property and MaxCode as the second. MaxCode is defined by using the Max aggregate on the Addresses collection of the contact.
The query builder methods do not provide aggregate methods.
However, you can use an Entity SQL query as the argument of the
SELECT query builder method to
perform the aggregate.
Remember that the collection being queried in the subquery is
based on the main query’s control variable, referred to with the
it alias by default.
Example 4-27 is the same query from Example 4-26, but expressed with query builder methods.
Although associations in the EDM minimize the need for joins in queries, sometimes a relationship may exist but there is no association to represent the relationship. In these and other cases, you can use nested queries or joins to bring the data together.
LINQ to Entities provides a JOIN operator as well as GROUPJOIN. Entity SQL provides a variety of
options in the JOIN FROM clause,
including inner joins, as well as left, right, and full outer joins. It
also enables joining multiple collections separated by commas.
The vOfficeAddresses entity
in the current model has all of the contact properties except for the
contact’s Title. Because there is
no association between vOfficeAddresses and Contact, you will need to use JOIN to combine the vOfficeAddresses entity properties with
the Title property.
Note
You could, of course, add the association to the model in this case, but then there would be no lesson here, would there?
Example 4-28 shows the syntax of a
LINQ JOIN.
Example 4-28. JOIN syntax for LINQ
FROM [variableA] IN collectionA JOIN [variableB] IN collection ON variableA.commonproperty EQUALS variableB.commonProperty SELECT .....
Example 4-29 shows
how to combine data from Contact
entities and vOfficeAddresses entities using the
JOIN.
Example 4-29. A LINQ to Entities query using a JOIN
VB
From con In context.Contacts _
Join add In context.vOfficeAddresses _
On con.ContactID Equals add.contactID _
Select New With {add.FirstName, add.LastName, _
con.Title, add.Street1, add.City, add.StateProvince}C#
from c in context.Contacts
join oa in context.vOfficeAddresses on c.ContactID equals oa.ContactID
select new { oa.FirstName, oa.LastName, c.Title, oa.Street1, oa.City,
oa.StateProvince };This provides an inner join where only entities with matching
ContactIDs are returned. Any
contacts with no match in the vOfficeAddresses will not be returned.
vOfficeAddresses with no match in
Contacts will not be returned
either.
Example 4-30 shows the syntax
of an Entity SQL JOIN.
Example 4-30. JOIN syntax for Entity SQL
SELECT variableA, variableB FROM collection as variableA JOIN Collection as variableB ON Property = Property
Note
Entity SQL has the ability to do cross joins. You can express
them explicitly; however, a JOIN
without an ON clause will
implicitly become a cross join, pairing every entity in the first
collection with every entity in the second collection. So, watch
out!
Example 4-31 demonstrates
the same JOIN query expressed in
Entity SQL.
Both LINQ and Entity SQL provide the ability to nest queries, and you have already seen some examples of this. When you write a query, anywhere a value is expected you can use another query in its place, as long as that query returns an acceptable type. You can use a nested query in place of an expression or a collection, as you will see in the following examples.
The goal of the previous JOIN
queries was to return properties from a Contact entity combined with properties from
the vOfficeAddresses entities where
the ContactID matches.
Example 4-32
shows how to express the previous query in LINQ using a nested query
instead of a JOIN. The query in
Example 4-32 uses a
nested query (which is highlighted) combined with the FirstOrDefault method in place of a
projected value to return results from vOfficeAddresses.
Example 4-32. Nested query in place of a SELECT expression in LINQ
VB From a In context.vOfficeAddresses _ Select New With {a.FirstName, a.LastName, _.Title = (From c In context.Contacts _Where c.ContactID = a.ContactID _Select c.Title).FirstOrDefault, _a.Street1, a.City, a.StateProvince}
C# from a in context.vOfficeAddresses select new { a.FirstName, a.LastName, Title = (from c in context.Contacts where c.ContactID == a.ContactID select c.Title).FirstOrDefault(), a.Street1, a.City, a.StateProvince }
There are a few notable twists to this query. The first should
be familiar: an anonymous type is not able to automatically name the
return from the nested query. Therefore, it is given the name
“Title”. The second twist is that the subquery returns an IQueryable of
String, not just a string, which
is why the FirstOrDefault method
is appended to the query.
You can also use the nested query in place of the collection being queried. The nested query merely returns another collection to be queried.
For example, rather than querying all vOfficeAddresses, you could create a
subquery that returns only vOfficeAddresses in Ontario and then query
against that. Example 4-33 is simplistic
and could easily be expressed without the nested query. The
technique can be useful when you are attempting to express queries
that are much more complex.
Example 4-33. Nested query in place of a target collection in LINQ
VB
Dim contacts = From add In _
(From oa In context.vOfficeAddresses _
Where oa.StateProvince = "Ontario" Select oa) _
Select ...C#
var contacts = from add in
(from oa in context.vOfficeAddresses
where oa.StateProvince == "Ontario" select oa)
select ...You can benefit from using nested queries to help with complicated queries by separating the nested query from the main query. Example 4-34 ties a subquery to a variable and then uses that variable in another query. This can make the code much more readable. When the query is executed, the Entity Framework will create a single query from the combined expressions.
Example 4-34. Breaking a nested query out of the main query in LINQ
VB
Dim subquery = From oa In context.vOfficeAddresses _
Where oa.StateProvince = " Ontario" Select oa
Dim contacts = From add In subquery _
Select ....C#
var subquery = from oa In context.vOfficeAddresses
where oa.StateProvince == " Ontario" select oa
var contacts = from add In subquery
select ....
Note
You can’t separate out a nested query that’s inside a projection, as in Example 4-32, because its filter condition is dependent on the main query.
With Entity SQL, the nested query works in the same manner, using the query in place of an actual value, though there’s no need to name the property it represents (see Example 4-35).
Example 4-35. Nested query in place of a SELECT expression in Entity SQL
SELECT add.FirstName, add.LastName,
ANYELEMENT(SELECT VALUE c.Title
FROM PEF.Contacts as c
WHERE c.ContactID=oa.ContactID),
oa.Street1, oa.City, oa.StateProvince
FROM PEF.vOfficeAddresses as oaThe query in Example 4-36 demonstrates replacing the queried collection with a nested query.
Example 4-36. Nested query in place of a FROM expression in Entity SQL
SELECT oa.FirstName, oa.LastName
FROM (SELECT VALUE oa
FROM PEF.vOfficeAddresses AS oa
WHERE oa.StateProvince='Ontario')
AS addYou can easily break this up for readability, because you are merely building strings, and you can concatenate the queries, as shown in Example 4-37.
Example 4-37. Breaking up a nested query in Entity SQL
VB
subQuery = "SELECT VALUE oa " & _
"FROM PEF.vOfficeAddresses AS oa " & _
"WHERE oa.StateProvince='Ontario'"
queryString = _
"SELECT add.FirstName, add.LastName FROM (" & subQuery & ") as add"C#
subQuery = "SELECT VALUE oa " +
"FROM PEF.vOfficeAddresses AS oa " +
"WHERE oa.StateProvince='Ontario'"
queryString = _
"SELECT add.FirstName, add.LastName FROM (" + subQuery + ") as add"
Both LINQ and Entity SQL provide operations for grouping data. You can use grouping in connection with aggregates or to shape data.
LINQ to Entities has a Group
operator (literally Group By in
Visual Basic and Group in C#) and a
GroupBy method (with eight
overloads). Entity SQL provides a GROUP
BY operator and a GroupBy
query builder method.
The results of the grouping can use automatic naming, and in other
cases can be explicitly named. In addition, an INTO GROUP clause is required in Visual Basic.
C# has an optional INTO
clause.
The constructs for VB and C# are quite different and it’s easiest to explain them with examples. Example 4-38 shows the simplest form of grouping in LINQ for both Visual Basic and C#.
Example 4-38. Simple grouping in LINQ to Entities
VB
From c In context.Contacts Group By c.Title Into GroupC#
from c in context.Contacts group c by c.Title into mygroupThe result of this query is an IQueryable of anonymous types. The anonymous
types contain two properties: one contains the title and the other
contains the collection of contacts that go with that title. The VB
query automatically named the property containing the title as “Title”,
as shown in Figure 4-5.
By default, C# uses the word Key as the name of the grouping property and doesn’t name the property that contains the grouped records, as you can see in Figure 4-6.
VB allows you to specify the property name rather than use the
default. In Visual Basic, to change the Title property of the preceding query to
MyTitle, you would use the syntax
Group By MyTitle=c.Title.
In VB, the Group property is
available to access the group. You can rename this as well. For example,
Into MyGroup = Group renames the
property to MyGroup.
The optional INTO clause in
C# allows you to specify a group name, but this is not exposed as a
property. You specify the name with INTO so that you can perform further
functions on the group. Note that in C#, using the INTO clause requires that you also use the
SELECT clause. The key property is then accessible as a
property of the group.
With the group specified, it is now possible to explicitly name
the properties in C#. LINQ queries in Visual Basic will imply a
SELECT statement if it is not used.
In this case, the query will still return Title and MyGroup by default without specifying
SELECT. Of course, you can shape
the data further by specifying your own output with an explicit
SELECT operator.
Example 4-39 demonstrates these changes to the previous queries.
Visual Basic provides a simple way to use aggregates in grouping
queries, by specifying one or more aggregates in the INTO clause separated by commas. In Example 4-40, your result will contain
the properties Max and Count.
Example 4-40. Chained aggregates in VB LINQ
VB
From con In context.Contacts _
Group By con.Title Into MyGroup = Group, _
Max(con.AddDate), Count()In C#, you need to explicitly project these properties in the
Select clause using methods and
predicates, as shown in Example 4-41.
There is so much more that you can do with grouping in LINQ. For now, we’ll take a look at one more variation: filtering on the grouping condition.
The Title fields in the
sample data contain Mr., Mrs., Ms., Sr., and a few other titles. Also,
some contacts have no title. Perhaps you would like to group on title,
but exclude empty titles. To filter what is being grouped, such as
“only group contacts with something in the Title field,” you can apply the filter to
the control variable, Title, to
make sure it contains a value.
You may, however, want to filter on a property of the Group. With LINQ you can continue to use the
WHERE operator, as shown in Example 4-42.
Example 4-42. Filtering on a Group property with LINQ
VB
From c In context.Contacts _
Group By c.Title Into MyGroup = Group, Count() _
Where (MyGroup.Count() > 150)C#
from c in context.Contacts
group c by c.Title into MyGroup
where MyGroup.Count() > 150
select new { MyTitle = MyGroup.Key,
MyGroup,
Count = MyGroup.Count()};In LINQ, you will also need to be aware of variables going out
of scope, as in the query shown in Example 4-43, which won’t
compile. The a in Group by a.CountryRegion is out of scope
because by this point in the query, you are working with the anonymous
type returned by the Select
statement. And the Select does need
to go before the Group
By.
Example 4-43. An out-of-scope variable preventing this query from compiling
VB
From a In context.Addresses _
Select a.Contact.FirstName, a.Contact.LastName, a.CountryRegion _
Group By a.CountryRegion Into MyGroup = Group, Count() _
Where (MyGroup.Count() > 150)You can avoid this problem by naming the anonymous type, and then grouping by a field within the name, as shown in Example 4-44.
Example 4-44. Naming variables to keep them from going out of scope
VB
From add In PEF.Addresses _
Select c = New With {add.Contact.FirstName,
add.Contact.LastName, _
add.CountryRegion} _
Group By c.CountryRegion Into MyGroup = GroupC#
from a in context.Addresses
let c= new {a.Contact.FirstName, a.Contact.LastName,
a.CountryRegion} group c by c.CountryRegion
into MyGroup where (MyGroup.Count() > 150)
select MyGroup;Both the Visual Studio documentation and the ADO.NET Entity
Framework documentation and samples can provide you with an astounding
array of data shaping that you can perform with Group By/groupby in LINQ, and even then there are
still many more.
Note
See the sidebar More Query Samples for links to these resources.
LINQ will spoil you with its grouping capabilities. Like SQL, Entity SQL comes with a lot of rules so that you can convert queries into a command tree and then into the provider’s query syntax.
For example, in SQL the most commonly encountered rule is that
every expression in the SELECT must
either be accounted for in the GROUP
BY clause or be wrapped in an aggregate. The same is true in
Entity SQL, which prevents you from being able to select entire
objects in the SELECT clause.
However, it is still possible to return entire objects and shape data
in Entity SQL by putting the GROUP
BY operator into a nested query. First take a look at Example 4-45, which shows some
simple grouping in Entity SQL.
Example 4-45. A simple GROUP BY example in Entity SQL
SELECT c.Title, COUNT(c.Title) FROM PEF.Contacts as c GROUP BY c.Title
The two projected expressions in the SELECT are covered by either the GROUP BY or an aggregate (COUNT). The query returns the
following:
[blank] 6 Mr. 255 Ms. 177 Sr. 3 Sra. 2
To group on an expression that is evaluated, such as "Title" + c.Title, the grouping must be
explicitly named and that name needs to be used as a projected
expression.
Example 4-46 shows the
Entity SQL syntax for creating an expression and grouping on it in the
same query. The expression, EvalTitle, is built in the GROUP BY clause and is used by name in the
SELECT.
Example 4-46. Grouping by a calculated expression
SELECT evalTitle,count(c.Title) FROM PEF.Contacts as c GROUP BY "Title: " +c.Title as evalTitle
Now, let’s take a look at how you can return full objects from
Entity SQL when using GROUP BY.
The trick is in using nested queries.
To reproduce the LINQ query that grouped by Title and returned each title with its
collection of contacts, you can use a nested query as an expression
in the SELECT statement as shown
in Example 4-47. It
seems as though the query does not have to follow the rule of being
part of the GROUP BY clause or
the target of an aggregate.
Example 4-47. An Entity SQL GROUP BY query that returns entities
SELECT TCon.Title,
(SELECT c FROM PEF.Contacts as c
WHERE c.Title= TCon.Title)
FROM PEF.Contacts as TCon
GROUP BY TCon.titleThe nested query returns a collection of contacts whose
Title property equals the current
title being returned by the group. Although this looks like it might
do some scary things on the server with respect to the generated
SQL, the SQL is similar to the SQL created as a result of the first
LINQ query in this section on grouping.
You saw that LINQ uses the WHERE clause to filter within a group. In
Entity SQL, you can use the HAVING clause for this purpose, as shown
in Example 4-48.
Example 4-48. Entity SQL’s HAVING clause, which helps with filtering
SELECT groupCon.Title,count(groupCon.ContactID) FROM PEF.Contacts as groupCon GROUP BY groupCon.title HAVING count(groupCon.ContactID)>150
This returns only the title groups that contain more than 150 contacts. The results will be as follows:
Mr. 255 Ms. 177
Like everything else this chapter has covered so far, we have
only skimmed the surface of GROUP
BY in Entity Framework queries. You will see more uses
throughout this book and can find more details (and plenty of rules)
in the documentation. The rest of this chapter will explain some
important concepts that have been exposed by the queries you’ve seen
so far.
Whether you write a query that returns entities, anonymous types,
DbDataRecords, or DbDataReaders, you can return shaped data.
You’ve seen this in several of the previous queries, with a variety of
shaped results. How you use this data depends on how the data is shaped.
Let’s take a further look at the results of some of the earlier
queries.
The LINQ and Object Services queries that returned entities defined in the model are not shaped. They are purely a collection of individual entities.
Note
Queries can also return graphs of data. In a graph, the related data is contained in the entity, and is not separated out to another field in the results. You’ll see examples of this later in this chapter.
For instance, Example 4-18 returned an
IQueryable of contacts. Example 4-14, however, returned
an anonymous type with two properties. The first property was a Contact entity and the
second was a collection of Address
entities related to that Contact. The code in Example 4-15 enumerated over
that data, albeit in a somewhat boring way, to demonstrate what the data
looked like. It showed the contacts and the addresses but did not truly
demonstrate the relationship between the two.
Example 4-49 executes the
same query and then enumerates through the anonymous types that result.
This time, however, the code accesses the Addresses as a navigation property of the
Contact.
Example 4-49. LINQ query creating shaped results
VB
Dim contacts = From a In context.Addresses _
Where a.CountryRegion = "Canada" _
Select New With {a, a.Contact}
For Each contact In contacts
Console.WriteLine("{0}: {1} Addresses", _
contact.Contact.LastName, _
contact.Contact.Addresses.Count())
For Each address In contact.Contact.Addresses
Console.WriteLine(" {0} {1}", address.Street1, address.City)
Next
Console.WriteLine()
Next
C#
var contacts =
from a in context.Addresses
where a.CountryRegion == "Canada"
select new { a, a.Contact };
foreach (var contact in contacts)
{
Console.WriteLine("{0}: {1} Addresses",
contact.Contact.LastName, contact.Contact.Addresses.Count());
foreach (var address in contact.Contact.Addresses)
{
Console.WriteLine(" {0} {1}", address.Street1, address.City);
}
Console.WriteLine();
}The WriteLine method doesn’t
access the a property of the
anonymous type, but instead navigates to the addresses through the
Contact property of the anonymous
type.
As the Contact and Address entities are materialized, the Entity
Framework recognizes that they are related to each other and wires them
up so that you can navigate between them. The Address objects have a Contact object in their Contact property and the Contact objects have Address objects in their Addresses property. This is a very high-level
explanation of an important function of the Entity Framework’s Object
Services API, which you will learn plenty about throughout the
book.
There is an interesting thing to be aware of with respect to how
the Entity Framework connects the related entities in the scenario laid
out in Example 4-49. If you
look at the following sample of the output, you can see that two
addresses belong to the contact “Alderson”. Both addresses are in
Montreal. But the first instance says that Alderson has only one
address. Not until the code has reached the second address is the
contact aware that two addresses exist in its Addresses navigation collection:
City: Nepean, LastName: Allison LastName: Allison, # Addresses: 1 .....Nepean City: Montreal, LastName: Alderson LastName: Alderson, # Addresses: 1 .....Montreal City: Montreal, LastName: Alderson LastName: Alderson, # Addresses: 2 .....Montreal .....Montreal City: Montreal, LastName: Steelman LastName: Steelman, # Addresses: 1 .....Montreal
The second address isn’t recognized initially because it hasn’t been materialized as an object yet. As the code enumerates through the query results for the first time, the objects are created from the query results as each contact or address is reached. Once the second address is encountered and turned into an object, its relationship to the contact is identified.
We will explore the object life cycle more deeply in a later chapter, but this should give you some idea for now about what’s going on in this example.
As you’ve seen already, projections in Object Services result in
DbDataRecords, as opposed to the
anonymous types that LINQ returns. However, even in these DbDataRecords, you can
still find complete entities and navigate through their associations.
You can express the query in Example 4-49 in Entity SQL, as shown in Example 4-50.
Example 4-50. Entity SQL resulting in addresses with their contacts
SELECT a,a.Contact FROM PEF.Addresses AS a WHERE a.CountryRegion='Canada'
This query results in an ObjectQuery of DbDataRecords that are structured as rows
and columns. Each row in this result has two columns (also called
fields). An Address entity is contained in the first
field and a Contact entity is
contained in the second field.
Figure 4-7
shows the first column of one of the DbDataRecords in the results. The item is an
Address entity. The second column
contains a Contact entity. So, even
though it is a DbDataRecord, it
still can contain known objects.
The code in Example 4-51 inspects the
Address entity in the first field
and the Contact entity in the second field. As
with the earlier LINQ example, the contacts will not be aware of all
of the related addresses until each address has been enumerated over.
Also, although the Item property
returns objects, thanks to implicitly typed local variables in VB 9
and C# 3, the compiler is able to know that c.Item("add") returns an Address entity and c.Item("Contact") returns a Contact entity. With the strongly typed
variables and the IntelliSense that results, it is easy to work with
the objects.
Example 4-51. Enumerating through and reading the shaped data from an ObjectQuery
VB
For Each record In addresses
Dim address = CType(record.Item("a"), Address)
Console.WriteLine("{0},{1}", _
address.City.Trim, _
address.Contact.LastName)
Dim con = CType(record.Item("Contact"), Contact)
Console.WriteLine("{0},{1}", con.LastName.Trim, _
con.Addresses.Count())
For Each a As Address In con.Addresses
Console.WriteLine("....." & a.City)
Next
Console.WriteLine()
NextC#
foreach (var record in addresses)
{
var address = (Address)(record["a"]);
Console.WriteLine("{0},{1}", address.City.Trim(),
address.Contact.LastName.Trim());
var con = (Contact)(record["Contact"]);
Console.WriteLine("{0},{1}",
con.LastName.Trim(), con.Addresses.Count());
foreach (Address a in con.Addresses)
{
Console.WriteLine("....." + a.City);
}
Console.WriteLine();
}The output of this example matches the output of the query in Example 4-49.
So far, all of the queries that involved returning related data have explicitly asked for that data in the query itself. The Entity Framework will only return data that you explicitly ask for. If your query asks only for contacts, the Entity Framework will not make an assumption that just because contacts have addresses, it should return the addresses anytime you query for contacts. Consider a typical model for sales information. A contact is related to a customer; a customer has sales orders; each sales order has line items; each line item relates to a product; each product comes from a vendor and is also related to a category. Can you imagine if you queried for contacts, and without expecting it, the entire contents of the database were returned—because it was all related?
It is possible to get related data after the fact. For example, if you queried for a selection of contacts, as you work with those contacts in code you can request the contacts’ addresses without performing another complete query.
This is referred to as deferred loading, and
although it does not require you to create and execute a new query, it
does require the explicit step of calling a Load method.
The following LINQ to Entities query returns an ObjectQuery of Contact entities:
VB
From c In context.Contacts Select cC#
From c in context.Contacts select cBecause the query does not explicitly request the addresses, the
Addresses.Count for every single
contact will be zero.
What you need to do is tell the Entity Framework to get the addresses for the current contact, as shown in the following code:
VB
For Each contact In contacts
contact.Addresses.Load
Console.WriteLine(contact.Addresses.Count)
NextC#
foreach (var contact in contacts)
{
contact.Addresses.Load();
Console.WriteLine(contact.Addresses.Count);
}When Load is called, Object
Services will execute a query to retrieve all of the addresses for
that contact. In the preceding example, after Load is called, the value of Count will be correct and all of the
Address entities for that contact
will be available.
There is a big performance consideration here. For each
contact, the code is forcing an extra round trip to the database. If
the query returns 40 contacts, one more trip is made to the database
for each contact when Load is
called. That’s 40 round trips to the server in addition to the
original trip made to execute the first query. This can be extremely
inefficient and might also get you into big trouble with the IT pros
in your company.
Load is a great choice in
cases where you want to inspect the contact and then load addresses
for only particular contacts. Perhaps you want to list all contacts,
but for contacts that were added after a particular date you need to
see how many addresses are in the database. The code in Example 4-52 demonstrates
this scenario, where you may determine it is more efficient to make
a small number of database trips rather than preloading addresses
for every contact.
Example 4-52. Loading addresses for some of the contacts
VB
For Each contact In contacts
Console.WriteLine(contact.LastName)
If contact.AddDate > CDate("1/1/2008") Then
contact.Addresses.Load()
End If
NextC#
foreach (var contact in contacts)
{
Console.WriteLine(contact.LastName);
if (contact.AddDate > System.Convert.ToDateTime("1/1/2008"))
contact.Addresses.Load();
}You can also perform deferred loading for EntityReference navigation properties—for
example, Address.Contact.
However, rather than load from the Contact property, you must load from the
additional property that was created: ContactReference. The Entity Framework
sees Address.Contact as merely a
Contact entity, and the Contact class does not have the Load method. It is the ContactReference property that has the
knowledge of how to load the related information. Each EntityReference navigation property will
have a related property with the word Reference
appended to its name. As a reminder, the additional property is
created when the classes are generated from the model.
Example 4-53
shows how to load ContactReference data for particular
addresses after the addresses have already been queried.
Example 4-53. Loading the Contact with ContactReference.Load
VB
Dim addresses = From a In context.Addresses Select a
For Each address In addresses
If address.CountryRegion = "UK" Then
address.ContactReference.Load()
End If
NextC#
var addresses = from a in context.Addresses select a;
foreach (var address in addresses)
{
if (address.CountryRegion == "UK")
address.ContactReference.Load();
}In cases where you know you will need all of the addresses up
front, it may be more efficient to retrieve them as part of the
original query. Although you have seen how to do this with projection
by including the addresses in the SELECT clause, the Include method is another way to achieve
this and may be preferable for a variety of reasons.
Include is a query builder
method and you can apply it to an ObjectQuery. Because context.Contacts is an ObjectQuery, you can use Include even within a LINQ query, as shown
in Example 4-54.
Example 4-54. The Include method in a LINQ to Entities query
VB
From c In context.Contacts.Include("Addresses") _
Where c.LastName = "Smith" _
Select cC#
from c in context.Contacts.Include("Addresses")
where c.LastName= "Smith"
select cThe argument for Include is a
string that is the name (or names) of the navigation properties to
bring back along with the contacts. This is referred to as
eager loading or eager
fetching.
You can use Include only when
returning an ObjectQuery of a
single entity type. You cannot use it with projections, and if you do
project, Include will be
ignored.
In the sample model, there is only one navigation property for
contact, which is Addresses. Imagine a sales model with a
number of entities and a variety of navigations. You could query
customers and eager-load the orders and all of the orders’ details by
querying Customers.Include("Orders.OrderDetails").
The string is called a query path because it
defines the path that the query should navigate through the model.
Additionally, you could eager-load the orders and the customers’
addresses by chaining the Include
methods like this:
Customers.Include("Orders.OrderDetails").Include("Addresses")Data shaping is one of the interesting benefits of Include. The previous Contacts.Include("Addresses") query
returns a set of Contact
entities. This does not have the same effect as projection, which
would have to return DbDataRecords.
Figure 4-8
shows the query results in the debugger’s QuickWatch window. You can
see that the results are strictly a set of Contact entities. Where are the
addresses?
Figure 4-9
drills into one of the contacts, and you can see that both of this
contact’s addresses are there. The Include brings in the related data, and
unlike the issue you saw with the addresses not being attached to
Mr. Alderson from Montreal until all of the addresses had been
enumerated through, all of these addresses are present as soon as
you get to the contact.
You can filter and sort, even when an Include is involved, though there is one
caveat: you cannot limit which records are included. You will always
get the entire EntityCollection referenced in the
Include.
You can, however, use the properties of the Include entities in many of the same ways
you can use properties of any related data when querying.
Example 4-55 uses
the CountryRegion field of
Address to limit which contacts
are retrieved. But be sure you are clear on the results. This will
return contacts that happen to have any of their addresses in the
United Kingdom. If a contact has multiple addresses and only one of
them is in the United Kingdom, you will still get all of those
addresses.
How would you apply Include
when creating an ObjectQuery
directly rather than using LINQ to Entities?
Include is a query builder
method and you can use it in the same manner as other query builder
methods. You can add it to CreateQuery methods or to an ObjectQuery returned by a CreateQuery. Example 4-56 shows how to
apply Include when using CreateQuery.
Example 4-56. The Include method in an Object Services query with Entity SQL
VB
Dim str = "SELECT VALUE c FROM PEF.Contacts as c "
Dim contacts = context.CreateQuery(Of Contact)(str).Include("Addresses")C#
var str = "SELECT VALUE c FROM PEF.Contacts as c ";
var contacts = context.CreateQuery<Contact>(str).Include("Addresses");The same rule applies for projections when using Entity SQL with
Include. If you project in your
query, Include will be ignored. It
is able to work only when complete entities are involved.
Warning
Pay attention to JOIN
queries. If you use Include in a
query that also has a JOIN, the
Include will be discarded—no
warnings, no compiler errors. Try a nested query instead, but
validate your results.
When using the Include method
to eager-load entity references, use the navigation property for that
property name (Contact), not the
EntityReference property (ContactReference) as
with the following ObjectQuery:
VB
Dim querystring = "SELECT VALUE add " & _
"FROM PEF.Addresses as add"
Dim addresses = PEF.CreateQuery(Of Address)(querystring) _
.Include("Contact")C#
var querystring = "SELECT VALUE add " +
"FROM PEF.Addresses as add"
var addresses = PEF.CreateQuery<Address>(querystring)
.Include("Contact")Just as you saw when using Include to load entity collections, an
entity object will be returned, not an anonymous type, and the entity
reference is loaded.
You have some things to consider when choosing between the
Load and Include methods. Although the Load method may require additional round
trips to the server, the Include
method may result in a large amount of data being streamed back to the
client application and then processed as the data is materialized into
objects. This would be especially problematic if you are doing all of
this work to retrieve related data that may never even be used. As is
true with many choices in programming, this is a balancing act that
you need to work out based on your particular scenario.
The documentation also warns that using query paths with
Include could result in very
complex queries at the data store because of the possible need to use
numerous joins. The more complex the model, the more potential there
is for trouble.
You could certainly balance the pros and cons by combining the
two methods. For example, you can load the customers and orders with
Include and then pull in the order
details on an as-needed basis with Load.
The correct choice will most likely change on a case-by-case basis.
Note
There are a few methods for retrieving a single entity without
even writing a query. They are called GetObjectByKey and GetObjectStateEntry. These methods have
counterparts called TryGetObjectByKey and TryGetObjectStateEntry. The methods
exist in another Entity Framework class, and you will learn more
about GetObjectByKey later in
this chapter and the rest in Chapter 17.
All of the queries so far have returned sets of data. What if you
wanted to retrieve a single entity or a single result? The queries
return IQueryables or ObjectQuery and you need to dig into those to
get at the actual data, which might be entities, anonymous types, or
DbDataRecords.
This is reasonable if you are returning multiple items, but what
about cases where you query for one particular item—for example, the
contact whose ContactID is 63—and you don’t want to have an IQueryable returned, but just the item?
LINQ to Entities has a pair of methods, First and FirstorDefault, which will return the first
item in the result set. These methods are not specific to LINQ to
Entities, but come from LINQ and may be familiar to you
already.
Example 4-57 shows two
techniques for using these methods. In the first technique, a query is
defined and in another line of code the First method is called. This will cause the
query to be executed and the contact entity to be returned. The second
technique appends the First method
directly to the query. Even though First is a method, you can combine it with the
query operator syntax by wrapping the query in parentheses. In this
case, the query is executed immediately and the contact is
returned.
Example 4-57. Querying with the First method
VB
Dim contacts = From c In context.Contacts Where c.ContactID = 63
Dim contact = contacts.First
Console.WriteLine(contact.LastName)
Dim singleContact = _
(From c In context.Contacts Where c.ContactID = 24 Select c).First
Console.WriteLine(SingleContact.LastName)C#
var contacts = from c in context.Contacts
where c.ContactID == 63 select c;
var contact = contacts.FirstOrDefault();
Console.WriteLine(contact.LastName);
var singleContact = (from c in context.Contacts where c.ContactID == 24
select c).First();
Console.WriteLine(SingleContact.LastName);There’s a potential problem here. If there are no items, First will throw an InvalidOperationException with the
message “Sequence contains no elements”. FirstorDefault protects you from the
exception by returning the default, which is generally a null (Nothing in VB).
In Example 4-58,
FirstorDefault is used to avoid an
exception being thrown. Contact in this case will be Nothing/null after the query is
executed.
The ObjectContext.GetObjectByKey method and its
counterpart, TryGetObjectByKey,
provide a way to query for an object without having to construct and
execute a query. However, this has a notable twist. The runtime will
first look in the existing instantiated objects to see whether the
object has already been retrieved. If it is found, this is what will be
returned. If not, the query to the data store will be executed
automatically and the object will be returned.
GetObjectByKey takes an
EntityKey type that defines what
object to retrieve based on its EntitySet, its key property name, and the
value of that property. For example, EntityKey("PEF.Contacts","ContactID",5)
defines an object in the Contacts
EntitySet with a
ContactID value of 5. Once the EntityKey has been created, GetObjectByKey(myEntityKey) will return the
object either from memory or from the database.
TryGetObjectByKey uses the .NET
Try pattern to avoid returning an
exception if the object is not found in memory or in the
database.
You will see both of these used many times in later chapters, and
you will learn all about the EntityKey class in Chapter 9.
There is one last concept to highlight before finishing this chapter and moving on: understanding when Entity SQL queries will return rows containing values, or just values.
By default, queries using Entity SQL (ObjectQuery and EntityClient queries) return rows. The rows
are contained in the ObjectQuery that
results, or in the EntityClient’s
DbDataReader. When the data is pulled
out of the row as part of the query process, this is referred to as
unwrapping. Then, rather than a row, the ObjectQuery and DbDataReader will contain the returned
value.
You just saw the use of the First and FirstorDefault methods to return a single
object, rather than an IQueryable,
which would then need to be enumerated through to get at the object.
Conceptually, Entity SQL queries that unwrap results are doing the
same.
Unwrapping is possible only when a single value is returned in the
ObjectQuery or DbDataReader. An Entity SQL query will return
rows with the same number of columns as items listed in the projection,
regardless of what type the item is—a string, an entity, or even a
collection. Take, for example, a simple projection of names as shown in
Table 4-1, or a projection that
returns shaped data. Table 4-2 shows rows, each
containing three strings and an EntityCollection. Each row in the results of
Table 4-3 contains an
entity and an EntityCollection. Note that the rows in
the tables represent a DbDataRecord
type.
Table 4-1. A simple projection of names
Column 1 | Column 2 | Column 3 | |
|---|---|---|---|
Row 1 | Mr. | John | Doe |
Row 2 | Sr. | Pablo | Rojas |
Row 3 | Mrs. | Olga | Kolnik |
Table 4-2. Rows containing three strings and an EntityCollection
Column 1 | Column 2 | Column 3 | Column 4 | |
|---|---|---|---|---|
Row 1 | Mr. | John | Doe |
|
Row 2 | Sr. | Pablo | Rojas |
|
Row 3 | Mrs. | Olga | Kolnik |
|
Table 4-3. Rows containing an entity and an EntityCollection
Column 1 | Column 2 | |
|---|---|---|
Row 1 |
|
|
Row 2 |
|
|
Row 3 |
|
|
Because neither Object Services nor EntityClient can return anonymous types, the
only way to return these multicolumn rows is to wrap them in rows where
the values are contained in columns. Once you have the result set in
memory, you can extract the entities or values programmatically and
interact with them as you have done in this chapter and the previous
chapter.
However, consider a query with only one value being returned in
each row. By default, you will still get a DbDataRecord, and that value will be the first
and only column of the row (see Table 4-4).
Table 4-4. Contact entities that are contained within rows
Column 1 | |
|---|---|
Row 1 |
|
Row 2 |
|
Row 3 |
|
By adding the VALUE keyword
(SELECT VALUE …), you’re signaling
that you want the value to be unwrapped. With Object Services, this will
result in an ObjectQuery of Contact entities. As you have seen, you must
specify the proper type for the ObjectQuery. This could be one of the EntityObject types defined in your model, or
some other type, such as a string or an integer. Look at the difference
in how you need to work with the results when the contact is wrapped
(Example 4-59) and unwrapped (Example 4-60). When it’s wrapped you still need to
cast the value in the first column (Item(0)) to a contact before you can work with
the contact, even though it’s the only value in the result.
Example 4-59. Wrapped contact
VB
Dim esql = _
"SELECT c FROM PEF.Contacts as c WHERE c.FirstName='Robert'"
Dim wrappedContacts = context.CreateQuery(Of DbDataRecord)(esql)
For Each record In wrappedContacts
Dim contact = CType(record.Item(0), Contact)
Console.WriteLine(contact.LastName)
NextC#
var esql =
"SELECT c FROM PEF.Contacts as c WHERE c.FirstName='Robert'";
var wrappedContacts = context.CreateQuery<DbDataRecord>(esql);
foreach (var record in wrappedContacts)
{
var contact = (Contact)(record[0]);
Console.WriteLine(contact.LastName);Example 4-60. Unwrapped contact
VB
Dim esql = _
"SELECT VALUE c FROM PEF.Contacts as c WHERE c.FirstName='Robert'"
Dim unwrappedContacts = context.CreateQuery(Of Contact)(esql)
For Each contact In unwrappedContacts
Console.WriteLine(contact.LastName)
NextC#
var esql =
"SELECT VALUE c FROM PEF.Contacts as c WHERE c.FirstName='Robert'";
var unwrappedContacts = context.CreateQuery<Contact>(esql);
foreach (var contact in unwrappedContacts)
Console.WriteLine(contact.LastName);
}Here are some rules to remember for Entity SQL queries:
Use
SELECTwhen projecting more than one value.When querying with
SELECT, theObjectQuerytype must be aDbDataRecord.You can use
SELECT VALUEwhen projecting a single value or entity.When querying with
SELECT VALUE, theObjectQuerytype must be the same type as the value being returned.
Breaking any of these rules will result in a runtime exception
when the Entity Framework attempts to generate the store’s SQL from
the Entity SQL or when the data is returned and the Entity Framework
is trying to align the returned type with the type defined for the
ObjectQuery.
Because EntityClient streams
results and does not materialize records, you won’t get entity
objects. However, the data that results will be shaped based on the
entity shape, and therefore, as you saw in some of the earlier
examples, you can cast the results back to the appropriate entity. You
can also cause the results to be wrapped or unwrapped.
Remember that DbDataRecords
can contain nested DbDataRecords,
or even nested DbDataReaders, which is how it’s
possible to shape the results.
Here are a variety of different queries and the results to
expect in EntityClient:
Query projecting two simple values:
SELECT c.FirstName,c.LastName FROM PEF.Contacts AS c
Each row of the
DataReaderthat results is aDbDataRecordwith two columns. Each column contains a string.Query projecting a single value that is an entity:
SELECT VALUE c FROM PEF.Contacts AS c
Each row of the
DataReaderthat results is aDbDataRecordwith one column. The column contains anIExtendedDataRecord, which is a type of aDbDataRecord. There is one column for every property in aContactentity, filled with the relevant data.Complex query projecting an entity and a collection of entities:
SELECT c, c.Addresses FROM PEF.Contacts AS c
Each row of the
DataReaderthat results is aDbDataRecord. There are two columns: the first contains anIExtendedDataRecordwith one column for each property of theContactentity, and the second contains a wholeDbDataReaderthat implementsIExtendedDataRecord. This allows the data to be cast to anEntityCollectionof address types.Query projecting a single entity using
SELECT VALUE:SELECT c FROM PEF.Contacts AS c
Each row of the
DataReaderthat results is anIExtendedDataRecord. There is one column for every property of theContactentity, filled with the relevant data.Query projecting a single simple type using
SELECT VALUE:SELECT VALUE c.LastName FROM PEF.Contacts AS c
Each row of the
DataReaderthat results is a string.
With LINQ to Entities, Entity SQL, Object Services, and EntityClient, the Entity Framework provides
myriad possibilities for querying data and shaping results.
Although it would take a few hundred more pages to ensure that you have seen an example of almost any type of query you may want to write, these past two chapters should leave you very prepared to venture forth.
In the next few chapters, you will learn about updating the data that you have queried and taking advantage of stored procedures.
Beginning with Chapter 8, you will start to write some solutions and be able to leverage many of these types of queries.
Get Programming Entity Framework now with the O’Reilly learning platform.
O’Reilly members experience books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.
