Joining and selecting only the first item based on clause

c# entity-framework-6

Question

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?

1
2
1/11/2019 4:37:09 PM

Accepted Answer

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();
0
1/14/2019 1:52:35 PM

Popular Answer

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


Related Questions





Related

Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow