时间戳

首页 > 解决方案 > SQL 到 Linq 的转换 - Max()、Groupby、Limit/page

linq - SQL 到 Linq 的转换 - Max()、Groupby、Limit/page

问题描述

我整天都在尝试将以下 mariadb sql 代码转换为 linq。有人可以帮助 linq 的新手吗？

select * from
(
select *, max(dateCreated) from CKDExtract a
inner join PatientExtract b
where a.PatientId = b.PatientExtractId
group by a.PatientID
) as t1
where t1.PatientGender like 'm'
LIMIT 0,100

这是我目前的进展：

        var query = (
                            from p in fhtDB.Ckdextract
                            group p by p.PatientId into g
                            orderby g.Key
                            join pr in fhtDB.PatientExtract on g.Key
                            equals pr.PatientExtractId
                            into h
                            select new
                            {
                                allRemainingItems = g.OrderByDescending(t => t.DateCreated).First(),
                                patientData = h
                            }
                      ).Skip(startRow).Take(endRow - startRow).ToList();
        ;

但是，这无法过滤掉 PatientGender。

谢谢你，肖恩。

标签： linq

解决方案

在我看来，你有两张桌子：PatientExtract和CkdExtract. 每个PatientExtract都有零个或多个CkdExtracts。每一个都CkdExtract属于一个PatientExtract。这种标准的一对多关系是使用外键实现的。类似于：

class PatientExtract
{
     public int PatientExtractId {get; set;}  // primary key

     public Gender Gender {get; set;}
     ... // other properties
}
class CkdExtract
{
     public int CkdExtractId {get; set;} // primary key

     // every CkdExtract belongs to exactly one PatientExtract using foreign key:
     public int PatientId {get; set;}

     ... // other properties
}

要求：给定一个 Gender 值 m、一个 skipValue 和一个 takeValue 作为输入，给我（某些属性）具有此性别的所有 PatientExtracts，每个 PatientExtracts 都有它们的 CkdExtracts。通过增加主键值来使用跳过/获取和排序来执行此操作

如果使用 LINQ 方法语法而不是查询语法，则查询很简单：

Gender gender = Gender.M;
IQueryable<PatientExtract> patientExtracts = ...
IQueryable<CkdExtract> ckdExtracts = ...
var result = patientExtracts                                  // take all PatientExtracts
    .Where(patientExtract => patientExtract.Gender == gender) // keep only the M genders

    .GroupJoin(ckdExtracts,                             // GroupJoin with CkdExtracts
    patientExtract => patientExtract.PatientExtractId,  // from each PatientExtract take primary key
    ckdExtract => ckdExtract => PatientId,              // from each CkdExtract take foreign key
    (patientExtract, ckdExtracts) => new                // when they match, make one new object
    {
        // select only the properties you actual plan to use
        PatientExtractId = patientExtract.PatientExtractId,
        // no need, because you know the value: Gender = PatientExtract.Gender,
        ... // other Patient properties

        CkdExtracts = ckdExtracts
            .OrderBy(ckdExtract => ckdExtract.DateCreated)
            .Select(ckdExtract => new
            {
                // again: select only the CkdExtract properties you plant to use
                Id = ckdExtract.CkdExtractId,
                CreationDate = ckdExtract.DateCreated,
                ...

                // no need for the foreign key, you know the value!
                // PatientExtractId = ckdExtract.PatientId,
            },
    })

    // not sure whether it is needed to order by primary key; probably already ordered
    .OrderBy(patient => patient.Id)
    .Skip(skipValue)
    .Take(takeValue);

推荐阅读