将SQL转换为LINQ,包括多种类型的连接。

huangapple go评论47阅读模式
英文:

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;

huangapple
  • 本文由 发表于 2023年2月14日 19:46:13
  • 转载请务必保留本文链接:https://go.coder-hub.com/75447379.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定