Selecting Distinct Count and Sum of columns received as sub-query in Entity Framework

Question

I want to get summarized data for a report that shows total amount & suppliers Count per decision in entity Framework Syntax. My Result needed to include a SUM of Amount and COUNT of total suppliers per decision.

I have a table of suppliers with the following columns:

Raw SQL query to get above data for a specific time period is:

SELECT S.Decision, SUM(S.Amount) AS TotalAmount, COUNT(DISTINCT S.SupplierNo) AS SupplierCount
FROM (SELECT * FROM Indentors WHERE Indentors.DecisionIssuedOn BETWEEN '2018-01-01' AND '2018-12-31') S
GROUP BY S.Decision

Which gives data as:

     SupplierCount | Amount 
     -----------------------
Approved     20 |  5000

Rejected     11 |  3000

In-Process   5  |  1500

Now from front end, the condition parameters can be anything from the given pool of options (dropdowns) which when selected add where clause in the exixting query like

WHERE Decision = 'Approved' AND SupplierGroup ='ABC' AND SubSupplier ='zxc'

The problem is I am having a hard time getting the desired result using Entity Framework lambda expressions instead of raw SQL.

What I did so far:

I checked for the availability of Options from fornt-end to build where clause as:

IQueryable<Supplier> suppliers = this.db.suppliers.OrderByDescending(i => i.Id);


            if (string.IsNullOrEmpty(selectedSupplierGroup) == false)
            {
                suppliers = suppliers.Where(i => i.SupplierGroup == selectedSupplierGroup);
            }

            if (string.IsNullOrEmpty(selectedSubSupplier) == false)
            {
                suppliers = suppliers.Where(i => i.SubSupplier == selectedSubSupplier);
            }
            if (string.IsNullOrEmpty(selectedDecision) == false)

            {
                suppliers = suppliers.Where(i => i.Decision == selectedDecision);
            }

            if (selectedDecisionIssuedOn.HasValue)

            {
                suppliers = suppliers.Where(i => i.DecisionIssuedOn >= selectedDecisionIssuedOn);
            }

              var result = suppliers
                        .GroupBy(i => i.Decision)
                        .Select(i => i.SupplierNo).Distinct().Count(); // Gives me error

The error is:

IGrouping does not contain a definition for SupplierNo, and no extension method blah blah blah...

But after that I am unable to get data as the raw query (described above) would get me. Thanks

What error do you get? Please provide as much information as you can :) — Viggo Lundén, May 10 '19 at 14:01
Similar question: https://stackoverflow.com/questions/448203/linq-to-sql-using-group-by-and-countdistinct — Metheny, May 10 '19 at 14:11

score 2 · Accepted Answer · answered May 10 '19 at 14:23

2

This should give you a similar result to your SQL query. Give it a try and see how you get on:

var results = suppliers
    .Where(i => i.DecisionIssuedOn >= selectedDecisionIssuedOn)
    .GroupBy(i => i.Decision)
    .Select(group => new
    {
        Decision = group.Key,
        TotalAmount = group.Sum(g => g.Amount),
        SupplierCount = group.Select(i => i.SupplierNo).Distinct().Count()
    });

answered May 10 '19 at 14:23

Adam Greenall

189
2
10

Sure, let me give it a try – Programmer May 10 '19 at 14:26
Can you please explain: In your answer, do I still have to use .Where(i=> i.DecisionIssuedOn) ... because where clause is being created dynamically in the code. – Programmer May 10 '19 at 14:35
@Programmer No, you can remove the Where if you're going to do it above. – Adam Greenall May 10 '19 at 14:46
That seems promising. Thank you for assistance. – Programmer May 10 '19 at 14:51

Selecting Distinct Count and Sum of columns received as sub-query in Entity Framework

1 Answers1