c# - 带有连接子句的 linq 中的不同值

Question

我有这种类型的代码。在 Material 表中它只有一个 MaterialId。但是在Report表中可以有多个MaterialId行。(一个materialId在Report表中可以有多个行)。

但是在 Material 表中我没有任何时间戳。但是在 Report 中有一列作为 TimeStamp，我想用它来获取最新的。

我现在只想要 ReportAdminModel 的不同值。

但是我怎样才能让它像那样工作呢？
我试过 Distinct() 但这不起作用。

list = (from material in db.Material
                        join reports in db.Report on material.MaterialId equals reports.MaterialId
                        select new ReportAdminModel
                        { MaterialId = material.MaterialId, MaterialStatus = material.ProcessApprovalStatus, FlowIndex = material.FlowIndex, ActualStartTime = reports.Timestamp })
                        .OrderByDescending(x => x.ActualStartTime).Take(item.NumberOfRows.Value).ToList();

Answer 1

如果我理解正确，试试这个:

list = (from material in db.Material
                join reports in (from rep in db.Report
                                 group rep by rep.MaterialId into grp
                                 let latestTimeStamp = grp.Max(o => o.Timestamp)
                                 select new
                                 {
                                     MaterialId = grp.Key,
                                     Timestamp = latestTimeStamp,
                                     //if you need any other field, just do something like :
                                     //SomeField = grp.Where(o => o.Timestamp == latestTimeStamp).Select(o => o.SomeField).FirstOrDefault();
                                 })    
                on material.MaterialId equals reports.MaterialId
                select new ReportAdminModel
                {MaterialId = material.MaterialId, 
                 MaterialStatus = material.ProcessApprovalStatus, 
                 FlowIndex = material.FlowIndex, 
                 ActualStartTime = reports.Timestamp, 
                }).OrderByDescending(x => x.ActualStartTime).ToList();