SanthoshKumar SanthoshKumar - 16 days ago 9
C# Question

Return Unique values and Sum LINQ

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


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

How can i do this?

Answer
  • Use GroupBy to convert a flat list to groups by RetailerName
  • Use Sum(i => i.InvoiceProfit) to compute totals
  • Use new { ... } to pair up retailers with their profit totals
  • Use OrderByDescending(p => p.TotalProfit) to get high-profit retailers to the top
  • Use Take(10) to limit the list to ten items.

Overall query would 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();