LINQ to Sql Left Outer Join with Group By and Havi

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!

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