Wednesday, 14 November 2012

Entity Framework Fun

I had an interesting problem today with EntityFramework. (I'm using the EF 5.x DbContext Generator for my POCOs, but I doubt it matters which POCO generator you use).

I have a SQL view that has some calculated columns, like this.

(X * Y) As MyCol

where both X and Y are decimal types.

I have been using the view for a while just fine, but then today it stopped working.

The error I was getting was:

"The property on ... could not be set to a 'Decimal' value. You must set this property to a non-null value of type 'Double'."

I thought this was odd, seeing as the code is auto-generated.

I subsequently discovered that the underlying data types in the tables used in the view (and the view below it) had been extended to increase accuracy, from decimal(18,2) to decimal (30,15). It then dawned on me that because the column datatype is not actually specified in the view but rather inferred from a calculated column, the auto-generated code might not really know the correct data type to use, and sure enough it was generating a nullable double not a nullable decimal.

The problem was fixed by going to the view and changing the column output to a known type, i.e.

cast((X * Y) as decimal(30,15)) As MyCol

I then removed everything from the .edmx and re-added it to re-generate the code.

The auto-generated code then picked up the correct data type (decimal instead of double) and all is well.

No comments:

Post a Comment