none
Data Model that doesn't match the Database version of the Entity in EF --> Won't this create problems later on? RRS feed

  • Question

  • We have an existing database that we are re-designing an application based on using MVC, EF 5, Linq, etc.  Despite having an existing database, the team has chosen to use Code-First instead of Database-first implementation of the Entity Framework (version 5), because it gives you more control over the classes.

    Several of my team members have defined their decimal types differently that the database definition. For example, in one instance, we have a primary key in a table that is a decimal type, decimal(18,0), but in the data model, the team member defined it as decimal without specifying a precision. By default, entity framework then defines the column as Decimal(18,2) which means it adds precision of 2, which then causes a discrepancy between the model and database of this primary key field.

    Surprisingly, it seems to works fine, unless you use a context initializer class, and create the database from the model classes (as one normally would do in code-first method) of which it then throws an error when generating the database from the model because the the primary key contains a non-zero precision for primary key and it is not allowed. So, I have added the fluent API call to resolve the issue in the OnModelCreating method to change the primary key to a zero precision which then also matches the database precision of decimal(18,0) and then everything works fine (for example: ModelBuilder.Entity<Example>.Property(d => d.ExampleKey).HasPrecision(18,0). However, the team member feels it is unnecessary to include the change in the code, and does not want to use the fluent API whatsoever, only data annotations on the data model.  Furthermore, the team member feels it is unnecessary, because this team has chosen not to use the context initializer to generate the database, and instead works from an existing shared development server copy of the production database. However, I like to check/validate my work, and use a Context Initializer in a separate project to verify what is generated from the data models / data annotations match the database version, as I have concerns that a data model too loosely defined from the database version could create additional headaches later on. They are also defining nvarchar types where the database uses varchar.  Unless you specify otherwise with a data annotation on a data model string property that its type is varchar (For example: [Column(TypeName="varchar")],  it will create it as an nvarchar type by default.  

    I have more concerns with this, as without a good data model, I can’t use data validation annotations to validate the model in Entity framework if the decimal precision don’t match. I also can’t generate a local copy of the database from the model (without every time adding code to my local copy to fix the issue), which as personal preference is used as described above to check/verify my work. Lastly, both Linq to Entiity and Entity Framework rely on the data model to generate SQL syntax dynamically, so an improperly defined data model that doesn’t really match up to the database it updates against, couldn't that result in data precision, truncation, and conversion issues in the SQL that is generated?

    Two summarize, I have two basic questions:

    •  Good or Bad idea to have data model that does not match the database?  Decimal precision differences seem the most dangerous, but what about non-Unicode (varchar) vs. Unicode (nvarchar)?
    • Any other way to define a property's decimal scale/precision on data model without using the fluent API? I have seen a version where you create a custom data annotation but it still uses the fluent API in the custom data annotation to apply the precision. 

     

    Please share your opinions and recommendations. Thanks in advance. J


    • Edited by E.D.MARQUEZ Tuesday, March 18, 2014 9:37 PM
    Tuesday, March 18, 2014 9:15 PM

Answers

  • Hello,

    >>Good or Bad idea to have data model that does not match the database?

    I do not think it is good idea to let data model does not match the database.

    >> Any other way to define a property's decimal scale/precision on data model without using the fluent API? I have seen a version where you create a custom data annotation but it still uses the fluent API in the custom data annotation to apply the precision.

    I do not sure whether the version of custom data annotation you see is the same with that I found, it is:

    [AttributeUsage(AttributeTargets.Property, Inherited = false, AllowMultiple = false)]
    
        public sealed class DecimalPrecisionAttribute : Attribute
    
        {
    
            public DecimalPrecisionAttribute(byte precision, byte scale)
    
            {
    
                Precision = precision;
    
                Scale = scale; 
    
            }
    
            public byte Precision { get; set; }
    
            public byte Scale { get; set; }
    
    }
    

    It just needs us to declare it above the decimal property without using fluent API:

    [DecimalPrecision(20, 10)]
    
    public Nullable<decimal> DeliveryPrice { get; set; }
    

    This can successfully create a (18,2) decimal column.

    And if you want to use the Code First with an existing database, I think the Entity Framework Power Tools will be helpful, it will help us create the Code First model from the existing database automatically, if you want to change the database, we can using the Code First Migrtions.

    You can have a try.

    Regards.


    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.

    Thursday, March 20, 2014 2:01 AM
    Moderator