Query Types in Entity Framework Core

Query Types, introduced in Entity Framework Core 2.1 enable you to return non-entity types that map to tables or views and can serve as the return type from FromSql method calls.

A query type in Entity Framework Core is very similar to an ad-hoc type (e.g. a DTO) in EF 6. It is a non-entity type. It doesn't need a key value and does not take part in add, update or delete operations. Where a query type differs from an ad-hoc type in EF 6 is that the query type forms part of the conceptual model. It must be mapped to either a table or a view in the database.

To illustrate this, here is a simple model representing customers and orders:

You can imagine that a real-world version of this model will include considerably more properties and any query against the relevant DbSet objects will return all columns. There will be occasions where just a subset of columns are required, as defined in the following database view named OrderHeaders:

The data returned from calling the view is represented by the following query type:

Entity types are included in the conceptual model as a result of being represented by DbSet properties on the DbContext object. The equivalent mechanism for including query types is the DbQuery class, and you can see how that works below with the DbSet objects for the Order, Orderitem and Customer types, and the DbQuery for the OrderHeader query type:

This is all that is required to enable querying of data in the same way as if the OrderHeaders property was a DbSet:

var orderHeaders = db.OrderHeaders.ToList();

As with normal DbSetqueries, you can also specify filter criteria:

var orderHeaders = db.OrderHeaders.Where(x => x.TotalItems > 15).ToList();

If you don't want to add DbQuery properties to your DbContext class, you can use configuration to include your query types in the model. The ModelBuilder type has gained a new method - Query - that enables this:

The ToView method is used to specify the view or table name that provides a source for the query type's data. When you use the DbQuery approach, convention will attempt to match the name of the property to a database object of the same name. The ToView method can be used to configure the mapping where the table or view name is not the same as the DbQuery property, or where the schema of the database object is not the default. In the following example, the OrderHeader query type is mapped to a view name vw_OrderHeaders within the Accounts schema:

Query types can take part in relationships. They can also form the return type of a raw SQL query with the inclusion of the FromSql method to the DbQuery type.

Summary

The inclusion of query types in Entity Framework Core is a good step forward. Although it has its limitations - types must be included in the model and map to database objects - they do enable much more efficient queries than previously. If you really want the ability to hydrate non-model types from queries similar to the way that EF 6 works, you have a number of options. You can wait - it seems that the EF team are looking at adding something in the future. Or you can use ADO.NET and hydrate your own objects, or finally, I recommend using Dapper for these read-only queries. It is very simple to use and extremely fast.

You might also like...