CODEWITHSUNDEEP
c#entity-frameworklinq
vuong quang
vuong quang

Reputation: 31

Filter by nullable datetime field using Linq to Entity

I'm running a LINQ-to-entities query on a table that contains a nullable DateTime with this Model:

public Nullable<DateTime> TheCloseDate { get; set; }

I has a variable DateTime and I want to filter one record with the correct a Datetime. For example:

var datefilter = = new DateTime(2022, 8, 18, 16, 32, 10, DateTimeKind.Local);
var xx= dt.Students
    .where( x => x.TheCloseDate.Hasvalue && x.TheCloseDate==datefilter)
    .FirstOrDefault()

Upvotes: 2

Views: 206

Answers (1)

Steve Py
Steve Py

Reputation: 34653

DateTime comparisons in database systems can be tricky. Coupling that to comparing to values provided by an application server ultimately linked to a client that could be in a different time zone than either is doubly and triply so.

The database can be storing date and time values relative to a given time zone or UTC depending on the DB engine and how they are configured. Then you have an application server that is running .Net localized to a given region. Something as simple as having a time zone mismatch between the application server and database server will cause problems where you query data showing there is data for 2022/08/18 16:32:10 but passing that results in a local time for a different time offset. Also consider that date times in the DB vs. .Net can have different resolutions in terms of going down to the ms or 100ns, etc.

The general recommendations when working with date + time values:

  1. If possible, standardize on storing UTC times. Convert local times in the application server to UTC. Convert UTC back to local time for the applicable client location. For web applications you might have a Web App Servers hosted across data centres across US-East and US-West, but have users accessing the application for data stored across any range of time zones. If the time zone is relevant then it should be recorded with the DateTime using DateTimeOffset rather than relying on Local Kind. Local Kind will base the time offset on the application server's time zone. If you don't care whether the time reflects a client's time zone and you just want to ensure that times are standardized, standardize on UTC.

  2. Consider always using ranges where you want to compare times.

For example, to find records down to the second based on a client's local time:

var clientTimezone = "Mountain Standard Time"; // Example, would come from configuration for that client.

var timeZone = TimeZoneInfo.FindSystemTimeZoneById(clientTimezone);
DateTime startUtcDateTime = TimeZoneInfo.ConvertTimeToUtc(new DateTime(2022, 8, 18, 16, 32, 10, timeZone);

datetime endUtcDateTime = startUtcDateTime.AddSeconds(1);

var students = dt.Students
    .Where( x => x.TheCloseDateUTC >= startUtcDateTime && x.TheCloseDateUTC < endUtcDateTime)
    .ToList();

Where a database is set to store UTC then it is advisable to suffix the DateTime field with "UTC". If storing local then suffix with "Local" so it is clear to developers what to expect to read or write, especially if the DB is not capable of storing offset information with the value. (Column set to datetime rather than datetimeoffset)

This would find all records where the close date might have fallen within that exact second. If you want the First or Last match (two users submitting rows at the same time could result in multiple records in the same second) then add an OrderBy clause on The CloseDate and take the FirstOrDefault;

var student = dt.Students
    .Where( x => x.TheCloseDateUTC >= startUtcDateTime && x.TheCloseDateUTC < endUtcDateTime)
    .OrderBy(x => x.TheCloseDateUTC) //To get first...
    // or .OrderByDescending(x => x.TheCloseDateUTC) // To get last....
    .FirstOrDefault();

If you want to store local times in the database then you should be working with DateTimeOffset rather than relying solely on DateTime and DateTimeKind, then consider what that means when you want to find a record for a particular client time (2022/08/19T16:32:10) When that might actually be (2022/08/19T16:32:10.000000-7:00) [MST-7:00] vs. what that means if someone in [EST-5:00] wants to query that time? Should that row be returned if they enter 2022/08/19T16:32:10 or 2022/08/19T18:32:10?

Upvotes: 1

Related Questions