如何从小数点转换为LINQ到实体的双倍[英] How to convert from decimal to double in Linq to Entity

问题描述

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

I tried to cast like following

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:

Casting to Decimal is not supported in Linq to Entity queries, because the required precision and scale information cannot be inferred.

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?

推荐答案

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.

A decimal with a precision of x decimals will always be accurate until the x-th decimal.

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.

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,
    });

Meaning:

• 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

其他推荐答案

You can avoid AsEnumerable() explaining to Entity how many fractional digits you want.

var list = (from row in model.Table 
            where ((decimal)row.A) * row.B < 1000 
            select new { A = row.A,
                         B = row.B ,
                         C = (((decimal)((int)row.A)*100))/100) * row.B}
           ).ToList();