none
Decimal column and Entity Framework RRS feed

  • Question

  • Hi everybody,

    In our application database many PK/FK columns are defined as decimal(17,0) - this application has a long history and originally database was in Visual FoxPro and then migrated to SQL Server, but the PK type never adjusted to use BIGINT instead (which should have been better).

    We're using Entity Framework application in our project which is converting old Visual FoxPro application into ASP.NET MVC /Entity Framework / AngularJS application.

    Anyway, I'm getting Arithmetic overflow error executing this line of code:

    var spLog = new SpLog()
                {
                    LogNo =   _sequenceAdapter.GeneratePrimaryKey("K_SP_LOG", 1),
                    ActivType = eventType.ActivType,
                    TagLine = FormatTagLine(eventId, eventType.TagLine, opcode),
                    Details = (operatorSession != null) ? operatorSession.UserHostAddress : userHostAddress,
                    Sourceapp = "SysManager",
                    //In cases where the operator sessions haven't been set yet because the operator hasn't logged in, 
                    //We can only assume the operator performing the action is the one attempting login events
                    Operator = (operatorSession != null) ? operatorSession.OpCode : opcode,
                    Salespoint = SysManagerConstants.SYSMANAGER_SALESPOINT_ID.ToString("D3"),
                    DateTime = DateTime.Now //TODO DateTime.Now or UTC? 
                };
    
                _spLogRepository.Add(spLog);

    which results in the following SQL command:

    exec sp_executesql N'INSERT [dbo].[sp_log]([log_no], [activ_type], [tag_line], [details], [sourceapp], [operator], [salespoint], [date_time])
    VALUES (@0, @1, @2, @3, @4, @5, @6, @7)
    ',N'@0 decimal(18,2),@1 int,@2 varchar(120),@3 varchar(max) ,@4 varchar(20),@5 char(6),@6 char(6),@7 datetime2(7)',@0=3438000001.00,@1=2110,@2='Login successful for ADMIN',@3='::1',@4='SysManager',@5='ADMIN ',@6='000   ',@7='2016-10-06 14:57:07.4041125'
    

    As you can see, for some reason EF passes first parameter as decimal(18,2) and the datetime parameter as datetime2(7) - this problem has been discussed here recently.

    The value I am passing for log_no is 

    select cast(3438000001.00 as  decimal(17,0))

    Direct cast to decimal(17,0) works fine, but cast to decimal(18,2) and then to decimal(17,0) results in the above error.

    The question is - is there any way to make sure the SaveChanges method of the Entity Framework correctly uses the actual parameter type instead of somehow using decimal(18,2)? 

    The LogNo property is defined as decimal in the Model and our models are auto-generated using Reverse POCO Generator, so I am not sure if there is any way to specify correct type using fluent API. Perhaps we should somehow map decimal(17,0) to bigint in our fluent API, I may need to also consult Simon Hudges if there is some way to make sure the correct parameter type is generated.

    Thanks in advance.


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog


    My TechNet articles

    Thursday, October 6, 2016 12:26 PM

Answers

  • Hi Naomi  N,

    >>so I suppose there is a way to set precision using Fluent API. I am wondering why it's not done?

    Yes, we need to use Fluent API like this:

     modelBuilder.Entity<SpLog>()
                    .Property(e => e.LogNo)
                    .HasPrecision(17, 0);

    >> DateTime = DateTime.Now //TODO DateTime.Now or UTC?

    DateTime.UtcNow tells you the date and time as it would be in Coordinated Universal Time, which is also called the Greenwich Mean Time time zone - basically like it would be if you were in London England, but not during the summer. DateTime.Now gives the date and time as it would appear to someone in your current locale.

    I'd recommend using DateTime.Now whenever you're displaying a date to a human being.

    Best regards,

    Cole Wu


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    • Marked as answer by Naomi N Friday, October 7, 2016 11:21 AM
    Friday, October 7, 2016 9:27 AM
    Moderator

All replies

  • I found this question

    http://stackoverflow.com/questions/9032919/set-decimal16-3-for-a-column-in-code-first-approach-in-ef4-3

    so I suppose there is a way to set precision using Fluent API. I am wondering why it's not done?


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog


    My TechNet articles

    Thursday, October 6, 2016 1:01 PM
  • What is strange is that I suddenly started to get this error. Yesterday I also updated SSDT to the latest version. I also run DB update and set compatibility level of DB to 2014 (it was 2008 before).

    I'll try restoring DB to the latest backup and re-try.

    How did it work before, I wonder? What could have changed to now cause this problem?

    I can try messing with the data to by-pass this error for now.

    Thanks in advance.


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog


    My TechNet articles

    Thursday, October 6, 2016 4:31 PM
  • Hi Naomi  N,

    >>so I suppose there is a way to set precision using Fluent API. I am wondering why it's not done?

    Yes, we need to use Fluent API like this:

     modelBuilder.Entity<SpLog>()
                    .Property(e => e.LogNo)
                    .HasPrecision(17, 0);

    >> DateTime = DateTime.Now //TODO DateTime.Now or UTC?

    DateTime.UtcNow tells you the date and time as it would be in Coordinated Universal Time, which is also called the Greenwich Mean Time time zone - basically like it would be if you were in London England, but not during the summer. DateTime.Now gives the date and time as it would appear to someone in your current locale.

    I'd recommend using DateTime.Now whenever you're displaying a date to a human being.

    Best regards,

    Cole Wu


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    • Marked as answer by Naomi N Friday, October 7, 2016 11:21 AM
    Friday, October 7, 2016 9:27 AM
    Moderator
  • Thanks, Cole!

    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog


    My TechNet articles

    Friday, October 7, 2016 11:21 AM