The preceding query used a shortcut that produced a query for you. But it didn’t really feel like a query. Now you’ll write an actual query using LINQ to Entities.

Remove the breakpoint that you set in the previous steps. In the line of code that created the contacts memory variable, replace context.Contacts with the query in Example 3-2, which retrieves a subset of the contacts.

VB
Dim contacts=From c In context.Contacts
             Where c.FirstName = "Robert"

C#
var contacts = from c in context.Contacts
               where c.FirstName == "Robert" select c;

Run the application again and you will see that only a small number of contacts are listed and they all have Robert as their first name.

Since you just dove right into the code, you might have a few questions. For instance, where did the Contact type come from? How did you go from an XML file (the EDMX file) to strongly typed .NET objects? Why is context.Contacts a query, and what is that context anyway?

One of the features of the EDM Designer tools is that the Designer automatically performs code generation based on the model. If you set the Solution Explorer to Show All Files, you’ll see an extra code file attached to the model, as shown in Figure 3-2.

Figure 3-2. An extra code file attached to the model

Expand the .edmx file in the Solution Explorer to see the generated code file. Open the file to see what’s in there.

The generated code file contains four classes. Figure 3-3 shows these classes in Visual Studio’s Class Designer view. The first is ProgrammingEFDB1Entities, which has taken the model’s EntityContainer name. The others are for each entity—Address, Contact, and vOfficeAddresses.

Figure 3-3. The four classes in Visual Studio’s Class Designer view

VB
Public ReadOnly Property Contacts() As  _
  Global.System.Data.Objects.ObjectQuery(Of Contact)

C#
public global::System.Data.Objects.ObjectQuery<Contact> Contacts

The entity classes

The three entities defined in the model are the source for the three entity classes. Each class inherits from the Entity Framework’s EntityObject class and has properties based on the properties defined in the model, including the Contact.Addresses and Address.Contact navigation properties (see Figure 3-4).

Figure 3-4. The entity classes in the Class Designer

But there’s something new in there, ContactReference, which is another way to access the Contact property. You’ll learn more about Reference properties in detail in Chapter 15. These classes have more members, but as they are not relevant to the querying you’ll do in this chapter, we will dissect them later in the book.

Dig deeper: don’t be afraid to poke around in the generated code file, but remember that any changes you make will be overwritten anytime the model is modified and saved.

You’ve seen that ProgrammingEFDB1.Contacts returns an ObjectQuery of Contact types. This is evident in the generated code as well as at runtime.

There is a small twist on this that you may have noticed. At design time, when you hover your mouse pointer over the contacts variable that the LINQ to Entities query returns, the DataTip doesn’t say that contacts is an ObjectQuery. Instead, it says that contacts is an IQueryable.

IQueryable is a LINQ query type. At design time, the compiler recognizes the LINQ query and does its best to tell you its return type. The compiler doesn’t realize that because it is a LINQ to Entities query, it will be processed by the Entity Framework and will result in an ObjectQuery. ObjectQuery implements IQueryable, so the two are very closely related.

IQueryable contains metadata about the query, such as the query expression and the provider being used. ObjectQuery is an IQueryable with additional query details that are specific to Entity Framework queries.

At runtime, you’ll see evidence of this again if you open the contacts query variable in the QuickWatch window (see Figure 3-5).

Figure 3-5. ObjectQuery implementing LINQ’s IQueryable

Once an IQueryable (and an ObjectQuery because it implements IQueryable) has been executed, it contains its query metadata as well as the new query results.

The results are described as an “enumerable type,” based on the class IEnumerable, which is similar to a Collection. An IEnumerable allows you to enumerate or iterate through each item in the collection as you did in the preceding code sample (i.e., in For Each/foreach). A Collection is an enhanced IEnumerable. Whereas an IEnumerable is read-only, the more familiar Collection class allows you to perform additional actions, such as adding or removing items from the group.

Why would you need another means of querying the EDM in addition to LINQ to Entities? The main reason lies in the use of the string-based query that you passed into the CreateQuery method. If you have constructed SQL queries before, this syntax looks familiar but not quite right. This is the specialized query language for the Entity Framework, and it’s called Entity SQL.

