Share via

Query

  • Article

Part of the Entity Framework FAQ .

6. Query

6.1. How is Span used? What is Span? (aka. How does the EF support eager-loading?)

The span feature makes it possible to pre-fetch related entities. Span can be defined as a builder method, "Include", which makes it simpler to specify span rules in LINQ to Entities queries. Include can be called on the ObjectQuery used in the from clause of the LINQ query.

Span makes it possible to specify that a query not only return an entity but return some set of related entities in a single round trip and automatically hook-up the graph. So, for instance, you could say that your customer query should also return the orders for each customer in a single round trip. We also have an automatic query re-write feature for the object layer that enables a feature we call Relationship Span where queries that retrieve entities will under-the-covers also return relationship information if the relationship is an EntityReference (rather than a collection). This is usually a quick operation since typically this relationship information is co-located in the relational database as a foreign key property, and it has the very useful property that for many cases entity graphs will automatically be hooked up as long as the relevant entities on either side of the relationship have been loaded. So, for example, if I query for a set of customers and then I separately do a query for a bunch of orders--any of those orders that are related to those customers will automatically bring along the relationship info and connect the orders up to the customers they go with. For more information, see Shaping Query Results (Entity Framework).

6.2. How can I do a filtered load of a relationship?

While the EF supports a nice eager-loading directive (Include described above), this directive and the Load method on EntityCollection and EntityReference always load the entire contents of a relationship. Sometimes, though, you might want to load only a subset of related entities. For information on how to do that, see this blog post and How to: Query Related Objects in an EntityCollection (Entity Framework).

6.3. How can I see the SQL which will be sent to the backend database as a result of my query?

If you are running against SQL Server, you can always use SQL Server Profiler to see what queries are sent to the database, but this only works with SQL Server, and it doesn’t work with the express edition. As an alternative, though, you can use the new method added in beta 3 of the EF, ToTraceString from either ObjectQuery or EntityCommand. For more information, see How to: View the Store Commands (Entity Framework).

6.4. How many Includes can I have in a query?

Each include will typically cause another join to show up in your query. At some point the query will become too complex to execute. It is always wise to test the performance of various ways to retrieve related entities, because for some scenarios Include is much faster because it means fewer roundtrips to the database, but for other scenarios additional roundtrips may be faster because of the simpler queries and reduction in redundant data which the extra joins incur. See forums.microsoft.com/Forums/ShowPost.aspx?PostID=3368976&SiteID=1 for some more thoughts. For more information, see Performance Considerations for Entity Framework Applications.

6.5. Why doesn’t relationship span happen with MergeOption.NoTracking?

The basic reason the EF doesn’t do relationship span with NoTracking queries is that NoTracking queries were designed to be as absolutely high performance as possible. We didn’t want to rewrite those queries to bring back additional data (which could possibly require a join to draw the data from a link table or something) unless the user explicitly asked for it. In the case of standard tracking queries, the stubs are so important that we decided to just turn it on by default, but for NoTracking queries that didn’t seem like the right answer.

While this info isn’t returned by default, you can usually rewrite the queries yourself to retrieve this information in a fairly straightforward fashion. For instance, if you were querying for orders and wanted to bring back the key of the related customer, then you could do something like this:

var query = ctx.CreateQuery<DbDataRecord>("select o, Ref(o.Customer) from Orders as o");

query.MergeOption = MergeOption.NoTracking;

7. LINQ to Entities

7.1. How can I write a linq query which restricts based on a many to many relationship?

For a many to many relationship, let’s say between Boy and Girl, the navigation properties Boy.Girl and Girl.Boy are of a collection type. Therefore we cannot use something like "girls.Contains(b.Girl)", this will cause the exception message because b.Girl is really a collection of Girls.

The correct way to write a join linq query would be:

var query = (

from girl in context.Girl //or some filtered subset

from boy in girl.Boy

select boy

).Distinct();

Pasted from <forums.microsoft.com/MSDN/ShowPost.aspx?PostID=2348831&SiteID=1>

7.2. How do I write a LINQ to Entities query which has the equivalent of the SQL “in” keyword?

In SQL, you might write a query that looks something like this: SELECT * FROM Foo WHERE blah IN (1, 3, 5, 7). With LINQ to Entities you might have a similar scenario except that you are selecting from an entityset and the list of values you want to compare against is stored in a LIST<T>. Unfortunately, the Entity Framework does not currently support collection-valued parameters. To work around this restriction, you can manually construct an expression given a sequence of values using the following utility method:

static Expression<Func<TElement, bool>> BuildContainsExpression<TElement, TValue>(

    Expression<Func<TElement, TValue>> valueSelector, IEnumerable<TValue> values)

