Interesting LinqToSql behaviour

Question

We have a database table that stores the location of some wave files plus related meta data. There is a foreign key (employeeid) on the table that links to an employee table. However not all wav files relate to an employee, for these records employeeid is null. We are using LinqToSQl to access the database, the query to pull out all non employee related wav file records is as follows:

var results = from Wavs in db.WaveFiles
              where Wavs.employeeid == null;

Except this returns no records, despite the fact that there are records where employeeid is null. On profiling sql server i discovered the reason no records are returned is because LinqToSQl is turning it into SQL that looks very much like:

SELECT Field1, Field2 //etc
FROM WaveFiles
WHERE 1=0

Obviously this returns no rows. However if I go into the DBML designer and remove the association and save. All of a sudden the exact same LINQ query turns into

SELECT Field1, Field2 //etc
FROM WaveFiles
WHERE EmployeeID IS NULL

I.e. if there is an association then LinqToSql assumes that all records have a value for the foreign key (even though it is nullable and the property appears as a nullable int on the WaveFile entity) and as such deliverately constructs a where clause that will return no records.

Does anyone know if there is a way to keep the association in LinqToSQL but stop this behaviour. A workaround i can think of quickly is to have a calculated field called IsSystemFile and set it to 1 if employeeid is null and 0 otherwise. However this seems like a bit of a hack to work around strange behaviour of LinqToSQl and i would rather do something in the DBML file or define something on the foreign key constraint that will prevent this behaviour.

Answer 1

I think you should double-check your dbml file. Sounds like Linq doesn't know that employeeid is a nullable column. Or look at your .cs file. The attributes for this column should look like this:

[Column(Storage="_employeeid", DbType="Int")]

and not:

[Column(Storage="_employeeid", DbType="Int NOT NULL")]

Answer 2

try this:

var results = from Wavs in db.WaveFiles
              where DbNull.Value.Equals(Wavs.employeeid)

another way and good practe a nice is to introduce a default employee where every wave file is associated to, that isn´t associated to a real employee

Answer 3

The column is defined as:

[Column(Storage="_employeeid", DbType="Int")]

The way round it whilst leaving the association was to do a left join from the employee entity collection.