The return type of this query at design time is an ObjectQuery (Of Contact), not IQueryable. An ObjectQuery has methods and properties that are not available to an IQueryable. But as you will learn later in this book, it is possible to cast the IQueryable to an ObjectQuery and then access those properties and methods. This means that even if you choose to use LINQ to Entities, you will still get to benefit from these properties and methods.

Entity SQL was actually the first syntax devised for querying entities. LINQ was being developed as a language extension by the VB and C# language teams, and eventually it became obvious that LINQ would be a fabulous addition to the Entity Framework, which is how LINQ to Entities came to be.

Entity SQL has its roots in SQL because it makes sense to start with something that is well known. However, because entities are different from relational data, Entity SQL deviates from SQL to provide the necessary capabilities for querying the EDM.

Looking more closely at the Entity SQL query string you built earlier, you’ll notice that, like LINQ to Entities, it defines a variable for use in the query: c. In LINQ this is referred to as a control variable, but in Entity SQL it is just called a variable.

Figure 3-6 deconstructs the query string without the WHERE clause. The variable is defined using the AS keyword and is referenced in the SELECT clause. The VALUE keyword specifies that you want to return a collection of single items; in this case, it will be Contact entities.

Figure 3-6. Deconstructing a simple Entity SQL query

The VALUE clause is required if you are selecting a single item, which can be an entity, a single property, or even an entity collection, as shown in the following code snippet:

SELECT VALUE c FROM ProgrammingEFDB1Entities.Contacts ...
SELECT VALUE c.FirstName FROM ProgrammingEFDB1Entities.Contacts ...
SELECT VALUE c.Addresses FROM ProgrammingEFDB1Entities.Contacts ...

If you are selecting multiple items, you cannot use VALUE, as shown here:

SELECT c, c.Addresses FROM ProgrammingEFDB1Entities.Contacts ....
SELECT c.LastName,c.Title FROM ProgrammingEFDB1Entities.Contacts ...

If you forget to use VALUE, an InvalidOperationException will be thrown at runtime telling you that .NET is unable to cast a System.Data.Object.MaterializedDataRecord to the type that you specified in the ObjectQuery.

If you include VALUE with multiple items, an EntitySqlException will be thrown that specifically tells you the following:

"SELECT VALUE can have only one expression in the projection list."

It will even tell you the line number and column number of the problem.

Without the VALUE clause, the results will be wrapped in rows and you will have to dig into the rows and columns to get at the data. Similar to the LINQ query, you are selecting FROM a collection. In this query, that collection is the entity set, Contacts; but it is necessary in Entity SQL to specify the EntityContainer as well. Again, c is a random variable name the query used to represent contact items within the Contacts entity set.

The WHERE clause in the Entity SQL uses SQL-like syntax, as in the following:

WHERE c.FirstName='Robert'

ObjectQuery allows you to create parameterized queries. Similar to other query languages, you use an @ placeholder in the string, and then define its value in a parameter.

To use a parameterized query, you can add parameters to an ObjectQuery created with the CreateQuery method of the ObjectContext or to one which you have instantiated explicitly, as shown in the following example. Using this method, you also need to pass the ObjectContext as a parameter when you instantiate an ObjectQuery.

Then you add parameters to the ObjectQuery prior to execution. To see how this works, you can rewrite the query you’ve been writing to enable dynamic changes to the query, like this:

VB
queryString = _
"SELECT VALUE c FROM ProgrammingEFDB1Entities.Contacts AS c " & _
"WHERE c.firstname=@firstName"

Dim contacts As New ObjectQuery(Of Contact)(queryString, context)
contacts.Parameters.Add(New ObjectParameter("firstName", "Robert"))

C#
queryString = 
"SELECT VALUE c FROM ProgrammingEFDB1Entities.Contacts AS c " +
"WHERE c.firstname=@firstName";

var contacts = new ObjectQuery<Contact>(queryString, context);
contacts.Parameters.Add(new ObjectParameter("firstName", "Robert"));

Although it may seem tempting, you cannot use parameters to replace property names in the query string. In other words, if you tried to create the Entity SQL string SELECT @myproperty FROM ProgrammingEFDB1Entities.Contacts AS c and you created a parameter that set @myproperty to c.LastName, the SQL that results would look like this:

SELECT 'c.LastName' FROM ProgrammingEFDB1.Entities.Contacts AS c

This is invalid SQL and will throw an error.

