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

Question

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?

Recommended for you: Get network issues from WhatsUp Gold. Not end users.

Answer 1

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

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

Sign up