从LINQ查询中获取第一个结果-当First& T& gt()成功时，为什么ElementAt< T& gt;(0)失败? [英] Getting the first result from a LINQ query - why does ElementAt<T>(0) fails when First<T>() succeeds?

问题描述

我有一个方法 AddStudent()，该方法寻找一个具有相同名称的学生，如果有一个具有相同名称的学生，则从数据库中返回一个现有的学生，否则它将创建一个新学生并将其添加到数据库中.

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.

当我尝试从LINQ查询中获取第一个结果时，当se = students.ElementAt<StudentEntity>(0);失败时，为什么se = students.First<StudentEntity>();成功会很好奇.两种方法不一样吗?

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

谢谢！

推荐答案

它失败，因为ElementAt方法是索引访问方法，并且实体框架不知道如何将其转换为SQL.

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

使用First方法时，Entity Framework可以将其转换为SQL查询中的TOP 1子句.非常非常简单.为了使用ElementAt，它必须基于窗口函数(ROW_NUMBER())构造一个更为复杂的查询，而且，这样做还不够复杂.

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.

实际上是记录的限制实体框架.完全不支持ElementAt扩展名.

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

这指示实体框架在AsEnumerable()调用之后不要尝试翻译"任何内容，因此，它将检索结果的 all (不仅仅是第一个)并遍历它们直到到达您想要的元素(在这种情况下，只是 happens 成为第一个).

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

但是，与仅使用First()相比，这会大大降低操作 ，因为它不仅从服务器获取1个结果，而且还获取所有结果并随后进行过滤.如果出于某种奇怪的原因，我需要获取第5个或第10个元素或第一个元素 other 而不是第一个元素，则只能使用这种解决方法.

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.

这篇关于从LINQ查询中获取第一个结果-当First& T& gt()成功时，为什么ElementAt< T& gt;(0)失败?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！