如何将这个SQL语句转换为LINQ?[英] How to convert this SQL statement to LINQ?

本文是小编为大家收集整理的关于如何将这个SQL语句转换为LINQ?的处理/解决方法,可以参考本文帮助大家快速定位并解决问题,中文翻译不准确的可切换到English标签页查看源文。

问题描述

我不知道如何将此 SQL 语句转换为使用 OUTER APPLY 和 TOP 的 LINQ.有人可以给出一个想法如何处理它.谢谢!

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

推荐答案

LINQ 本身不支持 SQL 样式的外连接(在结果集的水平添加的意义上),但您可以通过分组连接非常轻松地模拟它们如果加入没有找到结果,则返回一个空组.查看 MSDN 页面 How to: Perform Left Outer Joins (C# Programming Guide))如何:使用联接将数据与 LINQ 结合(可视化基本).

本文地址:https://www.itbaoku.cn/post/1556775.html

问题描述

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

推荐答案

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).