c# - 根据子句加入并仅选择第一项

Question

我有一个客户和一个订单数据库。我需要对所有新客户的首单进行一些统计，并按月统计新客户的首单数量。`

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

此查询为我提供了所有新客户的订单数量，但如果第一个订单日期大于提供的日期，我只需要获取每个新客户的第一个订单的总和。

是否可以通过查询(以及如何)来完成，或者我是否被迫使用 AsEnumerable 并在内存中完成？

Answer 1

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