C# LINQ: How to stack LINQ queries correctly

Question

I have a form that allows the user to perform a myriad of searches. The table(s) that need to be joined differ depending on the search criteria entered. (My example below is very simplistic because both tables use the same sub-tables to join on, but the actual problem is not as simple.)

I've been using a technique I call LINQ stacking, like this:

IQueryable<LogENT> results = Context.AssignedLogsENT.Where(l => l.AgencyId);

if(txtFirstName.Text != null)
    results = from r in results
         join a in Context.LogAssignmentsENT on r.DisplayLogId equals a.LogId
         join p in Context.PersonsENT on a.ObjectId equals p.DisplayPersonId
         && !a.Deleted &&
         p.FirstName.StartsWith(Object.FirstName)
         select r;

if(txtLastName.Text != null)
    results = from r in results
         join a in Context.LogAssignmentsENT on r.DisplayLogId equals a.LogId
         join p in Context.PersonsENT on a.ObjectId equals p.DisplayPersonId
         && !a.Deleted &&
         p.LastName.StartsWith(Object.LastName)
         select r;

So you see if a certain text field is set, I add to the query as necessary. This actually works fine, except that when I use SQL Profiler to view the generated query, it is INNER JOINing the tables each time I add a new criterion.

i.e. the LogAssignments table is included 3, 4, 5 times. Is there a way I can prevent it from JOINing the same table more than once?

Or, is there a better way I can do this? I've looked at Predicate Builder however it doesn't seem to permit joining tables, which is a requirement in my case.

Thanks!

Answer 1

You can build your base result and then dynamically add the where clauses.

Answer 2

If you use just one query, you could modify it something like this:

results = from r in results 
         join a in Context.LogAssignmentsENT on r.DisplayLogId equals a.LogId 
         join p in Context.PersonsENT on a.ObjectId equals p.DisplayPersonId 
         && !a.Deleted && 
         (txtFirstName.Text != null || p.FirstName.StartsWith(Object.FirstName)) &&
         (txtLastName.Text != null || p.LastName.StartsWith(Object.LastName))
         select r; 

Answer 3

IQueryable<LogENT> results = Context.AssignedLogsENT.Where(l => l.AgencyId);


results = from r in results
    join a in Context.LogAssignmentsENT on r.DisplayLogId equals a.LogId
    join p in Context.PersonsENT on a.ObjectId equals p.DisplayPersonId
    && !a.Deleted
    select r;

if(txtFirstName.Text != null)
    results = from r in results
         p.FirstName.StartsWith(Object.LastName)
         select r;

if(txtLastName.Text != null)
    results = from r in results
         p.LastName.StartsWith(Object.LastName)
         select r;