时间戳

首页 > 解决方案 > 通过 LINQ 检索非活动客户端

database - 通过 LINQ 检索非活动客户端

问题描述

我的代码有问题。我想检索上周所有不活跃的客户（没有订单）。这是我从数据库中获取的信息。 数据库图片中的信息 下面是我在 LINQ 中的代码，但它不返回任何内容。

DateTime daysBeforeToday = DateTime.Today.AddDays(-7);
var queryCount = context.Orders.Select(x=>x.ID).Count();
var query = (from o in context.Orders
where o.OrderDate >= daysBeforeToday
select new { CustomerName = o.CustomerName, ID = o.ID } into Customers
group Customers by Customers.CustomerName into pg
where queryCount == 0
select pg.Key);
return query.ToList();

请指教。谢谢

更新
我发现这个 SQL 语句有效：

SELECT CustomerName, MAX(OrderDate) as LastOrderDate
FROM Orders 
GROUP By CustomerName
having MAX(OrderDate) < dateAdd(day,-7, GetDate())

但是当我在 LINQ 中转换时它失败了。我做错了什么？

DateTime daysBeforeToday = DateTime.Today.AddDays(-7);
var queryMax = context.Orders.Select(x=>x.OrderDate).Max();

var query = (from o in context.Orders
            select new { CustomerName = o.CustomerName, Date = o.OrderDate } into Customers
            group Customers by Customers.CustomerName into pg
            where queryMax < daysBeforeToday
            select pg.Key);

return query.ToList();

标签： databaselinqclientorders

解决方案

像评论一样，Max应该为每个分组的客户计算 The ，如以下代码：

DateTime daysBeforeToday = DateTime.Today.AddDays(-7);

var query = (from o in context.Orders           
            group o by o.CustomerName into pg
            where pg.Max(x => x.OrderDate) < daysBeforeToday
            select pg.Key);

return query.ToList();

我希望你觉得这有帮助。

推荐阅读