I want to do a simple left outer join.

Let's make a very simple example to explain the problem.

I have a movie rating system. I have a Movie table, a user (who rates movies) table, and a rating table. The rating table has a rating column, and two foreign keys to the other two tables.

I want to get all users who HAVE NOT rated a movie.

When I write my left outer join in LINQ, I cannot project from DefaultIsEmpty to a RatingsEntity with a NULL field. This is because the entity properties are not null-able.

The problem is that the generated SQL's where clause compares a non null-able field to a null field to get the users who have not rated a movie. The result is that the query results are always empty.

Does anybody know of a way to make this work, short of adding an extra column in the table, or making the RatingsEntity a partial class and adding an extra null-able property?

Is what I am trying to do impossible with LINQ to LLBLGen Pro?

Thanks, Scott

I want to get all users who HAVE NOT rated a movie.

I think the query can be simplified to:

SELECT * FROM User
WHERE Id NOT IN (Select UserId FROM Rating)

I got this to work with a LINQ query based upon your SQL.

I thought though that there might be some trick to getting it to work as a left outer join.

Thanks for your help!!!

Scott