I have a model called
ElectricityBillSiteExceeding
public class ElectricityBillSiteExceeding
{
public string GroupInvoiceNumber { get; set; }
public int ElectricityBillMainId { get; set; }
public string SiteNo { get; set; }
public decimal BillSiteTotal { get; set; }
public decimal MaximumAmount { get; set; }
}
SELECT SUM(ElectricityBillSiteTotal),
ebs.ElectricityBillMainId,
SiteNo,
ebm.GroupInvoiceNumber,
es.MaximumAmount
FROM dbo.ElectricityBillSites ebs
LEFT JOIN dbo.ElectricityBillMains ebm
ON ebs.ElectricityBillMainId = ebm.ElectricityBillMainId
LEFT JOIN dbo.ElectricitySites es
ON ebs.SiteNo = es.SiteNumber
GROUP BY ebs.ElectricityBillMainId, SiteNo, ebm.GroupInvoiceNumber, es.MaximumAmount
HAVING SUM(ElectricityBillSiteTotal) <> 0 AND SUM(ElectricityBillSiteTotal) > es.MaximumAmount
List<ElectricityBillSiteExceeding>
public List<ElectricityBillSiteExceeding> GetAllElectricityBillSiteExceedings()
{
var groupedBillSitesThatExceed = from billSites in _context.ElectricityBillSites
join billMains in _context.ElectricityBillMains on billSites.ElectricityBillMainId equals
billMains.ElectricityBillMainId
join sites in _context.ElectricitySites on billSites.SiteNo equals sites.SiteNumber
//TODO: group by total, mainId, siteNo, GroupInv, MaxAmt and Having no total = 0 and total > max
select new
{
groupInv = billMains.GroupInvoiceNumber,
mainId = billMains.ElectricityBillMainId,
siteNo = billSites.SiteNo,
total = billSites.ElectricityBillSiteTotal,
max = sites.MaximumAmount
};
//TODO: Map the result set of the linq to my model and return
throw new NotImplementedException();
}
The correct Linq query for your sql is the following. See Left Join to understand the DefaultIfEmpty
and also the notes there about the use of ?.
in the following group by
.
(About the having
- in linq you just provide a where
after the group by
)
var result = from ebs in ElectricityBillSites
join ebm in ElectricityBillMains on ebs.ElectricityBillMainId equals ebm.ElectricityBillMainId into ebmj
from ebm in ebmj.DefaultIfEmpty()
join es in ElectricitySites on ebs.SiteNo equals es.SiteNumber into esj
from es in esj.DefaultIfEmpty()
group new { ebs, ebm, es } by new { ebs.ElectricityBillMainId, ebs.SiteNo, ebm?.GroupInvoiceNumber, es?.MaximumAmount } into grouping
let sum = grouping.Sum(item => item.ebs.ElectricityBillSiteTotal)
where sum > 0 && sum > grouping.Key.MaximumAmount
orderby sum descending
select new ElectricityBillSiteExceeding
{
GroupInvoiceNumber = grouping.Key.GroupInvoiceNumber,
ElectricityBillMainId = grouping.Key.ElectricityBillMainId,
SiteNo = grouping.Key.SiteNo,
BillSiteTotal = sum,
MaximumAmount = grouping.Key.MaximumAmount
};
The error you get:
An expression tree lambda may not contain a null propagating operator
By reading this I conclude that you have an older versino of the provider and thus replace the group by code from the code above with the following:
let GroupInvoiceNumber = ebm == null ? null : ebm.GroupInvoiceNumber
let MaximumAmount = es == null ? 0 : es.MaximumAmount
group new { ebs, ebm, es } by new { ebs.ElectricityBillMainId, ebs.SiteNo, GroupInvoiceNumber, MaximumAmount } into grouping