Linq: Using ElementAt on a database

I am trying to get the 2nd element of a database using the Linq method, ElementAt. However I am getting the below error:

LINQ to Entities does not recognize the method 'PersonData.Models.Person ElementAt[Person](System.Linq.IQueryable`1[PersonData.Models.Person], Int32)' method, and this method cannot be translated into a store expression.

I tried doing this:

var P1= db.Person.ElementAt(2);

But in vain. I also tried this:

var pl = from s in db.Persons select s;

var Person1 =pl.ElementAt(2);

But I get the same error. Any idea what I am missing? Thanks

Jon Skeet
people
quotationmark

Firstly, be aware that ElementAt is 0-based - if you want the second element, you should be using ElementAt(1), not ElementAt(2). (Think of it like array indexing.)

I'm not surprised that it's rejecting ElementAt on an unordered query, although I would hope for a better message. I believe that rows in a database table are inherently unordered - so taking an element by index makes little sense. (Likewise asking for the first or last element makes little sense.) Any operations relying on a position only make sense when that position is deterministic.

You can try ordering it first, e.g.

var person = db.Person.OrderBy(p => p.Id).ElementAt(1);

If that still doesn't work, you could try using Skip:

var person = db.Person.OrderBy(p => p.Id).Skip(1).FirstOrDefault();

Or if that still doesn't work:

var person = db.Person.OrderBy(p => p.Id).Skip(1).Take(1).AsEnumerable().First();

I would expect at least one of these solutions to work - and I've presented them in the order in which I'd prefer them.

people

See more on this question at Stackoverflow