How to create relationships between entities in the Entity Framework Code First Approach

Dhananjay Kumar / Wednesday, October 21, 2015

The Entity Framework Code First approach allows us to create a model as a plain class and then the database gets created from the domain model or entity class. In the Code First approach, the database gets created from the classes.

Some advantages of the Entity Framework Code First approach include (as stated in Scott Gu’s blog):

• Developing without ever having to open a designer or define an XML mapping file

• Defining your model objects by simply writing “plain old classes” with no base classes required

• Using a “convention over configuration” approach that enables database persistence without explicitly configuring anything

• Optionally overriding the convention-based persistence and using a fluent code API to fully customize the persistence mapping

Rather the delving more into theoretical concepts, in this post we will directly jump into code and create a table and database using the Code First approach. In this post we will learn how we can create entities and a relationship between entities in the Entity Framework Code First approach. In the EF Code First approach, there are two options to create the relationship between entities, through:

1. Data annotations

2. Fluent API

In this post we will use data annotations to create the relationship between entities.

Create database with one table

Let us start with creating a table named Student in a database with the code first approach. The domain class Student can be created as shown in the listing below:

namespace CodeFirstDemoApp

{

public class Student

{

public int Id { get; set; }

public string Name { get; set; }

public int Age { get; set; }

}

As you might have already noticed, the Student class is a plain class. Entity Framework will use the Student class to create the table in the database. The Student class represents the domain entity and it should not have any information or references of the database. Entity Framework will use the Student class to create the Student table.

Once the domain entity class is created, next we need to create a Context class which will inherit the DataContext class. The context class can be created as shown in the listing below:

using System.Data.Entity;

namespace CodeFirstDemoApp

{

public class Context : DbContext

{

public Context() : base()

{

}

public DbSet<Student> Students { get; set; }

}

We have created the Context class with the default constructor. Later in the post we will talk about various options in the constructor. In the context class we are doing the following tasks:

· Creating the default constructor. Since we are not passing any parameter in the constructor, so the EF will create a database with the name as Namespace.Class name. So in this case, the created database name will be CodeFirstDemo.Context.

· Since we are not passing a connection string information in the constructor of Context class, the EF will create a database in the default database server of SQL Server Express.

· To create a database in the desired server with the desired name, we need to create the connection string and pass that as a parameter in the Context constructor.

· To create the table in the database, create a public property of the generic DbSet type with domain entity passed in it.

So far we have created the Student entity class and the Context class. Now we can write a simple LINQ to Entity query to create the database and perform the operations as shown in the listing below:

using System;

using System.Linq;

namespace CodeFirstDemoApp

{

class Program

{

static void Main(string[] args)

{

CreateStudent();

Console.WriteLine("Student Creatred");

Context c = new Context();

var result = from r in c.Students select r;

foreach (var r in result)

{

Console.WriteLine(r.Name);

}

Console.ReadKey(true);

}

static void CreateStudent()

{

Student s = new Student

{

Id = 1,

Age = 12,

Name = "Foo"

};

Context c = new Context();

c.Students.Add(s);

c.SaveChanges();

}

Custom database name

When we work with the default constructor for the Context class, EF by default creates the database with a fully qualified name as Namespace.Contextclass name. However we can pass the desired name of the database mydb in this case in the constructor as shown in the listing below:

public Context()

: base("mydb")

{

}

In the SQL Express EF will create a database with the name mydb.

Working with Connection String

As of now we are relying on the EF to create the database. However we can pass a connection string to create a database at the desired server and a name. The connection string can be created in the config file as listed below:

</connectionStrings>

We have created a connection string to create a database in local db server. Next we need to pass the connection string name in the constructor of Context class as shown in the listing below:

public Context()

: base("name=democonnectionstring")

{

}

Relationship between Entities

In the Code First approach, we can create a relationship between entities using either of the two options:

1. Data Annotations

2. Fluent API

In this post we will create relationship using data annotations.

One to One Relationship

We may have a requirement to create one to one relationships between two entities. In other words, we need a Primary key – Foreign Key relationship between two entities. Let us say we have two entities and the following rules:

1. There are two entities named Student and StudentAccount

2. Student is a primary entity

3. StudentAccount is a dependent entity on Student

4. Primary key of StudentAccount will be foreign key of Student

We should not able to create a StudentAccount without a Student and there can only be one entry of Student in StudentAccount. Put simply, each Student will have one StudentAccount and no StudentAccount will exist without a Student.

Let us first create the primary entity: Student

public class Student

{

public int Id { get; set; }

public string Name { get; set; }

public int Age { get; set; }

public virtual StudentAccount StudentAccount { get; set; }

}

As you might have noticed, in the Student entity we have a virtual property of the type StudentAccount which is created as shown the listing below:

public class StudentAccount

{

public int Id { get; set; }

public string Name { get; set; }

public int Amount { get; set; }

[Required]

public virtual Student Student { get; set; }

}

Again you might have noticed that in the StudentAccount entity we have a virtual property of the type Student. Since Student is a primary entity, the virtual Student property in the StudentAccount entity is annotated Required. Further, a Context class can be created as shown in the listing below:

using System.Data.Entity;

namespace CodeFirstDemoApp

{

public class Context : DbContext

{

public Context()

: base("name=democonnectionstring")

{

}

public DbSet<Student> Students { get; set; }

public DbSet<StudentAccount> StudentAccounts{ get; set; }

}

Always remember that we cannot create a row in the StudentAccounts table unless we do not have a corresponding row in the Student table. Data can be created in the related table as shown in the listing below:

static void CreateStudent()

{

Student s = new Student

{

Id = 1,

Age = 12,

Name = "Foo"

};

StudentAccount sa = new StudentAccount

{

Amount = 300,

Name = "Sports Account",

Student = s

};

Context c = new Context();

c.Students.Add(s);

c.StudentAccounts.Add(sa);

c.SaveChanges();

}

As you might have noticed, we are setting the object of Student as a property of StudentAccount. We can retrieve records from both the tables as shown in the listing below:

Context c = new Context();

var result = from r in c.Students select r;

foreach (var r in result)

{

Console.WriteLine(r.Name);

Console.WriteLine(r.StudentAccounts.Amount);

}

To verify the relationship between entities in SQL Server Management Studio, we can see the columns created with the constraints and keys as shown in the image below:

Here the Id column of the StudentAccounts table is both the primary key and foreign key.

One to Many Relationship

We may have a requirement to create one too many relationship between two entities. Let us say we have two entities

1. There are two entities Student and StudentAddress

2. Student is a primary entity

3. StudentAddress is a dependent entity on Student

4. One Student can enroll in multiple StudentAddress

One Student can have many StudentAddress. One of the column of StudentAddress will have foreign key as primary key of Student.

Let us first create the primary entity Student,

public class Student

{

public Student()

{

StudentAddresses = new HashSet<StudentAddress>();

}

public int Id { get; set; }

public string Name { get; set; }

public int Age { get; set; }

public ICollection<StudentAddress> StudentAddresses { get; set; }

}

You might have noticed that we are creating a property of collection of StudentAddress and then creating a set value of the StudentAddress property in the constructor of Student. The StudentAddress class can be created as shown in the listing below:

public class StudentAddress

{

public int Id { get; set; }

public string Address { get; set;}

public int StudentId { get; set; }

public virtual Student Student { get; set; }

}

Again you might have noticed that in the StudentAddress entity we have a virtual property of the type Student. Since Student is a primary entity, the virtual Student property in the StudentAddress has a corresponding StudentId property.

Further, the Context class can be created as shown in the listing below:

public class Context : DbContext

{

public Context()

: base("name=democonnectionstring")

{

}

public DbSet<Student> Students { get; set; }

public DbSet<StudentAddress> StudentAddresses { get; set; }

}

Always remember that we cannot create a row in the StudentAddress table unless we do not have a corresponding row in the Student table. Data can be created in the related table as shown in the listing below:

static void CreateStudent()

{

Student s = new Student

{

Id = 1,

Age = 12,

Name = "Foo"

};

StudentAddress sa1 = new StudentAddress { Address = "Delhi", Id = 1 };

StudentAddress sa2 = new StudentAddress { Address = "Bangalore", Id = 2 };

s.StudentAddresses.Add(sa1);

s.StudentAddresses.Add(sa2);

Context c = new Context();

c.Students.Add(s);

c.SaveChanges();

}

As you might have noticed, we are adding the objects of StudentAddress to the Student. We can retrieve records from both tables as shown in the listing below:

static void Main(string[] args)

{

CreateStudent();

Console.WriteLine("Student Created");

Context c = new Context();

var result = from r in c.Students.Include("StudentAddresses") select r;

foreach (var r in result)

{

Console.WriteLine(r.Name);

foreach(var a in r.StudentAddresses)

{

Console.WriteLine(a.Address);

}

Console.ReadKey(true);

}

To verify the relationship between entities in SQL Server Management Studio, we can see the columns created with the constraints and keys as shown in the image below:

Many to Many Relationship

Last but not least, let us see how we can configure a many to many relationship. Let’s say we have a Student entity and a Subject entity. One Student can be enrolled in many Subjects and One Subject can have many Students. To create a many too many relationship between these entities, let us first create the Student entity as shown in the listing below:

public class Student

{

public Student()

{

Subjects = new HashSet<Subject>();

}

public int Id { get; set; }

public string Name { get; set; }

public int Age { get; set; }

public ICollection<Subject> Subjects { get; set; }

}

Here we’re creating a property of collection of Subjects and then creating a set value of the Subjects property in the constructor of Student. The Subject class can be created as shown in the listing below:

public class Subject

{

public Subject()

{

Students = new HashSet<Student>();

}

public int Id { get; set; }

public string Name { get; set; }

public virtual ICollection<Student> Students { get; set; }

}

In the Subject class also we are creating property of collection of Students and in the constructor of Subject class creating a set of students. This is all we need to do to create a many too many relationship between entities.

Further, the Context class can be created as shown in the listing below:

public class Context : DbContext

{

public Context()

: base("name=democonnectionstring")

{

}

public DbSet<Student> Students { get; set; }

public DbSet<Subject> Subjects { get; set; }

}

We can create the rows in the Students and Subjects table as shown in the listing below:

static void CreateStudent()

{

Student s = new Student

{

Id = 1,

Age = 12,

Name = "Foo"

};

Subject s1 = new Subject { Id = 1, Name = "Phy" };

Subject s2 = new Subject { Id = 2, Name = "Maths" };

s.Subjects.Add(s1);

s.Subjects.Add(s2);

Context c = new Context();

c.Students.Add(s);

c.SaveChanges();

}

We can retrieve records from both the tables as shown here:

static void Main(string[] args)

{

CreateStudent();

Console.WriteLine("Student Created");

Context c = new Context();

var result = from r in c.Students.Include("Subjects") select r;

foreach (var r in result)

{

Console.WriteLine(r.Name);

foreach(var a in r.Subjects)

{

Console.WriteLine(a.Name);

}

Console.ReadKey(true);

}

When we verify the relationship between the Student and Subject entity, we will find that EF has created an extra table to maintain the many to many relationship

So there you have it, that’s how to create relationships between entities in the Code First approach. In this post we started with working with single entities and then proceeded to create a relationship between the entities. I hope you find this post useful, thanks for reading.

Infragistics Ultimate 15.2 is here. Download and see its power in action!