EF6 Oracle时间戳&日期 [英] EF6 Oracle TimeStamp & Date
问题描述
我开始将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();
}
因此,我可以强制Entity Framework在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.
如果您需要更多详细信息,我已经写了一个博客文章并带有示例项目.
If you need more details I've written a blog post about this with an example project.
这篇关于EF6 Oracle时间戳&日期的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!