EF6 Oracle TimeStamp &日期

2022-01-20 00:00:00 datetime oracle entity-framework-6

我开始将 EntityFramework 6 与 Oracle(客户端 12.x)一起使用.我有几个包含 DATE 列和 TIMESTAMP 列的表.

I'm starting to use EntityFramework 6 with Oracle (client 12.x). I have severals tables with DATE columns and TIMESTAMP columns.

EF6 生成的所有查询都将 .NET DateTime 类型转换为 TIMESTAMP.所以查询性能很差.

All queries generated by EF6 convert .NET DateTime type to TIMESTAMP. So the query performance is very poor.

我尝试将 DATETIME 列的精度添加到 0，但生成的查询没有任何变化.

I tried to add a precision to 0 for the DATETIME columns but nothing changes on the generated query.

Property(_ => _.MyDate).HasColumnName("DATE_COLUMN").HasPrecision(0);

如何在我的代码中指定通过 TO_DATE 函数而不是 TO_TIMESTAMP 函数来翻译 where 子句?

How can I specify in my code to translate my where clause by a TO_DATE function and not by a TO_TIMESTAMP function ?

推荐答案

我遇到了 DATE Oracle 类型和 EF6.1 的类似问题.我的解决方法是使用 CodeFirstFunctions 库(仅适用于 EF6.1 及更高版本)和在Oracle中先指定一个转换函数:

I've had a similar issue with DATE Oracle type and EF6.1. My workaround was to use the CodeFirstFunctions library (available only for EF6.1 and later) and specify a conversion function first in Oracle:

create or replace FUNCTION TO_DATE_LOCAL 
(
  DATETIMETOCONVERT IN VARCHAR2 
, CONVERTFORMAT IN VARCHAR2 
) RETURN DATE AS 
BEGIN
  RETURN to_date(DATETIMETOCONVERT, CONVERTFORMAT);
END TO_DATE_LOCAL;

稍后在我的 DbContext 中:

And later in my DbContext:

[DbFunction("CodeFirstDatabaseSchema", "TO_DATE_LOCAL")]
public static DateTime ToDateLocal(string dateTimeToConvert, string convertFormat)
{
    // no need to provide an implementation
    throw new NotSupportedException();
}

所以我可以强制实体框架在 where 条件下使用 DATE 类型:

So I can force Entity Framework to use DATE type in a where condition:

var measurement = 
  context.Measurements
    .Where(m => m.MeasuredAt == 
          PlantContext.ToDateLocal("2016.01.01 10:00:00", "YYYY.MM.DD Hh24:MI:SS"))
    .FirstOrDefault();

如果您使用 Oracle，请小心使用大写字母作为函数名称和带有 CodeFirstFunctions 的模式名称.

Be careful to use capital letters for the function name and for the schema name with CodeFirstFunctions if you use Oracle.

如果您需要更多详细信息，我已经写了一个 blog post 与示例项目有关.

If you need more details I've written a blog post about this with an example project.

相关文章