How to convert string to DateTime in C# EF Core query

user_12659767 0
Viewed 6513
var res = Context.Exampletable
                 .Where(s => s.CompanyId == CompanyId &&  
                             Convert.ToDateTime(s.TextDate) >= DateTime.Now)
                 .Select(x => new Exampletable { TextDate = x.TextDate })
                 .FirstOrDefault();

This is the Linq for one of my problem statements. I want to fetch records future date records from current date & timestamp, so I am converting and comparing it to Datetime but I get this error:

The LINQ expression 'DbSet
.Where(a => Convert.ToDateTime(a.TextDate) > Convert.ToDateTime(DateTime.Now))' > 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()

Note: in Postgresql DB TextDate column has string datatype and contains values like '4/1/2020 10:00 AM'.

Please provide a solution for this.

ef-core-3.0 postgresql-9.1 entity-framework-core entity-framework linq
3 Answers

If you really can't change the underlying column type, then instead of unsupported Convert.ToDateTime use C# cast operator which maps to PostgreSQL CAST operator:

(DateTime)(object)s.TextDate >= DateTime.Now

Note that the "intermediate" cast to object is needed just to make the C# compiler happy.

P.S. I really have no idea why some methods of Convert like ToInt32 are supported, and other like ToDateTime are not. I guess just yet another EF Core inconsistency.

user_52025630

It always baffles me that people decide to store DateTime values as strings and then order users of the database to do calculations with the values. I can imagine you'd like to curse the person who decided to do this, especially because he decided to store it in this non-sortable fashion.

Best solution

If possible, change the database such that it stores DateTimes as DateTimes, or if your database language doesn't know how to do that, store the Ticks of the DateTimes as longs. Future users of the database will glorify your name!

long nowTicks = DateTime.Now.Ticks;
var result = Context.Exampletable
    .Where(example => example.CompanyId == CompanyId && example.DateTicks >= nowTicks);

Almost best solution

if the decision to save DateTimes as strings is a decision of the developers of PostgreSQL, then try to find if they have functions to handle these datetimes, especially comparison

The "it's getting worse" solution

Try to find out if PostgreSQL has string manipulation functions, so you can translate 4/1/2020 10:00 AM into something IComparable. It is difficult if you want to write code to compare this value with for example 4/1/2019 10:00 AM, or 4/2/2020 10:00 AM, so I guess it will be hell of a job to do this in SQL

user_22817900

With EF Core 5+ you can use ValueConverters as a "workaround" for this scenario and use the built in StringDateTimeConverter (https://apisof.net/catalog/e0dd77d4-73c3-6bba-e51a-4842a59894d1).

protected override void OnModelCreating(ModelBuilder modelBuilder)
{
    modelBuilder
        .Entity<Exampletable>()
        .Property(e => e.TextDate)
        .HasConversion<string>();
}

public class ExampleTable
{
 public DateTime TextDate {get; set;}
}

Then simply

DateTimeOffset someValue = some-value-here;
var query = DbContext.Entities.Where(e => e.TextDate <= someValue);

It is important that the parameter inside your LINQ query is a datetimeoffset for this to work as EF CORE doesn't generate a SQL with CAST operation for the column if you don't. See here for an example https://gist.github.com/dasiths/19b885c58442226d9fc8b89bc78511e4

The generated SQL will be like

((@__startSearch_0 >= CAST([s].[TextDate]) AS datetimeoffset))

Edit: To reiterate, using value converters is a "workaround" here. To see a full analysis of the options, I've written a full detailed analysis of the "work arounds" with the value converter hack here https://dasith.me/2022/01/23/ef-core-datetime-conversion-rabbit-hole/.

user_4224270
Related