Entity Framework FAQ: Querying
General LINQ Questions
How do I retrieve a single entity?
Answer: The following methods retrieve a single entity from the result of a LINQ query:
Note: Use First if you expect the object to be there. If not, use FirstOrDefault, but then you need to be able to handle a null from this method.
Note: Single and SingleOrDefault expect there to be one and only one item in the result set. If there is more than one item, both methods will throw an exception.
The following methods can also be used to retrieve a single entity:
How does "Include" work with multiple levels of branches?
Answer: You don't need to call the Include method more than once if you have multiple levels along the same branch. You only need to call it more than once if you have parallel branches. For example, the following code:
var q = from c in db.Customer.Include("SalesOrderHeader")
.Include("SalesOrderHeader.SalesOrderDetail")
.Include("SalesOrderHeader.SalesOrderDetail.Product")
where c.CustomerID == 514
select c;
Is equivalent to this code:
var q = from c in db.Customer.Include("SalesOrderHeader.SalesOrderDetail.Product")
where c.CustomerID == 514
select c;
How do I query trees using LINQ?
Answer: See the TechNet Wiki article How to Query Trees Using LINQ for a detailed answer.
Entity Framework Related LINQ Questions
Can I execute queries locally rather than at the database?
Question: I want to retrieve an object from ObjectContext without going to the database server. How can I execute queries locally rather than at the database?
Answer: [[Entity Framework]] queries are executed at the database rather than locally. The following blog shows how to write an extension method to return results based on what is in ObjectContext: http://blogs.msdn.com/b/dsimmons/archive/2009/02/21/local-queries.aspx.
Is data filtered on the database server or on the client?
Answer: All filtering performed before results are materialized is performed on the database server. Any filtering performed after materializing results (via ToList, ToArray etc.) will occur on the client.
How can I see the generated T_SQL of a query?
Question: How can I see the T-SQL that is generated when I execute my queries?
Answer: You can use ObjectQuery's ToTraceString. For more information, see http://social.msdn.microsoft.com/Forums/en-US/adodotnetentityframework/thread/4a17b992-05ca-4e3b-9910-0018e7cc9c8c. Note that it is not recommended to use ToTraceString for anything other than debugging purposes. If you are using SqlClient to connect to the database then T-SQL is also displayed in the Visual Studio Intelli-Trace window.
How do I use pre-compiled queries?
The following articles discuss how to work with pre-compiled queries:
http://msdn.microsoft.com/en-us/magazine/ee336024.aspx%20http://msdn.microsoft.com/en-us/library/bb896297.aspx
http://blogs.msdn.com/b/dmcat/archive/2010/03/08/potential-performance-issues-with-compiled-linq-query-re-compiles.aspx
http://thedatafarm.com/blog/data-access/using-pre-compiled-linq-to-entities-queries-in-web-apps-and-services/
http://blogs.msdn.com/b/dsimmons/archive/2010/01/12/ef-merge-options-and-compiled-queries.aspx
How do I specify merge options on queries?
Answer: For a detailed explanation of how to set the merge option on queries, see: http://blogs.msdn.com/dsimmons/archive/2010/01/12/ef-merge-options-and-compiled-queries.aspx
Is lazy loading on by default?
Answer: If you use the Entity Designer with default code generation then lazy loading will be on by default on the generated context. If you are using Self Tracking Entities code generation then lazy loading is off. If you construct an ObjectContext directly then lazy loading is off by default.
Can anonymous types participate in change tracking or updates?
Answer: No.
What are the for loading related objects?
Question: What are the different ways and considerations for loading related objects? Will using Load rather than Include improve performance?
Answer: The Loading Related Objects topic describes patterns that you can use to load related entities. Also, see http://thedatafarm.com/blog/data-access/the-cost-of-eager-loading-in-entity-framework/ for a comparison between Load and Include.
Where in the query should Include be placed? How do I add Include on top of a LINQ expression?
Answer: In general, LINQ to Entities will load from the database whatever information the query asks for in the outermost projection. It may appear that Include could be specified in many different locations in a query. The code will compile as long as you place Include anywhere ObjectQuery is available.
However, at run time, Include makes sense only in certain cases:
- Include only applies to items in the query results: objects that are projected at the outermost operation in the query, for instance, the last Select().
- The type of the results has to be an entity.
- The query cannot contain operations that change the type of the result between Include and the outermost operation (i.e., a GroupBy() or a Select() operation that changes the result type).
- The parameter taken by Include is a dot-delimited path of navigation properties that must be navigable from an instance of the type returned at the outermost operation.
Most times, if any of the conditions above is not fulfilled, the span information specified in Include will be ignored. While a query of the following form will usually work:
var query = from entity in context.MyEntitySet.Include("NavProperty")
where entity.ConditionProp == someValue
select entity
In general, it is often clearer to express it like this:
var query = from entity in context.MyEntitySet
where entity.ConditionProp == someValue
select entity;
var queryWithSpan = ((ObjectQuery)query).Include("NavProperty");
When Include is applied at the latest opportunity like this, not only will the code express more clearly the true intent of Include (include a navigation path in the query results), but the Entity Framework will also be able to detect more cases in which Include cannot be applied, and it will throw an exception rather than silently ignore the call.
The downside is that the casting can quickly become quite cumbersome. For that reason, the ADO.NET Entity Framework Extensions library defines an extension method on IQueryable that allows the last line to be written like this:
var query = query.Include("NavProperty");
How do I dynamically build a general-purpose query for the Entity Framework?
Answer: To dynamically build a general-purpose query for the Entity Framework, we recommend using Entity SQL (rather than LINQ) because Entity SQL is a better match for dynamic scenarios. You can use the query builder methods on ObjectQuery, which take strings for various parts of the query expression (like where, orderby, etc.). These methods will build up a query from various parts in a manner that helps protect against SQL injection attacks.
The following is an example of a query built with query builder:
var process = context.Processes
.Where("it.ProcessId = @id",
new ObjectParameter("id",pId));
You can find more information here: Query Builder Methods.
You can also construct and use Entity SQL queries directly by using the ObjectContext.CreateQuery method.
Building dynamic queries by using Entity SQL is easier than by using LINQ to Entities, but this doesn't mean it can't be done with LINQ. See the following links for more information:
What about injection attacks? How are parameterized LINQ queries created?
Question: When working with Entity SQL, it is strongly recommended to use parameterized queries with ObjectParameters or EntityParameters to avoid SQL injection attacks. What about injection attacks in LINQ to Entities queries? How do I create parameterized LINQ queries?
Answer: Unlike Entity SQL queries, LINQ to Entities queries are not composed by using string manipulation or concatenation, and they are not susceptible to common SQL injection attacks. For example, the following LINQ to Entities query will translate to a parameterized T-SQL query:
var query = from person in context.People
where person.LastName == LastName && person.FirstName == FirstName
select person;
Does Entity Framework 4 support projections into EntityType classes?
Answer: No. You can project into an anonymous class or a defined class that is not EntityType. If StudentGrade is EntityType, an exception will be thrown when you execute the following query:
var query = from g in context.StudentGrades
select new StudentGrade
{
EnrollmentID = g.EnrollmentID,
Grade = g.Grade
};
How And Why To Use DefiningQuery Element?
Question: Where can I find information about using the Defining Query element within the SSDL of the Entity Framework, advantages of using it, and scenarios where it would be helpful?
Answer: The following blog discusses defining query: http://blogs.microsoft.co.il/blogs/gilf/archive/2008/05/14/how-why-use-definingquery-element.aspx
Can I use "Contains" with EF LINQ queries? How do I do something like the SQL "IN" clause?
Answer: In EF4, support was added for Contains method. Prior to EF4, you need to work around the fact that it is missing. Several suggestions may be found in this stack overflow thread.