Chapter 9. An alternative way of querying: Entity SQL

 

 

In this chapter, we’ll cover one of the hidden gems of Entity Framework: Entity SQL. LINQ to Entities has made this language almost obsolete, and developers don’t feel comfortable with it—its string-based nature is less expressive than the object-oriented LINQ. But thinking that Entity SQL is useless is a huge mistake, because some types of queries are easier to write using such a language.

In the first part of the chapter, we’ll discuss the querying capabilities of Entity SQL, focusing on different types of common operations, exactly as we did in chapter 4 when discussing LINQ to Entities. After that, we’ll explain how to easily use query-builder methods to use Entity SQL in combination with the ObjectContext. Later, we’ll talk about some advantages Entity SQL has over LINQ to Entities and look at the Entity Client data provider, which is the lowest level of Entity Framework and is used to interact almost directly with the database.

We’ll begin by looking at the basics of querying.

 

 

9.1. Query basics

Entity SQL’s SQL-like structure makes it more familiar for developers. Let’s look at this similarity by analyzing a simple Entity SQL query. The following query retrieves all orders.

Listing 9.1. A simple Entity SQL query

SELECT
VALUE c
FROM OrderITEntities.Orders
AS c

The SELECT keyword is mandatory, and each query must begin with it. After SELECT, you must specify what data you want to retrieve. In this case, you’re retrieving a known CLR type, so you use the keyword VALUE. Next, you use the FROM keyword, followed by the full name of the entity set that you’re querying. Finally, you specify the alias of the entity set with the AS keyword.

 

Note

Unlike in SQL, the alias is mandatory and is commonly referred to as the defining variable. This is important, because in Entity SQL queries, you always refer to a property starting from the defining variable.

 

You’ve now written your first Entity SQL query. There are several ways to execute it:

• Through ObjectContext
• Through ObjectContext with query-builder methods
• Through Entity Client

For now, you’ll use ObjectContext features to execute Entity SQL queries. In section 9.8, we’ll talk about query-builder methods, and in section 9.9 we’ll show you how to use Entity SQL in combination with Entity Client.

To execute an Entity SQL query through the ObjectContext class, you have to invoke its CreateQuery<T> method, passing the Entity SQL query as the parameter, and the return type as the generic parameter. Here’s an example of consuming such a method:

C#

using (var ctx = new OrderITEntities())
{
  ObjectQuery<Order> result = ctx.CreateQuery<Order>(
    "SELECT VALUE c FROM OrderITEntities.Orders AS c");

  foreach(var item in result)
  {
    //code that handles orders
  }
}

VB

Using ctx = New OrderITEntities()
  Dim result = ctx.CreateQuery(Of Order)(
    "SELECT VALUE c FROM OrderITEntities.Orders AS c")

  For Each item As var In result
    'code that handles orders
  Next
End Using

The result variable contains a list of Order objects that you can use for whatever you need. Keep in mind that, as for LINQ to Entities, the query isn’t executed until the data are actually used by the code. This means the query is executed when the code reaches the foreach loop and not before.

Now that you understand the basics of Entity SQL, we can move on and start covering queries. As we mentioned, we’ll follow the same path as chapter 4, so we’ll begin with filtering and then move to projecting, grouping, sorting, and joining.

 

Note

We won’t show the context instantiation in the examples again. The ctx variable will represent it.

 

The first query we looked at returns all orders without applying any filters. In the next section, we’ll look at how to restrict the data returned.

9.2. Filtering data

Filtering the data you want returned is the most common operation of a query. Entity SQL performs this basic operation exactly as SQL does, with the WHERE clause. The following listing shows how to retrieve orders that must be shipped to New York.

Listing 9.2. Retrieving all orders shipped to New York

C#

var result = ctx.CreateQuery<Order>("SELECT VALUE c FROM OrderITEntities.Orders as c WHERE c.ShippingAddress.City =  @city");
result.Parameters.Add(new ObjectParameter("city", name));
return result.ToList();

VB

Dim result = ctx.CreateQuery(Of Order)("SELECT VALUE c FROM OrderITEntities.Orders as c WHERE c.ShippingAddress.City  = @city ")
result.Parameters.Add(New ObjectParameter("city", name))
Return result.ToList()

In the WHERE clause, two features come up clearly. The first is the syntax when dealing with complex properties. When you refer to the scalar value of a complex property, you have to reach it as if you were navigating to it in VB or C# code.

The second feature is the parameter syntax, which is the same syntax used when executing queries through the classic ADO.NET provider for SQL Server. After declaring the parameter in the query, you have to create an ObjectParameter and add it to the collection that contains parameters for the query.

 

Note

From now on, we won’t show the code required to run the query; we’ll focus on the Entity SQL syntax.

 

The query in listing 9.2 is fairly simple and deals with only one entity. In the real world, such queries are very rare; usually, multiple related entities are involved in a query. Understanding how to work with associations is fundamental to writing Entity SQL queries, and we’ll look at that next.

9.2.1. Working with associations

You know that in a model, associations are one-to-one, one-to-many, or many-to-many. Let’s start with one-to-one associations, like the one between an order and its customer.

Filtering with a Single Association

Filtering based on a single association is easy: you navigate to the property as if you were using it in code. Because the property represents a single instance, it’s seen as an extension of the main object, so its use is trivial.

The following snippet shows an example of filtering on an association and retrieving all orders that are placed by customers in New York:

