locked
How to filter modal picker to show elements that aren't picked yet? RRS feed

  • Question

  • Student has many Attendances

    Attendance has one Student

    Attendance has one Lecture

    Lecture has many Attendances

    I'd like to add new attendance for lecture. Lecture is implicitly selected on AddEditNewAttendance. User has to select student. Modal picker is used for selecting student.

    How to filter modal picker to show only students that didn't attend this lecture yet?

    SQL to filter students that didn't attend this lecture yet would something like:

    SELECT *
    FROM Students s
    WHERE s.Id NOT IN (
        SELECT a.StudentId
        FROM Attendances a
        WHERE a.LectureId = @lectureId
    )

    Monday, September 16, 2013 7:33 PM

Answers

  • I don't think your code does much of anything or exactly what you want:

    1. You never changed the query that was passed into the method. You just called Where on it, which doesn't do anything unless you assign the result of Where back to "query". So the result just returns whatever is the base result of the query, which is probably the entire entity set.
    2. Even if you assigned the result of Where back to "query", the query would become a large compositions of "if not student A and not student B and not student C...", which becomes less performant if you have a large number of students already attending the lecture.
    3. You are really executing multiple queries just to get the results you want, which isn't as performant as executing just the original query. To get a list of students who are not attending lecture:
    query = query.Where(student => !student.Attendances.Any(attendance => attendance.Lecture.Id == lectureId));

    To load the results of the query into the modal window picker, in the screen designer, select the query from the Choices property of the picker control.


    Justin Anderson, LightSwitch Development Team

    • Marked as answer by Vedran Marsic Sunday, September 22, 2013 6:51 AM
    Saturday, September 21, 2013 8:31 PM
    Moderator

All replies

  • To get the data I have customized Students server query. I've implemented PreprocessQuery for it.

    partial void StudentsNotAttendLecture_PreprocessQuery(int? lectureId, ref IQueryable<Student> query) {
        var studentsAttendIdList = Attendances.
            Where(attendance => attendance.Lecture.Id == lectureId).
            Select(attendance => attendance.Student.Id).Execute();
    
        foreach (var studentAttendId in studentsAttendIdList) {
            query.Where(student => student.Id != studentAttendId);
        }
    }

    I guess I could've done simmilar thing on the client.

    Anyway, I got the data I wanted. Now I have to load it to modal picker.

    Saturday, September 21, 2013 7:20 PM
  • I don't think your code does much of anything or exactly what you want:

    1. You never changed the query that was passed into the method. You just called Where on it, which doesn't do anything unless you assign the result of Where back to "query". So the result just returns whatever is the base result of the query, which is probably the entire entity set.
    2. Even if you assigned the result of Where back to "query", the query would become a large compositions of "if not student A and not student B and not student C...", which becomes less performant if you have a large number of students already attending the lecture.
    3. You are really executing multiple queries just to get the results you want, which isn't as performant as executing just the original query. To get a list of students who are not attending lecture:
    query = query.Where(student => !student.Attendances.Any(attendance => attendance.Lecture.Id == lectureId));

    To load the results of the query into the modal window picker, in the screen designer, select the query from the Choices property of the picker control.


    Justin Anderson, LightSwitch Development Team

    • Marked as answer by Vedran Marsic Sunday, September 22, 2013 6:51 AM
    Saturday, September 21, 2013 8:31 PM
    Moderator
  • You're right. My code doesn't work as expected. Yours does. Thank you.
    Sunday, September 22, 2013 6:50 AM