Linq query with DateTime Comparison is not working as expected

Question

Here the query that does not work as expected:

var source     = from conv in AuthRepository.Database.Conversations
                 from msg in AuthRepository.Database.ConversationMessages
                 let pId = playerId
                 let lastReadDate =
                 (
                     from rd in AuthRepository.Database.ConversationReadDates
                     where rd.player_id == pId && rd.conversation_id == conv.id
                     select rd.last_read_date
                 ).FirstOrDefault()
                 // retrieve last message
                 where msg.id ==
                 (
                     from msgs in AuthRepository.Database.ConversationMessages
                     where msgs.conversation_id == conv.id
                     orderby msgs.created descending
                     select msgs.id
                 ).FirstOrDefault()
                 // check if last message was read by player
                 where msg.created > lastReadDate
                 select conv.id;

return await source.CountAsync();

In my test "lastReadDate" is supposed to be the default value of DateTime since no last_read_date was found. But this where clause fail: where msg.created > lastReadDate I already checked that msg.created have a correct value.

But it work as as expected when I split the query in two by retrieving first the lastReadDate part and then use it in the second query like this:

var sourceReaddate = from rd in AuthRepository.Database.ConversationReadDates
                                 from conv in AuthRepository.Database.Conversations
                                 where rd.player_id == playerId && rd.conversation_id == conv.id
                                 select rd.last_read_date;
var readDate = await sourceReaddate.FirstOrDefaultAsync();

var source = from conv in AuthRepository.Database.Conversations
             from msg in AuthRepository.Database.ConversationMessages
             let pId = playerId
             let lastReadDate = readDate
             // check if player is authorized to see the conversation
             where !pId.HasValue ||
             (
                from auth in AuthRepository.Database.ConversationAuthorization
                let conversation = auth.conversation
                where conversation.id == conv.id && auth.player_id == pId.Value
                select conversation.id
             ).Any()
             // retrieve last message
             where msg.id ==
             (
                from msgs in AuthRepository.Database.ConversationMessages
                where msgs.conversation_id == conv.id
                orderby msgs.created descending
                select msgs.id
             ).FirstOrDefault()
             // check if last message was read by player
             where msg.created > lastReadDate
             where !conv.deleted
             select conv.id;
             
return await source.CountAsync();

I suspect this is a bug in EF Core (I use the version 3.1.6) maybe related to this issue? But maybe it's an error from my side. Do you have an idea on what is the problem?

Also here a very disturbing result with this query:

var source  = from conv in AuthRepository.Database.Conversations
              from msg in AuthRepository.Database.ConversationMessages
              let pId = playerId
              let lastReadDate =
              (
                 from rd in AuthRepository.Database.ConversationReadDates
                 where rd.player_id == pId && rd.conversation_id == conv.id
                 select rd.last_read_date
              ).FirstOrDefault()
              // retrieve last message
              where msg.id ==
              (
                 from msgs in AuthRepository.Database.ConversationMessages
                 where msgs.conversation_id == conv.id
                 orderby msgs.created descending
                 select msgs.id
              ).FirstOrDefault()
              select new 
              { 
                  msgCreated = msg.created,
                  lastReadDate, 
                  read = msg.created <= lastReadDate, 
                  unread = msg.created > lastReadDate,
                  sup = msg.created > default(DateTime),
                  defEqual = lastReadDate.Equals(default(DateTime)),
                  defSup = lastReadDate > default(DateTime),
                  defInf = lastReadDate < default(DateTime),
              };
              
var result = await source.ToListAsync();

Here the result of the above query:

{ 
    msgCreated = {07/29/2020 10:48:14}, 
    lastReadDate = {01/01/0001 00:00:00}, 
    read = false, 
    unread = false, 
    sup = true, 
    defEqual = false, 
    defSup = false, 
    defInf = false 
}

This is not normal that "read" and "unread" are both evaluated as false. Any idea why?

Answer 1

A workaround I found thanks to this post:

var source     = from conv in AuthRepository.Database.Conversations
                 from msg in AuthRepository.Database.ConversationMessages
                 let pId = playerId
                 let lastReadDate =
                 (
                     from rd in AuthRepository.Database.ConversationReadDates
                     where rd.player_id == pId && rd.conversation_id == conv.id
                     select (DateTime?)rd.last_read_date
                 ).FirstOrDefault() ?? default(DateTime)
                 // retrieve last message
                 where msg.id ==
                 (
                     from msgs in AuthRepository.Database.ConversationMessages
                     where msgs.conversation_id == conv.id
                     orderby msgs.created descending
                     select msgs.id
                 ).FirstOrDefault()
                 // check if last message was read by player
                 where msg.created > lastReadDate
                 select conv.id;

return await source.CountAsync();