Проекция запросов на представления

Обычно таблица в базе данных сопоставляется с определенным классом модели в Entity Framework. Но EF Core также позволяет сопоставлять наборы DbSet в контексте данных с представлениями в базе данных, а классы C# - с данными, которые возвращаются этими представлениями.

При определении классов, которые будут сопоставляться с данными представлений, следует учитывать следующие ограничения:

• Для них не надо определять первичный ключ

• Изменения в подобных типах не отлеживаются контекстом данных.

• Мы можем получать данные подобных типов. Но мы не можем добавлять, изменять или удалять объекты подобных типов.

• Классы сущностей не могут содержать навигационные свойства на подобные типы.

Рассмотрим простой пример. Допустим у нас есть следующие классы сущностей товара и производящей его компании:

public class Product
{
	public int Id { get; set; }
	public string? Name { get; set; }
	public int Price { get; set; }			// цена
	public int TotalCount { get; set; }	 // количество единиц данного товара

	public int CompanyId { get; set; }
	public Company? Company { get; set; }
}
public class Company
{
	public int Id { get; set; }
	public string? Name { get; set; }

	public List<Product> Products { get; set; } = new();
}

Каждая из этих сущностей будет сопоставляться с определенной таблицей в бд. Но, допустим, мы хотим группировать выборку по каждой компании и получать количество товаров компании и их полную сумму. Для этого мы можем использовать разные способы. Например, написать SQL-запрос и получать его результаты или написать представление в бд. В данном случае мы создадим представление. Но в начале определим следующий класс, который будет сопоставляться с представлением:

public class CompanyProductsGroup
{
	public string? CompanyName { get; set; }
	public int ProductCount { get; set; }	// количество товаров
	public int TotalSum { get; set; }		// совокупная цена всех товаров компании
}

Данный класс и будет представлять тип, который будет сопоставляться не с таблицей, а с данными представления, которое мы создадим позже.

Далее определим контекст данных:

using Microsoft.EntityFrameworkCore;

public class ApplicationContext : DbContext
{
    public DbSet<Product> Products { get; set; } = null!;
    public DbSet<Company> Companies { get; set; } = null!;
    public DbSet<CompanyProductsGroup> ProductsByCompany { get; set; } = null!;

    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        modelBuilder.Entity<CompanyProductsGroup>(pc =>
        {
            pc.HasNoKey();
            pc.ToView("View_ProductsByCompany");
        });
    }
    protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
    {
        optionsBuilder.UseSqlite("Data Source=helloapp.db");
    }
}

В классе контекста данных мы видим, что определено свойство public DbSet<CompanyProductsGroup> ProductsByCompany { get; set; }. Однако в отличие от других наборов DbSet оно будет сопоставляться с представлением. Для этого для этого типа в методе OnModelCreating() определяются две важные настройки.

Во-первых, указывается, что данная сущность (CompanyProductsGroup) не будет содержать ключа:

pc.HasNoKey();

Во-вторых, указывается, что данныя сущность будет сопоставляться с представлением БД:

pc.ToView("View_ProductsByCompany");

В данном случае представление называется "View_ProductsByCompany". Оно уже может существовать в базе данных, а может отсутствовать. Далее мы создадим это представление.

Теперь используем все это в программе:

using Microsoft.EntityFrameworkCore;

using (ApplicationContext db = new ApplicationContext())
{
    db.Database.EnsureDeleted();
    db.Database.EnsureCreated();
    // создаем представление
    db.Database.ExecuteSqlRaw(@"CREATE VIEW View_ProductsByCompany AS 
                                            SELECT c.Name AS CompanyName, Count(p.Id) AS ProductCount, Sum(p.Price * p.TotalCount) AS TotalSum
                                            FROM Companies c
                                            INNER JOIN Products p on p.CompanyId = c.Id
                                            GROUP BY c.Name");
    // добавляем начальные данные
    Company c1 = new Company { Name = "Apple" };
    Company c2 = new Company { Name = "Samsung" };
    Company c3 = new Company { Name = "Huawei" };
    db.Companies.AddRange(c1, c2, c3);
    Product p1 = new Product { Name = "iPhone X", Company = c1, Price = 70000, TotalCount = 2 };
    Product p2 = new Product { Name = "iPhone 8", Company = c1, Price = 40000, TotalCount = 4 };
    Product p3 = new Product { Name = "Galaxy S9", Company = c2, Price = 42000, TotalCount = 3 };
    Product p4 = new Product { Name = "Galaxy A7", Company = c2, Price = 14000, TotalCount = 5 };
    Product p5 = new Product { Name = "Honor 9", Company = c3, Price = 17000, TotalCount = 7 };
    db.Products.AddRange(p1, p2, p3, p4, p5);
    db.SaveChanges();
}

using (ApplicationContext db = new ApplicationContext())
{
    // обращаемся к представлению
    var companyProducts = db.ProductsByCompany.ToList();
    foreach (var item in companyProducts)
    {
        Console.WriteLine($"Company: {item.CompanyName} Models: {item.ProductCount} Sum: {item.TotalSum}");
    }
}

Для ясности я разделил блок инициализации БД и блок выборки из БД. В первом блоке using, где происходить инициализация базы начальными данными, также добавляется представление с помощью команды CREATE VIEW. (Предсталение можно естественно создать и вручную непосредственно в самой базе данных). Это представление как раз и отвечает за группировку данных. Обратите внимание, что название представления - "View_ProductsByCompany" совпадает с названием представления, с которым сопоставляется класс CompanyProductsGroup, а структура класса совпадает со структорой данных, которые возвращает представление.

db.Database.ExecuteSqlRaw(@"CREATE VIEW View_ProductsByCompany AS 
							SELECT c.Name AS CompanyName, Count(p.Id) AS ProductCount, Sum(p.Price * p.TotalCount) AS TotalSum
							FROM Companies c
							INNER JOIN Products p on p.CompanyId = c.Id
							GROUP BY c.Name");

Далее мы можем получить данные через это представление также, как мы получаем данные из таблиц:

var companyProducts = db.ProductsByCompany.ToList();

Консольный вывод программы:

Company: Apple Models: 2 Sum: 300000
Company: Huawei Models: 1 Sum: 119000
Company: Samsung Models: 2 Sum: 196000

И после выполнения кода в базе данных мы сможем увидеть две таблицы и одно представление: