Множественное использование count в Linq C#

#c_sharp #linq #lambda #count

Встала задача сделать выборку нескольких Count-значений из 2-ух таблиц базы данных.
Решил сделать это с помощью лямбда-выражений. На выходе получилось следующая реализация:

var counts = _context.Users.Select(user => new
{
   TotalUsers = _context.Users.Count(),
   TotalDeparments = _context.Depart.Count(),
   PeopleOver20YE= _context.Users.Count(c => DateTime.Now.Year - c.YearOfBirth >= 20),
   PeopleUnder20YE = _context.Users.Count(c => DateTime.Now.Year - c.YearOfBirth < 20)
}).First();


Это работает, но, очевидно, реализаия плохая. Как сделать нормальную count выборку
используя только лямбда-выражения?
    

Ответы

Ответ 1

Тоже самое, но без лишних запросов, только необходимые

var counts = new
{
    TotalUsers = _context.Users.Count(),
    TotalDeparments = _context.Depart.Count(),
    PeopleOver20YE = _context.Users.Count(c => c.YearOfBirth >= DateTime.Now.Year - 20),
    PeopleUnder20YE = _context.Users.Count(c => c.YearOfBirth < DateTime.Now.Year - 20)
};


Если запросить счетчики в отдельные переменные, то можно использовать только два
Users.Count, а третий вычислить через разность.

int currentYear = DateTime.Now.Year;//получаем заранее, где-то в начале бизнес-действия
int totalUsers = _context.Users.Count();
int totalDeparments = _context.Depart.Count();
int peopleOver20YE = _context.Users.Count(c => c.YearOfBirth >= currentYear - 20);
var counts = new
{
    TotalUsers = totalUsers,
    TotalDeparments = totalDeparments,
    PeopleOver20YE = peopleOver20YE,
    PeopleUnder20YE = totalUsers - peopleOver20YE
};


Ответ 2

Во-первых, результат может сильно отличаться от используемой СУБД, вернее, от LINQ-провайдера
для этой СУБД. Для SqlServer могут генерироваться одни запросы, для Oracle - другие,
для каждой СУБД - свои.

Во-вторых, Entity Framework (берём, конечно, последнюю версию: 6) и Entity Framework
Core тоже сильно различаются и генерируют разные sql-запросы и по разному себя ведут.
EF Core известен тем, что в некоторых случаях выполняет на клиенте те запросы, которые
обычный EF выполнит на сервере.

У меня нет возможности потестировать разные СУБД и версии EF. Возьму для опытов EF6
и SqlServer 2016.



Создадим БД используя Code First:

public class Departament
{
    public int Id { get; set; }
    public string Name { get; set; }
    public virtual ICollection Users { get; set; }

    public Departament()
    {
        Users = new List();
    }
}
public class User
{
    public int Id { get; set; }
    public string Name { get; set; }
    public int YearOfBirth { get; set; }
    public int DepartamentId { get; set; }
    public virtual Departament Departament { get; set; }
}
public class MyContext : DbContext
{
    public DbSet Users { get; set; }
    public DbSet Depart { get; set; }
}


Вставим данные:

using (var _context = new MyContext())
{
    var d1 = new Departament { Name = "dep1" };
    var d2 = new Departament { Name = "dep2" };
    _context.Depart.AddRange(new Departament[] { d1, d2 });

    var u1 = new User { Name = "nameA", YearOfBirth = 1991, Departament = d1 };
    var u2 = new User { Name = "nameB", YearOfBirth = 1992, Departament = d2 };
    var u3 = new User { Name = "nameC", YearOfBirth = 2001, Departament = d1 };
    var u4 = new User { Name = "nameD", YearOfBirth = 2002, Departament = d2 };
    _context.Users.AddRange(new User[] { u1, u2, u3, u4 });

    _context.SaveChanges();
    Console.WriteLine(_context.Users.Count());
    Console.WriteLine(_context.Depart.Count());
}


Для опытов этого достаточно.



Код автора из вопроса

using (var _context = new MyContext())
{
    _context.Database.Initialize(false);
    _context.Database.Log = Console.WriteLine;

    var counts = _context.Users.Select(user => new
    {
        TotalUsers = _context.Users.Count(),
        TotalDeparments = _context.Depart.Count(),
        PeopleOver20YE = _context.Users.Count(c => DateTime.Now.Year - c.YearOfBirth
>= 20),
        PeopleUnder20YE = _context.Users.Count(c => DateTime.Now.Year - c.YearOfBirth
< 20)
    })
    .First();

    Console.WriteLine(counts.TotalUsers + " " + counts.TotalDeparments + " " + counts.PeopleOver20YE
+ " " + counts.PeopleUnder20YE);
}


генерирует следующий sql:

