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,
2
Answers
This can be done with the following EF Core query:
It is translated into the following SQL:
If you need
LEFT JOIN
then it will be:Try the following query:
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: