SanthoshKumar SanthoshKumar - 1 year ago 90
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

is linked to

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
of all
next to each one - the purpose being "Top Ten Retailers"!

How can i do this?

Answer Source
  • 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)
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download