连接所有记录
本文关键字:记录 连接 | 更新日期: 2023-09-27 18:17:57
我创建了一个EF Join。frameStart
有19条记录,frameEnd
有42条记录,但是当我执行连接(framing
)时,只有10条记录。我认为只有10条记录,因为它连接了可以用匹配键分组的记录。代码如下。如何使它在执行连接时,得到所有记录的列表,甚至是那些不"匹配"的记录?
public class s84_Report_FrameLabor
{
public int CustomerID { get; set; }
public string CustomerName { get; set; }
public int SubdivisionID { get; set; }
public string SubdivisionName { get; set; }
public int LotNumber { get; set; }
public string InstallManagerStart { get; set; }
public string InstallManagerComplete { get; set; }
public DateTime FrameLaborStart { get; set; }
public DateTime FrameLaborComplete { get; set; }
public int Duration { get; set; }
/*
Frame Labor Start ------ Product ID: 26
Frame Labor Complete ------ Product ID: 8
*/
public static List<s84_Report_FrameLabor> getDurationReport()
{
using (var context = PrimaryConnection.returnNewConnection())
{
var frameStart = (from c in context.s84_Schedule
where c.ProductID == 26 && c.Completed == false
select new
{
CustomerID = c.CustomerID,
CustomerName = c.s84_Customer.CustomerName,
SubdivisionID = c.SubdivisionID,
SubdivisionName = c.s84_Subdivision.SubdivisionName,
LotNumber = c.LotNumber,
FrameLaborStart = c.CustomerExpectedDate
}).ToList();
var frameEnd = (from c in context.s84_Schedule
where c.ProductID == 8 && c.Completed == false
select new
{
CustomerID = c.CustomerID,
SubdivisionID = c.SubdivisionID,
LotNumber = c.LotNumber,
FrameLaborComplete = c.CustomerExpectedDate
}).ToList();
var framing = from c in frameStart
join e in frameEnd on new { c.CustomerID, c.SubdivisionID, c.LotNumber } equals new { e.CustomerID, e.SubdivisionID, e.LotNumber }
select new s84_Report_FrameLabor
{
CustomerID = c.CustomerID,
CustomerName = c.CustomerName,
SubdivisionID = c.SubdivisionID,
SubdivisionName = c.SubdivisionName,
LotNumber = c.LotNumber,
FrameLaborStart = c.FrameLaborStart,
FrameLaborComplete = e.FrameLaborComplete,
Duration = (e.FrameLaborComplete - c.FrameLaborStart).Days
};
return framing.ToList();
}
}
}
感谢Andre, stuart和James R.,我发现解决方案是使用EntityFramework DefaultIfEmpty()
。
var framing = from c in frameStart
join e in frameEnd on new { c.CustomerID, c.SubdivisionID, c.LotNumber } equals new { e.CustomerID, e.SubdivisionID, e.LotNumber } into jointable
from z in jointable.DefaultIfEmpty()
select new s84_Report_FrameLabor
{
CustomerID = c.CustomerID,
CustomerName = c.CustomerName,
SubdivisionID = c.SubdivisionID,
SubdivisionName = c.SubdivisionName,
LotNumber = c.LotNumber,
FrameLaborStart = c.FrameLaborStart,
FrameLaborComplete = z.FrameLaborComplete,
Duration = c.FrameLaborStart == null ? z.FrameLaborComplete == null ? (z.FrameLaborComplete - c.FrameLaborStart).Days : 0 : 0
};
return framing.ToList();