How To Get Sequence Number Of Row Using Entity Framework?

I have SQL query: WITH OrderedRecs AS (select ROW_NUMBER() OVER(ORDER BY RecID) as seqNumber,* from Records where RecordGroupID=7) SELECT * FROM OrderedRecs where RecordID=35 How

Solution 1:

A pure LINQ solution would be:

records.Where(rec => rec.RecordGroupID == 7)
    .OrderBy(rec => rec.RecId).AsEnumerable()
    .Select((rec, i) =>new { i, rec })
    .Where(anon => anon.i == 35)
    .Select(anon => anon.rec).First()

But than you must be happy with the fact that all records matching the first condition are fetched locally.

Edit:

After you comments: Propably something like:

IEnumerable<SortedRecord> GetSortedRecords(IQueryable<Record> records
    , Func<SortedRecord, bool> sortedRecordPredicate)
{
    return
      records.Where(rec => rec.RecordGroupID == 7)
        .OrderBy(rec => rec.RecId).AsEnumerable()
        .Select((rec, i) =>newSortedRecord{ Sequence = i, Record = rec })
        .Where(sortedRecordPredicate);
}

var result = GetSortedRecords(records, rec => rec.Record.RecordID = 35);

Where SortedRecord, obviously, is a type now, which enables you to enter all kinds of conditions. (syntax not checked).

A completely different approach would be to make a view of the query within the WITH clause.

Solution 2:

Couldn't you add SeqNumber as a derived (or discriminator) column to the base entity? See previous example. You would have to assign it yourself.