July 2018
Volume 33 Number 7
[Data Points]
EF Core 2.1 Query Types
By Julie Lerman
.jpg "Julie Lerman")
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 application 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 know that a type should be recognized as a query type. You do that 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 execute 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.
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 already defined, as well as a view named AuthorArticleCounts in the database:
public DbQuery<AuthorArticleCount>
AuthorArticleCounts{get;set;}
This alone will allow you to query a database view. Let’s back up, though, to look at the model shown in Figure 1.
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; }
}
I’m using a simple model with three entities to manage publications: Magazine, Article and Author.
In my database, in addition to the Magazines, Articles and Authors tables, I have a view called AuthorArticleCounts, defined to return the name and number of articles an author has written:
SELECT
a.AuthorName,
Count(r.ArticleId) as ArticleCount
from Authors a
JOIN Articles r on r.AuthorId = a.AuthorId
GROUP BY a.AuthorName
I’ve also created the AuthorArticleCount class that matches the schema of the view results. In the class, I made the property setters private to make it clear that this class is read-only, even though EF Core won’t ever attempt to track or persist data from a query type.
public class AuthorArticleCount
{
public string AuthorName { get; private set; }
public int ArticleCount { get; private set; }
}
With the database view in place and a class designed to consume its results, all I need to map them together is a DbQuery property in my DbContext—the same example I showed earlier:
public DbQuery<AuthorArticleCount> AuthorArticleCounts{get;set;}
Now EF Core will be happy to work with the AuthorArticleCount class, even though it has no key property, because EF Core understands this to be a query type. You can use it to write and execute queries against the database view.
For example, this simple LINQ query:
var results=_context.AuthorArticleCounts.ToList();
will cause the following SQL to be sent to my SQLite database:
SELECT "v"."ArticleCount", "v"."AuthorName"
FROM "AuthorArticleCounts" AS "v"
The results are a set of AuthorArticleCount objects, as shown in Figure 2.
.png "Results of One-to-One Query")
Figure 2 Results of One-to-One Query
And the ChangeTracker of the context used to execute the query is totally unaware of these objects.
This is a much nicer experience than past EF Core and Entity Framework implementations where database views were treated like tables, their results had to be entities and you had to take care not to accidentally track them with the change tracker.
It’s possible to execute queries without predefining a DbQuery in the DbContext class. DbSet allows this, as well, with the Set method of a DbContext instance. For a DbQuery, you can write a query as:
var results=_context.Query<AuthorArticleCount>().ToList();
Configuring Query-Type Mappings
This DbQuery worked easily because everything follows convention. When DbSets and their entities don’t follow EF Core conventions, you use the Fluent API or data annotations to specify the correct mappings in the OnModelCreating method. And you begin by identifying which entity in the model you want to affect using the ModelBuilder’s Entity method. Just as DbSet gained a cousin in DbQuery, the Entity method also has a new cousin: Query. Here’s an example of using the Query method to point the AuthorArticleCounts DbQuery to a view of a different name, using the new ToView method (similar to the ToTable method):
modelBuilder.Query<AuthorArticleCount>().ToView(
"View_AuthorArticleCounts");
The Query<T> method returns a QueryTypeBuilder object. ToView is an extension method. There are a number of methods you can use when refining the query type. QueryTypeBuilder has a subset of EntityTypeBuilder methods: HasAnnotation, HasBaseType, HasOne, HasQueryFilter, IgnoreProperty and UsePropertyAccessMode. There’s a nice explanation about ToView and ToTable highlighted as a Tip in the Query Types documentation that I recommend (bit.ly/2kmQhV8).
Query Types in Relationships
Notice the HasOne method. It’s possible for a query type to be a dependent (aka “child”) in a one-to-one or one-to-many relationship with an entity, although not with another query type. Also note that query types aren’t nearly as flexible as entities in relationships, which is reasonable in my opinion. And you have to set up the relationships in a particular way.
I’ll start with a one-to-one relationship between the Author entity and AuthorArticleCount. The most important rules for implementing this are:
- The query type must have a navigation property back to the other end of the relationship.
- The entity can’t have a navigation property to the query type.
In the latter case, if you were to add an AuthorArticleCount property to Author, the context would think the AuthorArticleCount is an entity and the model builder would fail.
I’ve enhanced the model with two changes:
First, I modified the AuthorArticleCount to include an Author property:
public Author Author { get; private set; }
Then I added a one-to-one mapping between Author and AuthorArticleCount:
modelBuilder.Query<AuthorArticleCount>()
.HasOne<Author>()
.WithOne();
Now I can execute LINQ queries to eager load the Author navigation property, for example:
var results =_context.AuthorArticleCounts.Include("Author").ToList();
The results are shown in Figure 3.
.png "Results of Eager Loading a One-to-One Relationship Between a Query Type and an Entity")
Figure 3 Results of Eager Loading a One-to-One Relationship Between a Query Type and an Entity
Query Types in a One-to-Many Relationship
A one-to-many relationship also requires that the query type be the dependent end, never the principal (aka parent). To explore this, I created a new view over the Articles table in the database called ArticleView:
CREATE VIEW ArticleView as select Title, PublishDate, MagazineId from Articles;
And I created an ArticleView class:
public class ArticleView
{
public string Title { get; set; }
public Magazine Magazine { get; set; }
public int MagazineId { get; set; }
public DateTime PublishDate { get; set; }
}
Finally, I specified that ArticleView is a query type and defined its relationship with the Magazine entity, where a Magazine can have many ArticleViews:
modelBuilder.Query<ArticleView>().HasOne(a => a.Magazine).WithMany();
Now I can execute a query that retrieves graphs of data. I’ll use an Include method again. Remember that there’s no reference to the query type in the Magazine class, so you can’t query for a graph of a magazine with its ArticleViews and see those graphs. You can only navigate from ArticleView to Magazine, so this is the type of query you can perform:
var articles=_context.Query<ArticleView>().Include(m=>m.Magazine).ToList();
Notice that I didn’t create a DbQuery so I’m using the Query method in my query.
The API documentation for HasOne, which you’ll find at bit.ly/2Im8UqR, provides more detail about using this method.
The New Defining Query Feature
Besides ToView, there’s one other new method on QueryTypeBuilder that never existed on EntityTypeBuilder, and that’s ToQuery. ToQuery allows you to define a query directly in the DbContext, and such a query is referred to as a “defining query.” You can write LINQ queries and even use FromSql when composing defining queries. Andrew Peters from the EF team explains that, “One use of ToQuery is for testing with the in-memory provider. If my app is using a database view, I can also define a ToQuery that will be used only if I’m targeting in-memory. In this way I can simulate the database view for testing.”
To start, I created the MagazineStatsView class to consume the results of the query:
public class MagazineStatsView
{
public MagazineStatsView(string name, int articleCount, int authorCount)
{
Name=name;
ArticleCount=articleCount;
AuthorCount=authorCount;
}
public string Name { get; private set; }
public int ArticleCount { get; private set; }
public int AuthorCount{get; private set;}
}
I then created a defining query in OnModelCreating that queries the Magazine entities, and builds MagazineStatsView objects from the results:
modelBuilder.Query<MagazineStatsView>().ToQuery(
() => Magazines.Select( m => new MagazineStatsView(
m.Name,
m.Articles.Count,
m.Articles.Select(a => a.AuthorId).Distinct().Count()
)
)
);
I could also create a DbQuery to make my new defining query a little more discoverable, but I wanted you to see that I can still use this without an explicit DbQuery. Here’s a LINQ query for MagazineStatsView. It will always be handled by the defining query:
var results=_context.Query<MagazineStatsView>().ToList();
Based on the data I’ve used to seed the database, the results of the query, shown in Figure 4, correctly show two articles and one unique author for MSDN Magazine, and two articles with two unique authors for The New Yorker.
.png "Results of Querying with a Defining Query")
Figure 4 Results of Querying with a Defining Query
Capture FromSql Results in Non-Entity Types
In previous versions of Entity Framework, it was possible to execute raw SQL and capture those results in random types. We are closer to being able to perform this type of query thanks to query types. With EF Core 2.1, the type you want to use to capture the results of raw SQL queries doesn’t have to be an entity, but it still has to be known by the model as a query type.
There’s one exception to this, which is that it’s possible (with a lot of limitations) to return anonymous types. Even this limited support can still be useful, so it’s worth being aware of. Here’s a query that returns an anonymous type using FromSql and a raw SQL query:
context.Authors.FromSql("select authorid,authorname from authors").ToList();
Returning anonymous types by querying entities only works when the projection includes the primary key of the type represented by the DbSet. If I didn’t include AuthorId, a runtime error would complain about AuthorId not being in the projection. Or if I began with context.Magazines.FromSql with the same query I just showed you, the runtime error would complain about MagazineId not being available.
A better use of this feature is to predefine a type and make sure the DbContext is aware of that type, either by defining a DbQuery or specifying modelBuilder.Query for the type in OnModelCreating. Then you can use FromSql to query and capture the results. As a somewhat contrived example, or perhaps I should say even more contrived than some of the examples I’ve used already, here’s a new class, Publisher, that’s not an entity or part of my PublicationsContext:
public class Publisher
{
public string Name { get; private set; }
public int YearIncorporated { get; private set; }
}
It, too, is a read-only class, as I have another application where I maintain Publisher data.
I created a DbQuery<Publisher> named Publishers in my context, and now I can use that to execute raw SQL query:
var publishers=_context.Publishers
.FromSql("select name, yearfounded from publishers")
.ToList();
Raw SQL can also be a call to execute a stored procedure. As long as the schema of the results match the type (in this case, Publisher), you can do that, even passing in parameters.
Putting the Polish on EF Core
If you’ve been holding off on using EF Core until it was production-ready, the time has finally come. EF Core 2.0 made a great leap in features and functionality, and version 2.1 now includes features that put a real polish on the product. The wait for features from EF6 to appear in EF Core has been due in part to the fact that the EF team has not just copied the old implementations but found smarter, more functional implementations. Query types are a great example of this, compared to the way that views and raw SQL were supported in earlier versions of Entity Framework. Be sure to check out the other new features in EF Core 2.1 by reading the “New Features in EF Core 2.1” section of the EF Core documentation at bit.ly/2IhyHQR.
Julie Lerman is a Microsoft Regional Director, Microsoft MVP, software team coach and consultant who lives in the hills of Vermont. You can find her presenting on data access and other topics at user groups and conferences around the world. She blogs at the thedatafarm.com/blog and is the author of “Programming Entity Framework,” as well as a Code First and a DbContext edition, all from O’Reilly Media. Follow her on Twitter: @julielerman and see her Pluralsight courses at bit.ly/PS-Julie.
Thanks to the following Microsoft technical expert for reviewing this article: Andrew Peters
Andrew Peters is a principal engineer on the Entity Framework team. During his 9 years on the team, Andrew has worked on, among other things, LINQ, Code First, and Migrations, and was one of the architecture leads for EF Core. In his spare time Andrew enjoys gaming, guitar, cooking and spending time with his young family.