Entity Framework Core 5似乎不支持Where/Include

Question

我在EF Core 5发布说明中读到现在支持包含/where子句。

var results = context.Attendees
.Include(a=>a.SessionPresenters.Where(b=>b.SessionId > 5000))
.Where(c => c.Id == 1124)

然而，当我查看生成的SQL时，1124在那里，但是>5000被忽略。我有什么不明白的吗?

这并不重要，但是下面是生成的SQL

SELECT `a`.`Company`, `a`.`FacebookId`, `a`.`Id`, `a`.`LinkedInId`, `a`.`PrincipleJob`, `a`.`TwitterHandle`, `a`.`UserBio`, `a`.`UserBioShort`, `a`.`UserFirstName`, `a`.`UserLastName`, `a`.`UserWebsite`, `a0`.`AmazonImageSmall`, `a0`.`Authors`, `a0`.`BookPublishedDate`, `a0`.`BookTitle`, `a0`.`DetailPageUrl`, `a0`.`Id`, `t1`.`c`, `t1`.`AllowHtml`, `t1`.`c0`, `t1`.`c1`, `t1`.`Id`, `t1`.`UrlPostToken`, `t1`.`CodeCampYearId`, `t1`.`Id0`, `t1`.`LectureRoomsId`, `t1`.`MaxAttendance`, `t1`.`c2`, `t1`.`Description`, `t1`.`Id1`, `t1`.`SessionSequence`, `t1`.`SessionsMaterialUrl`, `t1`.`SessionTimesId`, `t1`.`Title`, `t1`.`Id2`, `t1`.`c3`, `t1`.`Id3`, `t1`.`TagName`, `t1`.`Id00`
FROM `Attendees` AS `a`
LEFT JOIN `AttendeesAmazonBook` AS `a0` ON `a`.`Id` = `a0`.`AttendeesId`
LEFT JOIN (
SELECT FALSE AS `c`, `s0`.`AllowHtml`, COALESCE(`s0`.`Approved`, FALSE) AS `c0`, FALSE AS `c1`, `c`.`Id`, `c`.`UrlPostToken`, `s0`.`CodeCampYearId`, `s0`.`Id` AS `Id0`, `s0`.`LectureRoomsId`, `s0`.`MaxAttendance`, `s1`.`Id` IS NULL AS `c2`, `s1`.`Description`, `s1`.`Id` AS `Id1`, `s0`.`SessionSequence`, `s0`.`SessionsMaterialUrl`, `s0`.`SessionTimesId`, `s0`.`Title`, `s`.`Id` AS `Id2`, `t0`.`c` AS `c3`, `t0`.`Id` AS `Id3`, `t0`.`TagName`, `t0`.`Id0` AS `Id00`, `s`.`AttendeeId`
FROM `SessionPresenter` AS `s`
INNER JOIN `Sessions` AS `s0` ON `s`.`SessionId` = `s0`.`Id`
INNER JOIN `CodeCampYear` AS `c` ON `s0`.`CodeCampYearId` = `c`.`Id`
LEFT JOIN `SessionLevels` AS `s1` ON `s0`.`SessionLevel_id` = `s1`.`Id`
LEFT JOIN (
SELECT FALSE AS `c`, `t`.`Id`, `t`.`TagName`, `s2`.`Id` AS `Id0`, `s2`.`SessionId`
FROM `SessionTags` AS `s2`
INNER JOIN `Tags` AS `t` ON `s2`.`TagId` = `t`.`Id`
) AS `t0` ON `s0`.`Id` = `t0`.`SessionId`
) AS `t1` ON `a`.`Id` = `t1`.`AttendeeId`
WHERE `a`.`Id` = 1124
ORDER BY `a`.`Id`, `a0`.`Id`, `t1`.`Id2`, `t1`.`Id0`, `t1`.`Id`, `t1`.`Id1`, `t1`.`Id00`, `t1`.`Id3`

模型:

public partial class SessionPresenter
{
public int Id { get; set; }
public int AttendeeId { get; set; }
public int SessionId { get; set; }
public virtual Attendee Attendee { get; set; }
public virtual Session Session { get; set; }
}
public partial class Session
{
public int Id { get; set; }
....

无效的建议:

var results = context.Attendees
.Include(a => a.SessionPresenters.Where(b => b.SessionId > 5000))
.Where(c => c.Id == 1124).AsNoTracking()

Answer 1

因为第一次尝试用OP的代码解决这个问题失败了，所以让我们以其他方式模拟这种情况。我使用本教程中的应用程序作为概念验证:

https://learn.microsoft.com/en - us/aspnet/core/data/ef mvc/intro?view=aspnetcore - 5.0

下面的LINQ查询和所涉及的数据结构应该与问题中的内容相当:

• 课程到入学=一对多

• 招生对学生=多对一

  var results = _dbContext.Courses
  .Include(a => a.Enrollments.Where(b => b.StudentID > 1))
  .Where(c => c.CourseID == 1050)
  .ToList();
  

生成下面的SQL。WHERE [e].[StudentID] > 1证明滤波器是有效的。

SELECT [c].[CourseID], [c].[Credits], [c].[Title], [t].[EnrollmentID], [t].[CourseID], [t].[Grade], [t].[StudentID]
FROM [Course] AS [c]
LEFT JOIN (
SELECT [e].[EnrollmentID], [e].[CourseID], [e].[Grade], [e].[StudentID]
FROM [Enrollment] AS [e]
WHERE [e].[StudentID] > 1
) AS [t] ON [c].[CourseID] = [t].[CourseID]
WHERE [c].[CourseID] = 1050
ORDER BY [c].[CourseID], [t].[EnrollmentID]

模型类

public class Course
{
[DatabaseGenerated(DatabaseGeneratedOption.None)]
public int CourseID { get; set; }
public string Title { get; set; }
public int Credits { get; set; }
public ICollection<Enrollment> Enrollments { get; set; }
}
public enum Grade
{
A, B, C, D, F
}
public class Enrollment
{
public int EnrollmentID { get; set; }
public int CourseID { get; set; }
public int StudentID { get; set; }
public Grade? Grade { get; set; }
public Course Course { get; set; }
public Student Student { get; set; }
}
public class Student
{
public int ID { get; set; }
public string LastName { get; set; }
public string FirstMidName { get; set; }
public DateTime EnrollmentDate { get; set; }
public ICollection<Enrollment> Enrollments { get; set; }
}