JsonStatham JsonStatham - 3 years ago 146
C# Question

C# LINQ statement with joins, group by and having then mapped into list object

I have a model called

ElectricityBillSiteExceeding
that looks like this:

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; }
}


I want to create a list of this type and use it to feed a grid on one of my pages, the purpose is to show which site has bills that exceed the max amount allowed.

I have written the SQL which will give me this dataset, it looks like this:

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


I'm now in my repository trying to write the method which will go to the database and fetch this dataset so that I can power my grid for the user to see.

This is where I'm struggling. I have written a basic LINQ statement to select from a couple of tables, however I'm unsure how I can incorporate the group by and having clause from my SQL and also how I can then turn this IQueryable object into my
List<ElectricityBillSiteExceeding>
object.

What I have so far

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();
}


Can anyone point me in the right direction here?

Answer Source

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
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download