postgresql 如何翻译EF等价中的多分组SQL查询

qpgpyjmq 于 2023-02-04 发布在 PostgreSQL

关注(0)|答案(2)|浏览(150)

我有一个数据库（PostgreSQL），其中有一个主表student，附加信息amount和3个字典。我用字典ID的三个字段进行分组查询，从附加表中输出对象的数量和金额。如何将其翻译到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
);

我的SQL查询运行良好：

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

在将查询转换为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查询：

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

目前我有这样的解决方案，但是这样的请求最终会是最优的吗？另外，在这里需要添加一个null检查。

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

postgresql

来源：https://stackoverflow.com/questions/75328857/how-to-translate-sql-query-with-multiple-grouping-in-ef-equivalent

2条答案

按热度按时间

piztneat1#

这可以通过以下EF Core查询完成：

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

它被转换为以下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

如果您需要LEFT JOIN，则它将是：

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

赞(0）回复(0）举报 2023-02-04

uttx8gqw2#

请尝试以下查询：

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

不确定Cnt = x.Select(v => v.StudentId).Distinct().Count()是否会被翻译，这取决于EF Core版本。

- UPDATE**-添加了SQL查询的等效项：

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

赞(0）回复(0）举报 2023-02-04

我来回答

postgresql 如何翻译EF等价中的多分组SQL查询

2条答案

相关问题

热门标签

最新问答