Trying to join tables with predicate builder

Question

I have two tables that I want to join using predicate builder. In pseudo code, I want to return all StudentSchedule rows, joining with Student on StudentId, where StudentLastName = "Smith".

public class Student
{
    int StudentId {get;set;}
    string StudentFirstName {get;set;}
    string StudentLastName {get;set;}
}
class StudentSchedule
{
    int StudentScheduleId
    int StudentId
    string ClassName
}

I can do it for one entity just fine-

var studentBuilder = PredicateBuilder.True<Student>();

studentBuilder = studentBuilder.And(Student => Student.StudentId == studentId);

var students = context.Students.Where(studentBuilder).ToList();

Why are you using Predicate builder for this, surely you can do this using a standard query? — Alex, Sep 01 '17 at 15:43
And what is the relationship between Student and StudentSchedule - is it a one to one relationship? — Alex, Sep 01 '17 at 15:44
Alas Predicatebuilder is what I have to work with. And in this case, a student can have multiple StudentSchedules. — Amanda_Panda, Sep 01 '17 at 16:02

Robert McKee · Answer 1 · 2017-09-01T17:02:18.160

Then you should change your model to something like this:

public class Student
{
    public int StudentId {get;set;}
    public string StudentFirstName {get;set;}
    public string StudentLastName {get;set;}
    public virtual ICollection<StudentSchedule> StudentSchedules {get;set;}
}
class StudentSchedule
{
    public int StudentScheduleId {get;set;}
    public int StudentId {get;set;}
    public string ClassName {get;set;}
    public virtual Student Student {get;set;}
}

Then your query would be:

var students = db.Students
  .Include(s=>s.StudentSchedules)
  .Where(studentBuilder)
  .ToList();

Without predicate builder:

var students = db.Students
  .Include(s=>s.StudentSchedules)
  .Where(s=>s.StudentLastName == "Smith")
  .ToList();

My personal preference is to not repeat the entity type in the properties unless it's an external property, so my model would be like this:

public class Student
{
    public int Id {get;set;}
    public string FirstName {get;set;}
    public string LastName {get;set;}
    public virtual ICollection<StudentSchedule> Schedules {get;set;}
}
class StudentSchedule
{
    public int Id {get;set;}
    public int StudentId {get;set;}
    public string ClassName {get;set;}
    public virtual Student Student {get;set;}
}

Of course you might want a more detailed model that has "Subjects" (Name, Prerequisites, etc), "Classes" (A subject, a classroom, schedules, Staffs), "Students" (FirstName, LastName, Classes), "Staff" (FirstName, LastName, JobTitle, Classes) and "Schedules" (Day of week, start time, end time, Classes).

Trying to join tables with predicate builder

1 Answers1