Preventing "'System.DateTime' failed because the materialized value is null"

Question

I would like to prevent this from happening but in a different way rather than setting type of DateCreated to nullable DateTime.

The full exception reads like this:

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

When I run following query on the database I can see that there are no null records in there so I believe that I should avoid setting the DateCreate property to nullable DateTime as it does not make much sense to me (even though I know it would solve the problem). Is there something I am understanding wrong?

Following line from the query is causing the issue:

DateCreated = subJoined.dateUploaded

This is the full LINQ query:

using (var db = new ABEntities())
            {
                var features = (from textObject in db.textObjects
                                join container in db.containers.DefaultIfEmpty() on textObject.textObjectPK equals
                                    container.textObjectPK into tObjsContainerJoined
                                from subContainerJoin in tObjsContainerJoined.DefaultIfEmpty()
                                join tObjsMedia in db.media on subContainerJoin.mediaID equals
                                    tObjsMedia.mediaID into tObjsMediaJoined
                                from subJoined in tObjsMediaJoined.DefaultIfEmpty()
                                from textContainer in tObjsContainerJoined
                                where
                                    textObject.version == Constants.Versions.LATEST &&
                                    textObject.textObjectTypeID == Constants.News.FEATURES &&
                                    textObject.deployDate <= DateTime.Now
                                select new TextObject
                                {
                                    Id = textObject.textObjectID,
                                    Title = textObject.title,
                                    ContainerId = textContainer.containerID,
                                    Description = textContainer.container1,
                                    DateCreated = textObject.deployDate,
                                    Media = new Media
                                           {
                                               Title = subJoined.title,
                                               MediaFormat = subJoined.extension,
                                               MediaTypeID = subJoined.mediaTypeID,
                                               MediaFile = subJoined.fileName,
                                               Credit = subJoined.credit,
                                               MembersOnly = subJoined.membersOnly,
                                               LastModified = subJoined.lastModified,
                                               DateCreated = subJoined.dateUploaded
                                           },
                                    TypeId = textObject.textObjectTypeID
                                }).OrderByDescending(t => t.DateCreated).ToList();

                return features;
            }

Here is media class definition:

[Serializable]
    public class Media
    {
        public int Id { get; set; }
        public string MediaFile { get; set; }
        public string Title { get; set; }
        public string Credit { get; set; }
        public int? MediaTypeID { get; set; }
        public string MediaFormat { get; set; }
        public bool? isYoutube { get; set; }
        public string YoutubeID { get; set; }
        public int Width { get; set; }
        public int Height { get; set; }
        public int Views { get; set; }
        public string Description { get; set; }
        public int SiloID { get; set; }
        public DateTime DateCreated { get; set; }
        public bool IsVideo { get; set; }
        public int SegmentId { get; set; }
        public string Extension { get; set; }
        public bool? ShowOnHomepage { get; set; }
        public bool? MembersOnly { get; set; }
        public DateTime? LastModified { get; set; }
}

Query that was being generated - caught by SQL Profiler:

