Return Unique values and Sum LINQ

Question

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 1

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

Answer 2

I use combination of lambda and linq. See msdn : https://code.msdn.microsoft.com/LINQ-Join-Operators-dabef4e9

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;

namespace ConsoleApplication28
{
    class Program
    {
        static void Main(string[] args)
        {
            DataTable retailers = new DataTable();
            retailers.Columns.Add("RetailerID", typeof(int));
            retailers.Columns.Add("RetailerName", typeof(string));

            retailers.Rows.Add(new object[] { 123, "abc" });
            retailers.Rows.Add(new object[] { 124, "abd" });
            retailers.Rows.Add(new object[] { 125, "abe" });

            DataTable invoices = new DataTable();
            invoices.Columns.Add("InvoiceID", typeof(int));
            invoices.Columns.Add("InvoiceProfit", typeof(decimal));
            invoices.Columns.Add("RetailerID", typeof(int));

            invoices.Rows.Add(new object[] { 100, 200, 123 });
            invoices.Rows.Add(new object[] { 101, 201, 123 });
            invoices.Rows.Add(new object[] { 102, 202, 123 });
            invoices.Rows.Add(new object[] { 103, 203, 123 });
            invoices.Rows.Add(new object[] { 104, 204, 124 });
            invoices.Rows.Add(new object[] { 105, 205, 124 });
            invoices.Rows.Add(new object[] { 106, 206, 124 });
            invoices.Rows.Add(new object[] { 107, 207, 125 });
            invoices.Rows.Add(new object[] { 108, 208, 125 });
            invoices.Rows.Add(new object[] { 109, 209, 125 });

            var retailersAndInvoices = (from r in retailers.AsEnumerable()
                                        join i in invoices.AsEnumerable() on r.Field<int>("RetailerID") equals i.Field<int>("RetailerID")
                                        select new { name = r.Field<string>("RetailerName"), profit = i.Field<decimal>("InvoiceProfit") })
                                       .GroupBy(x => x.name)
                                       .Select(x => new { name = x.Key, totalProfit = x.Select(y => y.profit).Sum() }).ToList();


        }
    }
}