Linq -Return Unique Values and Sum of Another

Question

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

Tapan Patel · Answer

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.

Santhosh Kumar Chandran · Answer

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