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

Question

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();

The part OR M.MachineNo = (SELECT ParentMachineNo FROM Machine WHERE MachineNo = L.MachineNo)) is correct or not, because the subquery may return a list — MichaelMao
– MichaelMao, Commented Dec 21, 2022 at 6:47
@MichaelMao it is correct. That SELECT statement will only return a single value as ParentMachineNo is unique in table Machine. I don't know whether my query is efficient or not, but if I run the query, it return the result that I want as far as I know. — whatISboolean
– whatISboolean, Commented Dec 21, 2022 at 7:04

MichaelMao · Accepted Answer · 2022-12-21 08:34:07Z

1

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
    });

answered Dec 21, 2022 at 8:34

MichaelMao

2,8262 gold badges28 silver badges57 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

whatISboolean Over a year ago

Thank you! Work like a charm. I can't think of using FirstOrDefault() for context.Machine. Brilliant.

Collectives™ on Stack Overflow

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

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related