c# - 防止 "' System.DateTime' 失败，因为物化值为 null"

Question

我想以不同的方式防止这种情况发生，而不是将 DateCreated 的类型设置为可为 null 的 DateTime。

完整的异常是这样的:

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.

当我在数据库上运行以下查询时，我可以看到那里没有空记录，所以我认为我应该避免将 DateCreate 属性设置为可为空的 DateTime，因为它对我来说意义不大(即使我知道它会解决问题)。是不是我理解有误？

查询中的以下行导致了问题:

DateCreated = subJoined.dateUploaded

这是完整的 LINQ 查询:

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;
            }

这是媒体类定义:

[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; }
}

正在生成的查询 - 被 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

您可以将查询中的值转换为可空类型，并检查它是否为 null，而不是将数据类型设置为 Nullable。

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

您的查询将如下所示:

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;
}

为什么它在特定行上给出错误？

免责声明:这只是一个猜测(如果我错了请纠正我......)

可能是因为您进行了左联接并且 EF 期望某些值可以 为 null 并且它会抛出错误作为预防措施。它只会在 DateTime 数据类型上抛出错误，因为它知道当其他属性为 null(字符串和可空类型)时要做什么。