Generating sequence number from linq2db without materialization

Question

I have a method that gets items and assigns sequence number. Sequence number correspondes to the item's number ordered by TurnedOffOn field.

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

I want to generate sequence number without materialozation, or using ToArray(). I use linq2db, and tried different ways, and nothing worked so far

For example, this gives exception:

var orderedItems = allItems.OrderBy(log => log.TurnedOffOn);

var sequenceNumber = 0;
foreach (var item in orderedItems)
{
    item.SequenceNumber = sequenceNumber++;
}

return orderedItems;

And this gives exception also - index out of range

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, index) => new RequestLog // create a new instance to avoid in memory changes
         {
             Id = log.Id,
             SequenceNumber = index + 1,
             // other fields
         }).AsQueryable();
}

Addition:

I get this query

var query = _store.RequestsLog.Query(true).Where(filter);

from repository method

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(),
         //other fields
     });
}

And SqlText of this query is correct, if I run in Management Studio, it doesn't give any error:

SELECT
    [p].[Id],
    ROW_NUMBER() OVER(ORDER BY [p].[TurnedOffOn]),

But here is where exception goes:

var response = OrderItems(query, request.Sort)
    .TakePage(request.Page)
    .ProjectTo<RequestLogViewModel>(RequestLogMappers.Mapper.ConfigurationProvider)
    .AsPagedList(totalCount);

In method TakePage comes query with following SqlText: "((LinqToDB.Linq.ExpressionQuery<WaterAccidents.DAL.Models.RequestLog>)src).SqlText" выдал исключение типа "System.Reflection.TargetInvocationException"

This is method OrderBy, where probably the problem appears:

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

propertyName being passed to the OrderBy method is correct and exists on the RequestLog class.

And when I inspect inner exception like this

try
{
    response = OrderItems(query, request.Sort)
        .TakePage(request.Page)
        .ProjectTo<RequestLogViewModel>(RequestLogMappers.Mapper.ConfigurationProvider)
        .AsPagedList(totalCount);
}
catch (TargetInvocationException ex)
{
    var innerException = ex.InnerException; 
                                            
}

innerException: Message = "Member 'Ext' not found in type 'FKDH`2'."

Answer 1

linq2db supports Windows Functions and you can use ROW_NUMBER to achive desired sequence

return orderedItems.OrderBy(log => log.TurnedOffOn)
         .Select(log => new RequestLog
         {
             Id = log.Id,
             SequenceNumber = Sql.Ext.RowNumber().Over()
                .OderBy(log.TurnedOffOn)
                .ToValue(),
             // other fields
         });