none
Mapping decimal(13,0) problem

    Question

  • Hi,

    I'm migrating the aplication of my company (that nowadays run over SQL Server and Oracle) to ASP NET MVC and Entity Framework for persistence.

    A create my Entity Model based on SQL Server Database e separately I create a SSDL for Oracle (for Oracle I use DevArt dotConnect for Oracle Provider) and I get some pain troubles.

    My table primary keys are on SQL Server are of type decimal(13,0) and on Oracle are number(13,0) but Oracle map it's type to Int64 and SQL Server to decimal, but I need that SQL Server map it to Int64.

    I make these modification manually on Entity Data Model and for create records it's works fine, but when I have to delete or update some record I got these error:

    The specified value is not an instance of type 'Edm.Decimal'
    Parameter name: value

       at System.Data.Common.CommandTrees.DbConstantExpression..ctor(DbCommandTree commandTree, Object value, TypeUsage constantType)
       at System.Data.Mapping.Update.Internal.UpdateCompiler.GenerateValueExpression(DbCommandTree commandTree, EdmProperty property, PropagatorResult value)
       at System.Data.Mapping.Update.Internal.UpdateCompiler.GenerateEqualityExpression(DbModificationCommandTree commandTree, EdmProperty property, PropagatorResult value)
       at System.Data.Mapping.Update.Internal.UpdateCompiler.BuildPredicate(DbModificationCommandTree commandTree, PropagatorResult referenceRow, PropagatorResult current, TableChangeProcessor processor, Boolean& rowMustBeTouched)
       at System.Data.Mapping.Update.Internal.UpdateCompiler.BuildDeleteCommand(PropagatorResult oldRow, TableChangeProcessor processor)
       at System.Data.Mapping.Update.Internal.TableChangeProcessor.CompileCommands(ChangeNode changeNode, UpdateCompiler compiler)

    Someone can help me?

    Why Entity Framework mapping are so fixed? It could be more flexible?

    Regards,

    Douglas Aguiar
    Wednesday, November 04, 2009 11:06 PM

Answers

  • Hi Douglas,

     

    I had a test on the latest version of EF, EF 4 CTP2, and Visual Studio 2010 Beta 2, such mapping is not supported.  Insert operations work fine but the regular queries fail for an exception indicating that we must set Int64 instead of Decimal to the certain property. 

     

    For the current design of EF, when EF retrieve the db data, it turns the results of DbDataReader into CLR objects.  During such process, EF calls DbDataReader.GetValue() which returns raw value returned by SQL Server.  The value is decimal so it gets converted to CLR type Decimal, but the expected data type is Int64.   Thus, unfortunately, I don’t think there is any easier way to handle this problem other than modifying the data type in SQL Server to bigint.  

     

    The product team is trying to make it support in the next release after EF 4.  

     

    Have a nice day!

     

     

    Best Regards,
    Lingzhi Sun

    MSDN Subscriber Support in Forum

    If you have any feedback on our support, please contact msdnmg@microsoft.com


    Please remember to mark the replies as answers if they help and unmark them if they provide no help.
    Welcome to the All-In-One Code Framework! If you have any feedback, please tell us.
    • Marked as answer by Douglas Aguiar Thursday, November 12, 2009 1:09 AM
    Wednesday, November 11, 2009 9:20 AM

