CODEWITHSUNDEEP
c#entity-framework-6
Demonia
Demonia

Reputation: 342

Joining and selecting only the first item based on clause

I have a Customers and an Orders database. I need to make some statistics for the first order of all new customers and count the number of first orders from new clients by month.`

var date = new DateTime(now.Year - 1, now.Month, 1);
db.Orders
  .Where(o => o.Customer.IsNew && o.OrderDate > date)
  .GroupBy(o => new { o.OrderDate.Year, o.OrderDate.Month })
  .Select(g => new NewCustomerStatsModel {
     Month = g.Key.Month,
     Year = g.Key.Year,
     Count = g.Count()
  })
  .OrderBy(cs => cs.Year)
  .ThenBy(cs => cs.Month)
  .ToList();

This query provide me the number of orders for all new client but I need to get only the sum of the first order for each new Customer if the first order date is greater than the provided date.

Is it possible to do it with a query (and how) or am I forced to use AsEnumerable and do it in memory?

Upvotes: 2

Views: 63

Answers (2)

Demonia
Demonia

Reputation: 342

I could find the solution.

With some appropriate index, the performances are pretty good.

It's probably not a perfect solution, but I couldn't update the entities because it's not my Library.

var date = new DateTime(now.Year - 1, now.Month, 1);
var result = db.Orders
  .Where(o => o.Customer.IsNew && o.State != OrderState.Cancelled) // get all orders where the Customer is a new one.
  .GroupBy(o => o.Customer.Id) // group by customer
  .Select(g => g.OrderBy(o => o.OrderDate).FirstOrDefault()) // get the first order for every customer
  .Where(o => o.OrderDate > date) // restrict to the given date
  .GroupBy(o => new { o.OrderDate.Year, o.OrderDate.Month) }) // then group by month
  .Select(g => new NewCustomerStatsModel {
    Month = g.Key.Month,
    Year = g.Key.Year,
    Count = g.Count()
  })
  .OrderBy(g => g.Year)
  .ThenBy(g => g.Month)
  .ToList();

Upvotes: 0

StriplingWarrior
StriplingWarrior

Reputation: 156728

I need to make some statistics for the first order of all new customers

var clientFirstOrders = db.Customers.Where(c => c.IsNew)
    .Select(c => new{
        Customer = c, 
        FirstOrder = c.Orders.OrderBy(c => c.OrderDate).FirstOrDefault()
    })
    // might have to do (int?)FirstOrder.Id != null or something like that.
    .Where(e => e.FirstOrder != null);

and count the number of first orders from new clients by month.

var clientCountByFirstOrderMonth = clientFirstOrders 
    .GroupBy(e => new { e.FirstOrder.OrderDate.Year, e.FirstOrder.OrderDate.Month })
    .Select(g => new{g.Key.Year, g.Key.Month, Count = g.Count()});

Upvotes: 3

Related Questions