Getting the first result from a LINQ query - why does ElementAt<T>(0) fails when First<T>() succeeds?

Question

I have a method AddStudent() which looks for a student with the same name and returns an existing student from the database if there is a student with the same name, otherwise it creates a new student and adds it to the database.

I'm curious why se = students.First<StudentEntity>(); succeeds when se = students.ElementAt<StudentEntity>(0); fails when I try to get the first result from the LINQ query. Aren't the two methods the same?

The full code for the method is shown below.

public Student AddStudent(string name)
{
    using (SchoolEntities db = new SchoolEntities())
    {
        // find student with same name via LINQ
        var students = from s in db.StudentEntitySet
                       where s.name == name
                       select s;

        StudentEntity se = default(StudentEntity);

        // if student with the same name is already present, return 
        // that student
        if (students.Count<StudentEntity>() > 0)
        {
            // if i use ElementAt, if fails with a "LINQ to Entities does not
            // recognize the method 'StudentEntity ElementAt[StudentEntity]
            // (System.Linq.IQueryable`1[StudentEntity], Int32)' method, 
            // and this method cannot be translated into a store expression.", 
            // but not when I use First. Why?

            // se = students.ElementAt<StudentEntity>(0);
            se = students.First<StudentEntity>();
        }
        else
        {
            // passing 0 for first parameter (id) since it's represented by 
            // a BigInt IDENTITY field in the database so any value
            // doesn't matter.
            se = StudentEntity.CreateStudentEntity(0, name);
            db.AddToStudentEntitySet(se);
            db.SaveChanges();
        }

        // create a Student object from the Entity object
        return new Student(se);
    }
}

Thanks!

Answer 1

It fails because the ElementAt method is an indexed-access method and the Entity Framework doesn't know how to turn that into SQL.

When you use the First method, Entity Framework can translate this into a TOP 1 clause in a SQL query. It's very very simple. In order to use ElementAt, it would have to construct a much more complex query based on windowing functions (ROW_NUMBER()) and, well, it just isn't quite sophisticated enough to do that.

It's actually a documented limitation of the Entity Framework. The ElementAt extension simply isn't supported.

---

You could, in theory, write this instead:

se = students.AsEnumerable().ElementAt<StudentEntity>(0);

This instructs the Entity Framework not to try to "translate" anything after the AsEnumerable() call, so instead, it will retrieve all of the results (not just the first) and iterate through them until it gets to the element you want (which in this case just happens to be the first).

However, this will slow down the operation a lot compared to just using First(), because instead of just fetching 1 result from the server, it fetches all of them and filters afterward. I would only use this workaround if for some strange reason I needed to get the 5th or 10th element or some element other than the first one.