how to write this sql query using linq and lambda

Question

I got an inventory table and a stocktransactions table. Items from the inventory can be either sold or freely distributed. I want to write a query that retrieves for each item the total sold, the total distributed for free and the generated revenue for the sale transactions.

something along the lines of :

select i.Id,i.Title,
Sum(case when transactiontype=1 then s.MovedQuantity else 0 end) sold,

Sum(case when transactiontype=1 then s.MovedQuantity*s.UnitPrice else 0 end) Revenue,

Sum(case when transactiontype=0 then s.MovedQuantity else 0 end) distributed

From Inventory i,StockTransactions s

where i.Id=s.ItemId

group by i.Id,i.Title

how can this be done with linq/lambda?

I know how to use lambda with linq. I just need help translating this sql into a valid expression — kfc
– kfc, Commented Aug 12, 2013 at 16:21
Which part in particular are you having difficulty with? GroupBy, Select and Sum should do the trick in the right combination. — Chris
– Chris, Commented Aug 12, 2013 at 16:26
it's an int field that specifies whether the transaction is a sale,entry into stock or free copy distribution transaction — kfc
– kfc, Commented Aug 12, 2013 at 16:36

King King · Accepted Answer · 2013-08-12 16:45:26Z

0

This may be what you need:

var result = Inventory.Join(StockTransactions, x=>x.Id, x=>x.ItemId,(x,y)=>new {x,y})
                      .GroupBy(a=>new {a.x.Id, a.x.Title})
                      .SelectMany(a=>
                         new {
                                a.Key.Id,
                                a.Key.Title,
                                sold = a.Sum(b=> b.y.transactiontype == 1 ? b.y.MovedQuantity : 0),
                                Revenue = a.Sum(b=>b.y.transactiontype == 1 ? b.y.MovedQuantity * b.y.UnitPrice : 0),
                                distributed = a.Sum(b=> b.y.transactiontype == 0 ? b.y.MovedQuantity : 0)                       
                             });

answered Aug 12, 2013 at 16:45

King King

63.5k16 gold badges118 silver badges140 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

kfc Over a year ago

thanks, it is exactly what i needed. I simply replaced SelectMany with Select to make it work.

yo chauhan · Accepted Answer · 2013-08-12 16:52:16Z

0

Try this

 var result = ((from inv in inventry.AsQueryable()
                      join st in stockTransactions.AsQueryable()
                      on inv.Id equals st.Id
                      select new { ID = inv.Id, Title = inv.Title, MovedQuantity = st.MovedQuantity, UnitPrice = st.UnitPrice })
                      .GroupBy(s=>new {s.ID,s.Title})
                      .Select(res=>new {Id=res.Key.ID,Title=res.Key.Title,Sold=transactiontype==1?res.Sum(s=>s.MovedQuantity):0,
                      Revenue=transactiontype==1?res.Sum(s=>s.MovedQuantity*s.UnitPrice):0,
                      Distributed=transactiontype==1?res.Sum(s=>s.MovedQuantity):0})).ToList();

answered Aug 12, 2013 at 16:52

yo chauhan

12.4k4 gold badges44 silver badges59 bronze badges

Collectives™ on Stack Overflow

how to write this sql query using linq and lambda

2 Answers 2

1 Comment

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related