Dec 01, 2015
In a previous blog post we have seen how to use the code-first approach in the Entity Framework. In today's post, we will see how to map the entities to database tables without following the implicit naming conventions or using class annotations.
Annotations are very easy to use but sometimes we find ourselves in a situation where we simply can't modify an existing object model for the entities and there is some mismatch between the properties of the entities and the corresponding columns in database tables. This applies to the case when the database already exists and also when it will be created, but the naming conventions for columns and properties are different. When facing this type of requirement you can make use of the Fluent API and solve the problem programmatically without touching the code of the entities or changing the database schema.
You start by overriding the OnModelCreating method of the DbContext class.
public SalesModel : DbContext { ... protected override void OnModelCreating (DbModelBuilder modelBuilder) { ... } }
This method will be automatically called by the Entity Framework and it will receive an instance of the DbModelBuilder that will allow us to fully customise the model building process to meet our requirements. The parameter modelBuilder is the entry point to use the Fluent API and get full control on the mapping between entity classes in the model and tables in the backend database.
Let's say that we have an entity class called Product but the backing table in the database is called tblProducts. In that case, we just add to the body of the OnModelCreating method the following,
modelBuilder.Entity().ToTable("tblProducts");
Now we can fix the mapping for each column that happens to have a non-matching property name in the class Product. For example, let's say that the property Name of the class Product maps to the column ProductName in the table tblProducts.
modelBuilder.Entity().Property(p => p.Name).HasColumnName("ProductName");The Entity Framework expects the property associated with the primary key to be called Id or ProductId. If that is not the case then the Fluent API again comes to the rescue and we simply add the following statement into the OnModelCreating method:
modelBuilder.Entity().HasKey(p => p.ProductCode);
We can also use the code-first approach to generate the database as an initialisation step. Even in this scenario it could make sense to add some statements to the OnModelCreating method to have some degree of control on how the schema will be generated. One example is the specification of a max length property of a table column. In the class definition, we don't have a direct way to say that the return type of a property is a string with a specific maximum length. We can just declare the return type to be a string and the Entity Framework will simply map that string to a varchar(max) in the created database. But if we do want it to be a varchar(50) for example, we can specify that as follows,
modelBuilder.Entity().Property(p => p.Description).HasMaxLength(50);
As another example, let's say we want a column to be declared as non-nullable in the database schema,
modelBuilder.Entity().Property(p => p.Category).IsRequired();
Any properties that are not included in the OnModelCreating will be implicitly created with the same name and a matching SQL data type. We could have properties that are calculated in the entity model and don't need a backing column in the database. In that case we can explicitly ask the Entity Framework to ignore it, as follows
modelBuilder.Entity().Ignore(p => p.TotalCost);
As you can see from the above examples the Fluent API allows for a high degree of customisation of the mapping between the entity classes and the backing database tables without requiring the addition of attributes to the entity classes.
How do your Excel skills stack up?
Test NowNext up:
- Embedding Organisational Values into Performance Management
- Hide Parts of a Page from Certain Users in SharePoint
- Combining different chart types in Excel 2013
- Intro and setup for Xbox SmartGlass
- My mate Stevo said it was good and Norm just couldn’t wait his turn!
- Difference between ByVal and ByRef in VBA
- Fiddly actions with Word tables
- Windows as a Service – The new Update Model
- A Recipe for Frame Fun with InDesign CC
- Word 2013 easy customisations
Previously
- Custom Data Validation Rules in Excel
- Using a Calendar on your Website
- Ever typed a date into Excel and it turned into a number?
- What is ITIL?
- The best features of Excel 2013 - Part 1
- Why effective leadership is like yogurt
- AngularJS Service - The Basics
- The Quick Brown Fox...
- Customise the navigation drop-down in SharePoint
- From Rivals to Friends … The rise of a new behemoth – The Red Microsoft Hat.