Although Visual Basic and C# understand LINQ syntax, the CLR does not. One of the first things to happen when .NET processes LINQ queries is that it translates the query into a set of method calls on the collection being queried. All of the standard query operators (WHERE, SELECT, JOIN, etc.) have associated methods in .NET 3.5.

You can write your queries using the method syntax directly, if you prefer. Many developers do happen to prefer this, although many others would rather use the expression syntax. The MSDN documentation says, “In general, we recommend query syntax because it is usually simpler and more readable; however, there is no semantic difference between method syntax and query syntax.” Therefore, using one over the other is a matter of style and personal choice.

To write method-based queries, you will need to leverage a new feature introduced in .NET 3.5, called lambdas. Lambdas are inline methods that you can pass into a method to evaluate the method. If you are new to LINQ and lambdas and have never used anonymous delegates (which don’t even exist in Visual Basic), this will make more sense after you’ve seen some examples.

Let’s use the Where clause to compare working with a method rather than an operator. A standard Where clause is written as Where LastName='Hesse'. The Where() method requires the condition LastName='Hesse' as a parameter. You would write this condition very differently in C# and Visual Basic.

Here we’ll take a look at the query you used in the previous examples, now written using method-based queries. In Visual Basic, the expression begins with Function, to indicate that you are performing a function on a variable control; then it states the condition. The control variable, c in this example, is named on the fly:

Dim contacts = context.Contacts _
               .Where(Function(c) c.FirstName="Robert")

The C# LINQ to Entities query using the method-based syntax looks very different:

var contacts = context.Contacts
               .Where(c => c.FirstName=="Robert");

C# lambda expressions begin by identifying the control variable, followed by => (the lambda) and then the expression, [controlVariable].FirstName=='Robert'.

In the Where clauses, the expression that returns a Boolean is called a predicate. The query will return all of the contacts for which the expression evaluates to True.

Try it out:

VB
Dim contacts = context.Contacts _
               .Where(Function(c) c.FirstName="Robert") _
               .OrderBy(Function(foo) foo.LastName)

C#
var contacts = context.Contacts
               .Where((c) => c.FirstName == "Robert")
               .OrderBy((foo) => foo.LastName);

SELECT
[Extent1].[ContactID] AS [ContactID],
[Extent1].[FirstName] AS [FirstName],
[Extent1].[LastName] AS [LastName],
[Extent1].[Title] AS [Title],
[Extent1].[AddDate] AS [AddDate],
[Extent1].[ModifiedDate] AS [ModifiedDate]
FROM [dbo].[Contact] AS [Extent1]
WHERE N'Robert' = [Extent1].[FirstName]
ORDER BY [Extent1].[LastName] ASC

It’s possible to use Entity SQL with method syntax as well, although a limited number of methods are available—13 in fact, including Where and Select. These methods are called query builder methods. Query builder methods will do as their name suggests: build an ObjectQuery with the correct Entity SQL expression for you.

Although the query builder methods may look like some of the LINQ methods, they are definitely different. The compiler can tell when you are using a query builder method based on the parameter expression, which will contain either a lambda expression for LINQ queries or an Entity SQL expression.

Here is the latest query using Entity SQL as the method parameters:

VB
Dim contacts = context.Contacts _
               .Where("it.FirstName = 'Robert'") _
               .OrderBy("it.LastName")

C#
var contacts = context.Contacts
               .Where("it.FirstName = 'Robert'")
               .OrderBy("it.LastName");

The most common question regarding these expressions is “Where did it come from?” it is the default alias for the control variable. There is no opportunity to define the control variable as you have had to do with all of the other queries we have looked at so far, though it is possible to define your own for nested queries, as you’ll see in the next example.

When debugging, you can inspect the CommandText property of the contacts ObjectQuery to see that the query builder did indeed build the Entity SQL for you as shown in Example 3-4. It’s a little more complex than what you might have written yourself. This is a result of the query builder’s need to be flexible. Additionally, it does not specify the EntityContainer name in the expression, something that you can’t get away with when building the Entity SQL yourself.

SELECT VALUE it
FROM (SELECT VALUE it
       FROM ([Contacts]) AS it
       WHERE it.FirstName = 'Robert')
AS it
ORDER BY it.LastName

