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

Данное руководство устарело. Актуальное руководство: Руководство по Entity Framework Core 7

Как правило, каждая таблица в БД сопоставляется с определенным классом модели в 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 List<Product>();
}

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

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

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

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

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

	protected override void OnModelCreating(ModelBuilder modelBuilder)
	{
		modelBuilder.Entity<CompanyProductsGroup>((pc =>
		{
			pc.HasNoKey();
			pc.ToView("View_ProductsByCompany");
		}));
	}
	protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
	{
		optionsBuilder.UseSqlServer(@"Server=(localdb)\mssqllocaldb;Database=querytypesdb;Trusted_Connection=True;");
	}
}

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

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

pc.HasNoKey();

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

pc.ToView("View_ProductsByCompany");

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

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

using System;
using System.Linq;
using Microsoft.EntityFrameworkCore;

namespace HelloApp
{
    public class Program
    {
        public static void Main(string[] args)
        {
            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

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