Support row_number with linq

[yang-er]

Hi, can you provide some more intentions or expected results? I didn't get the point.

[Jonatthu]

https://github.com/dotnet/efcore/issues/13805 Basically I want to be able to generate the next query:

Microsoft.EntityFrameworkCore.Database.Command: Information: Executed DbCommand (213ms) [Parameters=[@__p_2='?' (DbType = Int64), @__p_3='?' (DbType = Int64)], CommandType='Text', CommandTimeout='30']
SELECT [t].[Id], [t].[CaptionId], [t].[ClientKeyId], [t].[CustomCaption], [t].[Position], [t].[ProfileId], [t].[Sub], [t].[Type], [t].[Uri]
FROM (
    SELECT [p].[Id], [p].[CaptionId], [p].[ClientKeyId], [p].[CustomCaption], [p].[Position], [p].[ProfileId], [p].[Sub], [p].[Type], [p].[Uri], ROW_NUMBER() OVER(PARTITION BY [p].[ProfileId] ORDER BY [p].[Id] DESC) AS [c]
    FROM [ProfileMedia] AS [p]
    WHERE [p].[ProfileId] IN (8, 32, 36, 37, 38, 39, 40, 41, 43, 44)
) AS [t]
WHERE ([t].[c] > @__p_2) AND ([t].[c] <= @__p_3)

My current code is doing

                    case ProfileMediaSelectorsEnum.uri:
                        batchQuery = query.Select(x => new
                        {
                            Entity = x,
                            RowNumber = EF.Functions.RowNumber(x.Uri, EF.Functions.OrderByDescending(x.Id))
                        });
                        break;

By using this library https://dev.azure.com/pawelgerr/Thinktecture.EntityFrameworkCore/_wiki/wikis/Thinktecture.EntityFrameworkCore.wiki/14/RowNumber-Support

But I would like to have a linq version so I can do dynamic query construction but for that would required not to use the EF.Function.RowNumber method and instead create it's own Linq extension compatible with OrderBy chain methods similar of what you are doing with Update Batch extension methods!

[yang-er]

So you are now able to query these data via applying something like EF.Functions.RowNumber, but you had to execute raw sqls to do update actions, right?

What about considering something like

IQueryable<MyEntity> myQuery = ...; (something includes the row_number)
IQueryable<int> myQueryKey = myQuery.Select(a => a.Id);
Db.Set<MyEntity>().Where(m => myQueryKey.Contains(m.Id)).BatchUpdate(...)

That is, you can only update entities by the ID of entities selected by myQuery.

Or you can also try something like BatchUpdateJoin, which consumes the source like second table/sub select query.

[Jonatthu]

Yes something like that!

[yang-er]

Do you have trouble executing these update query?