实体框架核心3.1-Oracle“指定的转换无效" [英] Entity Framework Core 3.1 - Oracle “Specified cast is not valid”

问题描述

在我的.NET Core 3.1应用程序中，我使用linq使用以下语句从视图中获取条目列表:

In my .NET Core 3.1 application I am using linq to get a list of entries from a view using the following statement:

List<VEntries> entryList = dbContext.VEntries.Where(x => x.Timestamp.HasValue && 
                                            x.Timestamp.Value.Date == dateTimeLocal.Date && 
                                            x.Timestamp.Value.Hour == dateTimeLocal.Hour)
                                .ToList();

dateTimeLocal 在这种情况下等于在本地时间搜索条目的时间戳.

dateTimeLocal in this case equals the timestamp to search entries for in local time.

此代码在我的本地环境中可以正常工作，但在生产系统上会引发以下异常:

This code works fine in my local environment but on the production system it throws the following exception:

System.InvalidCastException:指定的转换无效.

System.InvalidCastException: Specified cast is not valid.

在Oracle.ManagedDataAccess.Client.OracleDataReader.GetDecimal(Int32 i)
at lambda_method(Closure，QueryContext，DbDataReader，ResultContext，Int32 []，ResultCoordinator)
在Microsoft.EntityFrameworkCore.Query.Internal.QueryingEnumerable 1.Enumerator.MoveNext()在System.Collections.Generic.List 1..ctor(IEnumerable 1 collection)在System.Linq.Enumerable.ToList [TSource](IEnumerable 1源)

at Oracle.ManagedDataAccess.Client.OracleDataReader.GetDecimal(Int32 i)
at lambda_method(Closure , QueryContext , DbDataReader , ResultContext , Int32[] , ResultCoordinator )
at Microsoft.EntityFrameworkCore.Query.Internal.QueryingEnumerable1.Enumerator.MoveNext() at System.Collections.Generic.List1..ctor(IEnumerable1 collection) at System.Linq.Enumerable.ToList[TSource](IEnumerable1 source)

两个系统上的数据库视图相同，并且包含类型为 DATE 的列 Timestamp ，该列可以为空:

The database view is identical on both systems and contains a column Timestamp of type DATE which is nullable:

生成的模型如下:

public partial class VEntries
{
    public string QualityData { get; set; }
    public decimal? Value { get; set; }
    public DateTime? Timestamp { get; set; }
    public decimal? Average { get; set; }
    public decimal? Min { get; set; }
    public decimal? Max { get; set; }
}

这里可能是什么问题?

推荐答案

根据您发布的错误，我认为该错误与 DateTime 字段无关，但与 decimal代替:

According to the error you posted, I believe the error is not related to the DateTime field, but with the decimal ones instead:

在Oracle.ManagedDataAccess.Client.OracleDataReader.GetDecimal(Int32 i)

Oracle的 NUMBER 范围大于C#十进制，因此它可能无法将生产数据库中读取的一个非常大的数字转换为您的应用程序.我敢打赌，本地运行正常是因为在您的本地数据库中，没有那么多数字.

Oracle's NUMBER range is bigger than C# decimal, so it is probably not managing to convert one very large number read in the production database into your application. I bet that locally is working because in your local database you don't have any number as big.

可能的解决方案:

1. 在生产数据库中截断或舍入小数(如果可以的话).
2. 如果不可能采用第一种方法，则可以尝试为 VEntries 中的十进制值添加 ValueConverter ，以将 double 转换为十进制值(精度较低，但范围较大)为十进制(精度较高，范围较小)，反之亦然.

1. Truncate or round decimals in your production database (if it is an option).
2. If the first alternative isn't possible, you could try adding a ValueConverter for your decimal values in VEntries that converts from a double (less precision but bigger range) to decimal(more precision, less range) and vice-versa.

我还没有尝试过，但是应该看起来像这样:

I haven't tried it, but should look something like this:

protected override void OnModelCreating(ModelBuilder modelBuilder)
{
    modelBuilder
        .Entity<VEntries>()
        .Property(e => e. Value)
        .HasConversion(
            decimalValue => (double)decimalValue,
            doubleValue => (decimal)doubleValue);
       // Repeat for all decimal values
}

这篇关于实体框架核心3.1-Oracle“指定的转换无效"的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！