IQueryable没有在实体框架核心生成所需的查询

Question

我有一些类似于以下的实体:

public class Teacher
{
public int TeacherId { get; set; }
public string TeacherName { get; set; }
public int Age { get; set; }
public string CurrentGrade { get; set; }
public int YearsTeaching { get; set; }
pubilc ICollection<StudentFeedback> StudentFeedback { get; set; }
}
public class StudentFeedback
{
public int StudentFeedBackId { get; set; }
public int TeacherId { get; set; }
public int StudentId { get; set; }
public string Feedback { get; set; }
public Teacher Teacher { get; set; }
public Student Student { get; set; }
} 

public class Student
{
public int StudentId { get; set; }
public string StudentName { get; set; }
public int Age { get; set; }
public string CurrentGrade { get; set; }
}

我有一个存储库，其中有一个方法，我想返回一个教师或教师列表，其中返回的StudentFeedback属于正在查看它的学生(studentId存储在令牌中)。

假设我有一个Teacher (teacherId)和一个Student (userId)，它正在访问API端点。我目前有以下文件:

int teacherId = 2;
int userId = 20; // This is the currently logged in user, extracted from the token.
var query = _context.Teachers.AsQueryable();/* _context is the DataContext*/
query = query.Where(t => p.TeacherId == teacherId);
query = query.Where(u => u.StudentFeedback.Any(x => x.StudentId == userId));

然而，这仍然返回所有学生的所有StudentFeedback，只要userId (student)已经为正在讨论的教师提供了反馈。我看了看执行的查询，问题是studentId谓词在错误的地方。查询的一个非常粗略的版本是:

SELECT      *
FROM        (   SELECT  t.*
FROM    dbo.Teachers t
WHERE   (t.TeacherId = 2)
AND EXISTS (   SELECT   1
FROM     dbo.StudentFeedback t0
WHERE    (t.TeacherId = t0.TeacherId)
AND (t0.StudentId = 20))) p
LEFT JOIN   dbo.StudentFeedback sf ON p.TeacherId = sf.TeacherId

而应该是

SELECT      *
FROM        (   SELECT  t.*
FROM    dbo.Teachers t
WHERE   (t.TeacherId = 2)) p
LEFT JOIN   dbo.StudentFeedback sf ON p.TeacherId = sf.TeacherId
AND   sf.StudentId = 20

但我不知道如何做到这一点。我设置的IQueryable谓词是否有问题，或者我是否在数据上下文中的modelBuilder中错过了一些逻辑?感谢。

编辑:我正在使用实体框架核心5.0.2，我也使用Automapper与以下代码:

query.ProjectTo<TeacherDTO>(_mapper.ConfigurationProvider).AsNoTracking()

这是我目前得到的结果:

[
{
"teacherid": 2,
"teacherName": "Jane Smith",
"age": 35,
"currentGrade": "One",
"yearsTeaching": 12,
"studentFeedback": [
{
"studentFeedBackId": 12,
"teacherId": 6,
"studentId": 20,
"feedback": "Ms Smith is my favorite teacher"
} ,
{
"studentFeedBackId": 16,
"teacherId": 6,
"studentId": 43,
"feedback": "Ms Smith was so kind to me"
} ,
{
"studentFeedBackId": 21,
"teacherId": 6,
"studentId": 89,
"feedback": "Thank you Mrs Smith for being my teacher. I learned a lot."
} 
]
}
]

这是我想要返回的内容:

[
{
"teacherid": 2,
"teacherName": "Jane Smith",
"age": 35,
"currentGrade": "One",
"yearsTeaching": 12,
"studentFeedback": [
{
"studentFeedBackId": 12,
"teacherId": 6,
"studentId": 20,
"feedback": "Ms Smith is my favorite teacher"
} 
]
}
]

Answer 1

感谢@LucianBargaoanu通过在映射本身中使用where为我指出了正确的方向。解决方案是在使用Automapper时使用参数化:

本页的代码显示了一个示例:

string currentUserName = null;
cfg.CreateMap<Course, CourseModel>()
.ForMember(m => m.CurrentUserName, opt => opt.MapFrom(src => currentUserName));

和

dbContext.Courses.ProjectTo<CourseModel>(Config, new { currentUserName = Request.User.Name });

Answer 2

如果你想要一个连接，你应该在linq语句中使用join方法。参见https://www.tutorialsteacher.com/linq/linq-joining-operator-join。你得到了你在查询中所写的东西。Where(u => u.StudentFeedback.Any(x => x.StudentId == userId));. any翻译成存在

Answer 3

如果你使用Net5 EF，你可以添加一个Students属性到Teacher类:

public class Teacher
{
.....
pubilc ICollection<Student> Students { get; set; }
pubilc ICollection<StudentFeedback> StudentFeedbacks { get; set; }
}

你可以这样使用query:

var query = _context.Teachers.Include(i=> i.StudentFeedbacks)
.Where(t => 
t.TeacherId == teacherId 
&&  t.StudentFeedbacks.Any(x => x.StudentId == userId))
.ToArray();