Como consultar dados de visualizações de banco de dados no EF Core

Resumo : neste tutorial, você aprenderá como criar visualizações de banco de dados e consultar dados delas no EF Core.

Criando e consultando dados de uma visualização de banco de dados no EF Core

Para criar e consultar dados de uma visualização de banco de dados , siga estas etapas:

• Primeiro, defina uma classe de entidade que tenha propriedades mapeadas para as colunas das visualizações.
• Em segundo lugar, defina um DbSetda classe de entidade na DbContextclasse.
• Terceiro, substitua o OnModelCreating()método para mapear DbSetpara a visualização.
• Quarto, crie uma nova migração para gerenciar uma visualização do banco de dados executando o Add-Migrationcomando no gerenciador do Package Console. Na classe de migração gerada, implemente o Up()método para criar uma visualização e Down()o método para eliminar a visualização.
• Quinto, execute o Update-Databasecomando no Package Console Manager para criar a visualização no banco de dados
• Por fim, consulte os dados da visualização usando o arquivo DbSet.

Consultando dados de um exemplo de visualização de dados

Usaremos o banco de dados de exemplo do EF Core como ponto de partida. Para fins de demonstração, criaremos uma visualização a partir das tabelas Departmentse Employees:

Primeiro, defina uma classe de entidade chamada DepartmentSalary:

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

    public decimal TotalSalary { get; set; }
}Linguagem de código:  C#  ( cs )

Em segundo lugar, adicione o DbSetde DepartmentSalaryà HRContextclasse:

public DbSet<DepartmentSalary> DepartmentSalaries { get; set; }Linguagem de código:  C#  ( cs )

Terceiro, mapeie a DepartmentSalaryEntidade para a visualização do banco de dados:

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

    base.OnModelCreating(modelBuilder);
}Linguagem de código:  C#  ( cs )

Observe que você usa o nome do DbSet<DepartmentSalary>, que é DepartmentSalariespropriedade da HRContextclasse.

Como a view não possui chave, é necessário chamar o HasNoKey()método para informar o EF Core. Além disso, quando você usa HasNoKey()o método, o EF Core não rastreará as entidades retornadas da View.

Quarto, crie uma nova migração chamada AddDepartmentSalaryViewusando o Add-Command:

Add-Migration AddDepartmentSalaryViewLinguagem de código:  C#  ( cs )

Na AddDepartmentSalaryViewclasse, modifique o Up()método para executar uma instrução SQL que cria a DepartmentSalariesvisualização e Down()o método para eliminar a visualização :

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);
    }
}Linguagem de código:  C#  ( cs )

Quinto, execute o Update-Databasecomando para executar a migração que cria a DepartmentSalariesvisualização no banco de dados:

Update-DatabaseLinguagem de código:  C#  ( cs )

Se você examinar o banco de dados, verá que a DepartmentSalariesvisualização foi criada com sucesso, conforme mostrado na imagem a seguir:

Por fim, modifique o Program.csarquivo para consultar os dados da DepartmentSalariesvisualização. O seguinte retorna todas as linhas da visualização:

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}");
}Linguagem de código:  C#  ( cs )

Saída:

Engineering - $1,819,531
Finance - $2,085,811
Marketing - $2,451,363
Operations - $2,011,323
Sales - $2,044,996Linguagem de código:  texto simples  ( texto simples )

Nos bastidores, o EF Core executa uma consulta que seleciona dados da DepartmentSalariesvisualização no banco de dados:

-- Executed DbCommand (26ms) [Parameters=[], CommandType='Text', CommandTimeout='30']
SELECT [d].[Name], [d].[TotalSalary]
FROM [DepartmentSalaries] AS [d]Linguagem de código:  SQL (linguagem de consulta estruturada)  ( sql )

Como DepartmentSalariesé a DbSet, podemos usar outros métodos para consultar dados. Por exemplo, o seguinte seleciona departamentos cujo salário total é superior a dois milhões:

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}");
}Linguagem de código:  C#  ( cs )

Saída:

Operations - $2,011,323
Sales - $2,044,996
Finance - $2,085,811
Marketing - $2,451,363Linguagem de código:  texto simples  ( texto simples )

O EF Core gera uma SELECtinstrução com as cláusulas WHEREe ORDER BYpara consultar dados da DepartmentSalariesvisualização:

-- 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]Linguagem de código:  SQL (linguagem de consulta estruturada)  ( sql )

Resumo

• Use migrações para criar e descartar visualizações de banco de dados.
• Crie uma entidade DbSete mapeie-a DbSetpara a visualização para consultar dados dela.