So far, each of the models we use represents a table in our database, and, we use that model to make queries to that table. So, we have the Student model and the Students table, then we use something like context.Students.ToList() to be able to bring a list of students from the database. However, there will be times when we want to use a model as a result of our queries which does not necessarily represent a table in our database. EF Core 2.1 allows us to solve this problem using a Query Type.

A Query Type, is a model which is associated with a query or view, and not with a table from our database. Here are some of the advantages of using query models:

– It gives us the convenience of being able to express the result of arbitrary queries in terms of a class, thus, we can enjoy the advantages of a strongly typed language when it comes to consuming arbitrary queries.
– It allows us to centralize the queries we make.
– We do not have to worry about efficiency issues of the change tracker, because by definition, the query models do not represent tables in our database, and therefore, the change tracker does not track them.

Suppose we have a Student, Course and StudentCourse class, and that there is a many to many relationship between Student and Course, represented by StudentCourse.

Let’s see a first example. We want to centralize a query of the students, that brings its Id, name, and date of birth. The first thing we need is a class that holds these results:

public class StudentData
{
    public int Id { get; set; }
    public string Name { get; set; }
    public DateTime Birthdate { get; set; }
}

Now, with the Fluent API, we can make the following configuration in the OnModelCreating method:

modelBuilder.Query<StudentData>()
.ToQuery(() => Students.Select(x =>
 new StudentData { Id = x.Id, Name = x.Name, Birthdate = x.Birthdate }));

Notice that with the ToQuery method we are specifying a query. Therefore, we are saying that every time we speak with the StudentData entity, then the query that we are placing here is the one that is going to be executed. So we have the centralized query.

Then, to be able to use this query type:

var students = context.Query<StudentData>().ToList();

Now let’s see an example with a view. Suppose we have the following view of name StudentsWithActiveCourses:

SELECT        stu.Id, stu.Name, COUNT(*) AS CoursesQuantity
FROM            dbo.Students AS stu 
INNER JOIN
        dbo.StudentsCourses AS sco ON stu.Id = sco.StudentId
WHERE        (sco.IsActive = 'true')
GROUP BY stu.Id, stu.Name

This view gives us the ID of a student, his name, and the number of courses he is enrolled. We can run said view from EF Core using a query model. First we define the class that will hold the results of the view:

public class StudentWithCourses
{
    public int Id { get; set; }
    public string Name { get; set; }
    public int ActiveCourses { get; set; }
}

We will then define the query model in OnModelCreating as follows

modelBuilder.Query<StudentWithCourses>().ToView("StudentsWithActiveCourses");

Then, to use the view:

var studentWithCourses = context.Query<StudentWithCourses>().ToList();

So far we have used the context.Query<Class>() syntax to bring the results of the corresponding query. We can save this if we declare the query model using the DbQuery type at the level of the Data Context. This is quite similar to the use we give to DbSet. In the Data Context, we can place the following:

public DbQuery<StudentWithCourses> StudentWithCourses { get; set; }

So, to execute the view, we can do the following:

var studentWithCourses = context.StudentWithCourses.ToList();

Which is a slightly more readable syntax.

Summary

Use Query Types to represent the results of arbitrary queries. Also, you can use Query Types to execute views of your database.