CODEWITHSUNDEEP
c#asp.net-core
7 Reeds
7 Reeds

Reputation: 2539

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

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?

Upvotes: 0

Views: 390

Answers (1)

devNull
devNull

Reputation: 4219

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.

Upvotes: 2

Related Questions