LINQ queries and SQL expressions

[alexander...@gmail.com]

I've implemented an SQL expression:

[SqlExpression("row_number() over (order by {0} desc)", 0, ServerSideOnly = true)]

public static int RowNumberDesc<T>(T obj) { ... }

Аnd now I'm trying to use it in LINQ queries:

var query1 = db.GetTable<Role>()

    .Select(s => new { Id = s.Id, Number = SqlFunctions.RowNumberDesc(s.Id) });

var query2 = from u in db.GetTable<User>()

             join r in query1 on u.RoleId equals r.Id

             select new { u.Id, r.Number };

For the first query the SQL code looks fine:

SELECT

    [s].[RoleId] as [Id],

    row_number() over (order by [s].[RoleId] desc) as [c1]

FROM [Role] [s]

Concerning "query2", I'd like the code like below:

SELECT

    [u].[UserId] as [Id],

    [s].[c1] as [c1]

FROM [User] [u]

INNER JOIN (

    SELECT

        [r].[RoleId] as [Id],

        row_number() over (order by [r].[RoleId] desc) as [c1]

    FROM [Role] [r]

) s ON [u].[RoleId] = [s].[Id]

But I get the following result:

SELECT

    [u].[UserId] as [Id],

    row_number() over (order by [s].[RoleId] desc) as [c1]

FROM [User] [u]

INNER JOIN [Role] [s] ON [u].[RoleId] = [s].[RoleId]

How can I force my "row_number" function to stay in JOIN clause?