7

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?

stucampbell
  • 6,383
  • 5
  • 26
  • 25
  • http://stackoverflow.com/questions/1165028/how-do-i-add-rownumber-to-a-linq-query-or-entity duplicate? – ba__friend Aug 12 '09 at 11:57
  • 1
    @bassfriend - EF and LINQ-to-SQL have completely different support for such operations... – Marc Gravell Aug 12 '09 at 12:03
  • I was kind of hoping to see an answer here. I'm running into the same issue - the indexed overload doesn't work w/L2S. I'd be happy w/any incremental ID; guess I'll have to do it in multiple steps. – Paul Mrozowski Feb 18 '10 at 01:05

2 Answers2

2

(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.

Marc Gravell
  • 1,026,079
  • 266
  • 2,566
  • 2,900
-2

You have to include AsEnumerable()

i.e . 

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