Would like assistance in rewriting this query from SQL to LINQ.

Question

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 5-May-14 17:05pm

Member 10649324

Add a Solution

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

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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

Prasad Avunoori · Answer 1 · 2014-05-05T18:50:00

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