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.

Answer 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