Apply dynamic where clause to joined table

Question

I need to build a LINQ query that allows me to vary the where clause on a joined table but can't find a way to do it.

A simplified example of the two queries I'm constructing are:

var myQuery = from tab1 in context.Table1
              join tab2 in context.Table2 on Table1.ID equals Table2.Table1ID
              where tab1.TypeCode == 3
                    tab2.SomeID == 4 &&  
              select tab1.ID;

var myQuery2 = from tab1 in context.Table1
              join tab2 in context.Table2 on Table1.ID equals Table2.Table1ID
              where tab1.TypeCode == 3
                    tab2.SomeOtherID == 4 &&  
              select tab1.ID;

The only difference between them being the tab2.SomeID where clause changes to tab2.SomeOtherID.

If the changing where clause was being applied to tab1 I could just add a .Where to the query but how do I use a common query and specify a different tab2 where clause?

Answer 1

Dynamically Composing Expression Predicates

or

You need something like this? Use the Linq Dynamic Query Library (download includes examples).

Check out ScottGu's blog for more examples.

Answer 2

You can have optional parameters in queries:

int? someID = null;
int? someOtherID = 4;

var myQuery = from tab1 in context.Table1 
              join tab2 in context.Table2 on Table1.ID equals Table2.Table1ID 
              where tab1.TypeCode == 3 
               && (someID == null || tab2.SomeID == someID)
               && (someOtherID == null || tab2.SomeOtherID == someOtherID)
              select tab1.ID;

Linq-to-SQL will eliminate the parts that can be evaluated client-side from the query, so in the above example the where clause will filter on tab1.TypeCode and tab2.SomeOtherID.