Querying (Entity Framework 4.1)

  • [アーティクル]

The ObjectQuery generic class represents a query that can return a collection of zero or more typed objects. An ObjectQuery belongs to an ObjectContext that contains the connection and metadata information that is necessary to compose and execute the query. You can construct an ObjectQuery with a new operator and pass a query string and the object context to the constructor. However, a more common scenario is to use properties on an ObjectContext derived class to get an ObjectQuery instance that represents a collection of entity sets. Typically, the ObjectContext is subclassed, either by a class generated by the Entity Framework tools or by your POCO classes, and the properties on the object context return entity sets as either an ObjectQuery (in .NET Framework version 3.5 SP1) or as an ObjectSet (in .NET Framework version 4). The ObjectSet class extends the ObjectQuery class to provide functionality, such as adding and deleting objects, in the context of a typed entity set.

The default ObjectQuery provides a starting query that returns all entities of the specified type. This query can be further refined by using LINQ to Entities or query builder methods.

Query Execution

An object query is executed when:

  • It is enumerated by a foreach (C#) or For Each (Visual Basic) statement.

  • It is enumerated by a collection operation such as ToArray, ToDictionary or ToList.

  • The Execute method is explicitly called.

  • LINQ operators such, as First or Any are specified in the outermost part of the query. For more information, see Query Builder Methods.

Note, if as a result of a query execution, nothing was returned from the data source, the results will contain an empty collection and not a null.

Queries executed by the Entity Framework are evaluated against the data in the data source and the results will not reflect against the new objects in the object context. If an entity with the same identity as the one being queried for is already attached to the context, the data coming from the data source and the data already in the context are merged according to the MergeOption of the query. To get the data that is in the cache, use the GetObjectStateEntries method on the ObjectStateManager class. The ObjectStateManager manages the state of objects inside an object context, so if you want to get all the objects that were added, modified and unchanged, you can pass a bitwise OR of the following EntityState values to the GetObjectStateEntries method: Added, Modified, Unchanged. For more information see the blog that demonstrates how to perform local queries.

In the following example, the Execute method is called to execute a query:

Finding entities using a query

DbSet and IDbSet implement IQueryable and so can be used as the starting point for writing a LINQ to Entities query against the database. The following examples demonstrate how to use methods on DbSet and IDbSet to execute the queries against the store. Note that DbSet and IDbSet always involve a round trip to the database even if the entities returned already exist in the context:using (var context = new UnicornsContext()) { // Query for all unicorns with names starting with B var unicorns = from u in context.Unicorns where u.Name.StartsWith("B") select u; // Query for the unicorn named Binky var binky = context.Unicorns .Where(u => u.Name == "Binky") .FirstOrDefault(); }

Finding entities using primary keys

The Find method on DbSet uses the primary key value to attempt to find an entity tracked by the context. If the entity is not found in the context then a query will be sent to the database to find the entity there. Null is returned if the entity is not found in the context or in the database.Find is different from using a query in two significant ways:A round-trip to the database will only be made if the entity with the given key is not found in the context. Find will return entities that are in the Added state. That is, Find will return entities that have been added to the context but have not yet been saved to the database.

Finding an entity by single primary key

The following code shows some uses of Find:using (var context = new UnicornsContext()) { // Will hit the database var unicorn = context.Unicorns.Find(3); // Will return the same instance without hitting the database var unicornAgain = context.Unicorns.Find(3); context.Unicorns.Add(new Unicorn { Id = -1 }); // Will find the new unicorn even though it does not exist in the database var newUnicorn = context.Unicorns.Find(-1); // Will find a castle which has a string primary key var castle = context.Castles.Find("The EF Castle"); }

Finding an entity by composite primary key

The following code attempts to find a LadyInWaiting with PrincessId = 3 and CastleName = “The EF Castle”:using (var context = new UnicornsContext()) { var lady = context.LadiesInWaiting.Find(3, "The EF Castle"); }Note that in the model the ColumnAttribute was used to specify an ordering for the two properties of the composite key. The call to Find must use this order when specifying the two values that form the key.

Using Local to look at local data

The Local property of DbSet provides simple access to the entities of the set that are currently being tracked by the context and have not been marked as Deleted. Accessing the Local property never causes a query to be sent to the database. This means that it is usually used after a query has already been performed. The Load extension method can be used to execute a query so that the context tracks the results. For example:using (var context = new UnicornsContext()) { // Load all unicorns from the database into the context context.Unicorns.Load(); // Add a new unicorn to the context context.Unicorns.Add(new Unicorn { Name = "Linqy" }); // Mark one of the existing unicorns as Deleted context.Unicorns.Remove(context.Unicorns.Find(1)); // Loop over the unicorns in the context. Console.WriteLine("In Local: "); foreach (var unicorn in context.Unicorns.Local) { Console.WriteLine("Found {0}: {1} with state {2}", unicorn.Id, unicorn.Name, context.Entry(unicorn).State); } // Perform a query against the database. Console.WriteLine("\nIn DbSet query: "); foreach (var unicorn in context.Unicorns) { Console.WriteLine("Found {0}: {1} with state {2}", unicorn.Id, unicorn.Name, context.Entry(unicorn).State); } }Using the data set by the initializer defined in Part 1 of this series, running the code above will print out:In Local: Found 0: Linqy with state Added Found 2: Silly with state Unchanged Found 3: Beepy with state Unchanged Found 4: Creepy with state Unchanged In DbSet query: Found 1: Binky with state Deleted Found 2: Silly with state Unchanged Found 3: Beepy with state Unchanged Found 4: Creepy with state UnchangedThis illustrates three points:The new unicorn Linqy is included in the Local collection even though it has not yet been saved to the database. Linqy has a primary key of zero because the database has not yet generated a real key for the entity.The unicorn Binky is not included in the local collection even though it is still being tracked by the context. This is because we removed Binky from the DbSet thereby marking it as deleted.When DbSet is used to perform a query the entity marked for deletion (Binky) is included in the results and the new entity (Linqy) that has not yet been saved to the database is not included in the results. This is because DbSet is performing a query against the database and the results returned always reflect what is in the database.

Execute a polymorphic query

LINQ to Entities supports polymorphic queries—that is, queries for instances of a class and all instances of its subclasses, respectively. For example, consider the following query: IQueryable<BillingDetail> linqQuery = from b in context.BillingDetails select b;List<BillingDetail> billingDetails = linqQuery.ToList();linqQuery is polymorphic and returns a list of objects of the type BillingDetail, which is an abstract class but the actual concrete objects in the list are of the subtypes of BillingDetail: CreditCard and BankAccount. You can restrict your query to only return instances of a particular subclass. In LINQ to Entities, this can be specified by using OfType<T>() Method. For example, the following query returns only instances of BankAccount: IQueryable<BankAccount> query = from b in context.BillingDetails.OfType<BankAccount>() select b;

This section describes patterns that you can use to load related entities. Entity types can define navigation properties that represent associations in the data model. You can use these properties to load entities that are related to the returned entity by the defined association. When entities are generated based on the data model, navigation properties are generated for entities at both ends of an association. These navigation properties return either a reference on the "one" end of a one-to-one or many-to-one relationship or a collection on the "many" end of a one-to-many or many-to-many relationship.

Eager loading is the process whereby a query for one type of entity also loads related entities as part of the query. Eager loading is achieved by use of the Include method. For example, the queries below will load princesses and all the unicorns related to each princess.using (var context = new UnicornsContext()) { // Load all princesses and related unicorns var princesses1 = context.Princesses .Include(p => p.Unicorns) .ToList(); // Load one princess and her related unicorns var princess1 = context.Princesses .Where(p => p.Name == "Cinderella") .Include(p => p.Unicorns) .FirstOrDefault(); // Load all princesses and related unicorns using a string // to specify the relationship var princesses2 = context.Princesses .Include("Unicorns") .ToList(); // Load one princess and her related unicorns using a string // to specify the relationship var princess2 = context.Princesses .Where(p => p.Name == "Cinderella") .Include("Unicorns") .FirstOrDefault(); }Note that Include is an extension method in the System.Data.Entity namespace so make sure you are using that namespace.

It is also possible to eagerly load multiple levels of related entities. The queries below show examples of how to do this for both collection and reference navigation properties.using (var context = new UnicornsContext()) { // Load all castles, all related ladies-in-waiting, and all related // princesses var castles1 = context.Castles .Include(c => c.LadiesInWaiting.Select(b => b.Princess)) .ToList(); // Load all unicorns, all related princesses, and all related ladies var unicorns1 = context.Unicorns .Include(u => u.Princess.LadiesInWaiting) .ToList(); // Load all castles, all related ladies, and all related princesses // using a string to specify the relationships var castles2 = context.Castles .Include("LadiesInWaiting.Princess") .ToList(); // Load all unicorns, all related princesses, and all related ladies // using a string to specify the relationships var unicorns2 = context.Unicorns .Include("Princess.LadiesInWaiting") .ToList(); }Note that it is not currently possible to filter which related entities are loaded. Include will always being in all related entities.

Lazily Loading Entity Objects

Lazy loading is the process whereby an entity or collection of entities is automatically loaded from the database the first time that a property referring to the entity/entities is accessed. When using POCO entity types, lazy loading is achieved by creating instances of derived proxy types and then overriding virtual properties to add the loading hook. (See Part 8 for more information on working with proxies.) For example, when using the Princess entity class defined below, the related unicorns will be loaded the first time the Unicorns navigation property is accessed:public class Princess { public int Id { get; set; } public string Name { get; set; } public virtual ICollection<Unicorn> Unicorns { get; set; } }Lazy loading of the Unicorns collection can be turned off by making the Unicorns property non-virtual:public class Princess { public int Id { get; set; } public string Name { get; set; } public ICollection<Unicorn> Unicorns { get; set; } }Loading of the Unicorns collection can still be achieved using eager loading (see Eagerly loading related entities above) or the Load method (see Explicitly loading related entities below).

Lazy loading can be turned off for all entities in the context by setting a flag on the Configuration property. For example:public class UnicornsContext : DbContext { public UnicornsContext() { this.Configuration.LazyLoadingEnabled = false; } }Loading of related entities can still be achieved using eager loading (see Eagerly loading related entities above) or the Load method (see Explicitly loading related entities below).

Even with lazy loading disabled it is still possible to lazily load related entities, but it must be done with an explicit call. To do so you use the Load method on the related entity’s entry. For example:using (var context = new UnicornsContext()) { var unicorn = context.Unicorns.Find(1); var princess = context.Princesses.Find(2); // Load the princess related to a given unicorn context.Entry(unicorn).Reference(u => u.Princess).Load(); // Load the princess related to a given unicorn using a string context.Entry(unicorn).Reference("Princess").Load(); // Load the unicorns related to a given princess context.Entry(princess).Collection(p => p.Unicorns).Load(); // Load the unicorns related to a given princess using a string to // specify the relationship context.Entry(princess).Collection("Unicorns").Load(); }Note that the Reference method should be used when an entity has a navigation property to another single entity. On the other hand, the Collection method should be used when an entity has a navigation property to a collection of other entities.

The Query method provides access to the underlying query that the Entity Framework will use when loading related entities. You can then use LINQ to apply filters to the query before executing it with a call to a LINQ extension method such as ToList, Load, etc. The Query method can be used with both reference and collection navigation properties but is most useful for collections where it can be used to load only part of the collection. For example:using (var context = new UnicornsContext()) { var princess = context.Princesses.Find(1); // Load the unicorns starting with B related to a given princess context.Entry(princess) .Collection(p => p.Unicorns) .Query() .Where(u => u.Name.StartsWith("B")) .Load(); // Load the unicorns starting with B related to a given princess // using a string to specify the relationship context.Entry(princess) .Collection("Unicorns") .Query().Cast<Unicorn>() .Where(u => u.Name.StartsWith("B")) .Load(); }When using the Query method it is usually best to turn off lazy loading for the navigation property. This is because otherwise the entire collection may get loaded automatically by the lazy loading mechanism either before or after the filtered query has been executed.Note that while the relationship can be specified as a string instead of a lambda expression, the returned IQueryable is not generic when a string is used and so the Cast method is usually needed before anything useful can be done with it.

Sometimes it is useful to know how many entities are related to another entity in the database without actually incurring the cost of loading all those entities. The Query method with the LINQ Count method can be used to do this. For example:using (var context = new UnicornsContext()) { var princess = context.Princesses.Find(1); // Count how many unicorns the princess owns var unicornHaul = context.Entry(princess) .Collection(p => p.Unicorns) .Query() .Count(); }

Performance Considerations

When you choose a pattern for loading related entities, consider the behavior of each approach with regard to the number and timing of connections made to the data source versus the amount of data returned by and the complexity of using a single query. Eager loading returns all related entities together with the queried entities in a single query. This means that, while there is only one connection made to the data source, a larger amount of data is returned in the initial query. Also, query paths result in a more complex query because of the additional joins that are required in the query that is executed against the data source.

Explicit and lazy loading enables you to postpone the request for related object data until that data is actually needed. This yields a less complex initial query that returns less total data. However, each successive loading of a related object makes a connection to the data source and executes a query. In the case of lazy loading, this connection occurs whenever a navigation property is accessed and the related entity is not already loaded. If you are concerned about which related entities are returned by the initial query or with managing the timing of when related entities are loaded from the data source, you should consider disabling lazy loading. Lazy loading is enabled in the constructor of the Entity Framework -generated object context.

For more information, see Performance Considerations (Entity Framework).

No-tracking queries

Sometimes you may want to get entities back from a query but not have those entities be tracked by the context. This may result in better performance when querying for large numbers of entities in read-only scenarios. A new extension method AsNoTracking allows any query to be run in this way. For example:using (var context = new UnicornsContext()){ // Query for all unicorns without tracking them var unicorns1 = context.Unicorns.AsNoTracking(); // Query for some unitcorns without tracking them var unicorns2 = context.Unicorns .Where(u => u.Name.EndsWith("ky")) .AsNoTracking() .ToList();} In a sense Load and AsNoTracking are opposites. Load executes a query and tracks the results in the context without returning them. AsNoTracking executes a query and returns the results without tracking them in the context.

Executing Raw SQL Queries

This section discusses ways in which entities and other types can be queried from the database using raw SQL, and how raw non-query commands can be executed on the database.

Writing SQL queries for entities

The SqlQuery method on DbSet allows a raw SQL query to be written that will return entity instances. The returned objects will be tracked by the context just as they would be if there were returned by a LINQ query. For example:using (var context = new UnicornsContext()) { var unicorns = context.Unicorns.SqlQuery( "select * from Unicorns").ToList(); }Note that, just as for LINQ queries, the query is not executed until the results are enumerated—in the example above this is done with the call to ToList.Care should be taken whenever raw SQL queries are written for two reasons. First, the query should be written to ensure that it only returns entities that are really of the requested type. For example, when using features such as inheritance it is easy to write a query that will create entities that are of the wrong CLR type.Second, some types of raw SQL query expose potential security risks, especially around SQL injection attacks. Make sure that you use parameters in your query in the correct way to guard against such attacks.

Writing SQL queries for non-entity types

A SQL query returning instances of any type, including primitive types, can be created using the SqlQuery method on the DbDatabase class. For example:using (var context = new UnicornsContext()) { var unicornNames = context.Database.SqlQuery<string>( "select Name from Unicorns").ToList(); }The results returned from SqlQuery on DbDatabase will never be tracked by the context even if the objects are instances of an entity type.

Sending raw commands to the database

Non-query commands can be sent to the database using the SqlCommand method on DbDatabase. For example:using (var context = new UnicornsContext()) { context.Database.SqlCommand( "update Unicorns set Name = 'Franky' where Name = 'Beepy'"); }SqlCommand is sometimes used in a database initializer to perform additional configuration of the database (such as setting indexes) after it has been created by Code First.Note that any changes made to data in the database using SqlCommand are opaque to the context until entities are loaded or reloaded from the database.