How to Query Data from a Database View in EF Core

Summary: in this tutorial, you will learn how to create database views and query data from them in EF Core.

Creating and querying data from a database view in EF Core

To create and query data from a database view, you follow these steps:

First, define an entity class that has properties mapped to the columns of the views.
Second, define a DbSet of the entity class in the DbContext class.
Third, override the OnModelCreating() method to map the DbSet to the view.
Fourth, create a new migration to manage a database view by running the Add-Migration command in the Package Console manager. In the generated migration class, implement the Up() method to create a view and Down() method to drop the view.
Fifth, execute the Update-Database command in the Package Console Manager to create the view in the database
Finally, query data from the view using the defined DbSet.

Querying data from a data view example

We’ll use the EF Core Sample Database as the starting point. For demonstration purposes, we’ll create a view from the Departments and Employees tables:

First, define an entity class called DepartmentSalary:

public class DepartmentSalary
{
    public string Name { get; set; }

    public decimal TotalSalary { get; set; }
}Code language: C# (cs)

Second, add the DbSet of the DepartmentSalary to the HRContext class:

public DbSet<DepartmentSalary> DepartmentSalaries { get; set; }Code language: C# (cs)

Third, map the DepartmentSalary Entity to the database view:

protected override void OnModelCreating(ModelBuilder modelBuilder)
{
    modelBuilder.Entity<DepartmentSalary>()
                .HasNoKey()
                .ToView(nameof(DepartmentSalaries));

    base.OnModelCreating(modelBuilder);
}Code language: C# (cs)

Note that you use the name of the DbSet<DepartmentSalary>, which is the DepartmentSalaries property of the HRContext class.

Since the view has no key, you need to call the HasNoKey() method to inform EF Core. Also, when you use HasNoKey() method, EF Core will not track the entities returned from the View.

Fourth, create a new migration called AddDepartmentSalaryView using the Add-Command:

Add-Migration AddDepartmentSalaryViewCode language: C# (cs)

In the AddDepartmentSalaryView class, modify the Up() method to execute an SQL statement that creates the DepartmentSalaries view and Down() method to drop the view:

using Microsoft.EntityFrameworkCore.Migrations;

namespace HR.Migrations;

/// <inheritdoc />
public partial class AddDepartmentSalaryView : Migration
{
    /// <inheritdoc />
    protected override void Up(MigrationBuilder migrationBuilder)
    {

        var command = @"CREATE VIEW DepartmentSalaries AS 
                                SELECT d.Name, SUM(e.Salary) TotalSalary
	                            FROM Departments D 
	                            INNER JOIN Employees e ON e.DepartmentId = d.Id
	                            GROUP BY d.Name;";
        migrationBuilder.Sql(command);

    }

    /// <inheritdoc />
    protected override void Down(MigrationBuilder migrationBuilder)
    {
        var command = @"DROP VIEW DepartmentSalaries;";
        migrationBuilder.Sql(command);
    }
}Code language: C# (cs)

Fifth, run the Update-Database command to execute the migration which creates the DepartmentSalaries view in the database:

Update-DatabaseCode language: C# (cs)

If you examine the database, you’ll see the DepartmentSalaries view has been created successfully as shown in the following picture:

Finally, modify the Program.cs file to query data from the DepartmentSalaries view. The following returns all rows from the view:

using HR;

using var context = new HRContext();

// get all departments from the view
var departmentSalaries = context.DepartmentSalaries.ToList();
foreach (var ds in departmentSalaries)
{
    Console.WriteLine($"{ds.Name} - {ds.TotalSalary:C0}");
}Code language: C# (cs)

Output:

Engineering - $1,819,531
Finance - $2,085,811
Marketing - $2,451,363
Operations - $2,011,323
Sales - $2,044,996Code language: plaintext (plaintext)

Behind the scenes, EF Core executes a query that selects data from the DepartmentSalaries view in the database:

-- Executed DbCommand (26ms) [Parameters=[], CommandType='Text', CommandTimeout='30']
SELECT [d].[Name], [d].[TotalSalary]
FROM [DepartmentSalaries] AS [d]Code language: SQL (Structured Query Language) (sql)

Since the DepartmentSalaries is a DbSet, we can use other methods to query data. For example, the following selects departments whose total salary is greater than two million:

using HR;

using var context = new HRContext();

// get departments with salary more than 2 mils
var departmentSalaries = context.DepartmentSalaries
                            .Where(ds => ds.TotalSalary > 2_000_000)
                            .OrderBy(ds => ds.TotalSalary)
                            .ToList();

foreach (var ds in departmentSalaries)
{
    Console.WriteLine($"{ds.Name} - {ds.TotalSalary:C0}");
}Code language: C# (cs)

Output:

Operations - $2,011,323
Sales - $2,044,996
Finance - $2,085,811
Marketing - $2,451,363Code language: plaintext (plaintext)

EF Core generates a SELECt statement with the WHERE and ORDER BY clauses to query data from the DepartmentSalaries view:

-- Executed DbCommand (28ms) [Parameters=[], CommandType='Text', CommandTimeout='30']
SELECT [d].[Name], [d].[TotalSalary]
FROM [DepartmentSalaries] AS [d]
WHERE [d].[TotalSalary] > 2000000.0
ORDER BY [d].[TotalSalary]Code language: SQL (Structured Query Language) (sql)

Summary

Use migrations to create and drop database views.
Create an entity, DbSet, and map the DbSet to the view to query data from it.

Was this tutorial helpful ?