12

How to write LINQ statement that returns ROWNUMBER of element with id == something?

Ante
  • 8,567
  • 17
  • 58
  • 70

3 Answers3

12

There is no direct way to do this that I'm aware of. You'd have to pull the whole query down to the client, and the from there you could project in the row numbers. As an alternative, you could write a stored procedure that uses ROW_NUMBER, and then hit that proc from Linq to SQL.

In your case, the only way you're going to be able to do this would be client side. Keep in mind that the following statement is NOT going to do this at the server, but will pull down your whole table and get the index at the client...

using (var dc = new DataClasses1DataContext())
{
    var result = dc.Users
        .AsEnumerable()     // select all users from the database and bring them back to the client
        .Select((user, index) => new   // project in the index
        {
            user.Username,
            index
        })
        .Where(user => user.Username == "sivey");    // filter for your specific record

    foreach (var item in result)
    {
        Console.WriteLine(string.Format("{0}:{1}", item.index, item.Username));
    }
}
Scott Ivey
  • 40,768
  • 21
  • 80
  • 118
  • Thanks, Scott. So I guess you would ToList() it, and then IndexOf()? – Robert Harvey Jul 15 '09 at 17:13
  • sure, i also added an example of how you could do it with the overload of Select that gives you an index. – Scott Ivey Jul 15 '09 at 17:15
  • I was trying to come up with a solution using `.TakeWhile(xxx).Count()`, but it also can't be translated to Sql, and could have poor/improper results when the record doesn't exist at all. – Ryan Versaw Jul 15 '09 at 17:40
  • dc.Pictures .Select(p => p.IsBest) .AsEnumerable() .Select((p, i) => new {p.PictureID, i}) .Where(p => p.PictureID = id); i'm not that god with linq, i don't know why but p.PictureID isn't accessible. can you tell me why? – Ante Jul 15 '09 at 18:27
  • Get rid of your Select() that comes before the AsEnumerable(). The PictureID isn't there because you've only selected IsBest in your initial select. – Scott Ivey Jul 15 '09 at 19:32
  • If I user the Sql server profiler I see that this solution does a complete Select * order by ... Then after moving the result to local the Select statement is performed. Not very efficient – Harald Coppoolse Apr 07 '16 at 09:18
3

You should be able to use the Skip and Take extension methods to accomplish this.

For example, if you want row 10:

from c in customers
           where c.Region == "somewhere"
           orderby c.CustomerName
           select new {c.CustomerID, c.CustomerName} 
           .Skip(9).Take(1);
Robert Harvey
  • 178,213
  • 47
  • 333
  • 501
1

How To Project a Line Number Into Linq Query Results
How To Project a Line Number Into Linq Query Results

Community
  • 1
  • 1
Robert Harvey
  • 178,213
  • 47
  • 333
  • 501