LINQ正在查询列表中的列表

本文关键字:列表 查询 LINQ | 更新日期: 2023-09-27 17:58:25

我有这种情况:

我的模型视图:

public class Subject
{
   public int ID { get; set; }
   public string Name { get; set; }
   public int ProfessorID { get; set; }
   public string ProfessorFullName{ get; set; }
   public IList<Assistant> Assistants { get; set; }
}
public class Assistant
{
   public string AssistantFullName{ get; set; }
}

我的查询:

var subjects = from subject in Entities.Subjects
                            from professor in subject.Lecturers
                            where professor.Professor == true
                            select new SSVN.ModelView.Subject()
                            {
                                ID = subject.ID,
                                Name= subject.Name,
                                ProfessorFullName= professor.LastName+ " " + professor.Name,
                                Assistants= (from subject1 in Entities.Subjects
                                            from assistant in subject1.Lecturers
                                            where assistant.Professor == false
                                            select new SSVN.ModelView.Assistant()
                                            {
                                                AssistantFullName = assistant.LastName+ " " + assistant.Name
                                            }).ToList()
                            };

当我打电话给时

subjects.ToList();我得到异常:

LINQ to Entities does not recognize the method
'System.Collections.Generic.List`1[SSVN.ModelView.Assistant] ToList[Assistant]
(System.Collections.Generic.IEnumerable`1[SSVN.ModelView.Assistant])' method, and this 
method cannot be translated into a store expression.

LINQ正在查询列表中的列表

不能在linq内部调用ToList进行实体查询。Linq-to-entities查询将始终投影到IEnumerable<T>,所以如果您想要IList<T>,则必须在Linq-to-objects中调用它。

试试这个:

var subjects = (from subject in Entities.Subjects
                from professor in subject.Lecturers
                where professor.Professor == true
                select new 
                    {
                        ID = subject.ID,
                        Name= subject.Name,
                        ProfessorFullName= professor.LastName+ " " + professor.Name,
                        Assistants= (from subject1 in Entities.Subjects
                                     from assistant in subject1.Lecturers
                                     where assistant.Professor == false
                                     select new SSVN.ModelView.Assistant()
                                         {
                                             AssistantFullName = assistant.LastName+ " " + assistant.Name
                                         })
                    }).AsEnumerable().Select(x => new SSVN.ModelView.Subject
                         {
                            ID = x.ID,
                            Name = x.Name,
                            ProfessorFullName = X.ProffesorFullName,
                            Assistants = x.Assistants.ToList()
                         });

不能也不应该在IQueryablle查询中使用ToList()。请注意,此查询必须转换为SQL。