开发者

Entity Framework / SQL Server strange decimal division behaviour

I have a table in my SQL server 2008 R2 database which includes two nullable decimal(16,6) columns. Let's call them column1 and column2.

When I try to run a Linq query against the entity generated from this table:

Table.Select(r => new Foo
                  {
                     Bar = (r.Column1 + r.Column2) / 2m
                  }
            );

I get a System.OverflowException if column1 + column2 >= 15846. The message of the exception is only:

Conversion overflows.

With a bit of trial and error I've managed to make the query work with the following:

Table.Select(r => new Foo
                  {
                     Bar = (r.Column1 + r.Column2).HasValue ? 
                         (r.Column1 + r.Column2).Value / 2m : 0
                  }
            ); 

However, I was wondering if anyone could explain what was going wrong with the initial query.


Edit

The first query generates this SQL:

SELECT 
1 AS [C1], 
([Extent1].[Column1] + [Extent1].[Column2]) / cast(2 as decimal(18)) AS [C2]
FROM [dbo].[Tab开发者_如何学JAVAle] AS [Extent1]

With a value of 10000 for both columns, running the query manually in SSMS the result is 10000.0000000000000000000000000 (25 decimal zeros).

The second query has this SQL:

SELECT 
1 AS [C1], 
CASE WHEN ([Extent1].[Column1] + [Extent1].[Column2] IS NOT NULL)
     THEN ([Extent1].[Column1] + [Extent1].[Column2]) / cast(2 as decimal(18))
     ELSE cast(0 as decimal(18))
     END AS [C2]
FROM [dbo].[Table] AS [Extent1]

Running the query in SSMS returns 10000.00000000000000000000 (20 decimal zeros). Apparently there is a problem when EF tries to convert the first value (with 25 decimal zeros) into a decimal but with the second (with 20 decimal zeros) it works.

In the meantime it turned out that the problem also occurs with non-nullable columns and even a single decimal(16, 6) column. The following ...

Table.Select(r => new Foo
                  {
                     Bar = r.Column1 / 2m
                  }
            );

... throws the same conversion exception (with a value of 20000 in the Column1).

  • Why do those two SQL queries result in two different numbers of digits?
  • And why can't the first number be converted into a decimal by EF?
0

上一篇:

下一篇:

精彩评论

暂无评论...
验证码 换一张
取 消

最新问答

问答排行榜