c# - 如何将此 sql 转换为 efcore linq

Question

原始数据表：</p>

可以得到你想要的结果的SQL：</p>

select group_number,count(group_number) as group_count,receive_date 
from hS_HZXX 
where type = '1' 
group by group_number,receive_date;

像这样使用 ef core linq：

var hAATGroups = from p in dbContext.Set<HS_HZXX>()
                         where p.type == "1"
                         group p by new { Group_number = p.group_number, Sampling_date = p.sampling_date } into dto
                         select new DTO_HAATGroup
                         {
                             Group_number = dto.Key.Group_number,
                             HAAT_count = dto.Key.Group_number.Count(),
                             Sampling_date = dto.Key.Sampling_date.ToString("yyyy-MM-dd")
                         };

DTO_HAAT 组：

public class DTO_HAATGroup
{

    public string Group_number { get; set; }
    public int HAAT_count { set; get; }
    public string Sampling_date { get; set; }

}

执行linq的结果：

但我得到了错误的结果。. . 你能帮助我吗？如何将此 SQL 转换为正确的 Linq 语句。

标签： c#linq

Answer 1

我已经解决了这个问题，正确的Linq写法如下：

        var hAATGroups = from p in dbContext.Set<HS_HZXX>()
                         where p.type == "1"
                         group p by new { Group_number = p.group_number, Receive_date = p.receive_date } into dto
                         select new DTO_HAATGroup
                         {
                             Group_number = dto.Key.Group_number,
                             HAAT_count = dto.Count(),
                             Receive_date = dto.Key.Receive_date.ToString("yyyy-MM-dd")
                         };