Linq, Left Join and Dates...
- by BitFiddler
So my situation is that I have a linq-to-sql model that does not allow dates to be null in one of my tables. This is intended, because the database does not allow nulls in that field. My problem, is that when I try to write a Linq query with this model, I cannot do a left join with that table anymore because the date is not a 'nullable' field and so I can't compare it to "Nothing".
Example:
There is a Movie table, {ID,MovieTitle}, and a Showings table, {ID,MovieID,ShowingTime,Location}
Now I am trying to write a statement that will return all those movies that have no showings. In T.SQL this would look like:
Select m.*
From Movies m Left Join Showings s On m.ID = s.MovieID
Where s.ShowingTime is Null
Now in this situation I could test for Null on the 'Location' field but this is not what I have in reality (just a simplified example). All I have are non-null dates.
I am trying to write in Linq:
From m In dbContext.Movies _
Group Join s In Showings on m.ID Equals s.MovieID into MovieShowings = Group _
From ms In MovieShowings.DefaultIfEmpty _
Where ms.ShowingTime is Nothing _
Select ms
However I am getting an error saying
'Is' operator does not accept operands of type 'Date'. Operands must be reference or nullable types.
Is there any way around this? The model is correct, there should never be a null in the Showings:ShowTime table. But if you do a left join, and there are no show times for a particular movie, then ShowTime SHOULD be Nothing for that movie...
Thanks everyone for your help.
