Interesting LinqToSql behavior

Question

We have a database table that stores the location of some wave files and related metadata. There is a foreign key (employeeid) on the table that refers to the employees table. However, not all wav files are employee related; employeeid is null for these entries. We use LinqToSQl to access the database, the request to retrieve all the records associated with an unauthorized WAV file looks like this:

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

Except that no records are returned, even though there are records in which employeeid is null. On profiling a SQL server, I found that the reason that no records are returned is because LinqToSQl turns it into SQL, which is very similar:

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

Obviously, this does not return rows. However, if I go to the DBML constructor and remove the association and save. Suddenly, the same LINQ query turns into

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

those. if there is an association, then LinqToSql assumes that all records have a value for the foreign key (even if it is NULL, and the property appears as an object with a null value in the WaveFile object) and as such provides a where clause construct that will not return records.

- , LinqToSQL, . , , , IsSystemFile 1, employeeid null 0 . , LinqToSQl, - DBML - , .

Answer 1

:

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

, , , .

Answer 2

, dbml. , Linq , employeeid . .cs . :

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

:

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

Answer 3

try the following:

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

Another way and good practice is to familiarize the employee by default, with which each wave file that is not associated with a real employee is associated.