Page 12 - MSDN Magazine, July 2018

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 nav- igation property, for example:
var results =_context.AuthorArticleCounts.Include("Author").ToList();
The results are shown in Figure 3.
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; }
}
ToQuery allows you to define a query directly in the DbContext, and such a query is referred to as a “defining query.”
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:
ToQueryallowsyoutodefineaquerydirectlyintheDbContext,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(
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 meth- od on QueryTypeBuilder that never existed on EntityTypeBuilder, and that’s ToQuery.
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 Magazine- StatsView. 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.
Capture FromSql Results in Non-Entity Types
In previous versions of Entity Framework, it was possible to exe- cute 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
);
m.Name,
m.Articles.Count,
m.Articles.Select(a => a.AuthorId).Distinct().Count()
) )
8 msdn magazine
Data Points
Figure 4 Results of Querying with a Defining Query
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 rep- resented by the DbSet. If I didn’t include AuthorId, a runtime error would complain about AuthorId not being in the projection.