Add ROW_NUMBER to a LINQ query for specific Entity?

Question

I need an EF query to get the row number of a specific entity.

I've looked at this, and this.

Currently I have it working in this way:

    private DbContext Context;
    public int GetRowNumberQuery<TEntity>(int entityId)
    {
        var allEntities = this.Context.Set<TEntity>().ToList();

        return allEntities
            .Select((entity, index) => new { Index = index, Entity = entity })
            .Where(x => x.Entity.Id == entityId)
            .Select(x => x.Index)
            .SingleOrDefault();
    }

Obviously, this is very inefficient as it gets a list of all entities before selecting the index. If I remove the .ToList() in the first line, making the whole thing a LINQ query, it fails at the first Select with NotSupportedException saying:

LINQ to Entities does not recognize the method 'System.Linq.IQueryable1[<>f__AnonymousType12[System.Int32,MyEntityType]] Select[MyEntityType,<>f__AnonymousType12](System.Linq.IQueryable1[MyEntityType], System.Linq.Expressions.Expression1[System.Func3[MyEntityType,System.Int32,<>f__AnonymousType1`2[System.Int32,MyEntityType]]])' method, and this method cannot be translated into a store expression.

Can you please tell me how to get the ROW_NUMBER of a specific entity? Or is it impossible like this pretty old question suggests?

score 2 · Accepted Answer · answered Feb 28 '15 at 16:30

A) Entity Framework doesn't support ROW_NUMBER() (the examples given use EF to generate a query and then "attach" a number to each returned row client side, starting from 1 and going to n if there are n rows)

B) Even in TSQL the query would be complex:

SELECT TOP 1 ROW_NUMBER() OVER (ORDER BY ID) RN FROM SomeTable WHERE ID = 100

would return NULL if there are no rows or 1 if there is a row with ID 100

You would need something like

SELECT B.RN 
    FROM 
        (SELECT ID, ROW_NUMBER() OVER (ORDER BY ID) RN 
            FROM SomeTable) B 
    WHERE B.ID = 100

C) Clearly you can create a view/stored procedure that uses ROW_NUMBERand call it from EF

Add ROW_NUMBER to a LINQ query for specific Entity?

1 Answers1