I would like to prevent this, but in a different way, and not set the DateCreated type to NULL DateTime.
The full exception reads as follows:
Casting a value to the type System.DateTime failed because the materialized value is zero. Either the general parameter of the result type or the query should use a type with a null value.
When I run the following query in the database, I see that there are no null entries there, so I think that I should avoid setting the DateCreate property to NULL DateTime, as this does not make much sense to me (although I know this would solve the problem ) Am I misunderstanding something?
:
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