LINQ返回column1ID = MAX column1ID按column2ID分组的行

本文关键字：column1ID column2ID 返回 MAX LINQ | 更新日期: 2023-09-27 18:11:41

我对C# MVC和LINQ完全是新手。

我有一个包含复杂主键的表，由InvestigationActionID和DocketID以及与我需要取出并放入view的行相关的一堆数据组成。考虑到Docket可以有许多InvestigationActions，我想输出一个汇总页面，每个'Docket'只包含最近的InvestigationAction详细信息。

在纯MS SQL中，我可以很容易地实现这一点，如下所示:

SELECT a.DocketID, a.InvestigationActionID, a.ActionDate, a.PostDate, a.ActionType, a.InvestigationStatus 
    from [dbo].[InvestigationAction] a inner join
    (SELECT [DocketID], Max([InvestigationActionID]) as MaxIAID
         FROM [dbo].[InvestigationAction]
              group by DocketID) b
         on a.DocketID = b.DocketID and a.InvestigationActionID = b.MaxIAID

我已经尝试了一些如何实现类似的东西的建议，但我只是没有得到我想要的最好的我所做的是得到一行DocketID和InvestigationActionID都是1，但这真的没有帮助我。

下面是我尝试返回错误数据的查询示例:

from t in InvestigationActions
where !(from tt in InvestigationActions
        where tt.DocketID == t.DocketID &&
            tt.InvestigationActionID > t.InvestigationActionID
        select tt
        ).Any()
select t

感谢所有的帮助。

LINQ返回column1ID = MAX column1ID按column2ID分组的行

如果您正在使用实体框架，请尝试:

var db = new YourContext();
var queryMax = from b in db.InvestigationActions
               group b by b.DocketId into g
               select new { 
                    DocketId = g.Key, 
                    MaxIAID = g.Max(c=>c.InvestigationActionID) 
               };
var query = from i in db.InvestigationActions
            join m in queryMax on i.DocketID equals m.DocketID 
            and  i.InvestigationActionID equals b.MaxIAID
            select i;

否则:

var queryMax = from b in InvestigationActionsList
               group b by b.DocketId into g
               select new { 
                    DocketId = g.Key, 
                    MaxIAID = g.Max(c=>c.InvestigationActionID) 
               };
var query = from i in InvestigationActionsList
            join m in queryMax on i.DocketID equals m.DocketID 
            and  i.InvestigationActionID equals b.MaxIAID
            select i; //change this if you want, see comments below

如果您只想要在选择中列出的字段，请更改最后一行:

select i;

: 

select new { i.DocketID, i.InvestigationActionID, i.ActionDate, i.PostDate, i.ActionType, i.InvestigationStatus };

或:

select new InvestigationAction() { DocketId = i.DocketId, ... other fields that you may want };
相关文章：