Page 10 - MSDN Magazine, July 2018

Data Points JULIE LERMAN EF Core 2.1 Query Types
EF Core 2.1 is here! And there are many great new features and improvements. Rather than taking over the entire magazine to tell you about them all, I’ll share with you the new Query Type feature, which lets you more easily query the database without needing true entities with key properties to consume the results.
Prior to query types, it was possible to write queries against database views and to execute stored procedures with EF Core, but there were limitations. For the views, you had to rely on the fact that EF Core doesn’t know the difference between a view and a table in your database. You could create entities that were part of your DbContext model, create DbSets for those entities and then write queries against those DbSets. But a lot of caveats came with that workflow, such as having to take care not to edit the resulting objects and accidentally causing SaveChanges to attempt to execute an update command, which would fail in the database unless your view was updatable. When executing stored procedures using the FromSql method, you were again required to tie the results to a true entity that was part of your data model, which meant adding extra types to your data model that really didn’t need to be there.
The new Query Type enables easier paths to working with views, stored procedures and other means of querying your database. This is because the query type allows you to let EF Core interact with types that don’t have key properties and map to database objects that don’t have primary keys. EF has always been reliant on keys, so this is a big step for EF Core. Additionally, the query type will help you avoid any interaction with the change tracker, so you don’t have to add in code to protect your applica- tion from inadvertent runtime exceptions related to entities that aren’t updatable. You can even use query types to map to tables, forcing them to be read-only.
In this article I’m going to explore three capabilities enabled by query types:
• Querying against database views
• Another new feature called “defining queries”
• Capturing the results of FromSql queries with non-entity types
Vital to query types is letting the DbContext ModelBuilder knowthatatypeshouldberecognizedasaquerytype.Youdothat by creating a DbQuery property either in the context or with the ModelBuilder.Query method. Both are new.
If you’ve used EF or EF Core at all, you should be familiar with DbSet, the EF class that allows you to query and update entities
of a particular type through a DbContext. DbQuery is a cousin to DbSet, wrapping non-entity types and allowing you to exe- cute read-only queries against views and tables. And these types wrapped in a DbQuery are query types.
The EF Core convention for a DbQuery is similar to a DbSet in that EF Core expects the name of the DbQuery property to match the name of the database object to which it maps.
The new Query Type enables easier paths to working with views, stored procedures and other means of querying your database.
Two points you should be aware of are that migrations can’t build views for you based on mappings, and EF Core can’t reverse- engineer views (yet).
Mapping to and Querying a Database View
I’ll use DbQuery for the first example—mapping to a database view and querying from it. This DbQuery presumes there’s a class
Figure 1 The Entity Classes for the Sample Model
public class Magazine {
public int MagazineId { get; set; }
public string Name { get; set; }
public string Publisher { get; set; } public List<Article> Articles { get; set; }
}
public class Article {
public int ArticleId { get; set; }
public string Title { get; set; }
public int MagazineId { get; set; } public DateTime PublishDate { get; set; } public Author Author { get; set; }
public int AuthorId { get; set; } }
public class Author {
public int AuthorId { get; set; }
public string Name { get; set; }
public List<Article> Articles { get; set; }
}
Code download available at msdn.com/magazine/0718magcode.
6 msdn magazine