Convert SQL to Lambda Linq Expression with Inner Join on multiple condition-CodePudding

How do I convert this SQL to Lambda expression:

SELECT L.LotNo,
    L.ProcessCode,
    L.ProcessName,
    L.MachineNo,
    M.MaterialLotNo,
    M.MaterialName,
    M.StartTime,
    M.EndTime
  FROM LotProgress L
  INNER JOIN MaterialMount M
  ON (M.MachineNo = L.MachineNo OR M.MachineNo = 
    (SELECT ParentMachineNo FROM Machine WHERE MachineNo = L.MachineNo))
  AND (L.StartTime <= M.EndTime OR M.EndTime IS NULL) 
  AND (L.EndTime >= M.StartTime OR L.EndTime IS NULL)

I am struggluing to convert the multiple condition on Inner Join. This is as far as I can go:

var vewMaterialTrace = viewLotProgress
    .Join(viewMaterialMount, a => a.MachineNo, b => b.MachineNo, 
    (a, b) => new viewMaterial
    {
        LotNo = a.LotNo,
        ProcessCode = a.ProcessCode,
        ProcessName = a.ProcessName,
        MachineNo = a.MachineNo,
        OpeGroupCode = b.OpeGroupCode,
        OpeGroupName = b.OpeGroupName,
        MaterialLotNo = b.MaterialLotNo,
        MaterialName = b.MaterialName,
        StartTime = b.StartTime,
        EndTime = b.EndTime,
      }).ToList();

CodePudding user response：

I'm not sure my code is correct or not but is too long to add in comment. Please try

var result = context.LotProgress
    .Join(context.MaterialMount,
        l => l.MachineNo,
        m => m.MachineNo,
        (l, m) => new { l, m })
    .Where(x => x.l.MachineNo == x.m.MachineNo || x.m.MachineNo == context.Machine.FirstOrDefault(y => y.MachineNo == x.l.MachineNo).ParentMachineNo)
    .Where(x => x.l.StartTime <= x.m.EndTime || x.m.EndTime == null)
    .Where(x => x.l.EndTime >= x.m.StartTime || x.l.EndTime == null)
    .Select(x => new
    {
        LotNo = x.l.LotNo,
        ProcessCode = x.l.ProcessCode,
        ProcessName = x.l.ProcessName,
        MachineNo = x.l.MachineNo,
        MaterialLotNo = x.m.MaterialLotNo,
        MaterialName = x.m.MaterialName,
        StartTime = x.m.StartTime,
        EndTime = x.m.EndTime
    });