Select record with < DateTime returns EQUAL value in EF Core 6

Question

I try to select a date field of two records in a MS SQL Server accounting database table.

The first record's date is selected by id like this:

var lastDate = db.Set<Accounting>().Where(p => p.Id == accountingId).First().AccountDate;

This value is returned for the date:

Now I try to select the accounting record, that is before this date with the folowing request:

    var beforeAccounting = db.Set<Accounting>().Where(p => p.AccountDate < lastDate).OrderByDescending(p => p.AccountDate).FirstOrDefault();
    var dateBefore = beforeAccounting?.AccountDate ?? new DateTime(2000, 1, 1);

As there is only ONE record in the db at this time, I expected to get NULL with FirstOrDefault(), setting dateBefore to 01.01.2000 in the next line.

Couriously the request returns the same row from the database.

As you can see the date field is exactly the same (also anything else, because it is the same record)

But why is this record returned by .Where(p => p.AccountDate < lastDate) ? It is equal, not less isn't it? What am I doing wrong here? Tried the same code in LinqPad where it works like expected? Am I missing something?

Answer 1

I had a play with an example and I think there is something more missing from your particular scenario because I was not able to reproduce the issue. I have entities that have a CreatedAt DateTime value and did something essentially the same as your example:

using var context = new SoftDeleteDbContext();
var date = context.Posts.OrderBy(x => x.CreatedAt).Select(x => x.CreatedAt).First();
var test = context.Posts.Where(x => x.CreatedAt < date).OrderByDescending(x=>x.CreatedAt).FirstOrDefault();

I get a #null response on the test fetch. I also tried the fetching of the date loading the entire entity:

var date = context.Posts.OrderBy(x => x.CreatedAt).First().CreatedAt;

... just in case that made a difference, but it didn't.

I also tried adding a single tick to my CreatedAt datetime in case there might be some resolution issue, but no joy.

Perhaps check the DbContext configuration to ensure that you don't have some form of conversion or such on date time values, or anything odd with the column definition in the table.