SELECT VALUE o FROM OrderITEntities.Orders AS o
WHERE o.Customer.BillingAddress.City  = 'New York'

Like LINQ to Entities and unlike SQL, you don’t have to worry about joins between tables. The Entity Framework generator creates the SQL, making use of the mapping information.

Filtering with Collection Associations

Filtering based on a collection property is a bit more complicated than filtering based on a single reference property. This is because the filter must be applied on a value calculated on the collection data. It may be a count, a sum, or the mere existence of data in the associated collection.

For example, the next snippet retrieves orders that have at least one detail related to a product whose brand property is MyBrand:

SELECT VALUE o FROM OrderITEntities.Orders as o
WHERE EXISTS
  (SELECT d FROM o.OrderDetails AS d WHERE d.Product.Brand = 'MyBrand')

EXISTS is an Entity SQL function that’s equivalent to the Any method of LINQ to Entities and to the SQL EXISTS clause. In this case, it’s used to filter the orders based on the presence of at least one detail that contains a product with brand MyBrand.

 

Note

The LINQ to Entities syntax is different than that used by Entity SQL, but if you compare the SQL generated by the two languages, you’ll find it’s exactly the same. The only difference is in the parameter name; in LINQ to Entities, you can’t specify parameter names, whereas in Entity SQL you’re forced to do so.

 

Checking for the existence of a child element in a collection isn’t the only filter you may want to apply. Often, an aggregated value is the discriminator for a filter. For example, if you wanted to retrieve all orders with no discount, you could take those where the sum of the discount in the details was 0, as in the following snippet:

SELECT VALUE o FROM OrderITEntities.Orders as o
WHERE SUM(SELECT VALUE d.discount FROM o.OrderDetails as d) = 0

As you can see, the SUM function takes as input another query that returns the discount of each detail. In SQL, it would be impossible to write a query with this structure because SUM can be used only in the SELECT, HAVING, and GROUP BY clauses. Here you can use it in the WHERE clause, which makes the query more natural from a logical point of view. Thumbs up for Entity SQL!

Another example of filtering based on aggregated data is searching for orders that contain more than one product. In OrderIT, due to the discount policy, a single product can appear twice in an order, so you can have two details but one product. That means you can’t simply count the details to find orders with more than one product sold. By using the COUNT and DISTINCT functions, you can easily determine how many different products are in an order, as follows:

SELECT VALUE o FROM OrderITEntities.Orders AS o
WHERE COUNT(
        SELECT VALUE DISTINCT(d.product.productid) FROM o.OrderDetails AS d
      ) > 1

The DISTINCT clause removes duplicates, and then COUNT adds up how many products are in the order. Like SUM, the COUNT clause is used inside the WHERE clause. As you can see, this way of querying is more intuitive than plain old SQL.

Filtering based on a collection isn’t difficult when you understand the mechanism of working on aggregated values. But filtering based on data is only part of the game. You can also perform filtering on a count basis. The simplest example is when you want to retrieve only the first n rows, or you need to page data in a grid on a web page.

9.2.2. Paging results

How many times have you been asked to create a report containing the top 10 of something? It’s a common request. The top 10 (or 100) request can be considered a filtering matter because you’re narrowing down the number of objects returned.

To take only the first n occurrences, you use the LIMIT clause. It must be put at the end of the statement, followed by the number of objects you want returned, as in the following snippet:

SELECT VALUE o FROM OrderITEntities.Orders AS o ORDER BY o.OrderId LIMIT 5

Notice the use of the Order By clause. It’s mandatory when using LIMIT. If you don’t specify the sort field explicitly, you’ll get a runtime exception.

Another typical requirement is paging data in web applications. This goal is easily achievable by combining the use of LIMIT with SKIP. The latter’s task is to skip n rows before retrieving the number of rows specified by LIMIT.

Suppose you want to retrieve the second page of a grid that shows five records per page. In this case, you have to skip the first five rows and limit the retrieved ones to five. That’s what the following example does:

SELECT VALUE o FROM OrderITEntities.Orders AS o
ORDER BY o.orderid SKIP 5 LIMIT 5

It’s not mandatory to put SKIP before LIMIT, but inverting them would result in the query returning the first five occurrences, because the parser considers clauses in the order it receives them.

All the preceding queries return full entities. More precisely, the preceding queries return a list of Order objects. Often you don’t need the full instance, but only some of its properties. What you need is a projection.

9.3. Projecting results

The process of projecting data in Entity SQL is natural. As in SQL, you specify the projected data in the Entity SQL SELECT clause. What’s different between a query that returns an entity and one that returns a projection is that the VALUE keyword must be omitted for the projection.

Projecting with Entity SQL is also different from projecting in LINQ to Entities because the output result is different. Due to its typed syntax, LINQ to Entities can return an anonymous type with the selected properties. Entity SQL is string-based, and the compiler can’t create a class from strings.

9.3.1. Handling projection results

The output of a projected query is an ObjectQuery<DbDataRecord> instance. As you know, DbDataRecord is the class that represents a row in a DbDataReader. This means you have to forget strong typing and use a generic object, as in the following listing.

Listing 9.3. Using projected data

C#

var result = ctx.CreateQuery<DbDataRecord>("SELECT c.CompanyId, c.Name FROM OrderITEntities.Companies AS c");
foreach (var item in result)
  Console.WriteLine((int)item["CompanyId"] + "-" + (string)item["Name"]);

VB

