Linq2DB中避免物化生成序列号的实现问题

我需要实现一个获取数据项并分配序列号的功能，序列号对应按TurnedOffOn字段排序后的项编号。当前是通过ToArray()物化数据后循环分配序列号，希望避免物化实现该功能。尝试了多种Linq2DB方案均失败：

• 直接遍历IQueryable分配序列号，抛出异常；
• 使用带index的Select创建新RequestLog实例，出现索引越界异常；
• 使用Sql.Ext.RowNumber()生成序列号，生成的SQL在SSMS中可正常执行，但后续调用OrderItems、TakePage、ProjectTo进行排序分页投影时，抛出TargetInvocationException，内部异常提示“Member 'Ext' not found in type 'FKDH`2'”。

原始Query方法（物化分配序列号） #

// 原始Query方法
public IQueryable<RequestLog> Query(bool includeRelated = false)
{
    var allItems = _context.GetTable<RequestLog>().AsQueryable();
    if (includeRelated)
    {
        allItems = allItems
            .LoadWith(r => r.Request).ThenLoad(w => w.Addresses)
            .LoadWith(r => r.Request).ThenLoad(w => w.TurnedOffObjects)
            .LoadWith(r => r.Request).ThenLoad(w => w.Executor)
            .LoadWith(r => r.District)
            .LoadWith(r => r.RospotrebnadzorNotes);
    }
    var orderedItems = allItems.OrderBy(log => log.TurnedOffOn);
    var numberedItems = orderedItems.ToArray();
    for ( var i = 0; i< numberedItems.Length; i++)
    {
        numberedItems[i].SequenceNumber = i + 1;
    }
    return numberedItems.AsQueryable();
}

使用RowNumber的Query方法 #

// 使用RowNumber的Query方法
public IQueryable<RequestLog> Query(bool includeRelated = false)
{
    var allItems = _context.GetTable<RequestLog>().AsQueryable();
    if (includeRelated)
    {
        allItems = allItems
            .LoadWith(r => r.Request).ThenLoad(w => w.Addresses)
            .LoadWith(r => r.Request).ThenLoad(w => w.TurnedOffObjects)
            .LoadWith(r => r.Request).ThenLoad(w => w.Executor)
            .LoadWith(r => r.District)
            .LoadWith(r => r.RospotrebnadzorNotes);
    }
    var orderedItems = allItems.OrderBy(log => log.TurnedOffOn);
    return orderedItems.OrderBy(log => log.TurnedOffOn)
     .Select(log => new RequestLog
     {
         Id = log.Id,
         SequenceNumber = (int)Sql.Ext.RowNumber().Over().OrderBy(log.TurnedOffOn).ToValue(),
         //其他字段
     });
}

OrderBy扩展方法 #

// OrderBy扩展方法
public static IOrderedQueryable<T> OrderBy<T>(this IQueryable<T> src, string propertyName, bool reverse = false)
{
    var propertyInfo = typeof(T).GetProperty(propertyName);
    if (propertyInfo == null)
        throw new ArgumentException($"property {propertyName} not found on object {typeof(T).Name}");
    var keySelector = BuildKeySelector<T>(propertyInfo);
    var targetMethodName = !reverse ? "TargetOrderBy" : "TargetOrderByDescending";
    var methodInfo = typeof(QueryableExtensions).GetMethod(targetMethodName, BindingFlags.NonPublic | BindingFlags.Static);
    if (methodInfo == null)
        throw new Exception($"can't bind to generic target method {targetMethodName}");
    var method = methodInfo.MakeGenericMethod(typeof(T), propertyInfo.PropertyType);
    return method.Invoke(null, new[] {src, keySelector}) as IOrderedQueryable<T>;
}

内容的提问来源于stack exchange，提问作者user19627737