6

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)

Improve this question
12
  • 5
    Why do you need to do the conversion in sql? What data type is the column in your db and the datatype you want in c#? Commented Aug 10, 2021 at 14:17
  • 4
    "or to modify the DB (or create a view) to fix the problem." I mean, if it hurts when you hit your head against the wall, don't put up a pillow. Here is the listing of what EF can convert to MS Sql Server. Commented Aug 10, 2021 at 14:24
  • 3
    Why aren't the dates stored in a date or datetime colum in the first place? Commented Aug 10, 2021 at 14:35
  • 3
    Storing dates in the database using the proper data type is not a "workaround" it is the correct solution. What you are trying to do is the workaround. If you store the dates properly (or even add a computed column to enforce integrity and give you a column to use with the correct type e.g. db<>fiddle) then your problem goes away, and you don't need to worry about a workaround here, or any other time you need to treat your date as an actual date rather than a string Commented Aug 10, 2021 at 14:37
  • 2
    The answer is "you can't because Microsoft doesn't support weird scenarios where you're using the DBMS incorrectly". Commented Aug 10, 2021 at 14:45

2 Answers 2

Reset to default
17

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())
Improve this answer
Sign up to request clarification or add additional context in comments.

4 Comments

Thank's for the example of custom sql function. I'm gonna use it if everybody confirms there's no built-in function for this. Despite the lack of a native solution, eventually, are you aware of a nuget package (possibly dedicated to sql server) that adds this kind of things to improve SQL Server features support in EFCore ? I had a look but did not found anything relevant. Thank you.
(1) There is no "out-of-the-box" solution so far. What you see in the link is what they support. In general, their system of extending EF.Functions with provider (database) specific methods is far from perfect, in case you target multiple databases. The integrated CLR mapping is better, but again each provider is allowed to support translation or not, so until runtime there is no way to know if the query will translate or not (2) linq2db package has better support for some things missing in EF Core like (recursive) CTEs and window functions, but probably not for db specific functions.
Can you please help me with this question:stackoverflow.com/questions/68734040/…
You can use the value converter (learn.microsoft.com/en-us/ef/core/modeling/value-conversions) as another option. This uses SQL Server CAST to convert string to dates though. So it uses the logins default language format. If you can't change the login default language format then you have to run SET LANGUAGE "British English" (or your desired language) if the date format isn't the default US MM/dd/yyyy format. See here for more learn.microsoft.com/en-us/sql/t-sql/statements/…
7

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.

Improve this answer

4 Comments

This is the equivalent of CAST(mycolumn AS DATE) which won't allow you to specify a style like CONVERT does. As long as your string will CAST to a date though this will work as expected
@GarethD this is what I was expecting to find. It works but clearly I would not trust it too much, because it's heavily dependent of Culture and so...
Yeah, what I call the "double cast trick" (you can find it in some other my posts here) is another (and more general) approach which translates to SqlServer default data type conversions, which in general might not be the format used when storing numeric/date/time data as text.
works with integers stored as text too, just FYI

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.