c# - 使用联接时，Linq 查询不适用于 .Net Core

Question

我有以下在 ASP.Net MVC 应用程序中完美运行的 linq 查询：

var students = (from stu in db.Students
                        join tmphouse in db.Houses on stu.RoomID equals tmphouse.Location_ID into gr
                        from house in gr.DefaultIfEmpty()
                        join tmpsite in db.StudentSites on stu.Student_ID equals tmpsite.StudentId into gs
                        from site in gs.DefaultIfEmpty().Where(x => x.IsCurrent == true && x.PrincipleSite == true && x.StudentId == stu.Student_ID && x.SiteId == siteId)
                        join tmpval in db.StandardValues on site.StartingYearGroupId equals tmpval.SvId into gs1
                        from year in gs1.DefaultIfEmpty()
                        join tmpval2 in db.StandardValues on site.GroupId equals tmpval2.SvId into gs2
                        from grp in gs2.DefaultIfEmpty()
                        where stu.Current_Record == true
                        && site.IsCurrent == true && site.StatusId == statusId && site.PrincipleSite == true && site.SiteId == siteId
                        select stu);

但是较新的 ASP.Net Core 项目中的相同查询返回错误：by 'NavigationExpandingExpressionVisitor' failed。

我想避免自己编写 SQL，这是不可能的吗？有什么明显的我遗漏的东西吗？

解决方案

对于那些对我必须如何重写感兴趣的人：

var students = (from stu in db.Students
                join x in db.StudentSites on stu.Student_ID equals x.StudentId
                join tmploc in db.Houses on stu.RoomID equals tmploc.Location_ID into gj
                from loc in gj.DefaultIfEmpty()
                where stu.Current_Record == true
                && x.IsCurrent == true && x.StatusId == statusId && x.PrincipleSite == true && x.SiteId == siteId && x.StudentId == stu.Student_ID && x.StatusId == statusId
                select new ApiStudent
                {
                    ID = stu.Student_ID,
                    FirstName = stu.First_Name,
                    Surname = stu.Surname,
                    DateOfBirth = stu.Date_Of_Birth,
                    House = loc != null ? loc.LocationName : "",
                    YearID = x.StartingYearGroupId.HasValue && x.StartingYearGroupId.Value > 0 ? x.StartingYearGroupId.Value : 0,
                    Year = x.StartingYearGroupId.HasValue && x.StartingYearGroupId.Value > 0 ? db.StandardValues.Single(n => n.SvId == x.StartingYearGroupId.Value).StdValue : "",
                    GroupID = x.GroupId.HasValue && x.GroupId.Value > 0 ? x.GroupId.Value : 0,
                    Group = x.GroupId.HasValue && x.GroupId.Value > 0 ? db.StandardValues.Single(n => n.SvId == x.GroupId.Value).StdValue : "",
                });

标签： c#

Answer 1

这是由于EF Core 3.0中的一项重大更改 ，即：不再在客户端上评估 LINQ 查询

因此，以 EF Core 可以将表达式转换为 T-SQL 或将数据提取到内存中然后进行查询的方式编写查询。