dim result = ctx.CreateQuery(Of DbDataRecord)("SELECT c.CompanyId, c.Name FROM OrderITEntities.Companies AS c")
For Each item In result
  Console.WriteLine(item("CompanyId") + "-" + item("Name"))
Next

Returning data as a list of DbDataRecord objects isn’t the most desirable approach. First, the invoking code needs to be aware of the column names. Second, you lose the strong typing and compile-time checking.

Returning a class (DTO) in this case is the best way to go, but it’s a more time-consuming approach because it involves more steps. First you have to create an ad hoc class to hold the data. Then you have to execute the query. Finally, you need to use a LINQ to Objects query to move data from the DbDataRecord to the class. You can see the code in the following listing.

Listing 9.4. Returning projected data as typed objects

As you can see, the invoking code is now much more readable. The dirty stuff of converting the projection from a DbDataRecord to an object is handled by the data-access method. The invoker doesn’t need to worry about typing and column names because it works directly with objects.

 

Note

Instead of creating the DTO class, you could create a Company instance, setting only the CompanyId and Name properties. Although using the Company class works perfectly, this approach results in an object that’s only partially filled with values. A DTO is much better.

 

Another thing you can do with projections is concatenate properties. You can join all the billing-address properties of an order into a scalar property, as in the following snippet. This makes data binding easier:

SELECT (o.ShippingAddress.Address + '-' + o.ShippingAddress.City + '-' +
  o.ShippingAddress.Country + '-' + o.ShippingAddress.ZipCode)
  AS ShippingAddress
FROM OrderITEntities.Orders AS o

You can also group properties into a complex property. In this case, the column in the DbDataRecord doesn’t hold a scalar value, but another DbDataRecord, which in turn contains the scalar values. This nifty method of organization allows you to nest complex properties infinitely.

Grouping is achieved through the ROW function. The following query shows an example that returns only the city and street address of an order grouped into a single property:

SELECT o.OrderId, o.OrderDate,
ROW(o.ShippingAddress.City, o.ShippingAddress.Address)
  AS ShippingAddress
FROM OrderITEntities.Orders AS o

The way you access the scalar columns doesn’t change, but when you access the column that contains the grouping property, the situation is different because it contains a DbDataRecord instance containing the scalar columns. Figure 9.1 displays how the grouping property is organized.

Figure 9.1. The ShippingAddress column in the DbDataRecord is a DbDataRecord representing the complex property.

As a result of this organization, you have to cast the inner DbDataRecord’s column to DbDataRecord in order to access the scalar column’s value. The next snippet shows the simplicity of the code:

C#

var sa = ((DbDataRecord)item["ShippingAddress"])["Address"];

VB

Dim sa = DirectCast(item("ShippingAddress"), DbDataRecord)("Address")

There’s nothing more you need to know about projections based on single entities, so let’s move on to projecting with associations.

9.3.2. Projecting with associations

It’s no surprise that associations can participate in projections. As with filtering, you have to distinguish between projections that involve one-to-many associations and projections with a one-to-one association. That’s the only similarity between projecting and filtering.

Projecting with Single Associations

Handling projections for single associations is simple. Because a single association can be seen as a sort of extension of the class, retrieving it is easy.

You have two options when projecting a single association: return a projection of the main entity plus the entire associated one, or return a projection of the main entity plus a projection of the associated one.

In the first case, you need to put the name of the associated property in the SELECT clause, as follows:

SELECT o.OrderId, o.OrderDate, o.Customer FROM OrderITEntities.Orders AS o

In the output result, the column containing the associated property directly exposes the object (Customer). To retrieve its data, you cast the column to Customer and then access its properties.

If you project the association too, you need to put the properties in the SELECT clause using the usual navigation syntax, like this:

SELECT o.OrderId, o.OrderDate, o.Customer.CompanyId, o.Customer.Name
FROM OrderITEntities.Orders AS o

The result is flat because all properties are scalar values. There’s no need to do any additional processing.

Projecting with Collection Associations

There are three main ways you can project with collection associations, depending on what you need:

• Return partial data from the main entity, plus all the data from the collection.
• Return partial data from both the main entity and the associated collection.
• Return calculated values from the list of associated data.

Let’s start with the first case.

A typical situation is when you retrieve parts of the selected orders and their full details. To do this in Entity SQL, you have to put the property in the SELECT clause. The column containing the collection holds a List<T> instance, where T is the type of object in the collection. The value of the column must be cast to List<T> and then iterated. This is what the next listing does.

Listing 9.5. Projecting an order plus full details

The second case, returning partial data from both the main entity and the associated collection, often happens when you retrieve parts of the selected orders, plus the quantities, IDs, and products from their details. In Entity SQL, to project collection data, you insert a subquery in the SELECT specifying the columns of the association you want to retrieve. The subquery is shown in bold in the following example.

SELECT o.OrderId, o.OrderDate,
  (SELECT d.OrderDetailId, d.Product.ProductId, d.Quantity
   FROM o.OrderDetails As d) as Details
FROM OrderITEntities.Orders AS o

The output result is a three-column DbDataRecord whose third column is a collection. The difference between this collection and the one generated by the previous query is that because it contains a projected detail, it holds a list of DbDataRecord instances.

Even if the objects are different, the concept still remains the same: to access the association data, you have to cast the column to List<DbDataRecord> and iterate over the items in the list. Figure 9.2 illustrates the column data in this case.

Figure 9.2. The result of projecting both orders and details

