Linq -Return Unique Values and Sum of Another

Jon Bellamy

17y5.4kLINQ

Hi,

I have two tables:

1. Retailers
2. Invoices

Retailers has two columns:
1.1. RetailerID
1.2. RetailerName

Invoices has three columns:
2.1. InvoiceID
2.2. InvoiceProfit
2.3. RetailerID

Retailers.RetailerID is linked to Invoices.RetailerID (one-to-many).

What I want to do is write a linq (or in the form of a lambda exp) that returns Retailer.RetailerID, Retailer.RetailerName, Invoice.InvoiceProfit.

I can do this like so:

var retailers = from r in db.Retailers select t; var invoices = from i in db.Invoices select i; var retailersAndInvoices = from r in retailers join i in invoices on r.RetailerID equals i.RetailerID select new {t.RetailerName, i.InvoiceProfit};

However the snag I have is that I want to return only Distinct RetailerNames and the Sum of all Invoices.InvoiceProfit next to each one - the purpose being "Top Ten Retailers"!

I'm pulling my hair out trying to write the expression - does anyone know if you can do this?

Thanks - Jon

2 Replies

Know the answer? Post it — somebody with the same question will find it here.

Tapan Patel9y ago
I have prepared the sample query like this. Please go through it and try optimizing it. ( Although, it is as expected by your requirement there is a performance hit and hence, I am also suggesting to do the same in Stored proc or SQL server side which would save some time).

C#: Assuming that EF brings 3 or 4 (in my case) rows back as the list of Retailers and Invoices
List Retailers = dbcontext.RTables.ToList();
List Invoices = dbcontext.ITables.ToList();

var x = Retailers.Join(Invoices, retailer => retailer.id, invoice => invoice.Rid, (RetailerTable, InvoiceTable) => new { id = RetailerTable.id, rname = RetailerTable.rname, IProfit = InvoiceTable.IProfit })
                .GroupBy(z => new { z.id, z.rname })
                .Select(GroupedResult =>
                    new {
                            id=GroupedResult.Key.id,
                            name=GroupedResult.Key.rname,
                            profit =GroupedResult.Sum(y => y.IProfit)
                        }
                    )
                .OrderByDescending(k=>k.profit)
                .Take(3);
SQL Server:
SELECT DISTINCT rname,SUM(IProfit) FROM dbo.RTable
INNER JOIN dbo.ITable ON id=Rid
GROUP BY rname
Please mark it as answer if address the issue.
0
Santhosh Kumar Chandran9y ago
Your query should look like this,
var topTen = retailersAndInvoices
.GroupBy(ri => ri.RetailerName)
.Select(g => new {
Retailer = g.Key
, TotalProfit = g => g.Sum(i => i.InvoiceProfit)
})
.OrderByDescending(p => p.TotalProfit)
.Take(10)
.ToList();

0