希望有助于将此查询从SQL重写为LINQ。 [英] Would like assistance in rewriting this query from SQL to LINQ.

问题描述

我是这个项目的新手，这是在其中一个网格中显示的内容......他们想要相同的信息，我正在尝试使用同一桌面上的LINQ to Entities实现这一目标。

这是SQL我是LINQ的菜鸟。获得所有领域是直截了当的，但总和让我在LINQ中感到困惑，我认为不需要通过联接这样做...这实际上是我第一次看到有人离开加入了子查询。

  SELECT   TOP   100  *，TTLQty  FROM 项目
  LEFT   JOIN （ SELECT  SUM（Qty） AS  TTLQty，SUM（AvailQty） AS  TTLAvailQty，ItemCode  AS  ItemCode2 < span class =code-keyword> FROM 项 GROUP   BY  ItemCode）
 ItemsTTL  ON  Items.ItemCode = ItemsTTL.ItemCode2 

解决方案

  var  Items1 = FROM i  in  items 
 选择 i; 
 
  var  Items2 = FROM i  in 项目
 GROUP i BY i.code  into  groupItems 
 选择  new  {ItemCode2 =（ int ）groupItems.Key，TTLQty = groupItems.Max（i => i.Qty！=  null ），TTLAvailQty = groupItemsr.Max（i = >  i.AvailQty！=  null ）}; 
 
 
  var 结果=（来自 i1   Items1 
  join  i2   i1上的Items2 .ItemCode等于i2.ItemCode2 进入 i3 
 来自 i4  in  i3.DefaultIfEmpty（）
 选择  new  {X = i1 .col1，Y = i1.col2，Y = i4。}）。取（ 100 ）; 

I am new to this project and this is what is being displayed in one of the grids... they want the same information and I am trying to accomplish this with LINQ to Entities on the same table..
Here is the SQL I am a noob to LINQ. Getting all of the fields is straight forward but the sum is confusing me in LINQ and I don't think it would need to be done that way with the join... That is actually the first time I have ever seen someone left join a Subquery.

SELECT TOP 100 *, TTLQty FROM Items
LEFT JOIN (SELECT SUM(Qty) AS TTLQty, SUM(AvailQty) AS TTLAvailQty, ItemCode AS ItemCode2 FROM Items GROUP BY ItemCode) 
ItemsTTL ON Items.ItemCode = ItemsTTL.ItemCode2

解决方案

var Items1 = FROM i in Items
             select i;

var Items2 = FROM i in Items
            GROUP i BY i.code into groupItems
            select new {ItemCode2 =(int)groupItems.Key, TTLQty= groupItems.Max(i=>i.Qty!=null),     TTLAvailQty= groupItemsr.Max(i => i.AvailQty != null) };


var Result = (from i1 in Items1
       join i2 in Items2 on i1.ItemCode  equals i2.ItemCode2  into i3
       from i4 in i3.DefaultIfEmpty()
       select new { X = i1.col1,Y=i1.col2, Y =i4. }).Take(100); 

这篇关于希望有助于将此查询从SQL重写为LINQ。的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！