Guidance on adding filter expression to query

Question

The application I am working on produces reports. I am tryingto implement some of the filtering which can involve up to several dozen fields in various combinations. I had previously used Dapper with string concatenation to build out the SQL but am trying to move to EF 7. It was not to difficult to implement some of the simpler filters using LinqKit with the PredicateBuilder. However, I am a little stuck with this one. The concept is to filter by a field in a child table while having the children tables linked by a non-key field pulling the related information. The below code is a rough representation.

public partial class Person
{
    public int Id {get; set;}
    public string AltId {get; set;}
    public string Name {get; set;}
    public virtual Student Student { get; set; }
    public virtual Employee Employee { get; set; }
}
public partial class Employee
{
    public int Id {get; set;}
    public int PersonId {get; set;}
}
public partial class Student
{
    public int Id {get; set;}
    public int PersonId {get; set;}                 // FK to Person
    public virtual ICollection<StudentTestAvailability> StudentTestAvailability { get; } = new List<StudentTestAvailability>();
    public virtual ICollection<StudentTestHistory> StudentTestHistory { get; } = new List<StudentTestHistory>();
}
public partial class StudentTestAvailability
{
    public int Id {get; set;}
    public int StudentId {get; set;}                // FK to Student
    public DateTime DateAvailable {get; set;}
    public int LocationId {get; set;}               // FK to Locations
    public int TestNameId {get; set;}               // FK to Test names
    public virtual TestNames TestNames { get; set; }
    public virtual TestLocations TestLocations { get; set; }
}
public partial class StudentTestHistory
{
    public int Id {get; set;}
    public int StudentId {get; set;}                // FK to Student
    public DateTime DateTaken {get; set;}
    public int LocationId {get; set;}               // FK to Locations
    public int TestNameId {get; set;}               // FK to Test names
    public virtual TestNames TestNames { get; set; }
    public virtual TestLocations TestLocations { get; set; }
}

public class ReportRepo
{
    public async Task<List<Person>> BuildReport(Filter filter)
    {
        var predicate = PredicateBuilder.New<Person>(true);
        if (string.IsNullOrWhiteSpace(filter.AltId) == false) { predicate = predicate.And(a => a.AltId == filter.AltId); }
        // Several more conditions
        // Insert filter for name/location of test with availability test date = test taken date
        using var context = _factory.CreateDbContext();
        {
            return await context.Person
                .Include(i => i.Employee)
                .Include(i => i.Student).ThenInclude(t => t.StudentTestAvailability)
                .Include(i => i.Student).ThenInclude(t => t.StudentTestTaken)
                .AsExpandable()
                .Where(predicate)
                .ToListAsync();
        }   
    }
}

The filter that is desired is to link the availability and taken tables by the date field retrieving only records that match the nameId and / or locationId, depending on if provided (>0). The SQL previously used was similar to this but more extensive.

SELECT p.*,s.*,sth.*,sta FROM People p
INNER JOIN Student S ON p.id = s.PersonId
LEFT JOIN StudentTestHistory sth ON s.id = sth.StudentId
LEFT JOIN StudentTestAvailability sta ON t.id = sta.StudentId
Where sth.StudentId = tta.StudentId 
    AND sth.DateTaken = sta.DateAvailable 
    AND sth.TestNameId = ata.TestNameId 
    AND sth.locationId = ata.locationId 
    AND (@nameId<1 OR sta.TestNameId=@nameId)
    AND (@locationId<1 OR sta.locationId=@locationId)

Any assistance is appreciated.

EDIT: In response to the question regarding navigation properties. Below are the navigation properties in question.

Answer 1

I have rewritten your query to meet desired SQL. Removed AsExpandable(), it is not needed for PredicateBuilder, and actually I do not see why it is introduced here.

public class ReportRepo
{
    public async Task<List<Person>> BuildReport(Filter filter)
    {
        var predicate = PredicateBuilder.New<Person>(true);
        if (string.IsNullOrWhiteSpace(filter.AltId) == false) 
           { predicate = predicate.And(a => a.AltId == filter.AltId); }

        // Several more conditions
        // Insert filter for name/location of test with availability test date = test taken date

        using var context = _factory.CreateDbContext();
        {
            var persons = context.Person
                .Include(i => i.Employee)
                .Include(i => i.Student).ThenInclude(t => t.StudentTestAvailability)
                .Include(i => i.Student).ThenInclude(t => t.StudentTestTaken)
                .Where(predicate);

            var query = 
                from p in persons
                from sth in p.Student.StudentTestHistory
                from sta in p.Student.StudentTestAvailability
                where 
                    sth.StudentId == sta.StudentId 
                    && sth.DateTaken == sta.DateAvailable 
                    && sth.TestNameId == sta.TestNameId 
                    && sth.locationId == sta.locationId
                select new
                {
                    Person = p,
                    History = sth,
                    Avalability = sta
                };

            if (filter.NameId > 0)
                query = query.Where(x => x.Avalability.TestNameId == filter.NameId);
                    
            if (filter.LocationId > 0)
                query = query.Where(x => x.Avalability.LocationId == filter.LocationId);

            return await query
                .Select(x => x.Person)
                .ToListAsync();
        }   
    }
}