How to convert from decimal to double in Linq to Entity

Question

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?

`It does not allow cast operation` <= what is the Exception and its details? Or is it a compile time error? — Igor, Jan 24 '17 at 13:07
Is the error similar to this? http://stackoverflow.com/questions/41802366/get-result-function-in-linq-without-translate-to-store-expression/41802414#41802414 — Salah Akbari, Jan 24 '17 at 13:11
Casting to Decimal is not supported in Linq to Entity queries, because the required precision and scale information cannot be inferred. — Ali Fattahian, Jan 24 '17 at 13:13
Does it work if you cast the decimal to a double for the calculation? — Chris, Jan 24 '17 at 13:24
What do you mean by "I don't want to fetch data from database" ? — Magnus, Jan 24 '17 at 13:53
@Magnus I don't want to retrieve all data of the table. some guy suggested to use IEnumerable solution in other posts. so I dont want memory linq solutions. — Ali Fattahian, Jan 24 '17 at 13:58

Harald Coppoolse · Accepted Answer · 2017-01-24T14:37:22.707

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

"or he was just careless" That is true. Tnx. – Ali Fattahian Jan 24 '17 at 14:09 — Ali Fattahian, Jan 24 '17 at 14:09
I convert decimal to double for sake of efficiency. – Ali Fattahian Jan 24 '17 at 14:13 — Ali Fattahian, Jan 24 '17 at 14:13

score 0 · Answer 2 · answered Apr 26 '17 at 14:21

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();

How to convert from decimal to double in Linq to Entity

2 Answers2

Linked