Create Primary Key using Entity Framework Code First, entityframework

Create Primary Key using Entity Framework Code First, entityframework
Article: http://www.codeproject.com/Articles/813912/Create-Primary-Key-using-Entity-Framework-Code-Fir 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.

The followings 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 caseInsensitive)
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 createConsole Application.

1) From the Visual Studio clickFile-> New-> Project.Alternatively, you can click"New Project..."Link from the Visual Studio Start Page (View-> Start Page). In either way 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 ".

2) Add"EntityFramework"Nuget package to the Console Application.

A)If you want to install fromPackage Manager Console(View-> Other Windows-> Package Manager Console) Use command:

Install-Package EntityFramework

B)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 ".

3) Add a folder called"Models"To the root of the Console Application. Add two class filesStudent. csAndEducationContext. csTo the "Models" folder.

TheEducationContext. csFile will act as the context object for the application which will keep track of all the changes that will happen to the entities. InheritEducationContextClass fromDbContextLike follows:

Hide Copy Code

public class EducationContext : DbContext

Create a parameterized constructor insideEducationContextClass which takes the connections string name as the parameter. This step is used for pointing to the correct database from"App. Config"File.

Hide Copy Code

public EducationContext()   : base("EducationEntities"){}

You can replaceBase ("EducationEntities ")WithBase ("Name =EducationEntities ")Which does the same. It informs Code First to use the connection string where name ="EducationEntities"FromApp. configFile (as it is a Console Application ).

Now add <connectionStrings> section toApp. configFile as follows:

Hide Copy Code

<connectionStrings>  <add connectionString="Server={ServeName}; Database=Education;        Integrated Security=SSPI" name="EducationEntities" providerName="System.Data.SqlClient">  </add></connectionStrings>

For my case,{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 problem in setting up the connection strings you can refer to http://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlconnection.connectionstring%28VS.71%29.aspx and http://www.connectionstrings.com/sql-server-2008/ links.

Here I have given the database name"Education ".You can change it to anything you like.

4) Add a property "Id" of typeIntInsideStudent. csFile.

Hide Copy Code

public class Student{  public int Id { get; set; }}

TheStudentsClass will act as an entity forEducationContext.So create a property of typeDbSet <Student>InsideEducationContext. csFile as follows:

Hide Copy Code

public class EducationContext : DbContext{  public DbSet<Student> Students { get; set; }}

5) ModifyProgram. csFile 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.

Hide Copy Code

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 pressF5). In the SQL Server you can see there is database with nameEducationHaving two tables:_ MigrationHistoryAndStudents.

The_ MigrationHistoryTable (previous EdmMetadataTable upto EF 4.2) is used to keep track of all the changes that happens to the model.StudentsTable is created as we have created a property"Students"Of typeDbSet <Student>InsideEducationContextClass.

Points of Interest1) 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)InStudent. csFile there is a single property"Id"Of typeInt. 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 forStudentsTable is:

Hide Copy Code

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 fromStudent. csFile and add another property called"StudentId"Which matches the convention {class name} +" Id "I. e" Student "+" Id ".StudentClass now looks like:

Hide Copy Code

public class Student{    public int StudentId { get; set; }}

If you will run the application, it will create_ MigrationHistoryAndStudentsTables where"StudentId"Is the primary key ofStudentsTable.

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 pressDeleteKey. In either way it will bring up the "Delete Object" window. Check"Close existing connection"Checkbox from the bottom and click" OK "button.

Note: The convention for creating the primary key is case InsensitiveI. 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 STudenTiDThen in StudentsTable it will be the primary key and the column name will be STudenTiD.

C)Let's modifyStudent. csFile and add two propetiesIdAndStudentIdAs follows:

Hide Copy Code

public class Student{   public int Id { get; set; }   public int StudentId { get; set; }}

Here bothIdAndStudentIdMatches the convention for creating the primay key. Now run the application to see the effect in the database:

As shown in the below screenshot, it createsStudentsTableIdAs the primary key of the table.

If you change the order of the properties this will not change the primary key I. e stillIdWill 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 propertiesIdAndStudentIdInStudent. csFile and add[Key]AttributeStudentIdProperty as follows:

Hide Copy Code

public class Student{   public int Id { get; set; }      [Key]   public int StudentId { get; set; }}

If you run the application, it will create "Students" tableStudentIdAs the primary key. Here data annotation attribute take 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 propertiesPassportNumberAndCountryCode. BothPassportNumberAndCountryCodeUniquely identifies a record inPassportsTable. The class looks like:

Hide Copy Code

public class Passport{  [Key]  public string PassportNumber { get; set; }  [Key]  public string CountryCode { get; set; }  }

Add another property "Passports" of typeDbSet <Passport>InsideEducationContext. csFile as follows:

Hide Copy Code

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 modifyPassport. csFile 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:

Hide Copy Code

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 createPassportsTablePassportNumberAndCountryCodeAs the composite primary key.

The SQLPassportsTable is:

Hide Copy Code

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 intelliisense/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 providesHasKey ()Method. The Fluent API takes priority over the Data Annotation attributes.

To specify the mappings using Code First Fluent API, we have to overrideOnModelCreating ()Method.OnModelCreating ()Method is calledBeforeThe models are created.

A)Let's modifyEducationContext. csFile as follows to use the Fluent API ings:

Hide Copy Code

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 forStudentsTable.

TheStudentClass looks like:

Hide Copy Code

public class Student{   public int Id { get; set; }   [Key]   public int StudentId { get; set; }}

If you will run the application, it will createStudentsTableIdAs the primary key. Please note that we have[Key]Attribute assigned toStudentIdProperty inStudentClass. 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. ModifyOnModelCreating ()Method insideEducationContextClass as follows:

Hide Copy Code

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 takingPassportNumberAndCountryCodeProperties. If you run the application, it will createPassportsTable with composite primary key as follows:

Note:

Whenever Code First creates primary key (for intergral data types likeInt, longEtc.) by convention, data annotation or fluent API, it creates primary key column with identity turned on. You can turn off the identity by usingDatabaseGeneratedOption. NoneEnum. For primary key property whose data type isGuid, Code First doesn't create identity by default for that column. You can turn the identity on by usingDatabaseGeneratedOption. IdentityEnum. Entity Framework usesDatabaseGeneratedOptionTo decide what to do with the value of key attribute while inserting/updating records.

DatabaseGeneratedOptionEnum 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:

Hide Copy Code

[DatabaseGenerated(DatabaseGeneratedOption.None)]public int Id { get; set; }

Using Fluent API:

Hide Copy Code

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 more 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.

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)