Linq to Entities: Left join to get items NOT found in the join

Question

I've got two un-related (no FK's defined) tables. The first table contains some tasks for which a user may not have access. I need to find all those tasks - in this case, the joined table would contain nulls. How do I get them?

Here's the setup:

TimeData table 
- userID 
- taskID 
- hours

ApprovedTasks table (the one that should contain nulls)
- taskID 
- userID

The SQL query would look like this:

select * from TimeData td 
left join ApprovedTasks at
 on at.taskID = td.taskID and at.userID = td.userID
where at.taskID is null

Any way to pull that off using a LINQ to Entity query?

TIA

Answer 1

Check out... http://stackoverflow.com/questions/801757/disjoint-union-in-linq

This should work...

var approvedTaks = from at in ApprovedTasks.Except(
                                        from at2 in ApprovedTasks
                                        where at2.userID == userId and at2.taskID==taskId
                                        select at2)
                    where at.userID == userId and at.taskID==taskId
                    select at;

but sorry don't have the database handy to test it.