Query Builder Methods
The ObjectQuery class supports both LINQ to Entities and Entity SQL queries against a conceptual model. ObjectQuery also implements a set of query builder methods that can be used to sequentially construct query commands that are equivalent to Entity SQL. The following are the query builder methods of ObjectQuery, along with the equivalent Entity SQL statements:
| ObjectQuery method | Entity SQL statement |
|---|---|
Each query builder method returns a new instance of ObjectQuery. This enables you to construct a query the result set of which is based on the operations of the sequence of preceding ObjectQuery instances. The following example demonstrates how to use the Where method to filter returned Product objects by ProductID.
' Return Product objects with the specified ID.
Dim query As ObjectQuery(Of Product) = context.Products.Where("it.ProductID = @product", New ObjectParameter("product", productId))
// Return Product objects with the specified ID.
ObjectQuery<Product> query =
context.Products
.Where("it.ProductID = @product",
new ObjectParameter("product", productId));
Because an ObjectQuery implements IQueryable and IEnumerable, it is possible to combine the query builder methods implemented by ObjectQuery with LINQ-specific standard query operator methods, such as First or Count. Unlike query builder methods, LINQ operators do not return an ObjectQuery. For more information, see the Standard Query Operators Overview topic in the Visual Studio 2008 documentation.
Selecting Data
By default, an ObjectQuery returns zero or more entity objects of a specific type. Calling subsequent query methods, such as Where and OrderBy, affects the collection of objects returned by the original ObjectQuery. Some methods, such as Select and GroupBy, return a projection of the data as a DbDataRecord instead of an entity type. For more information, see Object Queries. The following example returns a collection of DbDataRecord objects that contain nested SalesOrderHeader entity types.
' Define a query that returns a nested
' DbDataRecord for the projection.
Dim query As ObjectQuery(Of DbDataRecord) = context.Contacts.Select("it.FirstName, it.LastName, it.SalesOrderHeaders") _
.Where("it.LastName = @ln", New ObjectParameter("ln", lastName))
// Define a query that returns a nested
// DbDataRecord for the projection.
ObjectQuery<DbDataRecord> query =
context.Contacts.Select("it.FirstName, "
+ "it.LastName, it.SalesOrderHeaders")
.Where("it.LastName = @ln", new ObjectParameter("ln", lastName));
Although query builder methods are applied sequentially, it is possible to construct the same type of nested sub-queries that are supported by Entity SQL. To do this, you must include the sub-query as Entity SQL in the method. The following example uses an Entity SQL SELECT sub-query within the Select method to include LastName records, nested in a result set and sorted alphabetically by the first letter of the last name:
' Define the query with a GROUP BY clause that returns
' a set of nested LastName records grouped by first letter.
Dim query As ObjectQuery(Of DbDataRecord) = _
context.Contacts.GroupBy("SUBSTRING(it.LastName, 1, 1) AS ln", "ln") _
.Select("it.ln AS ln, (SELECT c1.LastName FROM AdventureWorksEntities.Contacts AS c1 " & _
"WHERE SubString(c1.LastName, 1, 1) = it.ln) AS CONTACT").OrderBy("it.ln")
// Define the query with a GROUP BY clause that returns
// a set of nested LastName records grouped by first letter.
ObjectQuery<DbDataRecord> query =
context.Contacts
.GroupBy("SUBSTRING(it.LastName, 1, 1) AS ln", "ln")
.Select("it.ln AS ln, (SELECT c1.LastName " +
"FROM AdventureWorksEntities.Contacts AS c1 " +
"WHERE SubString(c1.LastName, 1, 1) = it.ln) AS CONTACT")
.OrderBy("it.ln");
Note
Use the ToTraceString method to see the data source command that will be generated by an ObjectQuery. For more information, see Object Queries.
Aliases
Query builder methods are applied sequentially to construct a cumulative query command. This means that the current ObjectQuery command is treated like a sub-query to which the current method is applied.
Note
The CommandText property returns the command for the ObjectQuery instance.
In a query builder method, you refer to the current ObjectQuery command by using an alias. By default, the string "it" is the alias that represents the current command, as in the following example:
' Return Product objects with a standard cost
' above 10 dollars.
Dim cost = 10
Dim productQuery As ObjectQuery(Of Product) = context.Products.Where("it.StandardCost > @cost")
productQuery.Parameters.Add(New ObjectParameter("cost", cost))
int cost = 10;
// Return Product objects with a standard cost
// above 10 dollars.
ObjectQuery<Product> productQuery =
context.Products
.Where("it.StandardCost > @cost", new ObjectParameter("cost", cost));
When you set the Name property of an ObjectQuery, that value become the alias in subsequent methods. The following example extends the previous one by setting name of the ObjectQuery to "product" and then using this alias in the subsequent OrderBy method:
' Return Product objects with a standard cost
' above 10 dollars.
Dim cost = 10
Dim productQuery As ObjectQuery(Of Product) = context.Products.Where("it.StandardCost > @cost")
productQuery.Parameters.Add(New ObjectParameter("cost", cost))
' Set the Name property for the query and then
' use that name as the alias in the subsequent
' OrderBy method.
productQuery.Name = "product"
Dim filteredProduct As ObjectQuery(Of Product) = productQuery.OrderBy("product.ProductID")
int cost = 10;
// Return Product objects with a standard cost
// above 10 dollars.
ObjectQuery<Product> productQuery =
context.Products
.Where("it.StandardCost > @cost", new ObjectParameter("cost", cost));
// Set the Name property for the query and then
// use that name as the alias in the subsequent
// OrderBy method.
productQuery.Name = "product";
ObjectQuery<Product> filteredProduct = productQuery
.OrderBy("product.ProductID");
Parameters
All query builder methods that take an Entity SQL string input also support parameterized queries. Parameter names in Entity SQL are defined in query expressions with the at (@) symbol as a prefix. For more information, see Parameters. Parameters are passed to query builder methods as an array of ObjectParameter instances. The following example passes two parameters to the Where method:
' Get the contacts with the specified name.
Dim contactQuery As ObjectQuery(Of Contact) = context.Contacts.Where("it.LastName = @ln AND it.FirstName = @fn", _
New ObjectParameter("ln", lastName), New ObjectParameter("fn", firstName))
// Get the contacts with the specified name.
ObjectQuery<Contact> contactQuery = context.Contacts
.Where("it.LastName = @ln AND it.FirstName = @fn",
new ObjectParameter("ln", lastName),
new ObjectParameter("fn", firstName));
Considerations for Using Parameters
The following considerations apply when using parameters with query builder methods:
The parameters passed to query builder methods are aggregated by subsequent instances of ObjectQuery in the sequence. They can be accessed using the Parameters property. After they have been added, parameters can be removed from the collection and the collection can be cleared, as long as the query has not been compiled or executed. Parameter names cannot be changed, but values can be changed at any time.
Parameters must be unique in the ObjectParameterCollection. There cannot be two parameters in the collection with the same name.
When using composition methods, such as Union, UnionAll, Intersect and Except, the parameter collections are merged. An exception is thrown when the sets of parameters are incompatible, incomplete, or when the same name exists in the parameter collections of both queries.