The last case, returning calculated values from a list of associated data, is common and useful when you need to retrieve orders and their total amounts. In this case, you don’t get a collection; you get a scalar value that is easier to manage. From the query perspective, the amount is calculated using a subquery, in the SELECT clause that returns only a property, as in the next snippet:

SELECT o.OrderId, o.OrderDate,
  SUM(SELECT VALUE (d.UnitPrice - d.Discount) * d.Quantity
      FROM o.OrderDetails As d) AS Total
FROM OrderITEntities.Orders AS o

The result is a list of DbDataRecord objects containing the OrderId, OrderDate, and Total columns. Because the Total column is a simple scalar value, it’s retrieved as a classic scalar column.

Now you understand two important pieces of Entity SQL: filtering and projecting. The next step is learning how to group data using this language.

9.4. Grouping data

Grouping data is a basic function of any advanced query language. SQL, LINQ, LINQ to Entities, and Entity SQL have this feature in their toolbox. Once again, grouping data in Entity SQL is similar to doing so in SQL. You use a GROUP BY clause to specify the grouping fields. That’s all.

As a first example, let’s extract the orders and group them by shipping city:

SELECT o.ShippingAddress.City,
  (SELECT VALUE o2
   FROM OrderITEntities.Orders AS o2
   WHERE o.ShippingAddress.City = o2.ShippingAddress.City) AS Orders
FROM OrderITEntities.Orders AS o
GROUP BY o.ShippingAddress.City

The output of this query is an ObjectQuery<DbDataRecord>, where each record contains a column for the shipping city and another for the orders, as shown in figure 9.3. To process the records extracted by the query, you need to iterate over them. Remember that the second column contains a List<Order> object and not a List<DbData-Record> object because the query didn’t perform any projection.

Figure 9.3. The result of a grouping query. The first column contains the grouping field—City, in this case. The second column contains the orders shipped to the city.

You can also group data on more than one property. For instance, you might have to group orders by shipping city and zip code. To do this, you’d include both columns in the GROUP BY clause, separating them with a comma (,) as in the following query:

SELECT o.ShippingAddress.City, o.ShippingAddress.ZipCode,
  (SELECT VALUE o2
   FROM OrderITEntities.Orders AS o2
   WHERE o.ShippingAddress.City = o2.ShippingAddress.City) AS Orders
FROM OrderITEntities.Orders AS o
GROUP BY o.ShippingAddress.City, o.ShippingAddress.ZipCode

The DbDataRecord output contains the columns in the SELECT clause.

Data retrieved by the query can be projected; you aren’t forced to return an entire entity. To do so, remove the VALUE keyword from the SELECT clause, and select the properties you need, as shown in the next snippet. The difference between this and the previous example is that the column that contains the projected data isn’t a List<Order> instance but a List<DbDataRecord> instance:

SELECT o.ShippingAddress.City,
  (SELECT o2.OrderId, o2.OrderDate
   FROM OrderITEntities.Orders AS o2
   WHERE o.ShippingAddress.City = o2.ShippingAddress.City) AS details
FROM OrderITEntities.Orders AS o
GROUP BY o.ShippingAddress.City

Sometimes you may need to filter data after it’s grouped. More often, you may need to filter on an aggregated value. For instance, you may want to retrieve data only for those cities that have more than two orders.

As with SQL, the solution is the HAVING clause. It must be put after the GROUP BY clause and can specify any valid Entity SQL expression (COUNT is used in the following example):

SELECT o.ShippingAddress.City,
  (SELECT VALUE o2 FROM OrderITEntities.Orders AS o2
   WHERE o.ShippingAddress.City = o2.ShippingAddress.City) AS details
FROM OrderITEntities.Orders AS o
GROUP BY o.ShippingAddress.City
HAVING COUNT(o.ShippingAddress.City) > 2

As you can see, grouping data is a fairly easy task. By mixing GROUP BY and HAVING, you can perform powerful queries. Next, it’s time to look at sorting.

9.5. Sorting data

Sorting in Entity SQL is identical to sorting in SQL. The ORDER BY clause is used, followed by a list of comma-separated columns that the sorting is based on. You can even set the sorting direction with the DESC and ASC keywords:

Here’s an example of sorting on one column:

SELECT VALUE o
FROM OrderITEntities.Orders AS o
ORDER BY o.ShippingAddress.City

And here’s a similar example of sorting on multiple columns:

SELECT VALUE o
FROM OrderITEntities.Orders AS o
ORDER BY o.ShippingAddress.City, o.ShippingAddress.ZipCode

Sorting is a simple matter of using the ORDER BY clause. But when associations are involved, you can do interesting things.

9.5.1. Sorting data based on associations

The first thing you may want to do when associations are involved is to sort data on the basis of an aggregated value calculated on a collection association. For instance, the next example sorts the orders based on their total amounts:

SELECT VALUE o
FROM OrderITEntities.Orders AS o
ORDER BY
  Sum(SELECT VALUE (d.UnitPrice - d.Discount) * d.Quantity
      FROM o.OrderDetails AS d)

What’s great in this query is that you can use a subquery in the ORDER BY clause. This is something SQL doesn’t allow—it’s another Entity SQL feature that makes life easier.

Another interesting Entity SQL feature is the ability to retrieve an entity, or a projection of it, and have only its associated collection data sorted. The following example retrieves the orders and sorts their details by the items sold:

