LINQ query to show result of 2 tables

Question

Here are my two tables

BuyShare

SoldShare

and the result table i want from LINQ query is

This result table contain companies name with there total buy and sold shares.

So far I code is:

var userHistoryAll = from buy in db.share_bought_history
                     join sold in db.share_sold_history
                     on buy.regist_id equals sold.regist_id
                     select new
                             {
                                 buy,
                                 sold
                             } into combine
                     group combine by combine.buy.comapnay_id
                     into final
                     select new
                             {
                                 cName = final.FirstOrDefault().buy.company.company_name,
                                 uBuy = final.Sum(x => x.buy.no_of_sahre),
                                 uSold = final.Sum(x => x.sold.no_of_sahre)
                             };

but I can't get desired result.

score 1 · Answer 1 · answered Mar 12 '17 at 15:57

Try following :

var userHistoryAll = (from buy in db.share_bought_history
                                 join sold in db.share_sold_history
                                 on buy.comapnay_id equals sold.comapnay_id
                                 select new { buy = buy, sold = sold})
                                 .GroupBy(x => x.buy.comapnay_id)
                                 .Select(x => new {
                                     cName=x.Key,
                                     uBuy = x.Select(y => y.buy.no_of_sahre).Sum(),
                                     uSold = final.Select(y => y.sold.no_of_sahre).Sum()
                                 }).ToList();

You don't have to do `x.Select(y => y.buy.no_of_sahre).Sum()`, you can just do `x.Sum(y => y.buy.no_of_sahre)` — Vikhram, Mar 12 '17 at 16:10
By this `uSold = final.Select(...` do you mean `uSold = x.Select(...` ? — Junaid umar, Mar 12 '17 at 17:28

score 1 · Accepted Answer · edited Mar 04 '23 at 16:47

1

The problem is that your data doesn't have a unique key to join on (at least that's my opinion)

So first make the groub by then the join.

I personally prefer the lambda syntax but you can transform it:

var buyGroub = BuyShare.GroubBy(x=>x.Id)
                       .Select(x=> new 
                       {
                          name = x.Name,
                          buy = x.Sum(s => s.Share)
                       }

var soldGroub = SoldShare.GroubBy(x=>x.Id)
                       .Select(x=> new 
                       {
                          name = x.Name,
                          sold = x.Sum(s => s.Share)
                       }

Now you can present however you like

var result = buyGroub.Join(soldGroub,
                          a=>a.name,
                          b=>b.name,
                          (a,b)=>new {a.Name , a.buy , b.sold});

Edit: to get the records which are in list1 and not in list2 and the other way around you'll need to perform an operation called full outer join .. check the accepted answer it has two arrays which are equivalent to your 2 lists

edited Mar 04 '23 at 16:47

marc_s

732,580
175
1,330
1,459

answered Mar 12 '17 at 16:10

Modar Na

873
7
18

@Vikhram i searched a bit but no result is it possible to join on non unique ? – Modar Na Mar 12 '17 at 16:17
the only problem now is that its not showing all entries from buy table. the scenario is buy table contain shares of 4 companies but user only sold shares from 3 companies. it mean 4th company is not in sold table. now the result of this query shows only 3 companies, I want all 4 companies with sold of 4th company as 0. – Junaid umar Mar 13 '17 at 16:22
An easier solution is that before performing the join code .. loop the first list and check if it has a corresponding record in the second and if not then add a record with same id and quantity 0 .. and the other way arround ... – Modar Na Mar 13 '17 at 16:48
Thanks but I try this and it works. `var result = from a in buyGroub join b in soldGroub on a.name equals b.name into temp from c in temp.DefaultIfEmpty() select new { cName = a.name, uBuy = a.buy, uSold = c != null ? c.sold : 0 };` – Junaid umar Mar 13 '17 at 17:18
Glad to help, if this answer solved your problem please mark it as accepted by clicking the check mark next to the answer.. it is to save the time of other users who will try to solve your question see: https://meta.stackexchange.com/questions/5234/how-does-accepting-an-answer-work – Modar Na Mar 13 '17 at 17:21
Or in this case u can merge the answers then accept it – Modar Na Mar 13 '17 at 17:23
i try, but my reputation is 14. As soon as it's 15 I'll. Thanks again – Junaid umar Mar 13 '17 at 17:59

LINQ query to show result of 2 tables

2 Answers2