SELECT
    [Limit1].[C5] AS [C1],
    [Limit1].[C1] AS [C2],
    [Limit1].[C2] AS [C3],
    [Limit1].[C3] AS [C4],
    [Limit1].[C4] AS [C5]
    FROM ( SELECT TOP (1)
        [GroupBy1].[A1] AS [C1],
        [GroupBy2].[A1] AS [C2],
        [GroupBy3].[A1] AS [C3],
        [GroupBy4].[A1] AS [C4],
        1 AS [C5]
        FROM     [dbo].[Users] AS [Extent1]
        CROSS JOIN  (SELECT
            COUNT(1) AS [A1]
            FROM [dbo].[Users] AS [Extent2] ) AS [GroupBy1]
        CROSS JOIN  (SELECT
            COUNT(1) AS [A1]
            FROM [dbo].[Departaments] AS [Extent3] ) AS [GroupBy2]
        CROSS JOIN  (SELECT
            COUNT(1) AS [A1]
            FROM [dbo].[Users] AS [Extent4]
            WHERE ((DATEPART (year, SysDateTime())) - [Extent4].[YearOfBirth]) >=
20 ) AS [GroupBy3]
        CROSS JOIN  (SELECT
            COUNT(1) AS [A1]
            FROM [dbo].[Users] AS [Extent5]
            WHERE ((DATEPART (year, SysDateTime())) - [Extent5].[YearOfBirth]) <
20 ) AS [GroupBy4]
    )  AS [Limit1]


Запрос с кучей соединений, но это один запрос. То есть будет выполнен один round trip.



Возьмём теперь код из ответа rdorn:

using (var _context = new MyContext())
{
    _context.Database.Initialize(false);
    _context.Database.Log = Console.WriteLine;

    var counts = new
    {
        TotalUsers = _context.Users.Count(),
        TotalDeparments = _context.Depart.Count(),
        PeopleOver20YE = _context.Users.Count(c => c.YearOfBirth >= DateTime.Now.Year
- 20),
        PeopleUnder20YE = _context.Users.Count(c => c.YearOfBirth < DateTime.Now.Year
- 20)
    };

    Console.WriteLine(counts.TotalUsers + " " + counts.TotalDeparments + " " + counts.PeopleOver20YE
+ " " + counts.PeopleUnder20YE);
}


Он генерирует 4 простых запроса:

SELECT
    [GroupBy1].[A1] AS [C1]
    FROM ( SELECT
        COUNT(1) AS [A1]
        FROM [dbo].[Users] AS [Extent1]
    )  AS [GroupBy1]

SELECT
    [GroupBy1].[A1] AS [C1]
    FROM ( SELECT
        COUNT(1) AS [A1]
        FROM [dbo].[Departaments] AS [Extent1]
    )  AS [GroupBy1]

SELECT
    [GroupBy1].[A1] AS [C1]
    FROM ( SELECT
        COUNT(1) AS [A1]
        FROM [dbo].[Users] AS [Extent1]
        WHERE [Extent1].[YearOfBirth] >= ((DATEPART (year, SysDateTime())) - 20)
    )  AS [GroupBy1]

SELECT
    [GroupBy1].[A1] AS [C1]
    FROM ( SELECT
        COUNT(1) AS [A1]
        FROM [dbo].[Users] AS [Extent1]
        WHERE [Extent1].[YearOfBirth] < ((DATEPART (year, SysDateTime())) - 20)
    )  AS [GroupBy1]


Естественно, сам собой напрашивается второй его вариант, с вычислением одного количества
на клиенте. Это будут три запроса. Однако, нам неизвестно, может у автора поле YearOfBirth
является nullable?



Что выгоднее: один сложный запрос или несколько простых? Думаю, любой спец по БД
(а я таковым не являюсь) скажет, что это зависит от многих факторов. Если запросы идут
через интернет, то лучше сократить их количество. Если БД расположена под боком, в
локальной сети или даже на том же компе, то, вероятно, лучше избавиться от сложного
запроса.



Похоже, чисто linq-ом не сделать простой и эффективный запрос.
Но можно сделать его вручную.

using (var _context = new MyContext())
{
    _context.Database.Initialize(false);
    _context.Database.Log = Console.WriteLine;

    int year = DateTime.Now.Year - 20;

    string sql = @"
declare @users int = (select count(Id) from [dbo].[Users]);
declare @depts int = (select count(Id) from [dbo].[Departaments]);
declare @over20YE int = (select count(Id) from [dbo].[Users] where YearOfBirth >= @year);
declare @under20YE int = (select count(Id) from [dbo].[Users] where YearOfBirth < @year);
select @users as TotalUsers, @depts as TotalDepartments, @over20YE as PeopleOver20YE,
@under20YE as PeopleUnder20YE;";

    var counts = _context.Database.SqlQuery(sql, new SqlParameter("year",
year)).First();

    Console.WriteLine(counts.TotalUsers + " " + counts.TotalDepartments + " " + counts.PeopleOver20YE
+ " " + counts.PeopleUnder20YE);
}


Модель для запроса:

public class Counts
{
    public int TotalUsers { get; set; }
    public int TotalDepartments { get; set; }
    public int PeopleOver20YE { get; set; }
    public int PeopleUnder20YE { get; set; }
}