Click here to Skip to main content
15,945,603 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
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.

SQL
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
Posted
Comments
DamithSL 5-May-14 23:22pm    
what have you tried?
Member 10649324 5-May-14 23:35pm    
I have tried several things I am going to bed but will send source first thing in the morning. Basically so far I have done a group by into a projection of an anonymous type and cannot get the sum operator to work. I will send source and you can see the problem I am sure.

1 solution

C#
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); 
 
Share this answer
 

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900