All replies

  • Hi Douglas,

     

    Welcome to ADO.NET Entity Framework and LINQ to Entities forum!

     

    Different from LINQ to SQL type mapping, Entity Framework has a more precise and secure design on data type mapping.  Although we can set the data type of the certain PK property to Int64, the data type of the column are still in type of decimal in SSDL:

    ================================================================
    <Property Name="DecimalID" Type="decimal" Nullable="false" Precision="13" />
    ================================================================

     

    When EF generates the certain update commands, such convert (Int64 to decimal(13, 0)) is not allowed in the current version EF (.NET 3.5 SP1).   Even we modify the column type in SSDL to bigint to avoid the problem at the EF side, we will also receive some PK validation error at the SQL Server side. 

    ================================================================
    <Property Name="DecimalID" Type="bigint" Nullable="false" />
    ================================================================

    One workaround is to define a custom property to convert the type manually, like the subsequent codes:

    ================================================================
        public partial class DecimalMappingIntTest

        {

            public Int64 Int64ID

            {

                get

                {

                    return Convert.ToInt64(this.DecimalID);

                }

                set

                {

                    this.DecimalID = Convert.ToDecimal(value);

                }

            }

    }
    ================================================================

    If you have any questions, please feel free to tell me.

     

    Have a nice day!

     

     

    Best Regards,
    Lingzhi Sun

    MSDN Subscriber Support in Forum

    If you have any feedback on our support, please contact msdnmg@microsoft.com


    Please remember to mark the replies as answers if they help and unmark them if they provide no help.
    Welcome to the All-In-One Code Framework! If you have any feedback, please tell us.
    Thursday, November 05, 2009 2:00 AM
  • Lingzhi,

    Thanks for the anwser. But it doesn't solve my problem of database independence. Really has no way to force Entity to map too "diferent" types, since my decimal has precision 0, then technically this decimal is a integer number.

    Can I change this default mapping hacking something in Entity, like change his internally mapping of System.Decimal:Edm.Decimal?

    Ps.: The error that I got, I suspect that is because of a association.

    I have a Entity named Province and another named Country and I think that the association between these Entities are causing the problem at update and delete.

    Regards,
    Douglas Aguiar
    Thursday, November 05, 2009 10:15 AM
  • Hi Douglas,

     

    We cannot change such mapping in the current version of Entity Framework in .NET 3.5 SP1.  Also the exception is not just caused by the association, but by incorrect mapping between the types in EDM’s CSDL and SSDL.  Sorry, I made a mistake in my first post, if we modify the CSDL type to Int64 and SSDL type to bigint.  Then the insert operation is fine.  However, when we query the data, EF does not allow us to set Decimal value retrieved from the database to Int64 type at the client side. 

     

    Douglas, do you create one single EDM for two database (SQL Server and Oracle)?     Could you please provide us with more detailed information about your scenario?

     

    Have a nice weekend!

     

     

    Best Regards,
    Lingzhi Sun

    MSDN Subscriber Support in Forum

    If you have any feedback on our support, please contact msdnmg@microsoft.com


    Please remember to mark the replies as answers if they help and unmark them if they provide no help.
    Welcome to the All-In-One Code Framework! If you have any feedback, please tell us.
    Friday, November 06, 2009 2:38 PM
  • Hi Douglas,

     

    Thank you for providing the detailed information.  I will check the latest version of EF and consult the product team to see whether we have some workaround on such problem. 

     

    I will keep you informed if there is any updated messages.  Thanks for your patient!

     

    Have a great day!

     

     

    Best Regards,
    Lingzhi Sun

    MSDN Subscriber Support in Forum

    If you have any feedback on our support, please contact msdnmg@microsoft.com


    Please remember to mark the replies as answers if they help and unmark them if they provide no help.
    Welcome to the All-In-One Code Framework! If you have any feedback, please tell us.
    Tuesday, November 10, 2009 9:46 AM
  • Hi Douglas,

     

    I had a test on the latest version of EF, EF 4 CTP2, and Visual Studio 2010 Beta 2, such mapping is not supported.  Insert operations work fine but the regular queries fail for an exception indicating that we must set Int64 instead of Decimal to the certain property. 

     

    For the current design of EF, when EF retrieve the db data, it turns the results of DbDataReader into CLR objects.  During such process, EF calls DbDataReader.GetValue() which returns raw value returned by SQL Server.  The value is decimal so it gets converted to CLR type Decimal, but the expected data type is Int64.   Thus, unfortunately, I don’t think there is any easier way to handle this problem other than modifying the data type in SQL Server to bigint.  

     

    The product team is trying to make it support in the next release after EF 4.  

     

    Have a nice day!

     

     

    Best Regards,
    Lingzhi Sun

    MSDN Subscriber Support in Forum

    If you have any feedback on our support, please contact msdnmg@microsoft.com


    Please remember to mark the replies as answers if they help and unmark them if they provide no help.
    Welcome to the All-In-One Code Framework! If you have any feedback, please tell us.
    • Marked as answer by Douglas Aguiar Thursday, November 12, 2009 1:09 AM
    Wednesday, November 11, 2009 9:20 AM
  • Thank you Lingzhi,

    I hope that this kind of flexibility came in a future version of EF, it'll be good for enterprises that wan't to improve it's product but don't wan't to change de DB (that's my case, my DB is solid but my app is little old, and I wan't to rebuild the app with a solid framework ASP NET MVC and EF).

    I can't change my DB now, it has hundreds of tables (works for me) and I can got more troubles with other data types then I'll try to use Entity in a near future. For now I'll use NHibernate and I build a interface that I can easily change from NHibernate to EF (Because I'll use Linq to NHibernate).


    Very thank you.
    Best Regards,

    Douglas Aguiar
    Development Manager [at] Siteware
    MCAD, MCT
    Thursday, November 12, 2009 1:22 AM
  • Fala Galera,

    Tive esse problema e a solução foi instalar o ultimo .net framework no servidor.

    Trabalhando com ODP.NET provider para oracle, funcionava local mas não no servidor. O mesmo erro só que com o tipo "decimal". Espero que ajude.

    Hi People,

    I had this trouble and my solution was to install the .net framework 4.5 on the server.

    I was working with ODP.NET provider for oracle database and EF, it worked on local machine but not on the server. Hope it helps.

    Regards,

    André Luiz Sobreiro

    Monday, May 20, 2013 9:34 PM
  • Bom dia André,

    Aconteceu o mesmo problema comigo, o ambiente de desenvolvimento estava com o framework 4.5 e o problema não acontecia. No ambiente de produção estava com a versão 4.0 do framework .Net e ocorria o problema.

    Valeu pela dica!

    Good morning Andrew,

    The same problem happened to me, was the development environment with the framework 4.5 and the problem did not happen. In the production environment was with version 4.0. Net framework and the problem occurred.

    Thanks for the tip!

    Thursday, May 29, 2014 1:46 PM