SELECT o.OrderId, o.OrderDate,
  (Select VALUE d
   FROM o.OrderDetails AS d
   ORDER BY d.Quantity) AS Details
FROM OrderITEntities.Orders AS o

When it comes to associations that point to a single reference, sorting is trivial. The reference points to a single instance, so you can navigate to properties as usual. In the following snippet, the orders are sorted by their customer city:

SELECT VALUE o
FROM OrderITEntities.Orders AS o
ORDER BY o.Customer.ShippingAddress.City

That’s all you need to know about sorting. In the next section, we’ll talk about another feature: joins between objects.

9.6. Joining data

You already discovered in chapter 4 that manually joining data is unnecessary because Entity Framework automatically takes care of relationships when generating SQL. But there are some situations where you may need to manually join objects, so you must be aware of this mechanism.

Once again, Entity SQL syntax for joins is identical to SQL syntax; you use the JOIN clause. In the following example, orders and companies are joined by the shipping city and the customer ID:

SELECT o.OrderId, o.OrderDate
FROM OrderITEntities.Orders AS o
JOIN OrderITEntities.Companies AS c ON o.Customer.CompanyId = c.CompanyId
  AND o.ShippingAddress.City = c.ShippingCity

The last feature needed to query data is the inheritance-querying mechanism. This is a bit different from what you’ve seen so far, because it’s something that doesn’t exist in SQL.

9.7. Querying for inheritance

When talking about inheritance, two types of queries can be performed:

• Queries that return objects, exposing them through the base class
• Queries that return only objects of a specified type

The first type of query is pretty simple and doesn’t involve any Entity SQL inheritance knowledge. Because the entity set you query exposes objects through the base class, you perform a query using properties on that class. At runtime, Entity Framework generates the concrete classes by analyzing the mapping information.

The second type of query requires some explanation, because Entity SQL inheritance features come into play via the OFTYPE function. This function is placed immediately after the FROM clause and accepts the full entity-set name and the full type name to be retrieved, as shown in the next snippet:

SELECT VALUE c
FROM OFTYPE(OrderITEntities.Companies, OrderIT.Model.Customer) AS c

Naturally, you can add filters. Because this query retrieves customers, you can add a WHERE clause on the customer properties. The next snippet looks for customers who are enabled to use the web service:

SELECT VALUE c
FROM OFTYPE(OrderITEntities.Companies, OrderIT.Model.Customer) AS c
WHERE c.WSEnabled = true

Not only can you filter data, but you can also project, sort, and group it any way you want.

This is all you really need to know to consider yourself an Entity SQL master, but Entity SQL is wide subject. It has a lot of functions and operators. MSDN has great documentation for the Entity SQL Language; you can find the MSDN language reference at http://mng.bz/f3ew.

So far, you’ve used the CreateQuery<T> method for querying, passing in the entire Entity SQL string. To simplify the creation of Entity SQL queries, you can use a set of methods, named query-builder methods, to shorten the code.

9.8. Using query-builder methods

Query-builder methods are a convenient way to organize Entity SQL queries and can often simplify dynamic query creation. These methods do exactly what their name suggests: they let you build an Entity SQL query using methods instead of having to write the entire Entity SQL on your own. Query-builder methods don’t cover all aspects of querying, but, in our experience, 90% of a project’s queries can be developed using them.

Query-builder methods aren’t extension methods like the LINQ to Entities methods. They’re included in the ObjectQuery<T> class and implement the fluent technique, which allows them to be chained, because they return an ObjectQuery<T> instance. The query-builder methods are listed in table 9.1.

Table 9.1. The query-builder methods

Method	Functionality
Distinct	Specifies that returned data must be unique
Except	Limits query results by excluding results based on the results of another object query
Include	Eager-loads related associations
Intersect	Limits query results by including only the results that exist in another object query
OfType<T>	Retrieves only instances of objects of the specified type
OrderBy	Defines the sorting properties
Select	Defines the properties to retrieve
SelectValue<T>	Defines the properties to retrieve, and returns them in the object specified
Skip	Sorts the data by a key, and skips the first n occurrences
Top	Returns only the first n occurrences
Union	Merges the results of two queries, removing duplicates
UnionAll	Merges the results of two queries
Where	Defines filters

Let’s see a simple query in action. This one retrieves all orders shipped to New York:

C#

var result = ctx.Orders.Where("it.ShippingAddress.City = 'New York'");

VB

Dim result = ctx.Orders.Where("it.ShippingAddress.City = 'New York'")

This query is simple; it uses the Where method to filter returned data. Despite its simplicity, there is one thing to point out: the it defining variable. it is the name of the defining variable, but the name can be changed programmatically using the ObjectQuery<T> instance’s Name property.

Note that you must be aware of some caveats when changing the defining variable. It’s bound to the chaining mechanism, which we’ll cover next.

9.8.1. Chaining methods

Chaining multiple query-builder methods together is a simple and powerful mechanism for shaping a query at runtime. Suppose you have a method with many filter parameters. When you create the query, you have to apply the filters only for the input parameters that have a value. In this case, you can call the Where method multiple times, as shown here.

Listing 9.6. Chaining query-builder methods

C#

ObjectQuery<Order> result = ctx.Orders;
if (city != String.Empty)
  result = result.Where("it.ShippingAddress.City = '" + city + "'");
if (zipCode != String.Empty)
  result = result.Where("it.ShippingAddress.ZipCode = '" + zipCode + "'");

VB

