LINQ statement that returns rownumber of element with id == something?

Question

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

Answer 1

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));
    }
}

Answer 2

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);

Answer 3

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