How to translate SQL query with multiple grouping in EF equivalent-CodePudding

I have a database (PostgreSQL) where there is a main table student, additional information amount and 3 dictionaries. I make a query with grouping by three fields of dictionary IDs, output the number of objects and the amount from an additional table with a condition. And how to translate it to EF Core 6?

create table region (id serial primary key, name varchar);
create table district (id serial primary key, name varchar);
create table department (id serial primary key, name varchar);

create table student (
    id serial primary key,
    name varchar,
    region_id bigint references region,        
    district_id bigint references district,
    department_id bigint references department
);

create table amount (
    id serial primary key,
    student_id bigint references student on delete cascade,
    value numeric,
    year int
);

My SQL query is working well:

select
    t.region_id,
    region."name" region_name,
    t.district_id,
    district."name" district_name,
    t.department_id,
    department."name" department_name,
    t.cnt,
    t.value
from (
    select
        region_id,
        district_id,
        department_id,
        count(distinct s.id) cnt,
        sum(a.value) "value"
    from student s 
    join amount a on s.id = a.student_id 
    where a.year = 2020
    group by region_id, district_id, department_id
) t
join region on t.region_id = region.id
join district on t.district_id = district.id 
join department on t.department_id = department.id

How do I get names from dictionaries when translating a query to EF?

[Table("student")]
public class Student
{
    [Key]
    [Column("id")]
    public int Id { get; set; }

    [Column("name")]
    public string? Name { get; set; }

    [Column("region_id")]
    public int? RegionId { get; set; }

    [Column("district_id")]
    public int? DistrictId { get; set; }

    [Column("department_id")]
    public int? DepartmentId { get; set; }

    [ForeignKey(nameof(RegionId))]
    public virtual Region? Region { get; set; }

    [ForeignKey(nameof(DistrictId))]
    public virtual District? District { get; set; }

    [ForeignKey(nameof(DepartmentId))]
    public virtual Department? Department { get; set; }

    public ICollection<Amount>? Amounts { get; set; }
}

EF query:

var result = await db.Student
    .GroupBy(x => new { x.RegionId, x.DistrictId, x.DepartmentId })
    .Select(x => new 
    {
        x.Key.RegionId,
        x.Key.DistrictId,            
        x.Key.DepartmentId,
        Cnt = x.Count(),
        Value = x.Sum(c => c.Amounts.Where(v => v.Year == 2020).Sum(v => v.Value))
    })
    .ToListAsync();

At the moment I have such a solution, but will such a request be optimal in the end? In addition, you need to add a null check here.

RegionName = x.First().Region.Name,
DistrictName = x.First().District.Name,
DepartmentName = x.First().Department.Name,

CodePudding user response：

This can be done with the following EF Core query:

var query = from student in db.Student
    join region in db.Region on student.RegionId equals region.id
    join district in db.District on student.DistrictId equals district.id
    join department in db.Department on student.DepartmentId equals department.id
    join amount in db.Amount on student.Id equals amount.student_id 
    where amount.Year == 2020
    group amount by new
    {
        student.RegionId,
        RegionName = region.Name,
        student.DistrictId,
        DistrictName = district.Name,
        student.DepartmentId,
        DepartmentName = department.Name
    } into g
    select new
    {
        g.Key.RegionName,
        g.Key.DistrictName,
        g.Key.DepartmentName,
        Cnt = g.Count(),
        Value = g.Sum(a => a.Value)
    };
var result = await query.ToListAsync();

It is translated into the following SQL:

SELECT r.name AS "RegionName", d.name AS "DistrictName", d0.name AS "DepartmentName",
    count(*)::int AS "Cnt", COALESCE(sum(a.value), 0.0) AS "Value"
FROM student AS s
INNER JOIN region AS r ON s.region_id = r.id
INNER JOIN district AS d ON s.district_id = d.id
INNER JOIN department AS d0 ON s.department_id = d0.id
INNER JOIN amount AS a ON s.id = a.student_id
WHERE a.year = 2020
GROUP BY s.region_id, r.name, s.district_id, d.name, s.department_id, d0.name

If you need LEFT JOIN then it will be:

var query = from student in db.Student
    join region in db.Region on student.RegionId equals region.id into rg
    from r in rg.DefaultIfEmpty()
    join district in db.District on student.DistrictId equals district.id into dg
    from d in dg.DefaultIfEmpty()
    join department in db.Department on student.DepartmentId equals department.id into dpg
    from dp in dpg.DefaultIfEmpty()
    join amount in db.Amount on student.Id equals amount.student_id
    where amount.Year == 2020
    group amount by new
    {
        student.RegionId,
        RegionName = r.Name,
        student.DistrictId,
        DistrictName = d.Name,
        student.DepartmentId,
        DepartmentName = dp.Name
    } into g
    select new
    {
        g.Key.RegionName,
        g.Key.DistrictName,
        g.Key.DepartmentName,
        Cnt = g.Count(),
        Value = g.Sum(a => a.Value)
    };

CodePudding user response：

Try the following query:

var query = 
    from s in db.Student
    from a in s.Amounts
    where a.Year == 2020
    group a by new 
    { 
        s.RegionId, 
        RegionName = s.Region.Name, 
        s.DistrictId, 
        DistrictName = s.District.Name, 
        s.DepartmentId, 
        DepartmentName = s.Department.Name 
    } into g
    select new 
    {
        x.Key.RegionId,
        x.Key.DepartmentName,
        x.Key.DistrictId,            
        x.Key.DistrictName,            
        x.Key.DepartmentId,
        x.Key.DepartmentName,
        Cnt = x.Select(v => v.StudentId).Distinct().Count(),
        Value = x.Sum(v => v.Value)
    };

var result = await query.ToListAsync();

Not sure that Cnt = x.Select(v => v.StudentId).Distinct().Count() will be translated, it depends on EF Core version.

UPDATE - added equivalent to the SQL query:

var groupingQuery = 
    from s in db.Student
    from a in s.Amounts
    where a.Year == 2020
    group a by new 
    { 
        s.RegionId, 
        s.DistrictId, 
        s.DepartmentId, 
    } into g
    select new 
    {
        x.Key.RegionId,
        x.Key.DistrictId,            
        x.Key.DepartmentId,
        Cnt = x.Select(v => v.StudentId).Distinct().Count(),
        Value = x.Sum(v => v.Value)
    };

var query = 
    from g in groupingQuery
    join region in db.Region on g.RegionId equals region.id
    join district in db.District on g.DistrictId equals district.id
    join department in db.Department on g.DepartmentId equals department.id
    select new
    {
        g.RegionId,
        RegionName = region.Name,
        g.DistrictId,
        DistrictName = district.Name,
        g.DepartmentId,
        DepartmentName = department.Name,
        g.Cnt,
        g.Value
    };

var result = await query.ToListAsync();