Dim result As ObjectQuery<Order> = ctx.Orders
If city <> String.Empty
  result = result.Where("it.ShippingAddress.City = '" + city + "'")
End If
If zipCode <> String.Empty
  result = result.Where("it.ShippingAddress.ZipCode = '" + zipCode + "'")
End If

The Entity SQL code generated by a chaining method isn’t necessarily what you may expect. If you were to create the Entity SQL string on your own, you would probably create a WHERE clause with multiple ANDs. Entity SQL behaves differently, generating a nested query for each call to the Where method. Although this may seem to be a cumbersome approach, it guarantees the needed flexibility in code generation.

The following query is the Entity SQL generated by the code in listing 9.6, obtained by inspecting the CommandText property of the ObjectQuery<T> class:

SELECT VALUE it
FROM
  (
    SELECT VALUE it
    FROM ([Orders]) AS it
    WHERE it.ShippingAddress.City = 'New York'
  ) AS it
WHERE it.ShippingAddress.ZipCode = '98765'

As you see, the second WHERE causes a query-nesting process.

Earlier, we said you can change the defining variable name programmatically, but you should be aware of a caveat when doing so. When you have just one method call, you can change the defining variable name and then use it in the method:

VB

ctx.Orders.Name = "o"
ctx.Orders.Where("o.ShippingAddress.ZipCode = '98765'")

C#

ctx.Orders.Name = "o";
ctx.Orders.Where("o.ShippingAddress.ZipCode = '98765'");

But if you’re chaining multiple methods, you have to change the name before each method. This is mandatory, because queries are nested, and it is automatically used as the defining variable name for each one:

VB

Dim result As ObjectQuery<Order> = ctx.Orders
result.Name = "o"
result = result.Where("o.ShippingAddress.ZipCode = '98765'")
result.Name = "o2"
result = result.Where("o2.ShippingAddress.ZipCode = '98765'")

C#

ObjectQuery<Order> result = ctx.Orders;
result.Name = "o";
result = result.Where("o.ShippingAddress.ZipCode = '98765'");
result.Name = "o2";
result = result.Where("o2.ShippingAddress.ZipCode = '98765'");

You should now understand why, despite its power, Entity SQL isn’t famous among Entity Framework adopters. Its string-based nature makes it less appealing than LINQ to Entities. Nevertheless, query-builder methods make things easier and are fantastic when the query must be created at runtime. Entity SQL is friendlier than LINQ to Entities in such cases.

9.8.2. Query-builder methods vs. LINQ to Entities methods

A typical situation when a query must be built dynamically is when the sorting field is decided at runtime based on user input. This often happens if the user can sort a grid by clicking a column header. Due to LINQ to Entities’ strong typing nature, this task can be handled only by using a set of if or switch statements.

Because Entity SQL is string-based, query-builder methods, and Entity SQL in general, make runtime construction easy. The following listing shows the code required for runtime query construction using both LINQ to Entities and query-builder methods.

Listing 9.7. Chaining query-builder methods vs. chaining LINQ to Entities methods

Wow. Just one statement , as compared to seven . Isn’t that great? You should now understand why we said that Entity SQL in combination with query-builder methods is more useful than LINQ to Entities in such cases.

Listing 9.6 uses string concatenation to create a query; this is bad. We just wanted to demonstrate that Entity SQL offers great flexibility in creating a query. But in that sort of situation, there’s no doubt that the best way to go is to use parameters. We introduced them in section 9.2, and we’ll discuss them in more detail in the next section.

9.8.3. Using parameters to prevent injection

Lots of queries are parameterized through arguments. For instance, a query that looks for customers by their billing city accepts the customer’s city as an argument. In this type of query, we strongly recommend you to use parameters to avoid SQL injection attacks. You should never concatenate user input to create Entity SQL code. If you have already developed solutions using ADO.NET, you should be accustomed to parameters. If not, you’ll find them easy to use.

 

Note

You can learn more about SQL injection and about how parameters help you in avoiding this type of attack from the MSDN article “SQL Injection” at http://mng.bz/76s4.

 

To simplify the use of parameters, each query-builder method that requires parameters (the majority) accepts a list of ObjectParameter objects as a second argument. The parameters are accepted as param/ParamArray, so you can declare and instantiate them in a single statement, as shown in the following snippet:

C#

ctx.Orders.Where("it.ShippingAddress.City = @city",
  new ObjectParameter("city", city));

VB

ctx.Orders.Where("it.ShippingAddress.City = @city",
  New ObjectParameter("city", city))

The SQL code generated by a LINQ to Entities query uses parameters, so there’s nothing you have to do to use them; with Entity SQL, you must explicitly use parameters in your query.

We’ve never seen it happen, but if you encounter a situation where you can’t use parameters, you’ll have to check the user input for malicious data. Even if the user input is smarter than your validation process, it still has to perform a complicated action: inject code that’s perfectly valid for Entity SQL and in turn SQL. Although Entity SQL and SQL are similar, there are subtle differences. For instance, Entity SQL doesn’t support the semicolon (;) character, which is often used in SQL injection to interrupt a SQL statement and create a new malicious one.

This doesn’t mean Entity SQL is more secure than plain old ADO.NET, but it surely makes life harder for attackers. Nevertheless, the greater difficulty of injection doesn’t mean you can lower your defenses; always take care of this aspect of security.

Naturally, all of these considerations apply to Entity SQL in general, and not only to query-builder methods.

Parameter Translation

