CODEWITHSUNDEEP
c#linqlinq-to-sql
Jeremy Coenen
Jeremy Coenen

Reputation: 1075

Linq to SQL multiple Left joins using multiple fields and subquery

Basically I want to be able to recreate this query in Linq 

SELECT * 
FROM Customers

LEFT JOIN Orders LastOrder
ON LastOrder.CustomerId = Customers.CustomerId
&& LastOrder.CreatedOn = (Select MAX(CreatedOn) FROM Orders WHERE CustomerId = Customers.CustomerId)

LEFT JOIN OrderStatus
ON OrderStatus.OrderStatusId = LastOrder.OrderStatusId

Upvotes: 0

Views: 1416

Answers (1)

Rex M
Rex M

Reputation: 144112

This should be equivalent:

from c in dc.Customers
join o1 in dc.Orders
on
new { o1.CustomerId,
        (from inner
         in dc.Orders
         where inner.CustomerId == c.CustomerId
         select inner.CreatedOn).Max()
    }
equals
new { c.CustomerId,
      o1.CreatedOn
    }
join o2 in dc.Orders
on
o1.OrderStatusId == o2.OrderStatusId
into joined
select joined

Upvotes: 1

Related Questions