Entity Framework Core 2.1: Query Types (Working with Views)

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:

.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 
        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


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.


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


  1. Thanks, but in Oracle don´t work when you access to a view


    When access with

    var studentWithCourses = context.Query().ToList();

    return ORA-00940

    In the same dbContext i have tables and this work ok!.


    • Sorry, it´s work in Oracle.

      Be careful with the fields of the view and the class, if the view has them in capital letters, they must be the same in the class, but it gives you an error ORA-00940.

      Gracias por el articulo, me acabo de dar cuanta que en varios cursos que me he apuntado el profesor eres tu, que pequeño es el mundo 🙂


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s