When an Entity SQL query with parameters is translated into SQL, the generated SQL uses the parameter feature of each database engine. For instance, OrderIT uses SQL Server, which has the sp_executesql stored procedure. This stored procedure accepts a string that represents both the SQL string and a set of parameters that are used to execute the query.

Generic solutions like O/RM have a generic structure that makes them ready for any situation. When it comes to parameters, this means many O/RM tools use the maximum size of the parameter type. For instance, a string parameter is translated in SQL as a varchar(8000).

This is one place where Entity Framework is optimized. It uses the real size of the value of the parameter, instead of assuming the maximum size for the type. Although it may seem negligible, this is a huge optimization.

So far, we’ve used the context as the gateway to the data. But Entity Framework has another layer that lets you communicate with the database: the Entity Client data provider.

9.9. Working with the Entity Client data provider

You learned in chapter 1 that the Object Services layer isn’t directly connected to the database. It’s situated on the Entity Client data provider (Entity Client from now on), which is an ADO.NET data provider built for Entity Framework. Entity Client is responsible for many Entity Framework internal behaviors (like transforming data read from the database into a format that’s later transformed into objects by the Object Services layer), and it uses the ADO.NET data provider specified in the connection string to physically interact with the database.

Because the Entity Client is an ADO.NET data provider, like OracleClient, SqlClient, and OleDb, it contains a set of classes that implement the standard ADO.NET base classes:

• EntityConnection—Inherits from DbConnection and represents the connection to a database. It adds some functionality to the base class.
• EntityTransaction—Inherits from DbTransaction and represents a transaction to the database. It’s a wrapper needed to implement a full ADO.NET provider.
• EntityCommand—Inherits from DbCommand and represents the class necessary to execute any command to the database.
• EntityParameter—Inherits from DbParameter and represents a parameter of a query.
• EntityDataReader—Inherits from DbDataReader and contains the result of a query executed by the EntityCommand class.

If you’re familiar with ADO.NET development, these classes will be nothing new.

Keep in mind that the Entity Client doesn’t physically connect to the database; it relies on the underlying ADO.NET provider. Entity Client is a wrapper that works with the EDM to generate SQL from queries and to shape the results of queries. The only language you can use to query the database via Entity Client is Entity SQL (LINQ to Entities works only with Object Services).

The following listing shows how you can retrieve orders by writing an Entity SQL query, not a SQL one.

Listing 9.8. Retrieving orders with Entity Client

C#

using (var conn = new EntityConnection(
  Parameters.ConnectionString))
{
  using (EntityCommand comm = new EntityCommand("SELECT VALUE o FROM OrderITEntities.Orders AS o", conn))
  {
    conn.Open();
    EntityDataReader reader =
      comm.ExecuteReader(
        CommandBehavior.SequentialAccess);
    while (reader.Read())
    {      ...
    }
  }
}

VB

Using conn As New EntityConnection(
  Parameters.ConnectionString)
  Using comm As New EntityCommand("SELECT VALUE o FROM OrderITEntities.Orders AS o", conn)
    conn.Open()
    Dim reader As EntityDataReader =
      comm.ExecuteReader(
        CommandBehavior.SequentialAccess)
    While reader.Read()
      ...
    End While
  End Using
End Using

This example is quite simple: it creates a connection, creates a command, executes a query, and consumes data. If you have written at least one query in your life, you should be able to follow what it does. Let’s focus on the objects that are used in it, starting with the connection.

9.9.1. Connecting with EntityConnection

EntityConnection is the class that establishes a connection with the underlying ADO.NET provider. Two main points are worth looking at here.

The first point to highlight is the connection string that’s passed to the EntityConnection class. In chapter 3, you learned the different ways you can pass the connection string to the ObjectContext. The same paths can be followed when using the EntityConnection class. You can pass the full connection string, or a formatted string that contains the keyword Name followed by an equal sign (=) and then the name of the connection string in the configuration file:

Name=OrderITEntities

The second thing to note is that EntityConnection exposes the StoreConnection property that represents the physical connection to the database. The property is of DbConnection type, but the real underlying type is the one you specified in the connection string.

Having access to the real database connection turns out to be particularly useful in scenarios that aren’t natively supported by Entity Framework. For instance, Entity Framework doesn’t support stored procedures that return multiple resultsets. To bypass this limitation, you can retrieve the physical connection to the database, launch the stored procedure, and then handle the result manually.

 

Note

You can also use this method to execute native queries on the database. But although this is technically possible, we strongly discourage it. As you’ll discover in the next chapter, you have other ways to launch native commands against the database.

 

EntityConnection is a gateway to the metadata of the EDM too. Through its GetMetadataWorkspace method, you can access all of the information of the EDM and get information about the objects, their relationships, and their mapping to the database. We’ll talk more about this subject in chapter 12.

9.9.2. Executing queries with EntityCommand

The EntityCommand class doesn’t introduce any new concepts that you haven’t seen in any other ADO.NET-specific provider. The only additional feature it introduces is the ability to enable or disable the caching of the query. The property involved is EnablePlanCaching, which is a Boolean whose default value is true. We’ll talk more about the plan-caching feature in chapter 19, which is dedicated to performance.

The ExecuteReader method has a peculiarity: you have to pass the Command-Behavior.SequentialAccess parameter. This indicates that when reading columns from the reader, you have to access them sequentially, not randomly. If you access the columns randomly, you’ll get an InvalidOperationException. The reason for this requirement is to avoid excessive memory usage. The following snippet shows examples of correct and incorrect code:

