IQueryable not generating the desired query in entity framework core

I have some entities similar to the following:

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

I have a repository with a method where I want to return a teacher or list of teachers where the StudentFeedback returned belongs to the student who is looking at it (the studentId is stored in a token).

So, lets say I have the Teacher (teacherId) and a Student (userId) who is hitting the API endpoint. I currently have the following:

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

However this is still returning all StudentFeedback from all Students, so long as the userId (student) has feedback provided for the teacher in question. I had a look at the query that gets executed and the problem is that the studentId predicate is in the wrong place. A very rough version of the query is:

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

Whereas it should be something like

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

but I don't know how to make that happen. Is there something wrong with the IQueryable predicates I've setup or have I missed some logic in the modelBuilder within the datacontext? Thank-you.

Edit: I am using Entity Framework Core 5.0.2 and I am also using Automapper with the following code:

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

Here is what I am getting back currently:

[
        {
            "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."
                } 
            ]
        }
    ]

here is what I want to be getting back:

[
    {
        "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"
            } 
        ]
    }
]

Upvotes: 0

Answers (3)

Woody

Reputation: 1787

Thanks @LucianBargaoanu for pointing me in the right direction by saying to have the where in the mapping itself. The solution is to use Parameterization when using Automapper:

The code from this pages shows an example:

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

and then

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

Upvotes: 1

Serge

Reputation: 43880

If you use Net5 EF you can just add a Students property to the Teacher class:

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

You can use query this way:

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

Upvotes: 0

TSungur

Reputation: 406

If you want a join you should use Join method in your linq statement. See https://www.tutorialsteacher.com/linq/linq-joining-operator-join . You got exactly what you wrote in your query. Where(u => u.StudentFeedback.Any(x => x.StudentId == userId)); .Any translates to exists.

Upvotes: 0

IQueryable not generating the desired query in entity framework core

Answers (3)

Related Questions