英文:
Convert SQL to LINQ with multiple types of joins
问题
我需要将一个SQL查询转换为LINQ,无论是查询语法还是方法语法。
SELECT
IA.ItemId,
IVT.ItemName,
C.DeliveryMethod,
SUM(OD.Quantity) AS Qty
FROM OrderHeader OH
INNER JOIN OrderDetail OD ON OH.OrderId = OD.OrderId
LEFT JOIN Customer C ON OH.CustomerId = C.Id
LEFT JOIN ItemAvailability IA ON OD.ItemId = IA.RecId
INNER JOIN Inventory IVT ON IA.ItemId = IVT.ItemId
WHERE OH.DeliveryDate = '02/03/2023'
AND OH.OrderType = 'Web'
GROUP BY IA.ItemId, Ivt.ItemName, C.DlvMode
ORDER BY IA.ItemId
我已经花了很长时间在Google和YouTube上搜索,但每个人都只提供了内连接、组合连接或左连接的示例。没有人提供了同时在同一查询中使用内连接和左连接的示例。
我知道一个简单内连接的语法如下:
SQL
SELECT OH.OrderId, OD.ItemId, OD.Quantity
FROM OrderHeader OH
INNER JOIN OrderDetail OD ON OH.OrderId = OD.OrderId
ORDER BY OD.ItemId
LINQ
from oh in OrderHeader
join od in OrderDetail on oh.OrderId equals od.OrderId
orderby ordt.IItemId
select new {
OrderId = oh.OrderId,
ItemId = od.ItemId,
Quantity = od.Quantity
}
我也知道左连接的LINQ语法。但我在弄清如何在一个LINQ查询中同时使用内连接和左连接方面遇到了困难。
我希望这里有人可以帮助,请。
英文:
I need to convert a SQL query into LINQ, either to Query Syntax or Method Syntax.
SELECT
IA.ItemId,
IVT.ItemName,
C.DeliveryMethod,
SUM(OD.Quantity) AS Qty
FROM OrderHeader OH
INNER JOIN OrderDetail OD ON OH.OrderId = OD.OrderId
LEFT JOIN Customer C ON OH.CustomerId = C.Id
LEFT JOIN ItemAvailability IA ON OD.ItemId = IA.RecId
INNER JOIN Inventory IVT ON IA.ItemId = IVT.ItemId
WHERE OH.DeliveryDate = '02/03/2023'
AND OH.OrderType = 'Web'
GROUP BY IA.ItemId, Ivt.ItemName, C.DlvMode
ORDER BY IA.ItemId
I've spent a long time Googling and YouTubing, but everyone provides examples of either just Inner Joins, Group Joins, or Left Joins. No one gave example of having both Inner Joins and Left Joins in the same query.
I know the syntax for a simple inner join like this:
SQL
SELECT OH.OrderId, OD.ItemId, OD.Quantity
FROM OrderHeader OH
INNER JOIN OrderDetail OD ON OH.OrderId = OD.OrderId
ORDER BY OD.ItemId
LINQ
from oh in OrderHeader
join od in OrderDetail on oh.OrderId equals od.OrderId
orderby ordt.IItemId
select new {
OrderId = oh.OrderId,
ItemId = od.ItemId,
Quantity = od.Quantity
}
And I know the LINQ syntax for left join also. But I am having trouble to figure out how to have both inner and left joins in one LINQ query.
I hope someone here can help, please.
答案1
得分: 1
只合并连接技巧在一起
var deliveryDate = ...;
var query =
from oh in OrderHeader
join od in OrderDetail on oh.OrderId equals od.OrderId
join c in Customer ON oh.CustomerId equals c.Id into cj
from c in cj.DefaultIfEmpty()
join ia in ItemAvailability on od.ItemId equals ia.RecId into iaj
from ia in iaj.DefaultIfEmpty()
join ivt in Inventory on ia.ItemId equals ivt.ItemId
where oh.DeliveryDate == deliveryDate && oh.OrderType == "Web"
group od by new { ia.ItemId, ivt.ItemName, c.DlvMode, c.DeliveryMethod } into g
select new
{
g.Key.ItemId,
g.Key.DeliveryMethod,
Qty = g.Sum(x => x.Quantity)
} into s
orderby s.ItemId
select s;
英文:
Just combine join techniques together
var deliveryDate = ...;
var query =
from oh in OrderHeader
join od in OrderDetail on oh.OrderId equals od.OrderId
join c in Customer ON oh.CustomerId equals c.Id into cj
from c in cj.DefaultIfEmpty()
join ia in ItemAvailability on od.ItemId equals ia.RecId into iaj
from ia in iaj.DefaultIfEmpty()
join ivt in Inventory on ia.ItemId equals ivt.ItemId
where oh.DeliveryDate == deliveryDate && oh.OrderType == "Web"
group od by new { ia.ItemId, ivt.ItemName, c.DlvMode, c.DeliveryMethod } into g
select new
{
g.Key.ItemId,
g.Key.DeliveryMethod,
Qty = g.Sum(x => x.Quantity)
} into s
orderby s.ItemId
select s;
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库,让每个人都能够通过互相帮助和分享经验来进步。
评论