本文介绍了使用 Entity Framework Core 在 LINQ 查询中将字符串转换为日期时间的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!

问题描述

使用 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.ToDateTimeDateTime.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 查询中将字符串转换为日期时间的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!

08-15 15:20
查看更多