Multiple use of count in Linq C#

First, the result can be very different from the DBMS used, or rather, from the LINQ provider for this DBMS. For SQLServer, some queries can be generated, for Oracle - others, for each DBMS - its own.

Secondly, the Entity Framework (we take, of course, the latest version: 6) and Entity Framework Core are also very different and generate different sql queries and behave differently. EF Core is known for in some cases executing on the client those queries that a normal EF does will execute on the server.

I don't have the ability to test different DBMS and EF versions. I will take EF6 and SQLServer 2016 for experiments.

Creating a database using Code First:

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

    public Departament()
    {
        Users = new List<User>();
    }
}
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<User> Users { get; set; }
    public DbSet<Departament> Depart { get; set; }
}

Insert the data:

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());
}

This is enough for experiments.

The author's code from the question

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);
}

Generates the following 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]

A request with a bunch of connections, but this is a single request. That is, one round will be executed trip.

Now let's take the code from the response 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);
}

It generates 4 simple queries:

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]

Naturally, the second option suggests itself, with the calculation of one quantity per client. These will be three requests. However, we do not know if the author's field YearOfBirth is nullable?

Which is more profitable: one complex query or several simple ones? I think any DB specialist (and I'm not one) he will say that it depends on many people factors. If the requests go over the Internet, it is better to reduce their number. If the database is located nearby, on a local network, or even on the same computer, then it is probably better to get rid of a complex query.

It seems that pure linq does not make a simple and efficient query.
But you can do it manually.

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<Counts>(sql, new SqlParameter("year", year)).First();

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

Model for the request:

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