Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

FirstOrDefault over DateTime when empty returns null #21836

Closed
Racines opened this issue Jul 29, 2020 · 0 comments · Fixed by #23321
Closed

FirstOrDefault over DateTime when empty returns null #21836

Racines opened this issue Jul 29, 2020 · 0 comments · Fixed by #23321
Assignees
Labels
area-query closed-fixed The issue has been fixed and is/will be included in the release indicated by the issue milestone. customer-reported type-bug
Milestone

Comments

@Racines
Copy link

Racines commented Jul 29, 2020

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?

@ajcvickers ajcvickers added this to the Backlog milestone Jul 31, 2020
@smitpatel smitpatel changed the title Linq query with FirstOrDefault() and DateTime comparison is not working as expected FirstOrDefault over DateTime when empty returns null Aug 31, 2020
@ajcvickers ajcvickers modified the milestones: Backlog, 6.0.0 Nov 5, 2020
@ajcvickers ajcvickers added the verify-fixed This issue is likely fixed in new query pipeline. label Nov 6, 2020
@ajcvickers ajcvickers modified the milestones: 6.0.0, MQ Nov 6, 2020
@ajcvickers ajcvickers assigned maumar and unassigned smitpatel Nov 6, 2020
@maumar maumar added closed-fixed The issue has been fixed and is/will be included in the release indicated by the issue milestone. and removed verify-fixed This issue is likely fixed in new query pipeline. labels Nov 13, 2020
maumar added a commit that referenced this issue Nov 13, 2020
@maumar maumar closed this as completed in f4628ea Nov 14, 2020
@ajcvickers ajcvickers modified the milestones: MQ, 6.0.0 Nov 25, 2020
@ajcvickers ajcvickers modified the milestones: 6.0.0, 6.0.0-preview1 Jan 27, 2021
@ajcvickers ajcvickers modified the milestones: 6.0.0-preview1, 6.0.0 Nov 8, 2021
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Labels
area-query closed-fixed The issue has been fixed and is/will be included in the release indicated by the issue milestone. customer-reported type-bug
Projects
None yet
Development

Successfully merging a pull request may close this issue.

4 participants