Introduction
This article describes the effect of Entity Framework Code First convention and configuration for creating Primary Key column.
Entity Framework requires each entity to have a key. By using this key, Entity Framework keeps track of the changes that happens to that entity.
Listed below are three different ways to create primary key using Code First:
- Convention: Property with name "
Id" or property with name {class name} + "Id" (both are case insensitive) - Data Annotation: [Key] attribute
- Fluent API:
Entity<T>.HasKey(p => p.PropertyName) function
Using the Code
The examples given in this article are created by using Visual Studio 2013 and SQL Server 2008.
To begin with, let's create a Console Application.
- From Visual Studio, click File -> New -> Project. Alternatively, you can click the "New Project..." link from the Visual Studio Start Page (View -> Start Page). Either ways, it will bring the "New Project" window.
In the "New Project" window, from the installed templates, select "Visual C#". From the middle panel, which contains list of Visual C# templates, select "Console Application".
- Add "
EntityFramework" nuget package to the Console Application.
- If you want to install from Package Manager Console (View -> Other Windows -> Package Manager Console) use command:
Install-Package EntityFramework - You can install the same from the Manage Nuget Package GUI. Right click on the console project and select "Manage Nuget packages". It will bring the "Manage Nuget Packages" window. From the left side, select "Online" tab and search for "EntityFramework". Choose the correct package and click "Install".
- Add a folder called "Models" to the root of the Console Application. Add two class files, Student.cs and EducationContext.cs to the "Models" folder.
The EducationContext.cs file will act as the context object for the application which will keep track of all the changes that will happen to the entities. Inherit the EducationContext class from DbContext like follows:
public class EducationContext : DbContext
Create a parameterized constructor inside EducationContext class which takes the connections string name as the parameter. This step is used for pointing to the correct database from the "App.Config" file.
public EducationContext()
: base("EducationEntities")
{
}
You can replace base("EducationEntities") with base("name=EducationEntities") which does the same. It informs Code First to use the connection string where name = "EducationEntities" from the App.config file (as it is a Console Application).
Now add <connectionStrings> section to the App.config file as follows:
<connectionStrings>
<add connectionString="Server={ServeName}; Database=Education;
Integrated Security=SSPI" name="EducationEntities"
providerName="System.Data.SqlClient">
</add>
</connectionStrings>
For my case, the {ServerName} was DUKHABANDHU-PC(computer name) as I was using the Windows credentials to login to MS SQL Server in my PC. Please change it according to your system or server. If you are facing problems in setting up the connection strings, you can refer to this link and this link.
Here, I have given the database name as "Education". You can change it to anything you like.
- Add a property "
Id" of type int inside the Student.cs file.
public class Student
{
public int Id { get; set; }
}
The Students class will act as an entity for the EducationContext. So create a property of type DbSet<Student> inside the EducationContext.cs file as follows:
public class EducationContext : DbContext
{
public DbSet<Student> Students { get; set; }
}
- Modify the Program.cs file to drop and recreate the database everytime when the application runs. It will not be a problem here if we will drop and recreate the database as we are not going to save any important data.
static void Main(string[] args)
{
Database.SetInitializer(new DropCreateDatabaseAlways<EducationContext>());
using (var context = new EducationContext())
{
context.Students.Add(new Student());
context.SaveChanges();
}
Console.WriteLine("Database Created!!!");
Console.ReadKey();
}
Now run the application (From Visual Studio, click the "Start" button or press F5). In the SQL Server, you can see that there is database with name Education having two tables: _MigrationHistory and Students.
The _MigrationHistory table (previous EdmMetadata table upto EF 4.2) is used to keep track of all the changes that happen to the model. The Students table is created as we have created a property "Students" of type DbSet<Student> inside EducationContext class.
Points of Interest
1) Using Convention
The Code First primary key convention is: Property with name "Id" or {class name} + "Id" will act as the primary key for that entity.
a) In the Student.cs file, there is a single property "Id" of type int. It matches with the Code First convention for creating primary key. Hence, there is a "Id" column in the "Students" table which is the primary key. See the below screenshot:
The SQL for the Students table is:
CREATE TABLE [dbo].[Students](
[Id] [int] IDENTITY(1,1) NOT NULL,
CONSTRAINT [PK_dbo.Students] PRIMARY KEY CLUSTERED
(
[Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF,
IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
b) Let's remove the "Id" property from the Student.cs file and add another property called "StudentId" which matches the convention {class name} + "Id", i.e., "Student" + "Id". The Student class now looks like:
public class Student
{
public int StudentId { get; set; }
}
If you will run the application, it will create _MigrationHistory and Students tables where "StudentId" is the primary key of the Students table.
Sometimes, Code First fails to drop the database and generates an exception as follows. It generally happens if some process is using the SQL Server database and during that time, Code First tries to drop that database. If it happens, manually delete the database from the SQL Server.
SqlException:
Cannot drop database "Education" because it is currently in use.
To delete the database manually, right click on the database and then select the "Delete" option from the context menu. Alternatively, select the database and press Delete key. Either ways, it will bring up the "Delete Object" window. Check the "Close existing connection" checkbox from the bottom and click "OK" button.
Note
The convention for creating the primary key is case insensitive, i.e., if you will change the case of the properties to lowercase (e.g., studentid), uppercase (STUDENTID) or mixedcase (e.g., STuDentiD), then it will still treat that property as the primary key but the name of the column in database will be in property case. So if you will name the property like STudenTiD, then in Students table, it will be the primary key and the column name will be STudenTiD.
c) Let's modify the Student.cs file and add two properties, Id and StudentId as follows:
public class Student
{
public int Id { get; set; }
public int StudentId { get; set; }
}
Here, both Id and StudentId matches the convention for creating the primary key. Now run the application to see the effect in the database:
As shown in the below screenshot, it creates Students table with Id as the primary key of the table.
If you change the order of the properties, this will not change the primary key, i.e., still Id will be the primary key.
2) Using [Key] Data Annotation Attribute
a) The [Key] attribute is used for creating primary key. It takes priority over the convention.
Let's add two properties, Id and StudentId in Student.cs file and add [Key] attribute to StudentId property as follows:
public class Student
{
public int Id { get; set; }
[Key]
public int StudentId { get; set; }
}
If you run the application, it will create "Students" table with StudentId as the primary key. Here, data annotation attribute takes priority over the convention.
b) Composite Primary Key: Composite key is the combination of two or more columns which uniquely identifies a row in a table.
Let's add a new "Passport.cs" file to the console application with two properties, PassportNumber and CountryCode. Both PassportNumber and CountryCode uniquely identify a record in Passports table. The class looks like:
public class Passport
{
[Key]
public string PassportNumber { get; set; }
[Key]
public string CountryCode { get; set; }
}
Add another property "Passports" of type DbSet<Passport> inside the EducationContext.cs file as follows:
public class EducationContext : DbContext
{
public DbSet<Student> Students { get; set; }
public DbSet<Passport> Passports { get; set; }
public EducationContext()
: base("EducationEntities")
{
}
}
If you run the application, it will throw an exception as follows:
Exception:
Unable to determine composite primary key ordering for type 'CodeFirst.Models.Passport'. Use the ColumnAttribute (see http://go.microsoft.com/fwlink/?LinkId=386388) or the HasKey method (see http://go.microsoft.com/fwlink/?LinkId=386387) to specify an order for composite primary keys.
It clearly says that you have to use [Column] attribute if you are creating composite key using [Key] data annotation attribute.
So let's modify the Passport.cs file to use [Column] attribute by specifying column order. The column order is the relative value of the columns in the table. It doesn't need to be index based.
Modify the Passport.cs file as follows:
using System.ComponentModel.DataAnnotations;
using System.ComponentModel.DataAnnotations.Schema;
namespace CodeFirst.Models
{
public class Passport
{
[Key]
[Column(Order = 10)]
public string PassportNumber { get; set; }
[Key]
[Column(Order = 20)]
public string CountryCode { get; set; }
}
}
If you run the application, it will create Passports table with PassportNumber and CountryCode as the composite primary key.
The SQL for Passports table is:
CREATE TABLE [dbo].[Passports](
[PassportNumber] [nvarchar](128) NOT NULL,
[CountryCode] [nvarchar](128) NOT NULL,
CONSTRAINT [PK_dbo.Passports] PRIMARY KEY CLUSTERED
(
[PassportNumber] ASC,
[CountryCode] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF,
ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
3) Using Fluent API
Fluent API is the programming approach where the current used method provides valuable method intellisense/option for next call. The Entity Framework Code First Fluent API provides several useful methods to do the mappings. For defining the primary key, it provides HasKey() method. The Fluent API takes priority over the Data Annotation attributes.
To specify the mappings using Code First Fluent API, we have to override the OnModelCreating() method. The OnModelCreating() method is called before the models are created.
a) Let's modify the EducationContext.cs file as follows to use the Fluent API mappings:
public class EducationContext : DbContext
{
public DbSet<Student> Students { get; set; }
public DbSet<Passport> Passports { get; set; }
public EducationContext()
: base("EducationEntities")
{
}
protected override void OnModelCreating(DbModelBuilder modelBuilder)
{
modelBuilder.Entity<Student>().HasKey(s => s.Id);
base.OnModelCreating(modelBuilder);
}
}
Here, we have set "Id" as the primary key for the Students table.
The Student class looks like:
public class Student
{
public int Id { get; set; }
[Key]
public int StudentId { get; set; }
}
If you will run the application, it will create Students table with Id as the primary key. Please note that we have [Key] attribute assigned to the StudentId property in Student class. Here, the Fluent API takes priority over data annotation.
b) Composite Primary Key using Fluent API: It is easy to create composite primary key using Code First Fluent API. Modify OnModelCreating() method inside EducationContext class as follows:
protected override void OnModelCreating(DbModelBuilder modelBuilder)
{
modelBuilder.Entity<Passport>().HasKey(s => new { s.PassportNumber, s.CountryCode});
base.OnModelCreating(modelBuilder);
}
Here, we are creating a composite primary key taking PassportNumber and CountryCode properties. If you run the application, it will create the Passports table with composite primary key as follows:
Note
Whenever Code First creates primary key (for intergral data types like int, long, etc.) by convention, data annotation or fluent API, it creates primary key column with identity turned on. You can turn off the identity by using DatabaseGeneratedOption.None enum. For primary key property whose data type is Guid, Code First doesn't create identity by default for that column. You can turn the identity on by using DatabaseGeneratedOption.Identity enum. Entity Framework uses DatabaseGeneratedOption to decide what to do with the value of key attribute while inserting/updating records.
DatabaseGeneratedOption enum has three members (see documentation):
Computed: The database generates a value when a row is inserted or updated.Identity: The database generates a value when a row is inserted.None: The database does not generate values.
Using Data Annotation
[DatabaseGenerated(DatabaseGeneratedOption.None)]
public int Id { get; set; }
Using Fluent API
modelBuilder.Entity<Student>().Property(s => s.Id).HasDatabaseGeneratedOption
(DatabaseGeneratedOption.None);
Conclusion
There are three different ways to create primary key using Code First. But Fluent API method is preferable as it separates the mapping logic from the domain classes. The data annotation attributes are limited. There are certain mappings which cannot be done using data annotation attribute but can be done by using Fluent API. Fluent API has the highest priority among the three for creating the tables and columns. Before creating the model, Code First takes convention into consideration first, then reads the data annotation attributes and finally reads the mappings specified by Fluent API. The order of priorities for creating primary key using Entity Framework Code First is:
Convention ---> Data Annotation ----> Fluent API (Highest Priority)
Notes to Use the Attached Source Code
The attached source code contains the final version of the walk-through. However, there are commented codes which can be used to test the Code First convention and configuration for creating primary key. Before running the application, please change the connection string according to your system or server.
The "Download source (Packages Excluded)" zipped file contains the light version of the demo where the packages are excluded. Please restore the packages if you are using this version.
History
- 5th September, 2014: Initial version
