Convert.ToDateTime not working

[TanielianVB]

Hi,
With the following table:

CREATE TABLE public."Values"
(
    "Guid" bigint NOT NULL,
    "RawValue" text COLLATE pg_catalog."default",
    CONSTRAINT "Values_pkey" PRIMARY KEY ("Guid")
)

The following sample:

    public class EFCoreTestContext : DbContext
    {
        public DbSet<Value> Values { get; set; }

        public static readonly ILoggerFactory DebugLoggerFactory = LoggerFactory.Create(builder => { builder.AddFilter((category, level) => category == DbLoggerCategory.Database.Command.Name && level == LogLevel.Information).AddConsole(); });

        protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder) => optionsBuilder
            .UseLoggerFactory(DebugLoggerFactory)
            .UseNpgsql("Host=localhost;Database=EFCoreTest;Username=test;Password=test");

        protected override void OnModelCreating(ModelBuilder modelBuilder)
        {
            modelBuilder
                .Entity<Value>(eb =>
                {
                    eb.ToTable("Values");
                    eb.HasKey(e => e.Guid);
                });
        }
    }

    public class Value
    {
        public long Guid { get; set; }

        public string RawValue { get; set; }
    }

    class Program
    {
        static void Main(string[] args)
        {
            using (var db = new EFCoreTestContext())
            {
                var r = (from e in db.Values
                         select new
                         {
                             Date = Convert.ToDateTime(e.RawValue)
                         })
                         .OrderBy(e => e.Date)
                         .ToList();
            }

            Console.ReadKey();
        }
    }

I'm getting this error:

System.InvalidOperationException: The LINQ expression 'DbSet
.OrderBy(v => Convert.ToDateTime(v.RawValue))' 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 was expecting this kind of query translated:

SELECT v."Guid", cast (v."RawValue" as timestamp with time zone) AS "Date"
FROM "Values" AS v
ORDER BY v."RawValue"

The values stored on the table in my case are something like this when they are DateTimes: "2020-01-21T14:33:29-03:00"
I searched for some cast method like the EF.Functions.ILike method but had no luck.

Thanks in advance,

[roji]

This is about expressing conversion from text to DateTime. We generally support Convert.ToInt32, ToDouble because those conversions are straightforward and unambiguous, whereas string->DateTime conversion depends on formatting (which will be different between .NET and the DB). However, it may make sense to provide such a translation, making it clear that it will happen in whatever way the database chooses to do it.

/cc @ajcvickers @smitpatel has there been a conversation about this, is there a tracking issue?

[TanielianVB]

So? I can start working on a PR as soon as a decision is taken.

[roji]

@ajcvickers @smitpatel I'm not sure anymore... have we decided anything on this?

[ajcvickers]

@roji I think we said that we would generate simple casts in the database, but would not try to be specific about how the conversions worked. However, I'm not sure either.

[smitpatel]

We had discussions about translating ToString method on object which would be whatever string representation of the value in database is.

[roji]

After discussion with the EF Core team, we think it's a good idea to add this. This would ideally first be submitted for the SQL Server provider (this is the relevant translator, and then submitted here for PostgreSQL (the files are virtually identical).

[TanielianVB]

Nice! Thanks for the reply.

[HesamKashefi]

Is there any updates on this?
I'm Trying to compare (>=) a constant DateTime to filter a column that has DateTime stored as string in the database but I get

System.InvalidOperationException: The LINQ expression 'DbSet<CustomValue>()
    .Where(c => c.CustomFieldId == __8__locals7_customFieldId_0 && c.Value != null && c.Value != "" && Convert.ToDateTime(c) >= __value_1)' 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 'AsEnumerable', 'AsAsyncEnumerable', 'ToList', or 'ToListAsync'. See https://go.microsoft.com/fwlink/?linkid=2101038 for more information.
   at Microsoft.EntityFrameworkCore.Query.QueryableMethodTranslatingExpressionVisitor.Translate(Expression expression)
   at Microsoft.EntityFrameworkCore.Query.RelationalQueryableMethodTranslatingExpressionVisitor.Translate(Expression expression)
   at Microsoft.EntityFrameworkCore.Query.QueryCompilationContext.CreateQueryExecutor[TResult](Expression query)
   at Microsoft.EntityFrameworkCore.Storage.Database.CompileQuery[TResult](Expression query, Boolean async)
   at Microsoft.EntityFrameworkCore.Query.Internal.QueryCompiler.CompileQueryCore[TResult](IDatabase database, Expression query, IModel model, Boolean async)
   at Microsoft.EntityFrameworkCore.Query.Internal.QueryCompiler.<>c__DisplayClass12_0`1.<ExecuteAsync>b__0()
   at Microsoft.EntityFrameworkCore.Query.Internal.CompiledQueryCache.GetOrAddQuery[TResult](Object cacheKey, Func`1 compiler)
   at Microsoft.EntityFrameworkCore.Query.Internal.QueryCompiler.ExecuteAsync[TResult](Expression query, CancellationToken cancellationToken)
   at Microsoft.EntityFrameworkCore.Query.Internal.EntityQueryProvider.ExecuteAsync[TResult](Expression expression, CancellationToken cancellationToken)
   at Microsoft.EntityFrameworkCore.Query.Internal.EntityQueryable`1.GetAsyncEnumerator(CancellationToken cancellationToken)
   at System.Runtime.CompilerServices.ConfiguredCancelableAsyncEnumerable`1.GetAsyncEnumerator()
   at Microsoft.EntityFrameworkCore.EntityFrameworkQueryableExtensions.ToListAsync[TSource](IQueryable`1 source, CancellationToken cancellationToken)
   at Microsoft.EntityFrameworkCore.EntityFrameworkQueryableExtensions.ToArrayAsync[TSource](IQueryable`1 source, CancellationToken cancellationToken)