How to convert this SQL statement to LINQ?

Question

I have no idea how to convert this SQL statement to LINQ that uses OUTER APPLY and TOP. Can somebody give an idea how deal with it. Thanks!

SELECT  Cust.CustomerName, Ord.OnlineOrderTitle, Pro.ProductTitle, 
Pic.PictureFilename, PCom.PictureCommentText, Ord.OnlineOrderDateAdded
FROM Customer as Cust
OUTER APPLY 
(SELECT * FROM OnlineOrder
WHERE CustomerID = Cust.CustomerID) as Ord
OUTER APPLY 
(SELECT * FROM Product
WHERE OnlineOrderID = Ord.OnlineOrderID) as Pro
OUTER APPLY 
(SELECT TOP 1 * FROM Accessory 
WHERE ProductID = Pro.ProductID) as Acc
OUTER APPLY 
(SELECT TOP 1 * FROM Picture 
WHERE ProductID = Pro.ProductID) as Pic
OUTER APPLY 
(SELECT TOP 1 * FROM PictureComment
WHERE PictureID = Pic.PictureID) as PCom
ORDER BY Ord.OnlineOrderDateAdded DESC

Answer 1

LINQ does not support SQL-style outer joins natively (in the sense of horizontal addition of resultsets), but you can simulate them very easily by doing a grouped join and returning an empty group if the join finds no results. Have a look at the MSDN pages How to: Perform Left Outer Joins (C# Programming Guide) and How to: Combine Data with LINQ by Using Joins (Visual Basic).