Entity lookup by row number

Question

In EF, when having to deal with indexes, we need to convert the entity collection to a list as IQueryable or IEnumerable do not implement any indexer (it would be meaningless due to the deferred execution mechanism). When having a pretty large collection using ToList for using indexes would be heavy.

This is why I sometimes need to look up entities by the row number (not the identity / primary key). For example, I have one record, I want to get its row number (position in the sorted table) and get the next record.

Can this be achieved in Entity Framework? I can send SQL queries to do this, but I'm curious if there is a Linqish and EF specific approach to do this.

have you tried `ElementAt()` method and this link: https://stackoverflow.com/questions/2471588/how-to-get-index-using-linq ? — Tim.Tang, Aug 21 '17 at 21:50
If you are having large list and need to do lookup by row number then consider using Skip/Take in your queries. — Smit, Aug 25 '17 at 23:06

score 1 · Accepted Answer · answered Aug 21 '17 at 22:43

This is possible through tweaking the DbContex and adding a fake entity that corresponds to the row number. This entity can be exploited for using the built-in ROW_NUMBER() function in SQL. For instance:

public class RowNumber{
    public Int64 Row_Number {get; set;}
}

followed by adding these two the DbContext

public virtual DbSet<RowNumber> RowNumber {get; set;}

protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        modelBuilder.Entity<RowNumber>(x => {
            x.HasKey(e => e.Row_Number);
        });
 ...
}

Now we can make the following query:

var rowNumber = dbContext.Set<RowNumber>().FromSql("SELECT Row_Number FROM (SELECT ROW_NUMBER() OVER(ORDER BY [Column] DESC) AS Row_Number, [Column] FROM [Table]) AS Query WHERE [Column] = {0}").ToList();

And yeah this is more efficient and of higher performance!

Your example for `EF 6` or `EF Core`? – Andrei Krasutski Oct 07 '19 at 13:22 — Andrei Krasutski, Oct 07 '19 at 13:22

Entity lookup by row number

1 Answers1