SELECT 
    [Project1].[textObjectPK] AS [textObjectPK], 
    [Project1].[textObjectID] AS [textObjectID], 
    [Project1].[title] AS [title], 
    [Project1].[containerID] AS [containerID], 
    [Project1].[container] AS [container], 
    [Project1].[deployDate] AS [deployDate], 
    [Project1].[title1] AS [title1], 
    [Project1].[extension] AS [extension], 
    [Project1].[mediaTypeID] AS [mediaTypeID], 
    [Project1].[fileName] AS [fileName], 
    [Project1].[credit] AS [credit], 
    [Project1].[membersOnly] AS [membersOnly], 
    [Project1].[C1] AS [C1], 
    [Project1].[dateUploaded] AS [dateUploaded], 
    [Project1].[textObjectTypeID] AS [textObjectTypeID]
    FROM ( SELECT 
        [Extent1].[textObjectPK] AS [textObjectPK], 
        [Extent1].[textObjectID] AS [textObjectID], 
        [Extent1].[textObjectTypeID] AS [textObjectTypeID], 
        [Extent1].[title] AS [title], 
        [Extent1].[deployDate] AS [deployDate], 
         CAST( [Extent3].[lastModified] AS datetime2) AS [C1], 
        [Extent3].[mediaTypeID] AS [mediaTypeID], 
        [Extent3].[fileName] AS [fileName], 
        [Extent3].[title] AS [title1], 
        [Extent3].[extension] AS [extension], 
        [Extent3].[credit] AS [credit], 
        [Extent3].[dateUploaded] AS [dateUploaded], 
        [Extent3].[membersOnly] AS [membersOnly], 
        [Join4].[containerID] AS [containerID], 
        [Join4].[container] AS [container]
        FROM    [dbo].[textObjects] AS [Extent1]
        LEFT OUTER JOIN  (SELECT [Extent2].[textObjectPK] AS [textObjectPK], [Extent2].[mediaID] AS [mediaID]
            FROM   ( SELECT 1 AS X ) AS [SingleRowTable1]
            INNER JOIN [dbo].[containers] AS [Extent2] ON 1 = 1 ) AS [Join1] ON [Extent1].[textObjectPK] = [Join1].[textObjectPK]
        LEFT OUTER JOIN [dbo].[media] AS [Extent3] ON [Join1].[mediaID] = [Extent3].[mediaID]
        INNER JOIN  (SELECT [Extent4].[containerID] AS [containerID], [Extent4].[textObjectPK] AS [textObjectPK], [Extent4].[container] AS [container]
            FROM   ( SELECT 1 AS X ) AS [SingleRowTable2]
            INNER JOIN [dbo].[containers] AS [Extent4] ON 1 = 1 ) AS [Join4] ON [Extent1].[textObjectPK] = [Join4].[textObjectPK]
        WHERE (1 = [Extent1].[version]) AND (2 = [Extent1].[textObjectTypeID]) AND ([Extent1].[deployDate] <= (SysDateTime()))
    )  AS [Project1]
    ORDER BY [Project1].[deployDate] DESC

Answer 1

Instead of setting the datatype to Nullable, you can convert your value in your query to a nullable type and check if this is null.

DateCreated = (DateTime?)subJoined.dateUploaded ?? DateTime.Now

Your query will look like this:

using (var db = new ABEntities())
{
    var features = (from textObject in db.textObjects
                    join container in db.containers.DefaultIfEmpty() on textObject.textObjectPK equals container.textObjectPK into tObjsContainerJoined
                    from subContainerJoin in tObjsContainerJoined.DefaultIfEmpty()
                    join tObjsMedia in db.media on subContainerJoin.mediaID equals tObjsMedia.mediaID into tObjsMediaJoined
                    from subJoined in tObjsMediaJoined.DefaultIfEmpty()
                    from textContainer in tObjsContainerJoined
                    where textObject.version == Constants.Versions.LATEST &&
                                    textObject.textObjectTypeID == Constants.News.FEATURES && textObject.deployDate <= DateTime.Now
                    select new TextObject
                    {
                          Id = textObject.textObjectID,
                          Title = textObject.title,
                          ContainerId = textContainer.containerID,
                          Description = textContainer.container1,
                          DateCreated = textObject.deployDate,
                          Media = new Media
                                 {
                                       Title = subJoined.title,
                                       MediaFormat = subJoined.extension,
                                       MediaTypeID = subJoined.mediaTypeID,
                                       MediaFile = subJoined.fileName,
                                       Credit = subJoined.credit,
                                       MembersOnly = subJoined.membersOnly,
                                       LastModified = subJoined.lastModified,
                                       DateCreated = (DateTime?)subJoined.dateUploaded ?? DateTime.Now
                                  },
                           TypeId = textObject.textObjectTypeID
                   }).OrderByDescending(t => t.DateCreated).ToList();

   return features;
}

Why does it gives an error on that specific line?

Disclaimer: this is just a guess (correct me if I'm wrong...)

Probably because you do a left join and EF expects that some values can be null and it throws an error as a precaution. It only throws an error at the DateTime datatype because it knows what to do when the other properties are null (strings and nullable types).