The situation doesn’t change if you access columns by name. You always have to access them in sequential order.

The EntityCommand class has a unique feature: the way it sizes parameters. In the previous section, you saw that Entity Framework automatically adjusts the size and type of a query parameter based on the value it contains. Although that’s a good thing, in some scenarios you may want full control over parameter sizes or types. In this situation, the EntityParameter class comes into play.

EntityParameter isn’t any different from other providers. Its instantiation process, properties, and methods are exactly the same. The fact that you can control parameter type and size is all that makes it different from the LINQ to Entities and ObjectParameter implementations of parameters.

9.9.3. Processing query results with EntityDataReader

Hardly surprisingly, the EntityDataReader class is the container for data returned by the EntityCommand’s ExecuteReader method. As you’ll discover, this DbDataReader implementation is likely the most different from any of the other providers you have seen. In terms of query results, what’s their format in the data reader? Are the results shaped like the database query, or are they formatted like classes? The answer is twofold. The data is shaped like classes, but it’s held in a generic structure like the DbDataRecord instead of a typed object.

This has two effects. First, when the data is retrieved from the database, the Entity Client uses the projection, if present, or queries the EDM to understand how the data should be returned to the code. Figure 9.4 illustrates how the data is organized in logical, not in database, format.

Figure 9.4. The structure of an EntityDataReader record returned by listing 9.8

Second, although you work with a data reader, you aren’t connected to the database. The underlying ADO.NET provider data reader that contained the data pulled off by the query has already been processed by the Entity Client. This may seem unusual if you’re accustomed to ADO.NET development.

Now you know how the data is shaped, but how do you access it? When the column holds a scalar value, you get the value by name or index; but when the column contains a complex property, you have to cast it to DbDataRecord and then access its scalar columns. In the case shown in figure 9.4, casting the ShippingAddress columns to DbDataRecord gives you access to the inner columns.

 

Note

Columns inside the inner DbDataRecord must be accessed sequentially too.

 

Wouldn’t it be good if you could transform a DbDataReader into objects? This would make your code easier to understand. Well, thanks to the ObjectContext class’s Translate method, this is possible.

Transforming a Dbdatareader into Objects

Working with a DbDataReader isn’t type safe and is extremely error prone. Working with objects is far more appealing. Thanks to the ObjectContext class’s Translate method, you can transform a DbDataReader into a list of objects in a single line of code. This method uses the same materialization process used by the ObjectContext’s ExecuteStoreQuery method: it sets properties by comparing their names with the columns in the reader.

The Translate method accepts a generic parameter representing the object that’s materialized and the DbDataReader instance, as shown in the following listing.

Listing 9.9. Materializing order details

What’s good about Translate is that it accepts a DbDataReader . Because DbDataReader is the base class for all ADO.NET data readers, you can use this method to materialize data readers from queries made through any ADO.NET provider (SQL Server, OLE DB, and so on). Furthermore, Translate isn’t bound to the EDM, so it can materialize any CLR object. The only rule is that all properties must have a counterpart in the data reader.

What’s bad about Translate is that because it uses the same materialization mechanism used by ExecuteStoreQuery, it suffers from the same limitations.

So far, you’ve used the Entity Client to query the database. In almost all cases, this is how you’ll use this layer. But occasionally, you may need to execute a Create, Update, Delete (CUD) operation that you can’t or don’t want to perform using the context. In that situation, the Entity Client is the only way to go.

9.9.4. Going beyond querying with Entity Client

Often there are scenarios where it’s wiser to adopt a manual solution, even if you could use Entity Framework. A typical example is when you need to perform a bulk operation. Suppose that in OrderIT you wanted to increase the price of all products by 5 percent.

If you handle this requirement the Entity Framework way, you’ll have to retrieve the all products, modify their prices, and send the updates back to the database. If you have 100 products, you’ll have to launch 101 commands: 1 for retrieval and 100 for the updates. Awful.

You can avoid this waste of resources by using a stored procedure that performs an update. You’ll learn exactly how to perform such a task in the next chapter, which discusses stored procedures; the point here is that you can invoke such a stored procedure using the Entity Client.

The method that allows you to invoke stored procedures is EntityCommand’s ExecuteNonQuery. To use this method, you set the CommandType property of the command to CommandType.StoredProcedure and set the command text to the name of the stored procedure. Naturally, everything must be mapped in the EDM, but you’ll see more about that in the next chapter.

 

 

9.10. Summary

In this chapter, you have seen the full querying potential of Entity SQL. You’re now aware of all the querying mechanisms of Entity Framework. The only part you haven’t learned about yet is the use of database functions in Entity SQL—that’s a subject we’ll cover in chapter 11.

After reading this chapter, you should understand how to use Entity SQL for projecting, grouping, filtering, and sorting data, and to perform other typical query-related tasks. You have even learned how to simplify the generation of Entity SQL queries by using query-builder methods. We also covered the subject of combining LINQ to Entities queries and query-builder methods to further ease query composition at runtime. You also learned about possible security breaches, and how to mitigate the problem by using parameters.

In the last part of the chapter, you learned how to use the Entity Client data provider to execute queries on a layer closer to the database and to process the results as generic objects and not as typed ones.

Now it’s time to investigate how Entity Framework deals with stored procedures. There’s a lot to learn about this subject, because you can both query and update data, and many scenarios aren’t particularly obvious.