{

    if (null == valueSelector) { throw new ArgumentNullException("valueSelector"); }

    if (null == values) { throw new ArgumentNullException("values"); }

    ParameterExpression p = valueSelector.Parameters.Single();

    // p => valueSelector(p) == values[0] || valueSelector(p) == ...

    if (!values.Any())

    {

        return e => false;

    }

    var equals = values.Select(value => (Expression)Expression.Equal(valueSelector.Body, Expression.Constant(value, typeof(TValue))));

    var body = equals.Aggregate<Expression>((accumulate, equal) => Expression.Or(accumulate, equal));

    return Expression.Lambda<Func<TElement, bool>>(body, p);

}

Using this utility method, you can rewrite:

var query1 = from e in context.Entities

             where ids.Contains(e.ID)

             select e;

as

var query2 = context.Entities.Where(

    BuildContainsExpression<Entity, int>(e => e.ID, ids));

8. Entity SQL and ObjectQuery Builder Methods

8.1. What is Entity SQL? Why introduce a new query language (eSQL); why not use SQL? 

SQL is the time proven query language for data access. However, since EDM introduces an enhanced data model which builds on entities, rich types and relationships, we need a query language that enables programmers to reason, express and write queries in terms of EDM abstractions. EntitySQL was designed to address this need. Among the many requirements, it is worth to call out some driving aspects of the language design:

Types: Support EDM types in a clean and expressive way;

SQL based: Provide a natural transition for SQL developers. While EntitySQL was heavily influenced by SQL, EntitySQL is not pure SQL. It is worth mentioning that EntitySQL does not prevent one from writing classic SQL queries, users can navigate relationships using classic joins/foreign-keys. EntitySQL also supports relationships to simplify this common task.

Composable and orthogonal: In some SQL implementations, certain expressions can only be placed in specific constructs. In contrast, EntitySQL expressions can be placed virtually anywhere. For instance, sub-queries are treated uniformly regardless where they are placed. A sub-query in the SELECT clause is treated in the same way as in a FROM clause, as opposed to SQL in which sub-queries in the SELECT clause must evaluate to scalars and as collection in a FROM clause. LIKE is another example. It can be used anywhere a Boolean condition is expected, even as a top level query. @myString LIKE @myPattern is a valid EntitySQL query that will be evaluated at the database and returns True, False or Null.

First class collections: EntitySets are treated as collections of a given entity type, therefore NorthwindContainer.Products is a valid query that returns the collection of all Products. {1,2,3} is a collection of integers constructed “inline”. Tables in the SQL sense are just collections of a given entity type. Collections can be created, nested and projected as any other EDM type.

Provider neutral: An interesting implication of writing queries against a conceptual model is that queries and applications can be virtually provider agnostic. When other provider writers make their database of choice available through the ADO.Net Entity Framework, queries written in EntitySQL may be reused across different store providers. The EntitySQL language exposes the same set of constructs regardless of the specific provider implementation.

For more information, see Entity SQL Overview.

Pasted from <blogs.msdn.com/adonet/default.aspx?p=2>

8.2. Why have Entity SQL when you support LINQ?

There are at least three main reasons why the Entity Framework supports Entity SQL as well as LINQ:

1. A text-based, late-bound query language makes some scenarios much easier to develop such as cases where you cannot fully determine the query until runtime. (In fairness these scenarios can also be accomplished with LINQ but they are often much more difficult.) The EntityDataSourceControl for databinding with ASP.NET is a great example of these scenarios.

2. EntityClient needs a text-based query language in order to support the standard ado.net provider API and access patterns where you create a connection, from that create a command where you specify a text string, and then execute the command in order to get back a DataReader.

3. It's a matter of taste. Entity SQL will be very familiar to developers who have spent a lot of time working with SQL, and the transition to Entity SQL may be easier/more natural than the transition to LINQ.

8.3. What does the SelectValue builder method do? What is “select value” in Entity SQL?

The Select and SelectValue builder methods on ObjectQuery both do a very similar thing in that they allow you to project particular values from an overall entity or other query result. The difference is that with Select you ALWAYS get back an enumeration of DbDataRecords. If you select two properties, then you will get back DbDataRecords with two columns. If you select only one property, you will get back DbDataRecords with one column.

In the case of SelectValue, though, you can only specify one property and instead of getting back an enumeraiton of DbDataRecords with one column, you will just get back an enumeration with the value out of that column. So if you had advWorksContext.Product.SelectValue<Int32>(“Length(it.Name)”), the return type would be an enumeration of Int32s. If you did the same thing except with Select, first off you would not supply a generic type parameter to the method (it would just be advWorksContext.Product.Select("Length(it.Name)"), and secondly you would get back an enumeration of DbDataRecords that had one column which was an Int32 type.

These builder methods map directly onto the entitySQL "select" and "select value" commands. Select value just says, "I'm only returning one thing, and so I want the value of that thing rather than a record containing it.". You can use it with properties and scalars or even with whole entities. For more information, see Query Builder Methods (Entity Framework).

Comments