LINQ to Sql Left Outer Join with Group By and having Clause
I wasted one day trying to translate the sql query into a LINQ lambda expression but failed.
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 a 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 does not group or sum values.
Please help me!
Thank!
+3
source to share
1 answer
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 DEMO FIDDLE to be more clear.
UPDATE:
For the left outer join, you need to do those as join your condition into somealias
well from alias in somealias.DefaultIfEmpty()
.
Here is the left outer join version that gives the 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
};
You can also check out this SO post on how to do a left outer join in LINQ
UPDATE 2:
When using the request method, you must use the GroupJoin () method for the left outer join.
Here is the above code with a Query method:
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();
+5
source to share