Соединение и группировка таблиц

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

Соединение таблиц

Для объединения таблиц по определенному критерию в Entity Framework Core используется метод Join. Для примера возьмем модели из прошлой темы:

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

	public List<User> Users { get; set; } = new List<User>();
}

public class User
{
	public int Id { get; set; }
	public string Name { get; set; }
	public int Age { get; set; }

	public int CompanyId { get; set; }
	public Company Company { get; set; }
}
public class ApplicationContext : DbContext
{
	public DbSet<Company> Companies { get; set; }
	public DbSet<User> Users { get; set; }
        
	protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
	{
		optionsBuilder.UseSqlServer(@"Server=(localdb)\mssqllocaldb;Database=helloappdb;Trusted_Connection=True;");
	}
}

Например, в нашем случае таблица пользователей и таблица компаний имеет общий критерий - id компании, по которому можно провести объединение таблиц:

using(ApplicationContext db = new ApplicationContext())
{
    var users = db.Users.Join(db.Companies, // второй набор
        u => u.CompanyId, // свойство-селектор объекта из первого набора
        c => c.Id, // свойство-селектор объекта из второго набора
        (u, c) => new // результат
        {
            Name=u.Name, 
            Company = c.Name, 
            Age=u.Age
        });
    foreach (var u in users)
        Console.WriteLine($"{u.Name} ({u.Company}) - {u.Age}");
}

Метод Join принимает четыре параметра:

• вторую таблицу, которая соединяется с текущей

• свойство объекта - столбец из первой таблицы, по которому идет соединение

• свойство объекта - столбец из второй таблицы, по которому идет соединение

• новый объект, который получается в результате соединения

В итоге данный запрос будет транслироваться в следующее выражение SQL:

SELECT [u].[Name], [c].[Name], [u].[Age]
FROM [Users] AS [u]
INNER JOIN [Companies] AS [c] ON [u].[CompanyId] = [c].[Id]

Аналогичного результата мы могли бы достигнуть, если бы использовали оператор join:

var users = from u in db.Users
    join c in db.Companies on u.CompanyId equals c.Id
    select new { Name=u.Name, Company = c.Name, Age=u.Age };

Соединение трех таблиц

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

public class Country
{
    public int Id { get; set; }
    public string Name { get; set; }
}
public class Company
{
    public int Id { get; set; }
    public string Name { get; set; }
    public int CountryId { get; set; }
    public Country Country { get; set; }
	public List<User> Users { get; set; } = new List<User>();
}
public class User
{
    public int Id { get; set; }
    public string Name { get; set; }
    public int Age { get; set; }

    public int CompanyId { get; set; }
    public Company Company { get; set; }
}
public class ApplicationContext : DbContext
{
	public DbSet<Country> Countries { get; set; }
	public DbSet<Company> Companies { get; set; }
	public DbSet<User> Users { get; set; }
        
	protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
	{
		optionsBuilder.UseSqlServer(@"Server=(localdb)\mssqllocaldb;Database=helloappdb;Trusted_Connection=True;");
	}
}

Объединим три таблицы в один набор:

using (ApplicationContext db = new ApplicationContext())
{
	// пересоздаем базу данных
	db.Database.EnsureDeleted();
	db.Database.EnsureCreated();

	Country usa = new Country { Name = "USA" };

	Company microsoft = new Company { Name = "Microsoft", Country = usa };
	Company google = new Company { Name = "Google", Country = usa };
	db.Companies.AddRange(microsoft, google);

	User tom = new User { Name = "Tom", Age = 36, Company = microsoft };
	User bob = new User { Name = "Bob", Age = 39, Company = google };
	User alice = new User { Name = "Alice", Age = 28, Company = microsoft };
	User kate = new User { Name = "Kate", Age = 25, Company = google };

	db.Users.AddRange(tom, bob, alice, kate);
	db.SaveChanges();
}
using (ApplicationContext db = new ApplicationContext())
{
	var users = from user in db.Users
				join company in db.Companies on user.CompanyId equals company.Id
				join country in db.Countries on company.CountryId equals country.Id
				select new
				{
					Name = user.Name,
					Company = company.Name,
					Age = user.Age,
					Country = country.Name
				};
	foreach (var u in users)
		Console.WriteLine($"{u.Name} ({u.Company} - {u.Country}) - {u.Age}");
}

Группировка

Для группировки данных по определенным критериям применяется оператор group by, либо метод GroupBy(). Например, сгруппируем пользователей по компаниям:

using(ApplicationContext db = new ApplicationContext())
{
    var  groups = from u in db.Users
					group u by u.Company.Name into g
					select new
					{
						g.Key,
						Count = g.Count()
					};
    foreach (var group in groups)
    {
        Console.WriteLine($"{group.Key} - {group.Count}");
    }
}

В данном случае критерием для объединения групп служит название компании, то есть столбец Name из связанной таблицы Companies. Критерий, по которому проводится группировка, является ключом. Ключ мы можем получить через свойство Key, которое есть у группы. Кроме того, у группы есть метод Count(), с помощью которого можно получить количество объектов в группе.

При выполнении запроса будет создаваться следующее SQL-выражение:

SELECT [c].[Name] AS [Key], COUNT(*) AS [Count]
FROM [Users] AS [u]
INNER JOIN [Companies] AS [c] ON [u].[CompanyId] = [c].[Id]
GROUP BY [c].[Name]

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

Google - 2
Microsoft - 2

Аналогично работает метод GroupBy():

var groups = db.Users.GroupBy(u => u.Company.Name).Select(g => new
{
	g.Key,
	Count = g.Count()
});