How to get row serial number using Entity Framework?

Question

I have an 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 to get the same result using entity framework?

Answer 1

Failed to add SeqNumber as a derived (or discriminator) column to the base object? See the previous example . You will have to assign it yourself.

Answer 2

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 you should be satisfied that all records matching the first condition are selected locally.

Edit:

After your comments: 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) => new SortedRecord{ Sequence = i, Record = rec }) .Where(sortedRecordPredicate); } var result = GetSortedRecords(records, rec => rec.Record.RecordID = 35); 

Where SortedRecord , obviously, now there is a type that allows you to enter all kinds of conditions. (syntax not marked).

A completely different approach would be to present the request in a WITH clause.