Linq, Left Join and Dates...

Question

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.

Answer 1

Using a left join isn't really helping you here. Since there can never be any results in the right-hand table, you might as well just retrieve the left-hand table and only the left. This is a simple "not in" / "not exists" query:

From m in dbContext.Movies _
Where Not dbContext.Showings.Any(Function(s) s.MovieID = m.MovieID) _
Select m

Answer 2

If there is no record in the Showings table, then the entire object in the query should be nothing. The date should never come into play under such a scenario. A left join would be written like this, selecting only the movies where the showing is missing

Dim query = From m In dbContext.Movies _
            Group Join s In dbContext.Showing On m.ID Equals s.MovieID Into g = Group _
            From item In g.DefaultIfEmpty() _
            Where item Is Nothing _
            Select m