Bug #65127 EF: Decimal type should have digits at right of decimal point
Submitted: 27 Apr 2012 6:20 Modified: 28 Sep 2012 18:53
Reporter: Laurent Sibilla Email Updates:
Status: Closed Impact on me:
None 
Category:Connector / NET Severity:S3 (Non-critical)
Version:6.5.4 OS:Windows
Assigned to: Fernando Gonzalez.Sanchez CPU Architecture:Any
Tags: decimal, entity framework

[27 Apr 2012 6:20] Laurent Sibilla
Description:
Using entity decimal type and CreateDatabase function, the values are stored with 0 digits at the right of the decimal point. Here is the script generated :
CREATE TABLE `Entity1Set`(
	`Id` int NOT NULL AUTO_INCREMENT PRIMARY KEY, 
	`Value` decimal NOT NULL);

How to repeat:
Open the solution here attached and run it.

Suggested fix:
I suggest to have default precision that would be suitable for lots of needs like decimal(12, 4).

In ProviderService.cs, add these two lines in GetColumnType:
    private string GetColumnType(TypeUsage type)
    {
      string t = type.EdmType.Name;
      if (t.StartsWith("u", StringComparison.OrdinalIgnoreCase))
      {
        t = t.Substring(1).ToUpperInvariant() + " UNSIGNED";
      }
      else if (String.Compare(t, "guid", true) == 0)
          return "CHAR(36) BINARY";
+     else if (String.Compare(t, "decimal", true) == 0)
+         return "DECIMAL(12, 4)";
      return t;
    }
[27 Apr 2012 6:22] Valeriy Kravchuk
Thank you for the problem report. What exact Connector/Net version,m x.y.z, do you use?
[27 Apr 2012 6:23] Laurent Sibilla
Example project showing the issue

Attachment: ConsoleApplication1.zip (application/zip, text), 5.91 KiB.

[27 Apr 2012 6:24] Laurent Sibilla
Here is the version number. May has it been solved in a newer version ?
[21 May 2012 10:55] Bogdan Degtyariov
Thanks for providing the test project.
The problem is verified.

The output trace shows the Decimal column without precision:

-- Created on 21/05/2012 8:50:45 PM
CREATE TABLE `Entity1Set`(
	`Id` int NOT NULL AUTO_INCREMENT PRIMARY KEY, 
	`Value` decimal NOT NULL);

I tried setting precision in the entity properties and even editing 
Model1.edmx as xml to add:

<Property Type="Decimal" Name="Value" Nullable="false" Precision="10" Scale="5" />

Unfortunately, the column declaration remained the same.
[28 Sep 2012 18:53] John Russell
Added to changelog for 6.4.6, 6.5.5, 6.6.3: 

Using the Entity Data Model Designer decimal type and CreateDatabase
function, the values were stored with 0 digits at the right of the
decimal point. With this fix, the default is 2 digits to the right of
the decimal point, and any precision specified through the Entity
Data Model Designer is applied correctly.