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 do I get the same result using entity framework?
2 Answers
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) => new SortedRecord{ 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.
Comments
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.
where seqNumber=35?RecordIDis a field inRecords. The seqNumber will only number the records that satisfyRecordGroupID=x, so the row number will depend on that condition, is that OK?