使用 Entity Framework Core 在 LINQ 查询中将字符串转换为日期时间 [英] Convert String to DateTime in LINQ query with Entity Framework Core

问题描述

使用 EF Core 5 和 SQL Server，我试图弄清楚如何转换在 LINQ 查询中存储为字符串的日期(在 SQL Server 端也是如此).

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).

我在 EF.Functions 中进行了搜索，但我无法找到进行此类日期解析的正确方法.

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

我也尝试过 Convert.ToDateTime 和 DateTime.Parse，但它们似乎都没有 LINQ 翻译.

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

直接的 SQL 等价物是 Convert(datetime, mycolumn, 102).如果微软没有做任何事情来支持像原生 SQL 这样简单易用的东西，我真的会很惊讶，所以如果我错过了一些明显的东西，我很抱歉，但我已经在网上搜索过，并在投降之前做了一些尝试.

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:请注意，我知道在应用 Where() 或修改数据库(或创建视图)以更改列类型之前调用 ToList() 等解决方法.

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.

我的问题主要是:如何用EF调用Convert(datetime, mycolumn, 102)

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

推荐答案

您可以使用 EF Core 标量函数映射相对轻松地添加所需的不受支持的方法.

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.

现在您只需要从 OnModelCreating 覆盖调用它:

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

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

然后在 LINQ to Entities 查询中使用它:

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())

这篇关于使用 Entity Framework Core 在 LINQ 查询中将字符串转换为日期时间的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！