0

I'm fairly new to EF Core, and I am trying to filter a set of jobs based on whether the appointment date is in the past and the status.

Why would this result in an error?

var maxJobAgeDate = DateTime.Now.AddDays(-14);

JobSummaries.Where(j => DateTime.Parse(j.AppointmentDate) < maxJobAgeDate && j.JobStatus == JobStatus.CompleteSignOffConfirmed);

The error:

The LINQ expression 'DbSet .Where(j => DateTime.Parse(j.AppointmentDate) < __maxJobAgeDate_0 && (int)j.JobStatus == 4)' could not be translated. Either rewrite the query in a form that can be translated, or switch to client evaluation explicitly by inserting a call to either AsEnumerable(), AsAsyncEnumerable(), ToList(), or ToListAsync(). See https://go.microsoft.com/fwlink/?linkid=2101038 for more information.

I'm using EF Core.

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Chris
  • 3,113
  • 5
  • 24
  • 46
  • 1
    It most probably is `DateTime.Parse` method. What is the format of the `AppointmentDate` string in `DateTime.Parse(j.AppointmentDate)` – Zee Mar 17 '22 at 18:01
  • @Zee "2022-03-01" etc – Chris Mar 17 '22 at 18:03
  • 1
    Why are you parsing a string into a DateTime _inside the query_? Why is a DateTime object stored in your database as a string? – gunr2171 Mar 17 '22 at 18:03
  • 2
    Why is this a string field instead of `date`? That's a database bug. Fix it so you don't have to parse anything – Panagiotis Kanavos Mar 17 '22 at 18:04
  • Thanks for the responses, all. I have just started on the project and did not create the data structures or database. I will question why `AppointmentDate` is not a `DateTime`. – Chris Mar 17 '22 at 18:09

1 Answers1

0

You need to specify the date format while converting in the Non-standard ISO types.

Try this.

JobSummaries.Where(j => DateTime.ParseExact(j.AppointmentDate,"yyyy-MM-dd",CultureInfo.InvariantCulture) < maxJobAgeDate 
                && j.JobStatus == JobStatus.CompleteSignOffConfirmed).ToList();

I strongly recommend fixing this at a database level

Zee
  • 622
  • 4
  • 13
  • Same error: `The LINQ expression 'DbSet.Where(j => DateTime.ParseExact(s: j.AppointmentDate, format: "yyyy-MM-dd", provider: __InvariantCulture_0) < __maxJobAgeDate_1 && (int)j.JobStatus == 4)' could not be translated. Either rewrite the query in a form that can be translated, or switch to client evaluation explicitly by inserting a call to either AsEnumerable(), AsAsyncEnumerable(), ToList(), or ToListAsync().` – Chris Mar 17 '22 at 18:15
  • I added a .ToList() at the end to force evaluation. Try it now – Zee Mar 17 '22 at 18:18