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

Answer 1

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 [e-mail]

---

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.

Answer 2

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

Answer 3

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 [e-mail]

---

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.

Answer 4

Lingzhi,

I have one single EDM and a separated SSDL file for Oracle.

See a example of part of my connection string for oracle and SQL Server:

<!-- SQL Server ConnectionString -->
<add name="PortalSimDBSql" connectionString="metadata=res://*/PortalSIMModel.csdl|res://*/PortalSIMModel.ssdl|res://*/PortalSIMModel.msl;provider=System.Data.SqlClient;provider connection string=&quot;Data Source=VMSWSERVER02;Initial Catalog=SiteAir_Desenv;Persist Security Info=True;User ID=swdev;Password=swdev;MultipleActiveResultSets=True&quot;" providerName="System.Data.EntityClient" />

<!-- Oracle ConnectionString -->
<add name="PortalSimDBOra" connectionString="metadata=res://*/PortalSIMModel.csdl|res://PortalSim.Dal/PortalSim.Dal.PortalSIMModelOra.ssdl|res://*/PortalSIMModel.msl;provider=Devart.Data.Oracle;provider connection string=&quot;User Id=SiteAir_Desenv;Password=siteair;Server=vmswserver03;Direct=true;Sid=ora10g;Persist Security Info=True&quot;" providerName="System.Data.EntityClient" />

Look the diference in oracle connection string, I make a reference for a another SSDL resource. With it I can make EF Database Independente, but I had some pain on type compatibilization. I have an existing database and I can't just change the types in DB.

Need more info?
In some later version of EF I could do that?

Regards,
Douglas Aguiar

Answer 5

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 [e-mail]

---

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.

Answer 6

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 [e-mail]

---

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.

Answer 7

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