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 following example retrieves all authors ordered by their last name:

var data = from a in Authors select a orderby a.LastName

Method syntax used chained method calls. Many of the method names also resemble SQL:

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 and Single methods:

  • First
  • FirstOrDefault
  • Single
  • SingleOrDefault

In addition, there are asynchronous versions of each of the above.

Note: From EF Core 1.1.0, the DbSet.Find method was also implemented. This is examined a bit later.

The First, and FirstOrDefault methods are typically used to return one result from potentially many matches - or null, the default, if no records match the query criteria. The Single and SingleOrDefault methods are used to return a single record where only one should match the criteria specified. All of these methods result in immediate execution of the query, meaning that the SQL is generated and executed against the database.

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]

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 exression 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

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 entities by their key:

var author = context.Authors.Find(1);

Retrieving multiple objects

Queries for retrieving 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
}

Note: Grouping is currently done in-memory, 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. The generated SQL will currently order by the grouping criteria. There is an open issue for moving the translation of GroupBy to the database.

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();

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.

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.


Created:
Last updated: 14/03/2017 16:33:41
Proficiency Level: Beginner