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

Subaxillary

Have you tried running with SQL logging enabled, then running the raw SQL query against the database to see if it returns null values for the date? – Assortment

@MarcL. good idea, will try that tomorrow and let you know about the results. – Subaxillary

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).

Hymenopteran

Hey, even though I like your solution (as we don't have to change the class property data type) it still does not explain the problem why we would be checking for nulls as we are not expecting any null values. – Subaxillary

@JakubHolovsky I edited my question with a possible explanation. It is just a guess though... – Hymenopteran

@Hymenopteran Actually I don't think it matters there's a Left join. I got the same error as the OP & I get it if string values are null, too, until I commented out those properties from my LINQ expression. I was trying to assign a null date to an anonymous model/function thingy ( var x = from v in ctx.MyView select new { Id = x.ID ... } ), and even though my field was a DateTime? in my model that represents my SQL View, the anonymous function doesn't seem to allow DateTime?. But your DateCreated = (DateTime?)subJoined.dateUploaded ?? DateTime.Now is an absolute GEM!!! Thank you! – Fixate

@Hymenopteran One caveat, for u & others arriving here by Google like me-if u don't want today's date & really do want a blank, I use DateTime stdDate = DateTime.Parse("01/01/1800"); then put stdDate instead of DateTime.Now. Then, I'll iterate back through the records: foreach (var a in x) { MyViewModel mvm = new MyViewModel(); mvm.ID = a.ID; } -adding them to a ViewModel that I add to a List<MyViewModel> that I ultimately return. I call that repository function in my controller & iterate. When my nullable date appears, I replace stdDate with null in a new MyViewModel -Add to list, ret. – Fixate