Linq to SQL nullable int?

Question

I have the following linq query (pretty simple):

var jobs = from j in db.jobmsts
    join jd in db.jobdtls on j.jobdtl_id equals jd.jobdtl_id
    where j.jobmst_type != 1 && j.jobmst_prntid.Equals(folder.FolderID)
    orderby j.jobmst_name
    select new
        {
            JobID = j.jobmst_id,
            JobName = j.jobmst_name,
            Description = j.jobmst_desc,
            Source = jd.Source
        };

folder.FolderID is a nullable int (int?) that is passed into the method as a parameter. So, when it's null, the query returns no results. But if I execute the same query in TSQL:

select * from jobmst j 
join jobdtl jd on j.jobdtl_id = jd.jobdtl_id 
where j.jobmst_type != 1 and j.jobmst_prntid is null

I get 6 records. Oddly, if I substitute "folder.FolderID" with "null" (in the linq code), it does return 6 records. So, obviously there's a difference between "null" and an object with a null value. I just don't know what is it and how to fix this.

FYI, I had the code so that the where clause looked like this:

where j.jobmst_type != 1 && j.jobmst_prntid == folder.FolderID

But it didn't produce any results either.

Any help would be appreciated.

Answer 1

As I already mentioned it in a comment: Compare nullable types in Linq to Sql

The answer to that question also applies here: Linq to SQL behaves kind of strangely when querying nullable types. The solution is to check for null explicitly if the value of the nullable type is null. If you don't want to write two queries, just write different where clauses (you have to mix in some lambda syntax):

Expression<Func<Job,bool>> predicate;
if(folder.FolderID == null) {
    predicate = j=>j.jobmst_prntid == null && j.jobmst_type != 1;
} else {
    predicate = j=>j.jobmst_prntid == folder.FolderID && j.jobmst_type != 1;
}

//...
.Where(predicate)
select new
{
    // ...
}