Entity framework equivalent to SQL 'IS NULL'

Question

I am trying to translate the following query into entity framework:

SELECT            Subjects.Name
FROM              Subjects LEFT OUTER JOIN
                        Projects_Subjects ON Subjects.ID = Projects_Subjects.Section_ID
WHERE             (Projects_Subjects.Project_ID IS NULL) OR
                        (Projects_Subjects.Project_ID <> 2)

Where subjects is a simple table with subject.ID, subject.Name and Projects_Subjects is a table effecting the many-to-many relationship between subjects and projects, with Projects_Subjects.Project_ID and Projects_Subjects.Subject_ID columns.

This query works fine in SQL, in that it returns all the subjects which are not yet associated with the project with ID = 2.

I am trying to translate this to Linq to Entities. So far I have:

db.Subjects.Where(s => s.Projects == null || !s.Projects.Select(p => p.ID).Contains(ProjectID))

Which the compiler is happy with as far as syntax goes, but at runtime I get the following error:

'System.Data.Objects.DataClasses.EntityCollection`1'. Only primitive types (such as Int32, String, and Guid) and entity types are supported.

There is no other info really. As far as I can tell it doesn't like the s.Projects == null bit as it works fine if I take that out. Question is: How can I perform that check on the entity collection?

Sounds like a lot of problems with NULLs and entity framework: http://stackoverflow.com/questions/682429/how-can-i-query-for-null-values-in-entity-framework http://stackoverflow.com/questions/1126133/linq-to-sql-nullable-types-in-where-clause http://stackoverflow.com/questions/2411894/handling-null-values-in-where-clause-using-linq-to-sql http://stackoverflow.com/questions/586097/compare-nullable-types-in-linq-to-sql http://stackoverflow.com/questions/2097539/linq-where-column-null-reference-not-the-same-as-column-null — Code Magician, Aug 19 '11 at 05:08
Nevermind, I am an idiot. There is no need to check for null, because that is the default state... The query works fine without the s.Projects == null bit. — yu_ominae, Aug 19 '11 at 05:09

score 4 · Answer 1 · answered Aug 19 '11 at 05:13

The answer is that I am an idiot. The default for a linq to entity query is that it will return everything and you just need to subtract from there. So simply

db.Subjects.Where(s => !s.Projects.Select(p => p.ID).Contains(ProjectID))

Actually does what I wanted it to do... Sorry for the waste of time.

score 1 · Accepted Answer · answered Aug 19 '11 at 05:18

You're not checking for a column to be null, but for an associated property (another entity) to be null. As you point out, that's the default state.

That exception that you got basically means that only POCO types can be sent to SQL for comparison in where / order by statements against SQL types. EF thinks you're trying to compare objects here so complains.

gandarez · Answer 3 · 2011-08-19T14:52:26.000

Something like this?

var subjects = from subject in dc.GetTable<Subjects>()
                               join entityProjects_Subjects in dc.GetTable<Projects_Subjects>()
                                    on subject.ID equals entityProjects_Subjects.Section_ID into tempProjects_Subjects
                               from projects_subjects in tempProjects_Subjects.DefaultIfEmpty()
                               where projects_subjects.Project_ID != 2 || projects_subjects == null
                               select new
                               {                                   
                                   Name = subject.Name
                               };

Entity framework equivalent to SQL 'IS NULL'

3 Answers3