Even when used as a method parameter, Entity SQL provides the same benefits over LINQ to Entities as it does when being used in a query expression. If you need to dynamically build the strings for the expressions or if you need to leverage provider-specific functions, once again Entity SQL is the way to go. Another interesting difference is that using query builder methods with Entity SQL expressions removes any syntax differences between Visual Basic and C#.

Whether you use LINQ predicates or Entity SQL predicates, at compile time the Entity Framework will be able to determine which query compilation path to choose by looking at the predicate.

VB
Dim contacts = context.Contacts _
               .Where("it.FirstName = 'Robert'")
contacts.Name = "cons"
Dim orderedContacts = contacts.OrderBy("cons.lastname")

C#
var contacts = context.Contacts.Where("it.FirstName = 'Robert'");
contacts.Name = "cons";
var orderedContacts = contacts.OrderBy("cons.lastname");

With other client providers, the connection connects directly to the data store. However, the EntityConnection provides a connection to the EDM. When you created the model with the ADO.NET Entity Data Model Wizard, you may remember seeing the odd connection string in the wizard’s page where you selected the connection. An EntityConnection string consists of pointers to the compiled EDM schema files as well as a database connection string.

The wizard wrote the EntityConnection string into the app.config file. You can open this file from the Solution Explorer and see that the ConnectionString named ProgrammingEFDB1Entities is composed of three parts: the metadata, provider, and provider connection string.

The metadata contains file path pointers to the three schema files that are created from the model when the project is built. The data provider refers to the SqlClient provider that is being used to connect to the SQL Server database in this example. And finally, the provider connection string is a standard database connection string:

metadata=res://*/Model1.csdl|res://*/Model1.ssdl|res://*/Model1.msl;
provider=System.Data.SqlClient;
provider connection string=
 "Data Source=MyServer;
  Initial Catalog=ProgrammingEFDB1;
  Integrated Security=True;
  MultipleActiveResultSets=True"

EntityConnection provides for an easy way to reference the connection string in the app.config file, which is to set a name property to the same name of the connection string: for example, "name=ProgrammingEFDB1Entities". As you saw in Example 3-5, the quotes are required.

Creating the EntityCommand is no different from creating any other provider command and setting its CommandText. The CommandText here is the Entity SQL expression defined in the variable, queryString.

With EntityClient, the SequentialAccess CommandBehavior is required for the ExecuteReader method. With other DbDataReaders, rows must be accessed sequentially, but the columns within the rows need not be. This rule exists to control memory consumption.

DbDataReaders are streams of data and are, by definition, forward-only. This also means that the columns must be read in this way, which makes the next bit of code in Example 3-5 a little cumbersome.

In the string concatenation, you want to combine the fields to read Title FirstName LastName. But this is not the order of the fields returned in the DataReader. Title is the fourth column in the row, whereas FirstName is the second column and LastName is the third; therefore, you cannot read the Title data first, and instead must read the fields in the order in which they are streaming.

That is why this method creates the variables prior to building the string—so the data can be extracted in sequential order. Once the variables exist, you can build the string. This is an important lesson to remember, regardless of how you plan to use the streamed data returned by the EntityClient.

As you write queries, it’s a good idea to keep an eye on the generated SQL, since you can write queries in different ways and get the same results, but perhaps not generate the same store command.

A good example of this is when you combine .NET methods with LINQ. The String class has a handy method named StartsWith that evaluates the first character in a string:

VB
From c In context.Contacts _
Where c.LastName.StartsWith("S")

C#
from c in PEF.Contacts
where c.LastName.StartsWith("S")
select c

Even though T-SQL has a handy LEFT function, because of the way .NET evaluates StartsWith it is not interpreted as LEFT. Instead, you get the following WHERE clause in SQL Server:

WHERE (CAST(CHARINDEX(N'S', [Extent1].[LastName]) AS int)) = 1

The use of CAST has a negative performance impact in SQL.

Visual Basic actually has a LEFT method that you can use in LINQ:

From c In PEF.Contacts _
Where Left(c.LastName, 1) = "S"

This query does cause the T-SQL LEFT function to be used:

WHERE N'S' = (LEFT([Extent1].[LastName], 1))

Unfortunately, C# does not have a comparable function. The best choice for C# is to use Substring:

from c in PEF.Contacts
where c.LastName.Substring(0, 1) == "S"
select c

This reults in the following WHERE clause:

WHERE N'S' = (SUBSTRING([Extent1].[LastName], 0 + 1, 1))