假设我们有一个表T,它有两列A和B,分别具有浮点数和货币类型.我想编写一个类似以下T-SQL语句的linq查询:
Suppose we have table T which has two columns A and B with float and money types respectively. I want to write a linq query like following T-SQL statement:
Select A, B, A * B as C From SomeTable Where C < 1000我试图像下面那样投射
var list = (from row in model.Table where ((decimal)row.A) * row.B < 1000 select new { A = row.A, B = row.B , C = ((decimal)row.A) * row.B} ).ToList();,但不允许进行强制转换操作.引发异常:
but it does not allow the cast operation. It throw an exception:
在Linq to Entity查询中不支持将数据转换为十进制,因为 无法推断出所需的精度和比例信息.
Casting to Decimal is not supported in Linq to Entity queries, because the required precision and scale information cannot be inferred.
我的问题是如何在Linq中将double转换为十进制?我不想从数据库中获取数据. 更新: 我注意到将十进制转换为双精度,但是反向操作会引发异常.所以 为什么不能将double转换为十进制? sql server在t-sql中也执行相同的机制吗?会不会影响精度?
My question is how to convert double to decimal in Linq? I don't want to fetch data from database. Update: I notice the converting decimal to double works but reverse operation throws the exception. So, Why can't we convert double to decimal? Does Sql server do the same mechanism in t-sql too? Doesn't it affect precision?
推荐答案浮点数(双精度)和小数点之间的区别是浮点数精确到小数点.如果将float的值设置为10.123,则在内部可以将其值设置为10.1229999999999,该值非常接近10.123,但不完全相同.
The difference between a float (double) and a decimal, is that a float is decimal precise. If you give the float a value of 10.123, then internally it could have a value 10.1229999999999, which is very near to 10.123, but not exactly.
精度为x个小数的十进制始终是精确的,直到第x个小数.
A decimal with a precision of x decimals will always be accurate until the x-th decimal.
您的数据库设计人员认为A型不需要十进制精度(或者他只是粗心大意).使计算结果比输入参数更精确没有意义.
The designer of your database thought that type A didn't need decimal accuracy (or he was just careless). It is not meaningful to give the result of a calculation more precision than the input parameters.
如果您确实需要将结果转换为小数,则将公式计算为float/double,并在AsEnumerable之后转换为小数:
If you really need to convert your result into a decimal, calculate your formula as float / double, and cast to decimal after AsEnumerable:
(我对您的语法不太熟悉,所以我将使用扩展方法的语法)
(I'm not very familiar with your syntax, so I'll use the extension method syntax)
var list = model.Table.Where(row => row.A * row.B < 1000) .Select(row => new { A = row.A, B = row.B, }) .AsEnumerable() .Select(row => new { A = row.A, B = row.B, C = (decimal)row.A * (decimal)row.B, });含义:
- 从我的表格中,仅获取具有诸如row.A * row.B之类的值的行 < 1000.
- 从每个选定的行中,从A和B列中选择值.
- 将这两个值传输到本地内存(= AsEnumerable),
- 为每个传输的行创建一个具有三个属性的新对象:
-
A和B具有已传输的值.
- C得出转移的A和B的十进制值的乘积
- From my Table, take only rows that have values such that row.A * row.B < 1000.
- From each selected row, select the values from columns A and B.
- Transfer those two values to local memory (= AsEnumerable),
- for every transferred row create a new object with three properties:
- A and B have the transferred values.
- C gets the the product of the decimal values of transferred A and B
更多推荐
如何在Linq到Entity中从十进制转换为双精度
发布评论