CODEWITHSUNDEEP
c#sql-serverlinqdateentity-framework-core
AFract
AFract

Reputation: 9700

Convert String to DateTime in LINQ query with Entity Framework Core

Using EF Core 5 and SQL Server, I am trying to figure how to convert a date stored as a string in LINQ query (so on SQL Server side).

I have searched in EF.Functions but I was unable to find the proper method for such date parsing.

I have also tried Convert.ToDateTime and DateTime.Parse, but they seem both to not have LINQ translations.

The direct SQL equivalent is Convert(datetime, mycolumn, 102). I would be really surprised if Microsoft did nothing to support something as simple and easy to use in native SQL, so I'm sorry if I miss something obvious, but I have searched on the web and did a few attempts before to surrender.

PS : please note that I am aware of workarounds like calling ToList() before to apply Where(), or to modify the DB (or create a view) to change the column type.

My question is mainly : how to call with EF Convert(datetime, mycolumn, 102)

Upvotes: 5

Views: 8033

Answers (2)

AFract
AFract

Reputation: 9700

Speaking about weird solutions, I have noticed that using

(DateTime)(object)t.Date

inside the query does the job.

For example :

dbContext.Table.Max(t => (DateTime)(object)t.Date);

Will work perfectly. I guess there's a little black magic behind this, and it will only work for most "direct" date formats and cultures conversions. I'll check later with SQL profiler or another solution what kind of SQL it emits.

Upvotes: 6

Ivan Stoev
Ivan Stoev

Reputation: 205799

You can relatively easily add the desired unsupported method using the EF Core scalar function mapping.

For instance, add the following class (with the necessary usings):

namespace Microsoft.EntityFrameworkCore
{
    public static class SqlFunctions
    {
        public static DateTime? ToDateTime(this string s, int format) => throw new NotSupportedException();

        public static ModelBuilder AddSqlFunctions(this ModelBuilder modelBuilder)
        {
            modelBuilder.HasDbFunction(() => ToDateTime(default, default))
                .HasTranslation(args => new SqlFunctionExpression(
                    functionName: "CONVERT",
                    arguments: args.Prepend(new SqlFragmentExpression("date")),
                    nullable: true,
                    argumentsPropagateNullability: new[] { false, true, false }),
                    type: typeof(DateTime),
                    typeMapping: null));

            return modelBuilder;
        }
    }
}

The second method is for convenience and does the actual mapping.

Now all you need is to call it from OnModelCreating override:

if (Database.IsSqlServer()) modelBuilder.AddSqlFunctions();

and then use it inside the LINQ to Entities query:

var query = db.Set<MyEntity>()
    .Where(e => e.MyProp.ToDateTime(102) > DateTime.Today
    .ToQueryString();
// SELECT ..... WHERE Convert(date, [e].[MyProp], 102) > CONVERT(date, GETDATE())

Upvotes: 16

Related Questions