The query to RowNumber with subquery to {Id, RowNumber} allways returns 1.

[Jurnicek]

Hello Pawel,

We are currently solving the task "Get the number of the data page on which the given item is located".

We have an IOrderedQueryable (potentially with a where clause), we know the Id of the searched item and we ask for the index of the item with the given id in the given IQueryable.

Because sorting is not known at compile time, but only at runtime, I had to use LinqKit to build some parts of the queries.

First, I wrote GetRowNumberOrderByClause extension function, which gets an Expression <Func<TItem, RowNumberOrderByClause >> expression from IOrderedQueryable . Then I used this function for creating the expression:

Expression<Func<ProjektListItem, long>> idExpr = (item) => item.Id;
Expression<Func<ProjektListItem, RowNumberOrderByClause>> orderByExpr = sortedModel.GetRowNumberOrderByClause();
Expression<Func<ProjektListItem, long>> rnExpr = (item) => EF.Functions.RowNumber(orderByExpr.Invoke(item));

The query retrieving the set of Id and RowNumber works correctly:

from item in sortedModel.AsExpandable()
           select new
           {
                 Id = idExpr.Invoke(item),
                 RowNumber = rnExpr.Invoke(item)
           }

The items are sorted according to the set sort order, and each RowNumber increases from 1 to the number of items as I require.

So now I want to use this query as a subquery to get the index of the item with the given Id. I want to get the index of an item whose Id matches fId:

var testQuery = (
          from item in sortedModel.AsExpandable()
          select new
          {
                    Id = idExpr.Invoke(item),
                    RowNumber = rnExpr.Invoke(item)
          }
).AsSubQuery().Where(r => r.Id == fId).Select(r => r.RowNumber);

var test = await testQuery.FirstOrDefaultAsync();

Unfortunately, the result of this query is always 1.

Please try to test how the compiler behaves when you want to get RowNumber for the given Id from the subquery giving the pair {Id, RowNumber}.

P.S:
The project is in .NET5, packages Thinktecture.EntityFrameworkCore.SqlServer 3.0.0-beta11 and LinqKit.Microsoft.EntityFrameworkCore 5.0.24 used.

OrderingExtensions.zip

[Jurnicek]

Solved!

var testQuery = (
                            from item in model.AsExpandable()
                            let p = new
                             {
                                 RowNumber = idExpr.Invoke(item) == fId ? rnExpr.Invoke(item) : 0
                             } select p
                         ).Distinct();

var test =await testQuery.ToListAsync();

Even from a table with a million records, I get a list of two numbers, namely 0 and the number I need to get.

[PawelGerr]

Hi, thanks for your feedback.
I see you've found a solution, still, please update the nuget packages to 3.0.0-beta012.
With this version your original query should work as well. Now, you can compare both approaches and choose the most performant one.

[PawelGerr]

Oh, I have a typo in the version, it should be 3.0.0-beta12 instead of 3.0.0-beta012. In about 10min the CI will build another package with correct version :)

[Jurnicek]

Hi Pawel,

Thank you for your quick reply.

I tried the following query and it works correctly:

var testQuery = (
        from item in sortedModel.AsExpandableEFCore()
                select new
                {
                      Id = idExpr.Invoke(item),
                      RowNumber = rnExpr.Invoke(item),
                }).AsSubQuery()
                .Where(r => r.Id == fId)
                .Select(r => r.RowNumber);

It is possible that the error was in my work with LinqKit: Previously I used the AsExpandable() function instead of AsExpandableEFCore (), although I work in EF Core.

Thank you again for your quick response.

Have a nice day,

Jan Jurníček

[PawelGerr]

AsSubQuery was checking whether the provider of the LINQ query was the one of the EF Core but it was not. LinqKit uses its own query provider, so the check failed.

[Jurnicek]

Pawel, thank you for your help.

Your package has helped me a lot, it is an excellent job and without it I would not be able to solve this task.