c# - 在linq中联合3个表和具有最大id的嵌套表的返回值

如您所见，我在那里有桌子：

线：

id       Linename
1        line1
2        line2
3        line3

联合：

id       lineId   jointname
1        1        joint1
2        2        joint2
3        1        joint3

适应了：

id      jointid    fitupdate     state
1       1          2012/12/12    acc
2       1          2013/12/12    rej
3       2          2015/12/12    acc
4       2          2016/12/12    rej

结果我需要：

id     Linename      jointname    fitupdate     state
1      line1          joint1      2013/12/12    rej
2      line2          joint2      2016/12/12    rej

fitup表有一个state，我需要基于最大ID的最终状态。

在fitup表中，每个关节有多行，但在结果查询中我需要最大id的日期（字符串）。

这是我的查询：

var q = from j in _ctx.Joints
    join l in _ctx.Lines on j.LineId equals l.Id
    join spo in _ctx.Spools on j.SpoolId equals spo.Id
    join sup in _ctx.Supports on j.SupportId equals sup.Id
    join shee in _ctx.Sheets on j.SheetId equals shee.Id
    join Fit in _ctx.FitUpDetails on j.Id equals Fit.JointId into g2
    from y2 in g2.DefaultIfEmpty()

    join weld in _ctx.WeldDetails on j.Id equals weld.JointId into g
    from y1 in g.DefaultIfEmpty()
    join end in _ctx.Ends on j.EndId equals end.Id

    join basemat in _ctx.BaseMaterials on j.BaseMaterialId equals basemat.Id
    join TestPack in _ctx.TestPackages on j.TestPackageId equals TestPack.Id

    group new { j, l,y2,y1} by new { shee, j, l, spo, sup, y2, y1, end, basemat, TestPack } into grouping
    let maxFitById = grouping.Select(item => item.y2)
                     .Where(item => item != null)
                     .OrderByDescending(item => item.Id)

    let maxweldById = grouping.Select(item => item.y1)
   .Where(item => item != null)
   .OrderByDescending(item => item.Id)

    select new ViewFront()
    {
        Id = grouping.Key.j.Id,
        LineId = grouping.Key.l.LineNumber,
        SubmitDateTime = grouping.Key.j.SubmitDateTime,
        JointNumber = grouping.Key.j.JointNumber,
        BaseMaterialId = grouping.Key.basemat.Name,
        FitUpAccept = maxFitById.FirstOrDefault().StateStep1,
        FitUpAcceptMain = maxFitById.FirstOrDefault().StateStep2,
        JointClass = grouping.Key.j.JointClass,
        End = grouping.Key.end.Name,
        JointSize = grouping.Key.j.JointSize,
        LeftMaterialItemCode = grouping.Key.j.LeftMaterialItemCode,
        LeftMaterialLength = grouping.Key.j.LeftMaterialLength.ToString(),
        MagneticTest = grouping.Key.j.MagneticTest,
        PenetrationTest = grouping.Key.j.PenetrationTest,
        PostWeldHeatTreatment = grouping.Key.j.PostWeldHeatTreatment,
        RemarkState = grouping.Key.j.RemarkState,
        RightMaterialItemCode = grouping.Key.j.RightMaterialItemCode,
        RightMaterialLength = grouping.Key.j.RightMaterialLength.ToString(),
        RadiographyTest = grouping.Key.j.RadiographyTest,
        SheetId = grouping.Key.shee.SheetNumber,
        ShopField = grouping.Key.j.ShopField,
        SpoolId = grouping.Key.spo.SpoolNumber,
        SupportId = grouping.Key.sup.SupportNumber,
        TestPackageId = grouping.Key.TestPack.PackageNumber,
        THK = grouping.Key.j.THK,
        UltrasonicTest = grouping.Key.j.UltrasonicTest,
        WeldAccept = maxweldById.FirstOrDefault().StateStep1,
        WeldAcceptMain = maxweldById.FirstOrDefault().StateStep2
    };

在此查询中，FitUpAccept是状态。

联合表数据


焊缝


适应了：


结果：

最佳答案

以下代码可以满足您的需求。现在一些解释：


为了简化起见，我只保留了与所描述的输出数据相关的表。
如果grouping by-如果像选择那样选择整个对象，则将始终获得单个记录的“组”-我仅通过group key所需数据-在这种情况下为非聚合字段。确保您没有按要对其进行聚合操作的相同字段进行分组（例如y2）
因为它是left join的FitUpDetails，所以我必须确保删除所有null记录，并且每当我访问该对象的属性时，都要确保它不为null-?.的c＃6.0语法。
现在，对于“按最大ID”部分-如果我把它写成文字：“按X分组数据，然后按Y分组，则首先记录->其属性”


所以到代码：

var result = (from j in Joints
              join l in Lines on j.LineId equals l.Id
              join f in FitUpDetails on j.Id equals f.JointId into g2
              from y2 in g2.DefaultIfEmpty()

              group new { j, l, y2 } by new { j.Id, l.LineName, j.JointName } into grouping

              let maxFitById = grouping.Select(item => item.y2)
                                      .Where(item => item != null)
                                      .OrderByDescending(item => item.Id)
                                      .FirstOrDefault()

              select new
              {
                  Id = grouping.Key.Id,
                  LineName = grouping.Key.LineName,
                  JointName = grouping.Key.JointName,
                  FitUpdate = maxFitById?.FitUpdate,
                  State = maxFitById?.State
              }).ToList();

用它来测试它：

List<dynamic> Joints = new List<dynamic>
{
    new { Id = 1, LineId = 1, JointName = "joint1" },
    new { Id = 2, LineId = 2, JointName = "joint2" },
    new { Id = 3, LineId = 1, JointName = "joint3" },
};

List<dynamic> Lines = new List<dynamic>
{
    new { Id = 1, LineName = "line1" },
    new { Id = 2, LineName = "line2" },
    new { Id = 3, LineName = "line3" },
};

List<dynamic> FitUpDetails = new List<dynamic>
{
    new { Id = 1, JointId = 1, FitUpdate = new DateTime(2012,12,12), State = "acc" },
    new { Id = 2, JointId = 1, FitUpdate = new DateTime(2013,12,12), State = "rej" },
    new { Id = 1, JointId = 2, FitUpdate = new DateTime(2015,12,12), State = "acc" },
    new { Id = 4, JointId = 2, FitUpdate = new DateTime(2016,12,12), State = "rej" },
};

关于c# - 在linq中联合3个表和具有最大id的嵌套表的返回值，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/38747154/