LINQ to Sql Left Outer Join with Group By and Having Clause

Question

I lost a day to try translate a sql query to LINQ lambda expression but not success.

My sql query:

SELECT a.ID,
       Sum(b.[Value]) AS [Value],
       c.ContractValue
FROM   Contracts a
       LEFT JOIN DepositHistories b
              ON b.ContractID = a.ID
       INNER JOIN LearningPackages c
               ON a.LearningPackageID = c.ID
GROUP  BY a.ID,
          c.ContractValue
HAVING Sum(b.[Value]) < c.ContractValue
        OR Sum(b.[Value]) IS NULL
        OR Sum(b.[Value]) = 0

This is LINQ query:

var contracts = (
                from a in db.Contracts
                from b in db.LearningPackages.Where(e => e.ID == a.LearningPackageID).DefaultIfEmpty()
                group a by new
                {
                    a.ID,
                    b.ContractValue
                } into g
                from c in db.DepositHistories.Where(e => e.ContractID == g.Key.ID).DefaultIfEmpty()
                where g.Sum(e => c.Value) < g.Key.ContractValue || g.Sum(e => c.Value) == null
                select new
                {
                    ID = g.Key.ID,
                    ContractValue = g.Key.ContractValue,
                    Value = g.Sum(e => c.Value != null ? c.Value : 0)
                }
                ).ToList();

My result:

  ID  ContractValue    Value  
  1      6000000      500000  
  1      6000000      500000  
  1      6000000      500000  
  1      6000000      500000  
  1      6000000      500000  
  3      7000000      500000  
  3      7000000      500000  
  3      7000000      500000  
  4      6000000      500000  
  5      6000000      0  
  6      6000000      0

It's not group and sum the values.

Please help me!

Thanks!

We're not a code writing service. We help fix code that isn't working or is incomplete. — Enigmativity, Jan 01 '15 at 11:58
are you sure you dont want this INNER JOIN to be executed before your LEFT JOIN ???? — M.Ali, Jan 01 '15 at 11:58
@M.Ali: joins aren't necessarily executed in the order they are declared. — Mitch Wheat, Jan 01 '15 at 11:59
Thanks for code. Could you please now tell us what means "but not success" ? — AFract, Jan 01 '15 at 12:08
@MitchWheat but still it wouldn't make any sense to have a LEFT before INNER join since the inner join will filter out all the non-matching rows (unless it is intentional). — M.Ali, Jan 01 '15 at 12:10

score 5 · Accepted Answer · edited May 23 '17 at 11:50

you can do it like this:

var result = from b in db.DepositHistories
             join a in db.Contracts on b.CotractID equals a.ID
             join c in db.LearningPackages on a.LearningPackageID equals c.ID
             group b by new{ a.ID,c.COntractValue} into g
             where g.Sum(x=>x.Value) < g.Key.COntractValue 
             || g.Sum(x=>x.Value) == null 
             || g.Sum(x=>x.Value) == 0
            select new 
                  { 
                   ID = g.Key.ID, 
                   Value = g.Sum(x=>x.Value), 
                   ContractValue = g.Key.COntractValue
                  };

I made a DEMO FIDDLE to be more clear.

UPDATE:

For left outer join you have to do join your condition into somealias and them from alias in somealias.DefaultIfEmpty().

Here is the version with left outer join which gives correct results:

var result = from a in Contracts
             join b in DepositHistories on a.ID equals b.CotractID into e
             from f in e.DefaultIfEmpty()
             join c in LearningPackages on a.LearningPackageID equals c.ID
             group f by new 
                       { 
                          a.ID, 
                          c.COntractValue 
                       } into g
             where g.Sum(x => x==null ? 0 : x.Value) < g.Key.COntractValue 
             ||  g.Sum(x => x==null ? 0 : x.Value) == 0
             select new 
                   { 
                      ID = g.Key.ID, 
                      Value = g.Sum(x => x == null ? 0 : x.Value), 
                      ContractValue = g.Key.COntractValue 
                   };

UPDATED FIDDLE DEMO

You can also check this SO post about How to do left outer join in LINQ

UPDATE 2:

Using query method you have to use GroupJoin() method for left outer join.

Here is the above code with Method Query:

var Result = Contracts.GroupJoin(DepositHistories, 
                                    a => a.ID, 
                                    b => b.CotractID, 
                                    (a, b) => new { a = a, b = b })
                                  .Join(LearningPackages, 
                                  a => a.a.LearningPackageID, 
                                  b => b.ID, 
                                  (a, b) => new { a = a, b = b })
                                  .GroupBy(e => new 
                                                    { 
                                                        e.a.a.ID, 
                                                        e.b.COntractValue 
                                                    }, 
                                                    (k, g) => new 
                                                                { 
                                                                    ID = k.ID, 
                                                                    ContractValue = k.COntractValue, 
                                                                    Value =  g.Sum(x => x == null ? 0 : x.a.b.Sum(d=>d.Value)) 
                                                                }
                                            ).Where(x => x.Value < x.ContractValue || x.Value == 0).ToList();

UPDATED FIDDLE WITH METHOD QUERY

Thanks for your code, but this code not get all my contracts, which have DepositHistories is null. — Son Le, Jan 01 '15 at 12:32
My sql query have left join, how to can I make this in LINQ query? — Son Le, Jan 01 '15 at 12:39
@SơnLê check the updated post with updated demo, it will now give accurate results now it is left outer join — Ehsan Sajjad, Jan 01 '15 at 12:51
the ``into e from f in e.DefaultIfEmpty()`` is doing left outer join — Ehsan Sajjad, Jan 01 '15 at 12:56
@EhsanSajjad I try convert this code to method query, you can edit to show beuty code: Contracts .Join(DepositHistories, a => a.ID, b => b.ContractID, (a, b) => new { a = a, b = b }) .Join(LearningPackages, a => a.a.LearningPackageID, b => b.ID, (a, b) => new { a = a, b = b }) .GroupBy(e => new { e.a.a.ID, e.b.ContractValue }, (k, g) => new { ID = k.ID, ContractValue = k.ContractValue, Value = g.Sum(x => x == null ? 0 : x.a.b.Value) }) .Where(x => x.Value < x.ContractValue || x.Value == 0) .ToList() How to can I using DefaultIfEmpty() in this? Thanks! — Son Le, Jan 01 '15 at 14:21
@Son Le In method query we have groupjoin method see the SO post whose link I added in my answer, currently I am on cell fone so cannot do much, will update my post from PC — Ehsan Sajjad, Jan 01 '15 at 14:37
@EhsanSajjad, Thanks for your code but yesterday I had make code like you, but this still don't show left join result, your code also. — Son Le, Jan 02 '15 at 06:47
@EhsanSajjad, Seem you changed source code again after my above comment? — Son Le, Jan 08 '15 at 02:43

LINQ to Sql Left Outer Join with Group By and Having Clause

1 Answers1

UPDATE:

UPDATE 2:

Linked