c# - 中断更改后如何在 EF Core 3.1 中创建复杂的动态查询？

Question

我有这个函数返回 IQueryable :

private IQueryable<string> GetActiveCellPhoneNumbersUpToDate(long serviceToken, DateTime date, bool? isPrepaid = null)
{
    var to = date.AddDays(1).Date;
    var query = ViewRepository
        .All
        .Where(i => i.ServiceToken == serviceToken)
        .Where(i => i.Date < to);
    if (isPrepaid.HasValue)
    {
        query = query.Where(i => i.IsPrepaid == isPrepaid);
    }
    query = query.OrderByDescending(i => i.Date);
    var result = query
        .GroupBy(i => i.CellPhoneNumber)
        .Where(i => i.First().ActionId == (int)SubscriptionAction.Subscription)
        .SelectMany(i => i.ToList())
        .Select(i => i.CellPhoneNumber)
        .Distinct();
    return result;
}

这个函数将被称为另一个仅用于计数的函数:

var prepaidsCount = GetActiveCellPhoneNumbersUpToDate(serviceToken, DateTime.Date, true);
var postPaidsCount = GetActiveCellPhoneNumbersUpToDate(serviceToken, DateTime.Date, false);

当我执行它时，我看到 a breaking change of EF 3.0 ， 说的是:

Processing of the LINQ expression 'i => i .ToList()' by 'NavigationExpandingExpressionVisitor' failed. This may indicate either a bug or a limitation in EF Core. See https://go.microsoft.com/fwlink/?linkid=2101433 for more detailed information.

正如重大变更说明中所述，我需要用户 AsEnumerable或 ToList前复合体 Where子句来执行那部分 LINQ 并将数据带入 RAM，然后继续我的查询。

但是对于需要动态查询的大量数据来说，这绝对是疯狂且低效的。

这有什么替代品？我们如何创建动态复杂查询以在运行时进行翻译并且只返回一个单一的标量值？

更新:现实世界的需求不是 Hello World 的例子。它们需要将复杂的过滤和排序和分组等功能混合在一起，才能从关系结构中提取数据。过去，我们会出于这些目的使用存储过程。将几个参数传递给数据库，并编写丑陋、难以测试、难以维护、每周类型化、抗重构的 SQL 代码来获取数据。

现在我想到的唯一选择是降级回那些丑陋的存储过程。这个噩梦在 EF 3.1 中成为现实吗？

更新 2:这是我的场景。我有一个表格，我在其中存储特定服务中的手机号码订阅/取消。该表的简化版本是:

create table Subscriptions
(
    Id,
    CellPhoneNumber,
    ServiceId,
    Date,
    ActionId
)

这些可以是记录:

John,+1-541-754-3010,15,2019-10-13 12:10:06.153,1
John,+1-541-754-3010,15,2019-10-18 12:10:06.153,2

在这里我们可以看到，John 订阅了服务 15，并在其中停留了 5 天，然后他取消了。如果我们想报告我们在 2019-10-14 有多少订阅者，John 将被计算在内。因为那个时候，他最后一个 Action 就是报名。但是如果我们想在 2910-11-03 报告我们有多少订阅者然后约翰的最后一个 Action 是离开服务，他不应该被计算在内。

Answer 1

依赖于间隔或记录的当前状态的查询可能很棘手。通常，我们必须搜索在指定时间段内具有一种状态但没有另一种状态的订阅。这至少需要一个子查询或 CTE。即使使用索引，这也可能很昂贵，因为它需要对目标表进行两次查找或扫描。

欢迎任何避免这种情况的技巧。在这种特殊情况下，操作 ID 为 1 和 2，获取活跃订阅者的一种简单方法是获取 MAX(ActionID) 不是 2 或小于 2 的订阅者，例如:

SELECT COUNT(Distinct cellnumber)
FROM Subscriptions 
WHERE Date <=@reportDate ....
GROUP by CellNumber
HAVING MAX(ActionID)<2

LINQ 中的等价物是

var actives= ctx.Subscriptions
                .Where(sub=>sub.Date <= reportDate )
                .GroupBy(sub=>sub.CellNumber)
                .Where(grp=>grp.Max(sub=>sub.ActionId)<2)  // Results in a HAVING clause
                .Distinct()
                .Count();

添加其余的标准:

var query = ctx.Subscriptions
                .Where(sub=>sub.Date <= reportDate && sub.ServiceToken == serviceToken);
if(isPrepaid.HasValue)
{
    query = query.Where(sub => sub.IsPrePaid==isPrepaid);
}

var actives= query.GroupBy(sub=>sub.CellNumber)
                  .Where(grp=>grp.Max(sub=>sub.ActionId)<2)
                  .Distinct()
                  .Count();

SQL Server 2016 时态表

如果我们有幸使用 SQL Server 2016 或更高版本，我们可以转换 Subscriptions放入一个时态表中，并简单地计算在某个时间点具有特定状态的订阅。我们可以使用:

SELECT COUNT(DISTINCT CellPhoneNumber)
FROM Subscriptions  FOR SYSTEM_TIME AS OF @someTime
WHERE ActionID<2

EF Core 不直接支持时态表，所以我们需要使用 FromSqlRaw对于查询的那部分:

var query = ctx.Subscriptions
                .FromSqlRaw("select * from Subscriptions FOR SYSTEM_TIME AS OF {0}",
                            reportDate)
                .Where(sub=>sub.Date <= reportDate && sub.ServiceToken == serviceToken);
if(isPrepaid.HasValue)
{
    query = query.Where(sub => sub.IsPrePaid==isPrepaid);
}

var actives= query.Distinct()
                  .Count();

该查询不涉及分组。它不取决于 Action 值的实际数量或顺序，也不会被每个订阅的多个记录混淆。