How to find the ROW_NUMBER() of a row with Linq to SQL

Question

Linq to SQL makes use of ROW_NUMBER() for paging purposes, i.e. When you use Skip() and Take().

However, I can't seem to find a way of actually accessing ROW_NUMBER() values myself in the query. I need to find the ROW_NUMBER() of a record within a query (without bringing all the records back).

I've done this successfuly in T-SQL and Linq to Objects, but a Linq to SQL solution is proving elusive.

In Linq to Objects I can get the row number like this:

var rowTarget =
    mainQuery
        .Select((obj, index) => new { obj.ID, Index = index })
        .SingleOrDefault(x => x.ID == targetID);

// rowTarget.Index is the answer

But Linq to SQL does not support the overrides of Select() that use an index parameter (and that makes sense, really - ROW_NUMBER() would only be used if Skip() and Take() were being used).

I can't seem to find a solution that doesn't result in all records being returned from the database.

Is this even possible?

Answer 1

(edit: tried it; still gives "Unsupported overload used for query operator 'Select'")...

Why are you using SelectMany here? Have you tried it with the version of Select that includes the index?

.Select((obj, index) => new { obj.ID, Index = index })
    .SingleOrDefault(x => x.ID == targetID);

You should also probably include an explicit OrderBy - LINQ-to-SQL lets you get away without it most of the time, but EF doesn't.