尽管已设置精度,但MSSQL表中的十进制数会四舍五入 [英] Decimal number in MSSQL table gets rounded although precision is set
问题描述
我的c#对象具有十进制属性:
My c# object has a decimal property:
public decimal LastPrice { get; set; }
在处理对象时,将设置十进制值.例如:
While processing my object, the decimal value gets set. For example:
LastPrice = 0.091354;
我修改了DbContext来提高十进制精度,如另一篇stackoverflow文章所述:
I modified my DbContext to increase the decimal precision as explained in another stackoverflow post:
protected override void OnModelCreating(ModelBuilder modelBuilder)
{
foreach (var property in modelBuilder.Model.GetEntityTypes()
.SelectMany(t => t.GetProperties())
.Where(p => p.ClrType == typeof(decimal) || p.ClrType == typeof(decimal?)))
{
property.SetColumnType("decimal(38, 10)");
}
}
Microsoft Sql Server Management Studio中的表设计视图反映了此配置.这是从SSMS编写的脚本表:
The tables design view in Microsoft Sql Server Management Studio reflects this configuration. Here is the table scripted from SSMS:
CREATE TABLE [dbo].[TradedCurrencyPairs](
[Id] [int] IDENTITY(1,1) NOT NULL,
[LastPrice] [decimal](38, 10) NOT NULL,
...
当我在调试过程中检查对象(将其添加到我的DbContext中)时,它看起来不错:
When I check the object during debugging as it gets added to my DbContext, it looks good:
但是在数据库中,最终结果为0.0000000000.
But in the database it ends up as being 0.0000000000.
据我所知,该值仍在四舍五入,就好像它的精度为2一样.应该为0.09232的值将变为0.0900000000.因此,所有小数点仍然会减少.
As far as I can tell, the value is still being rounded as if it would have a precision of 2. A value that should be 0.09232 becomes 0.0900000000. So all decimals still get cut.
我尝试了几种不同的数据注释:
I have tried several different Data Annotations:
// [PrecisionAndScale(20, 10)]
//[RegularExpression(@"^\d+\.\d{20,10}$")]
//[Range(0, 99999999999999999999.9999999999)]
[Range(typeof(decimal), "20", "10")]
但是他们没有帮助.从SSMS插入数据可以正常工作:插入交易货币对值('tzt','ttt','rrrr','20120618 10:34:09 AM','hgghghg',0.123456、1、0.123456789、0.123456、0.123456);去
but they didn't help. Inserting data from SSMS works fine: INSERT INTO TradedCurrencyPairs VALUES ('tzt', 'ttt', 'rrrr', '20120618 10:34:09 AM' , 'hgghghg', 0.123456, 1, 0.123456789, 0.123456, 0.123456); go
我的DbModelSnapshot列如下所示:
My DbModelSnapshot for the column looks like this:
b.Property<decimal>("LastPrice")
.HasPrecision(10)
.HasColumnType("decimal(20, 10)");
我也尝试过:
TradedCurrencyPair TestPair = new TradedCurrencyPair("one", "two", "Bibox", DateTime.Now, "unknown", 0.1234567890M, 1, 0.1234567890M, 0.1234567890M, 0.1234567890M);
context.TradedCurrencyPairs.Add(TestPair);
context.SaveChanges();
结果是一样的...
在设置该值与在数据库中结束之间的某个地方,它会被修改:/
Somewhere between setting the value and it ending up in the database, it gets modified :/
这是SQL表:
/****** Object: Table [dbo].[TradedCurrencyPairs] Script Date: 25/07/2020 09:32:32 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[TradedCurrencyPairs](
[Id] [int] IDENTITY(1,1) NOT NULL,
[TradedCurrency] [nvarchar](max) NULL,
[BaseCurrency] [nvarchar](max) NULL,
[Exchange] [nvarchar](max) NULL,
[DateTime] [datetime2](7) NOT NULL,
[TransactionType] [nvarchar](max) NULL,
[LastPrice] [decimal](20, 10) NOT NULL,
[ExchangeInternalPairId] [int] NOT NULL,
[High24h] [decimal](20, 10) NOT NULL,
[Low24h] [decimal](20, 10) NOT NULL,
[Volume24h] [decimal](20, 10) NOT NULL,
CONSTRAINT [PK_TradedCurrencyPairs] PRIMARY KEY CLUSTERED
(
[Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
不使用实体框架的工作方式:
SqlCommand command = new SqlCommand("insert into TradedCurrencyPairs values('one', 'two', 'Bibox', convert(datetime, '18-06-12 10:34:09 PM', 5), 'unknown', 0.1234567890, 1, 0.1234567890, 0.1234567890, 0.1234567890); ", cnn);
This way the decimals do not get modified. So EF causes the issue.
有人可以向我解释我做错了什么吗?
Could anybody please explain to me what I am doing wrong?
非常感谢!
推荐答案
当我从此处发布代码时:
When I posted the code from here:
我仍然习惯使用EF Core 3,因此我启用了EF Core 3的代码.我不记得我使用Beta软件包并将它们更新为EF Core 5预览.
I was still used to using EF Core 3, so I enabled the code for EF Core 3. I didn't remember I use beta packages and had them updated to EF Core 5 preview.
所以使用这个:
foreach (var property in modelBuilder.Model.GetEntityTypes()
.SelectMany(t => t.GetProperties())
.Where(p => p.ClrType == typeof(decimal) || p.ClrType == typeof(decimal?)))
{
// EF Core 3
// property.SetColumnType("decimal(20, 10)");
// property.SetPrecision(10);
// EF Core 5
property.SetPrecision(18);
property.SetScale(6);
}
代替此:
foreach (var property in modelBuilder.Model.GetEntityTypes()
.SelectMany(t => t.GetProperties())
.Where(p => p.ClrType == typeof(decimal) || p.ClrType == typeof(decimal?)))
{
EF Core 3
property.SetColumnType("decimal(20, 10)");
property.SetPrecision(10);
// EF Core 5
// property.SetPrecision(18);
// property.SetScale(6);
}
工作完美.
对不起,由于我的愚蠢错误,我浪费了您的时间:/
I'm sorry that I have wasted your time because of my stupid mistake :/
这篇关于尽管已设置精度,但MSSQL表中的十进制数会四舍五入的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!