Convert SQL to Linq with multiple joins , SUM and group by

Question

I am trying to convert an SQL query to LINQ where the query has 2 left joins and group by.

SELECT t1.Shop,t1.Stake, t2.Payout, t3.Anticipation FROM
    (SELECT Shop, sum(Stake) AS 'Stake' FROM Bets GROUP BY Shop) t1
  FULL OUTER join
    (SELECT Shop, SUM(Amount) AS 'Payout' FROM Transactions where TransactionType = 1 GROUP BY Shop) t2 on t1.Shop = t2.Shop
  FULL OUTER join 
    (SELECT Shop, sum(WinAmount) as 'Anticipation' from Bets where [Status] = 'W' group by Shop) t3 on t1.shop  = t3.shop

This is the SQL query that works and below you can see the LINQ code that I have tried but it does not work

var obj = (from b in _context.Bets
group b by b.Shop into betsList
join t in _context.Transactions.Where(x = >x.TransactionType == TransactionType.Acceptance) on betsList.Key equals t.Shop
group t by t.Shop into transactionsList
from t in transactionsList.DefaultIfEmpty()
join ant in _context.Bets.Where(x = >x.Status == 'W') on b.Id equals ant.Id into anticipationList
from ant in anticipationList.DefaultIfEmpty()
select new {
    Shop = betsList.Key,
    Stake = betsList.Sum(k = >k.Stake),
    Payout = transactionsList.Sum(x = >x.Amount),
    Anticipation = anticipationList.Sum(x = >x.WinAmount)
}).ToList();

Sample output data from the SQL query is:

[mre], a little sample for input will be nice. and a copy pastable one. an img will be a bad idea. — Drag and Drop, Mar 03 '21 at 10:02
You can perhaps isolate your issue by trying to write in linq part of the SQL `(SELECT Shop, sum(Stake) AS 'Stake' FROM Bets GROUP BY Shop)`. => `var stakeBets = _context.Bets.GroupBy().Select()` into different variables and validate the result. The the full outer join will be easier to write. https://stackoverflow.com/questions/5489987/linq-full-outer-join — Drag and Drop, Mar 03 '21 at 10:04

score 1 · Answer 1 · answered Mar 03 '21 at 13:23

Better to divide query in small parts. This one should work, it is not FULL OUTER JOIN but, I think, with your data it may work:

var betsQuery = 
    from b in _context.Bets
    group b by new { b.Shop } into g
    select new 
    {
        g.Key.Shop,
        Stake = g.Sum(x => x.Stake)
    };

var transactionQuery = 
    from t in _context.Transactions
    where t.TransactionType == TransactionType.Acceptance
    group t by new { t.Shop } into g
    select new 
    {
        g.Key.Shop,
        Payout = g.Sum(x => x.Amount)
    };

var antisipationQuery =
    from b in _context.Bets
    where b.Status == 'W'
    group b by new { b.Shop } into g
    select new 
    {
        g.Key.Shop,
        Anticipation = g.Sum(x => x.WinAmount)
    };

var query = 
    from b in betsQuery
    join t in transactionQuery on b.Shop equals t.Shop into tg
    from t in tg.DefaultIfEmpty()
    join a in antisipationQuery on b.Shop equals a.Shop into ag
    from a in ag.DefaultIfEmpty()
    select new
    {
        b.Shop,
        b.Stake,
        Payout = (double?)t.Payout,
        Anticipation = (double?)a.Anticipation
    };

var result = query.ToList();

Convert SQL to Linq with multiple joins , SUM and group by

1 Answers1