Querying data via the DbSet

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.