时间戳

首页 > 解决方案 > IQueryable 未在实体框架核心中生成所需的查询

api - IQueryable 未在实体框架核心中生成所需的查询

问题描述

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

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 存储在令牌中）。

因此，假设我有一个正在访问 API 端点的教师 (teacherId) 和一个学生 (userId)。我目前有以下内容：

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（学生）为相关教师提供了反馈。我查看了执行的查询，问题是 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 谓词是否有问题，或者我错过了数据上下文中模型构建器中的一些逻辑？谢谢。

编辑：我正在使用 Entity Framework Core 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"
            } 
        ]
    }
]

标签： api.net-coreentity-framework-coreautomapperiqueryable

解决方案

感谢@LucianBargaoanu 通过说要where在映射本身中为我指明正确的方向。解决方案是在使用Automapper时使用Parameterization：

此页面中的代码显示了一个示例：

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

推荐阅读