Jun 21, 2016

In a previous blog post we compared the different approaches to create a Data Access Layer to MVC applications using the Entity Framework. In particular we went into some of the details of how to use the Code-First approach. In today’s blog we move one step further and show you what to do when there is a mismatch between the schema of the database and the classes you are using to model the business entities.

Entity Framework Code-First uses conventions to name tables and columns and to identity which property should be used as a primary key. But in many scenarios you already have a database and you don’t want to use the same naming conventions of the database schema with your entity classes.

One approach to solve this mismatch problem is to use Data Annotations as we show next.

Start by adding the following namespaces to your class definition:

using System.ComponentModel.DataAnnotations;
using System.ComponentModel.DataAnnotations.Schema;

Now let’s say you want to map a class with the name Customer with a table named tblCustomers2015. By default EF would expect a table with the name Customers, the plural form of your class name. In this case you have to use the Table annotation as follows

[Table(“tblCustomers2015”)]
public class Customer
{ … }

For the primary key EF expects a match to ID or CustomerID. It’s not case sensitive. If there is a mismatch you just use the Key attribute.

[Table(“tblCustomers2015”)]
public class Customer
{
   [Key]
   public string AccountNumber { get; set; }
  …
}

In case of a composite key you can apply the Key attribute to more than one column, but in this case you also need to acknowledge the order of the key properties.

[Table(“tblCustomers2015”)]
public class Customer
{
   [Key]
   [Column(Order = 1)]
   public string TerritoryId { get; set; }
   [Key]
   [Column(Order = 2)]
   public string AccountNumber { get; set; }
  … }

There are a few more data annotations which are more targeted to the scenario where you don’t have a database and EF will create one for you with the necessary customisations.

To learn more about using the EF in MVC applications we recommend the course 20486 - Developing ASP.NET MVC 4 Web Applications.