Data querying in Entity Framework Core is performed against the DbSet
properties of the DbContext. The DbSet represents a collection of entities of a specific type - the type specified by the type parameter.
Queries are specified using Language Integrated Query (LINQ), a component in the .NET Framework that provides query capability against collections in C# or VB. LINQ queries can be written using query syntax or method syntax. Query syntax shares a resemblance with SQL. The EF Core provider that you use is responsible for translating the LINQ query into the actual SQL to be executed against the database.
The following example uses query syntax to define a query that retrieves all authors ordered by their last name:
var data = from a in Authors select a orderby a.LastName
Method syntax uses chained method calls. Many of the method names also resemble SQL. The next example shows the previous query expressed using method syntax:
var data = context.Authors.OrderBy(a => a.LastName);
This guide uses method syntax in query examples.
Retrieving a single object
Queries that return single entities are performed using variations of the First
, FirstOrDefault
, Single
, SingleOrDefault
and Find
methods:
In addition, there are asynchronous versions of each of the above.
First and FirstOrDefault
The First
, and FirstOrDefault
methods are intended to be used to return one result from potentially many matches. If you expect at least one record to match the criteria, you can use the First
method. If there is a possiblity of no records matching the criteria, use the FirstOrDefault
method, which will return null
, the default, in the event of no records being found. Both of these methods result in immediate execution of the query, meaning that the SQL is generated and executed against the database as soon as the method call is reached.
The First
method results in a SELECT TOP(1)
query fetching all columns from the table that maps to the DbSet
:
var author = context.Authors.First();
Resulting SQL:
SELECT TOP(1) [a].[AuthorId], [a].[FirstName], [a].[LastName]
FROM [Authors] AS [a]
Single and SingleOrDefault
The Single
and SingleOrDefault
methods are used to return a single record where only one should match the criteria specified. The Single
method generates a SELECT TOP(2)
query. If more than one result is returned by the query, an InvalidOperationException
is generated with the message:
Sequence contains more than one element
For this reason, you are very unlikely to use the Single
method without specifying some criteria, usually a unique key or index value. You can specify the criteria as a lambda expression in a Where
method call, or by passing it directly to the Single
method call:
var author = context.Authors.Where(a => a.AuthorId == 1).Single();
var author = context.Authors.Single(a => a.AuthorId == 1);
Both approaches result in identical SQL being generated:
SELECT TOP(2) [a].[AuthorId], [a].[FirstName], [a].[LastName]
FROM [Authors] AS [a]
WHERE [a].[AuthorId] = 1
If it is possible for the query to generate no matching results, you should use the SingleOrDefault
method which will return null
in that event.
Find
The DbSet.Find
method is familiar to users of earlier versions of Entity Framework that support the DbSet
API. The method takes the key value(s) of the entity to be retrieved as opposed to a lambda expression, providing a less verbose option for retrieving single entities by their key:
var author = context.Authors.Find(1);
The Find
method is shorthand (syntactic sugar) for the SingleOrDefault
method, which is why it requires a key value as a parameter. There should be no possibility of the Find
method returning multiple results. However, it is possible for the Find
method to return null
.
Retrieving multiple objects
Queries for retrieving values relating to multiple objects are only executed against a database when the data is iterated over. This is known as deferred execution. Data is iterated over when you use a foreach
loop, or a finalising method on the query such as ToList
, Sum
or Count
. Prior to that, the LINQ method calls represent the definition of the query to be executed. The following example essentially defines a query that will retrieve all products from the database:
var products = context.Products; // define query
foreach(var product in products) // query executed and data obtained from database
{
...
}
The query is not executed until the foreach
loop is reached. The next example demonstrates the use of ToList
to force immediate execution:
var products = context.Products.ToList(); // define query and force execution
Filtering and Ordering
The Where
method is the principal method for filtering results:
var products = context.Products.Where(p => p.CategoryId == 1);
The filter criteria are passed in to a lambda as an expression that returns a boolean. The expression can include multiple conditions:
var products = context.Prducts.Where(p => p.CategoryId == 1 && p.UnitsInStock < 10);
The OrderBy
, OrderByDescending
, ThenOrderBy
and ThenOrderByDescending
methods are used for specifying the order of results:
var products = context.Products.OrderBy(p => p.ProuctName);
vat categories = context.Categories.OrderBy(c => cCategoryName).ThenOrderBy(c => c.CategoryId);
Grouping
The GroupBy
method is used to group results. the following query produces all products in the database grouped by their CategoryId
value:
var groups = context.Products.GroupBy(p => p.CategoryId);
This results in a collection of types that implement the IGrouping
interface. The types have a Key
property, which holds the value of the value that was used for grouping i.e. the CategoryId
value in this case. Each group has a collection of the elements that were selected, so they can be iterated:
var groups = context.Products.GroupBy(p => p.CategoryId);
foreach(var group in groups)
{
//group.Key is the CategoryId value
foreach(var product in group)
{
// you can access individual product properties
}
}
If you want to use mutiple properties to group by, you will use an anonymous type to represent the Key
:
var groups = context.Products.GroupBy(p => new {Supplier = p.SupplierId, Country = p.CountryId});
Now the elements of the grouping criteria become properties of the Key
:
foreach(var group in groups)
{
//group.Key.SupplierId is the SupplierId value
//group.Key.Country is the CountryId value
}
Note: Grouping is done in-memory in EF Core versions up to 2.1, which means that in the examples above, the data is obtained from the database and then the grouping is performed in the client application by C# code if you are working with older versions of EF Core. The generated SQL orders by the grouping criteria.
Translation of GroupBy
was moved to the database in EF Core 2.1.
Returning Non-Entity Types
If you only want to return a subset of properties (as opposed to effectively executing a SELECT *
command), you can project the data into a new form, either as a non-entity type or as an anonymous type. In this example, a type named ProductHeader
is defined specifically to act as a container for a subset of data from the products table:
public class ProductHeader
{
public int ProductId { get; set; }
public string ProductName { get; set; }
}
List<ProductHeader> headers = context.Products.Select(p => new ProductHeader{
ProductId = p.ProductId,
ProductName = p.ProductName
}).ToList();
This snippet illustrates an anonymous type being used as the container for the data, resulting in a SQL query that only retrieves the ProductId and ProductName columns from the database:
var headers = context.Products.Select(p => new {
ProductId = p.ProductId,
ProductName = p.ProductName
}).ToList();
As of EF Core 2.1, you can also use Query Types to return non-entity types.
Include related data
The Include
method is used to eagerly load related data. You pass in the navigation property that you want to include in the result set. The following query will retrieve all authors and their books:
var authors = context.Authors.Include(a => a.Books).ToList();
You can chain calls to the Include method to load data from multiple relationships:
var authors = context.Authors
.Include(a => a.Biography)
.Include(a => a.Books)
.ToList();
You can use the ThenInclude
method to retrieve data from second and subsequent level relationships. In the next example, the Book
entity is assumed to have a navigation property to a Publisher
entity:
var authors = context.Authors
.Include(a => a.Books)
.ThenInclude(b => b.Publisher)
.ToList();
The query brings back all authors, and their books, and each book's publisher.
The Include
method is only effective if you return entity types. If you attempt to use the Include method that returns a non-entity type (see previous section), the Include
will be ignored. It will not form part of the SQL that's generated.
See also Lazy Loading
NoTracking Queries
Any entities that your query returns are automatically tracked by the context. In cases where the data is read-only i.e. it is being used for display purposes on a web page and will not be modified during the current request, it is not necessary to have the context perform the extra work required to set up tracking. The AsNoTracking
method stops this work being done and can improve performance of an application:
var cars = context.Cars.AsNoTracking().ToList();
If you have a series of read-only queries to perform against the same instance of the context, you can configure the tracking behaviour at context-level instead of using the AsNoTracking
method in each query:
using (var context = new SampleContext())
{
context.ChangeTracker.QueryTrackingBehavior = QueryTrackingBehavior.NoTracking;
var cars = context.Cars.ToList();
var customers = context.Customers.ToList();
...
}
Non-entity types are not tracked by the context.