C # LINQ: how to stack LINQ queries correctly

Question

I have a form that allows the user to perform many requests. The table (s) to be combined varies depending on the search criteria entered. (My example below is very simplified because both tables use the same sub-tables for joining, but the actual problem is not so simple.)

I use a method that I call the LINQ stack, for example:

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 specific text field is specified, I add to the request as necessary. This really works fine, except that when I use SQL Profiler to view the generated query, this is an INNER JOINing table every time I add a new criterion.

. LogAssignments 3, 4, 5 . , ?

, ? Predicate Builder, , , , .

!

Answer 1

, :

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 2

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;

Answer 3

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