CODEWITHSUNDEEP
c#linq
random
random

Reputation: 316

Use Linq to get Top 10 Customers

I have the following two POCO's

public class Customers
{
    public Guid Id { get; set; }
    public string FirstName{ get; set; }
    public string LastName{ get; set; }
}

and

public class Orders
{
    public Guid Id { get; set; }
    public Guid CustomerId { get; set; }
    public string Product { get; set; }
    public DateTime Date { get; set; }
}

Assuming my Data Service returns a List<Customers> and List<Orders>, how would I use Linq to Get the Top 10 Most Active Customers (most orders)?

I want to return Customer.Id, Customer.FirstName, Customer.LastName, Count, (as Json from a controller action), so I created a new POCO to hold results. something like the following:

public class MostActive
{
    public Guid Id { get; set; }
    public string FirstName { get; set; }
    public string LastName { get; set; }
    public int Count { get; set; }
}

Although it's not working, here is what my current attempt looks like so far:

var results = (from c in Customers
              from o in Orders
              where c.Id == o.CustomerId
              orderby c.Id.Count descending
              select new MostActive
                  {
                      FirstName = c.FirstName,
                      LastName = c.LastName,
                      Id = c.Id,
                      Count = h.Id.Count
                  }).Take(10);

Upvotes: 3

Views: 776

Answers (2)

Anton&#237;n Lejsek
Anton&#237;n Lejsek

Reputation: 6103

With foreign keys properly defined this should be easy

    Customers.Select(c => new MostActive {
              FirstName = c.FirstName,
              LastName = c.LastName,
              Id = c.Id,
              Count = c.Orders.Count()
              })
        .OrderByDescending(o => o.Count)
        .Take(10);

Foreign keys are defined in database. Under linq I assumed Linq2SQL or EF, both generate those relations automatically from database.

Upvotes: 1

Rhumborl
Rhumborl

Reputation: 16609

This involves a few steps, which you can break down as so:

  1. Group orders by customer ID
  2. Sort groupings by total
  3. Get the top (first) 10
  4. Associate the relevant customers
  5. Create the result object

Something like this should do it:

var results = Orders
    .GroupBy(o => o.CustomerId)
    .OrderByDescending(og => og.Count())
    .Take(10)
    .Select(og => new {
        Customer = Customers.Single(c => c.Id.Equals(og.Key)),
        Orders = og
    })
    .Select(c => new MostActive {
        Id = c.Customer.Id,
        FirstName = c.Customer.FirstName,
        LastName = c.Customer.LastName,
        Count = c.Orders.Count()
    });

Or to carry on where you started from, which is nearly there, this will also work. As you said the main part is adding the grouping and then doing a join to customers:

var results = (
    from o in Orders
    group o by o.CustomerId into og // create a grouping of orders by customerID
    orderby og.Count() descending // sort by number of orders in each grouping
    join c in Customers on og.Key equals c.Id // og.Key is the grouping Key (CustomerId)
    select new MostActive
    {
        FirstName = c.FirstName,
        LastName = c.LastName,
        Id = c.Id,
        Count = og.Count()
    }).Take(10);

Upvotes: 3

Related Questions