Using linq to return records from sql server based on users datetimeoffset

Question

I have a dateTime offset value that gets passed from the UI which looks like

startDate:2022-04-25 21:08:02.2973333 +00:00 but the date i entered is actually the 26th

i want to get the records from the db for everything before the 26th and equal to the 26th.

this is what i tried

await _repository.GetProjectedListAsync(q=>q.Where(x => x.Created <= DateTime.Parse( query.StartDate.ToString()).ToString("yyyy/MM/dd") ).Select(FirstName))).ToList();

the created column in sql looks like the following

the above conversion didnt work,it doesnt return any results. Is there a way i could convert the repected date to return the records where Created <= Startdate ?

Answer 1

First, let's see the code:

var textDate = DateTime.Parse(query.StartDate.ToString()).ToString("yyyy/MM/dd")

await _repository.GetProjectedListAsync(q => 
    q.Where(x => x.Created <= startDate)
       .Select(FirstName)))
    .ToList();

If you have a variable with the same value, it's better parse outside. Then, you only do the parse once. If you look textDate:

• Take StartDate and convert to string.
• Then, parse to date.
• And convert again to string.

This sentence do nothing because you convert to string and back to date:

DateTime.Parse(query.StartDate.ToString())

So we have:

var textDate = query.StartDate.ToString("yyyy/MM/dd")

I suppose you want work only with date (without time)

var startDate = query.StartDate.Date

If I'm not wrong, you want something like this:

var startDate = query.StartDate.Date
await _repository.GetProjectedListAsync(q => 
    q.Where(x => x.Created <= startDate)
       .Select(FirstName)))
    .ToList();