Dynamic Where clauses with multiple joins using Linq

Question

I need to build a dynamic where clause in a Linq statement with multiple joins.

.Net 3.5
Linq-To-Sql

I have these incoming parameters for the Linq statement, only the "UID" is required.

int uid = 23702;  // <-- Only one Required
string courseNumber = "";
string title = "";
int? categoryId = null;
int? typeId = null;

I've been testing this out in LinqPad and while I've gotten the query to work with all Where clauses in place, the Nullable int parameters end up returning incorrect results.

Here's my Linq statement:

var ci = course_instances;

var query = courses.Join(ci, 
    c => c.course_id, 
    i => i.course_id, 
    (c, i) => new
{
    c = c,
    i = i
}).Join(user_courses, 
    temp => temp.i.instance_id, 
    uc => uc.instance_id, 
    (temp, uc) => new
{
    temp = temp,
    uc = uc
})
.Where (temp1 => (temp1.uc.uid == uid))
.Where (temp1 => (temp1.temp.c.course_number.Contains(courseNumber)))
.Where (temp1 => (temp1.temp.c.title.Contains(title)))
//.Where (temp1 => (temp1.temp.c.course_type_id == typeId))  
//.Where (temp1 => (temp1.temp.c.course_category_id == categoryId))
.Select (temp1 => new CourseSearchMyCourses
{
    // snipped the many properties
});

I've tried using PredicateBuilder, but it returns the error:

The type arguments for method 'System.Linq.Queryable.Where(System.Linq.IQueryable, System.Linq.Expressions.Expression>)' cannot be inferred from the usage. Try specifying the type arguments explicitly.

Here's my PredicateBuilder Linq attempt:

var conditions = PredicateBuilder.True<user_course>();
conditions = conditions.And(c => c.uid == uid);

var ci = course_instances;

var query = courses.Join(ci, 
    c => c.course_id, 
    i => i.course_id, 
    (c, i) => new
{
    c = c,
    i = i
}).Join(user_courses, 
    temp => temp.i.instance_id, 
    uc => uc.instance_id, 
    (temp, uc) => new
{
    temp = temp,
    uc = uc
})
.Where (conditions)

.Select (temp1 => new CourseSearchMyCourses
{
    // snipped the many properties
});

BTW, I also tried using "System.Linq.Dynamic" using string queries, and got the error the " and " isn't recognized.

Any help is appreciated.

Thanks.

score 0 · Answer 1 · edited May 23 '17 at 12:29

Linq predicates with nullable type variables get translated into a SQL predicate = NULL. But that is totally different than what it should be: IS NULL.

You expect to get the rows where course_type_id is empty, but the = comparison returns no results because NULL is not a value and the comparison returns UNKNOWN. I think that is the cause of your "incorrect results".

If this is your problem, a fix can be found here.

Dynamic Where clauses with multiple joins using Linq

1 Answers1