LINQ, Left Join, throwing exception...failed because the materialized value is null

I'm stumped, how do i fix this? The ProductAvailability table in my query does not have a record for each product found and throws the following error each time I run it.

The cast to value type 'DateTime' failed because the materialized value is null. Either the result type's generic parameter or the query must use a nullable type.

How do I fix this error? I tried casting paj.DateAvailable to (DateTime?) and also checking for null but that doesn't 开发者_如何转开发seem to fix the issue. Hmmm?

Here is my query. Any ideas?

var query = (from p in entities.Products
             join pa in entities.ProductAvailabilities on p.ProductId equals pa.ProductId into joinProductAvailabilities
             from paj in joinProductAvailabilities.DefaultIfEmpty()
             where  ps.IsActive
             select new { ProductId = p.ProductId, DateAvailable = paj.DateAvailable }).Distinct();

You could try explicitly declaring a type for the result.

public class MyProductQueryResult
{
  public int ProductId {get;set;}
  public DateTime? DateAvailable {get;set;}
}


from p in entities.Products 
from paj in p.ProductAvailabilities.DefaultIfEmpty()
select new MyProductQueryResult()
  {ProductId = p.ProductId, DateAvailable = paj.DateAvailable}

Since things are nice and explicit, this should get the typing right. However, one would think that casting paj.DateAvailable would be enough, so there might be something more to your situation.