C#, ASP.Net Core and Linq: dealing with null dates

Question

I am having trouble craft a multi join query in Visual Studio 2019, C#, ASP.Net core.

I have two tables:

sessions(
    id          int primary key,
    start_id    int not null,   // foreign key to "start event" event.id
    end_id      int             // foreign key to "end event" event.id
)

events(
    id  int primary key,
    username    varchar(32) not null,
    deviceName  varchar(64) not null,
    eventName   varchar(8) not null,
    eventDate   datetime not null
)

Paired events like login/logout are put into the event table. A trigger on INSERTs to the events table handles the management of the sessions table. For this question assume that the sessions table could look like:

 id | start_id | end_id
----+----------+--------
  1 | 1        | null
  2 | 2        | 3

events:
 id | userName | deviceName | eventName | eventDate
----+----------+------------+-----------+-----------
  1 | alice    | moose      | login     | 2019-03-11 14:02:54
  2 | bob      | juno       | login     | 2019-03-11 15:11:08
  3 | bob      | juno       | logout    | 2019-03-11 17:18:22

In SSMS I can write the query I want as:

SELECT
    sessions.id,
    StartEvents.userName,
    StartEvents.deviceName,
    StartEvents.eventDate as startDate,
    ISNULL(EndEvents.eventDate, GETDATE()) as endDate
FROM sessions
JOIN events StartEvents
    ON sessions.start_id = StartEvents.id
LEFT JOIN events EndEvents
    ON sessions.end_id = EndEvents.id
WHERE StartEvents.eventDate >= @myStart
      AND ISNULL(EndEvents.eventDate, GETDATE()) <= @myEnd

I have tried this in C# as:

var result = (
    from sessions in db.Sessions
    join StartEvents in db.Events
        on sessions.Start_id equals StartEvents.Id
    join EndEventsTmp in db.Events
        on sessions.End_id equals EndEventsTmp.Id into EndEventsTmp2
    from EndEvents in EndEventsTmp2.DefaultIfEmpty()
    where StartEvents.Machine.Trim().ToUpper().Equals(machine.Trim().ToUpper())
          & StartEvents.eventDate >= myStart
          & (EndEvents.eventDate ?? DateTime.Now) <= myEnd
    orderby StartEvents.Date
    select new UserTrackingToFullCalendar {
        Id = StartEvents.Id,
        User = StartEvents.userName,
        Device = StartEvents.deviceName.ToUpper(),
        Start = StartEvents.eventDate,
        End = EndEvents.eventDate ?? DateTime.Now
    }
    ).ToList();

I am told that "Left operand of '??' should be a reference or nullable type". The problem is that the underlying field ("eventDate" in events) can not be null. How do I deal with this?

devNull · Accepted Answer · 2019-04-22 20:31:06Z

2

The eventDate will never be null since, as you said, it's not nullable. The nullable object that you would want to check for is the EndEvents (since you are doing a left join against that entity). You could use the null-conditional operator syntax here:

EndEvents?.eventDate ?? DateTime.Now

This will fall into the right hand side (DateTime.Now) in the case that EndEvents is null.

answered Apr 22, 2019 at 20:31

devNull

4,2791 gold badge19 silver badges17 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

7 Reeds Over a year ago

Thanks, you put me on the right track. After trying your suggestion I am now told that "An expression tree lambda may not contain a null propagating operator". What i had to do was: (eventLogEnd == null ? DateTime.Now : eventLogEnd.Date) <= myEnd. I have no idea why your syntax didn't work.

Collectives™ on Stack Overflow

C#, ASP.Net Core and Linq: dealing with null dates

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related