Page 11 - MSDN Magazine, July 2018

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.
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 AuthorArticle- Count 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.
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 DbQue- ry 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 cor-
rect 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. QueryType- Builder has a subset of EntityTypeBuilder methods: HasAnnota- tion, HasBaseType, HasOne, HasQueryFilter, IgnoreProperty and UsePropertyAccessMode. There’s a nice explanation about ToView and ToTable highlighted as a Tip in the Query Types documenta- tion 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 relation- ship 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.
It’s possible to execute queries without predefining a DbQuery in the DbContext class.
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 AuthorArticle-
Count is an entity and the model builder would fail. I’ve enhanced the model with two changes:
}
msdnmagazine.com
July 2018 7
Figure 2 Results of One-to-One Query
Figure 3 Results of Eager Loading a One-to- One Relationship Between a Query Type and an Entity