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: stackoverflow.com/questions/682429/… stackoverflow.com/questions/1126133/… stackoverflow.com/questions/2411894/… stackoverflow.com/questions/586097/… stackoverflow.com/questions/2097539/… — Code Magician
– Code Magician, Commented Aug 19, 2011 at 5: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
– yu_ominae, Commented Aug 19, 2011 at 5:09

yu_ominae · Accepted Answer · 2011-08-19 05:13:04Z

4

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.

answered Aug 19, 2011 at 5:13

yu_ominae

2,9337 gold badges41 silver badges77 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Kirk Broadhurst · Accepted Answer · 2011-08-19 05:18:23Z

1

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.

answered Aug 19, 2011 at 5:18

Kirk Broadhurst

28.9k20 gold badges111 silver badges179 bronze badges

Comments

gandarez · Accepted Answer · 2011-08-19 14:52:26Z

0

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
                               };

edited Aug 19, 2011 at 14:52

answered Aug 19, 2011 at 14:35

gandarez

2,7024 gold badges37 silver badges48 bronze badges

Collectives™ on Stack Overflow

Entity framework equivalent to SQL 'IS NULL'

3 Answers 3

Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related