Relationship in Entity Framework Using Code First Approach With Fluent API

Introduction

A relationship, in the context of databases, is a situation that exists between two relational database tables when one table has a foreign key that references the primary key of the other table. Relationships allow relational databases to split and store data in various tables while linking disparate data items. For example, if we want to store information about a Customer and his Order then we need to create two tables, one for the Customer and another for the Order. Both tables, Customer, and Order, will have a relationship one-to-many so whenever we retrieve all orders of a customer then we can easily retrieve them.

There are several types of database relationships. In this article, I will cover the following.

• One-to-One Relationships
• One-to-many or Many to One Relationships
• Many-to-Many Relationships

Entity Framework Code First allows us to use our own domain classes to represent the model that Entity Framework relies on to perform querying, change tracking, and updating functions. The Code First approach follows conventions over the configuration but it also gives us two ways to add a configuration over classes. One uses simple attributes called DataAnnotations and another uses Code First's Fluent API, which provides you with a way to describe configuration imperatively, in code. This article will focus on tuning up the relationship in the Fluent API.

To understand the relationship in the Entity Framework Code First approach, we create an entity and define its configuration using the Fluent API. We will create two class library projects, one library project (EF.Core) has entities, and another project (EF.Data) has these entities configured with DbContext. We also create a unit test project (EF.UnitTest) that will be used to test our code. We will use the following classes that are in a class diagram to explain the preceding three relationships.

Figure 1.1. Class Diagram for Entities.

As in the preceding class diagram, the BaseEntity class is a base class that is inherited by each other class. Each derived entity represents each database table. We will use two derived entity combinations from the left side to explain each relationship type and that's why we create six entities.

So, first of all, we create the BaseEntity class that is inherited by each derived entity under the EF. Core class library project.

using System;

namespace EF.Core
{
    public abstract class BaseEntity
    {
        public Int64 ID { get; set; }
        public DateTime AddedDate { get; set; }
        public DateTime ModifiedDate { get; set; }
        public string IP { get; set; }
    }
}

We use navigation properties to access a related entity object from one to another. The navigation properties provide a way to navigate an association between two entity types. Every object can have a navigation property for every relationship in which it participates. Navigation properties allow you to navigate and manage relationships in both directions, returning either a reference object (if the multiplicity is either one or zero-or-one) or a collection (if the multiplicity is many).

Now let's see each relationship one by one.

Our Roadmap towards Learning MVC with Entity Framework

• Code First Migrations with Entity Framework
• CRUD Operations Using Entity Framework 5.0 Code First Approach in MVC
• CRUD Operations Using the Repository Pattern in MVC
• CRUD Operations Using the Generic Repository Pattern and Unit of Work in MVC
• CRUD Operations Using the Generic Repository Pattern and Dependency Injection in MVC

One-to-One Relationship

Both tables can have only one record on either side of the relationship. Each primary key value relates to only one record (or no records) in the related table. Keep in mind that this kind of relationship is not very common and most one-to-one relationships are forced by business rules and don't flow naturally from the data. In the absence of such a rule, you can usually combine both tables into one table without breaking any normalization rules.

To understand one-to-one relationships, we create two entities, one is User, and another is UserProfile. One user can have a single profile, a User table that will have a primary key and that same key will be both primary and foreign keys for the UserProfile table. Let’s see Figure 1.2 for a one-to-one relationship.

Figure 1.2. One-to-One Relationship

Now we create both entities User and UserProfile in the EF. Core project under the Data folder. Our User class code snippet is as in the following.

namespace EF.Core.Data
{
    public class User : BaseEntity
    {
        public string UserName { get; set; }
        public string Email { get; set; }
        public string Password { get; set; }
        public UserProfile UserProfile { get; set; }
    }
}

The UserProfile class code snippet is as in the following.

namespace EF.Core.Data
{
    public class UserProfile : BaseEntity
    {
        public string FirstName { get; set; }
        public string LastName { get; set; }
        public string Address { get; set; }
        public virtual User User { get; set; }
    }
}

As you can see in the preceding, in both code snippets each entity is using another entity as a navigation property so that you can access the related object from each other.

Now we define the configuration for both entities that will be used when the database table is created by the entity. The configuration defines another class library project EF. Data under the Mapping folder. Now create two configuration classes for each entity. For the User entity, we create the UserMap entity.

using System.ComponentModel.DataAnnotations.Schema;
using System.Data.Entity.ModelConfiguration;
using EF.Core.Data;

namespace EF.Data.Mapping
{
    public class UserMap : EntityTypeConfiguration<User>
    {
        public UserMap()
        {
            // Key
            HasKey(t => t.ID);

            // Fields
            Property(t => t.ID).HasDatabaseGeneratedOption(DatabaseGeneratedOption.Identity);
            Property(t => t.UserName).IsRequired().HasMaxLength(25);
            Property(t => t.Email).IsRequired();
            Property(t => t.AddedDate).IsRequired();
            Property(t => t.ModifiedDate).IsRequired();
            Property(t => t.IP);

            // Table
            ToTable("Users");
        }
    }
}

We will use the same way to create the configuration for other entities as for the User. EntityTYpeConfiguration<T> is an important class that allows configuration to be performed for an entity type in a model. This is done using the modelbuilder in an override of the OnModelCreate method. The Constructor of the UserMap class uses the Fluent API to map and configure properties in the table. So let's see each method used in the constructor one by one.

1. HasKey(): The Haskey() method configures a primary key on the table.
2. Property(): The Property method configures attributes for each property belonging to an entity or complex type. It is used to obtain a configuration object for a given property. The options on the configuration object are specific to the type being configured.
3. HasDatabaseGeneratedOption: It configures how values for the property are generated by the database.
4. DatabaseGeneratedOption.Identity: DatabaseGeneratedOption is the database annotation. It enumerates a database-generated option. DatabaseGeneratedOption.Identity is used to create an auto-increment column in the table by a unique value.
5. ToTable(): Configures the table name that this entity type is mapped to.

Now create the UserProfile configuration class, the UserProfileMap class.

using System.Data.Entity.ModelConfiguration;
using EF.Core.Data;

namespace EF.Data.Mapping
{
    public class UserProfileMap : EntityTypeConfiguration<UserProfile>
    {
        public UserProfileMap()
        {
            // Key
            HasKey(t => t.ID);

            // Fields
            Property(t => t.FirstName);
            Property(t => t.LastName);
            Property(t => t.Address).HasMaxLength(100).HasColumnType("nvarchar");
            Property(t => t.AddedDate);
            Property(t => t.ModifiedDate);
            Property(t => t.IP);

            // Table
            ToTable("UserProfiles");

            // Relationship
            HasRequired(t => t.User)
                .WithRequiredDependent(u => u.UserProfile);
        }
    }
}

In the code snippet above we defined a one-to-one relationship between both User and UserProfiles entities. This relationship is defined by the Fluent API using the HasRequired() and WithRequiredDependent() methods so these methods are as in the following.

1. HasRequired(): Configures a required relationship from this entity type. Instances of the entity type will not be able to be saved to the database unless this relationship is specified. The foreign key in the database will be non-nullable. In other words, UserProfile can’t be saved independently without a User entity.
2. WithRequiredDependent(): (from the MSDN) Configures the relationship to be required: required without a navigation property on the other side of the relationship. The entity type being configured will be the dependent and contain a foreign key to the principal. The entity type that the relationship targets will be the principal in the relationship.

Now define the connection string in the App. config file under EF. Data Project so that we can create a Database with an appropriate name. The connection string is.

<connectionStrings>
    <add name="DbConnectionString" connectionString="Data Source=sandeepss-PC;Initial Catalog=EFCodeFirst;User ID=sa; Password=*******" providerName="System.Data.SqlClient" />
</connectionStrings>

Now we create a context class EFDbContext (EFDbContext.cs) that inherits the DbContext class. In this class, we override the OnModelCreating() method. This method is called when the model for a context class (EFDbContext) has been initialized, but before the model has been locked down and used to initialize the context such that the model can be further configured before it is locked down. The following is the code snippet for the context class.

using System;
using System.Data.Entity;
using System.Data.Entity.ModelConfiguration;
using System.Linq;
using System.Reflection;

namespace EF.Data
{
    public class EFDbContext : DbContext
    {
        public EFDbContext()
            : base("name=DbConnectionString")
        {
        }

        protected override void OnModelCreating(DbModelBuilder modelBuilder)
        {
            var typesToRegister = Assembly.GetExecutingAssembly().GetTypes()
                .Where(type => !String.IsNullOrEmpty(type.Namespace))
                .Where(type => type.BaseType != null && type.BaseType.IsGenericType
                    && type.BaseType.GetGenericTypeDefinition() == typeof(EntityTypeConfiguration<>));
            
            foreach (var type in typesToRegister)
            {
                dynamic configurationInstance = Activator.CreateInstance(type);
                modelBuilder.Configurations.Add(configurationInstance);
            }

            base.OnModelCreating(modelBuilder);
        }
    }
}

As you know the EF Code First approach follows convention over configuration so in the constructor we just pass the connection string name, the same as an App. Config file and it connects to that server. In the OnModelCreating() method, we used a reflection to map an entity to its configuration class in this specific project.

We create a Unit Test Project EF.UnitTest to test the code above. We create a test class UserTest that has a test method UserUserProfileTest(). This method creates a database and populates User and UserProfile tables as per their relationship. The following is the code snippet for the UserTest class.

using System;
using System.Data.Entity;
using EF.Core.Data;
using EF.Data;
using Microsoft.VisualStudio.TestTools.UnitTesting;

namespace EF.UnitTest
{
    [TestClass]
    public class UserTest
    {
        [TestMethod]
        public void UserUserProfileTest()
        {
            Database.SetInitializer<EFDbContext>(new CreateDatabaseIfNotExists<EFDbContext>());
            using (var context = new EFDbContext())
            {
                context.Database.Create();

                User user = new User
                {
                    UserName = "ss_shekhawat",
                    Password = "123",
                    Email = "[email protected]",
                    AddedDate = DateTime.Now,
                    ModifiedDate = DateTime.Now,
                    IP = "1.1.1.1",
                    UserProfile = new UserProfile
                    {
                        FirstName = "Sandeep",
                        LastName = "Shekhawat",
                        Address = "Jaipur and Jhunjhunu",
                        AddedDate = DateTime.Now,
                        ModifiedDate = DateTime.Now,
                        IP = "1.1.1.1"
                    }
                };

                context.Entry(user).State = EntityState.Added;
                context.SaveChanges();
            }
        }
    }
}

Now run the Test method and you get your table in the database with data. Run a select query in the database and get results like this.

SELECT [ID], [UserName], [Email], [Password], [AddedDate], [ModifiedDate], [IP]
FROM [EFCodeFirst].[dbo].[Users]

SELECT [ID], [FirstName], [LastName], [Address], [AddedDate], [ModifiedDate], [IP]
FROM [EFCodeFirst].[dbo].[UserProfiles]

Now execute the preceding query and then you will get results as in the following figure.

Figure 1.3. Result of User and UserProfile.

That’s it for One-to-One relationships.

One-to-many Relationship

The primary key table contains only one record that relates to none, one, or many records in the related table. This is the most commonly used type of relationship.

To understand this relationship, consider an e-commerce system where a single user can make many orders so we define two entities, one for the customer and another for the order. Let’s see the following figure.

Figure 1.4. One-to-many Relationship

The customer entity is as follows.

using System.Collections.Generic;

namespace EF.Core.Data
{
    public class Customer : BaseEntity
    {
        public string Name { get; set; }
        public string Email { get; set; }
        public virtual ICollection<Order> Orders { get; set; }
    }
}

The Order entity code snippet is as in the following.

using System;

namespace EF.Core.Data
{
    public class Order : BaseEntity
    {
        public byte Quantity { get; set; }
        public decimal Price { get; set; }
        public long CustomerId { get; set; }
        public virtual Customer Customer { get; set; }
    }
}

You have noticed the navigation properties in the code above. The Customer entity has a collection of Order entity types and the Order entity has a Customer entity type property, which means a customer can make many orders.

Now create a class, the CustomerMap class in the EF.Data project to implement the Fluent API configuration for the Customer class.

using System.ComponentModel.DataAnnotations.Schema;
using System.Data.Entity.ModelConfiguration;
using EF.Core.Data;

namespace EF.Data.Mapping
{
    public class CustomerMap : EntityTypeConfiguration<Customer>
    {
        public CustomerMap()
        {
            // Key
            HasKey(t => t.ID);

            // Properties
            Property(t => t.ID).HasDatabaseGeneratedOption(DatabaseGeneratedOption.Identity);
            Property(t => t.Name);
            Property(t => t.Email).IsRequired();
            Property(t => t.AddedDate).IsRequired();
            Property(t => t.ModifiedDate).IsRequired();
            Property(t => t.IP);

            // Table
            ToTable("Customers");
        }
    }
}

Now create another mapping class for the Order entity configuration.

using System.ComponentModel.DataAnnotations.Schema;
using System.Data.Entity.ModelConfiguration;
using EF.Core.Data;

namespace EF.Data.Mapping
{
    public class OrderMap : EntityTypeConfiguration<Order>
    {
        public OrderMap()
        {
            // Key
            HasKey(t => t.ID);

            // Fields
            Property(t => t.ID).HasDatabaseGeneratedOption(DatabaseGeneratedOption.Identity);
            Property(t => t.Quantity).IsRequired().HasColumnType("tinyint");
            Property(t => t.Price).IsRequired();
            Property(t => t.CustomerId).IsRequired();
            Property(t => t.AddedDate).IsRequired();
            Property(t => t.ModifiedDate).IsRequired();
            Property(t => t.IP);

            // Table
            ToTable("Orders");

            // Relationship
            HasRequired(t => t.Customer)
                .WithMany(c => c.Orders)
                .HasForeignKey(t => t.CustomerId)
                .WillCascadeOnDelete(false);
        }
    }
}

The code above shows that a Customer is required for each order and the Customer can make multiple orders and relationships between both made by foreign key CustomerId. Here we use four methods to define the relationship between both entities. The WithMany method, allows us to indicate which property in Customer contains the Many relationship. We add to that the HasForeignKey method to indicate which property of Order is the foreign key pointing back to the customer. The WillCascadeOnDelete() method Configures whether or not cascade delete is on for the relationship.

No, we create another unit test class in the EF.UnitTest Project to test the code above. Let’s see the test method that inserts data for the customer that has two orders.

using System;
using System.Collections.Generic;
using System.Data.Entity;
using EF.Core.Data;
using EF.Data;
using Microsoft.VisualStudio.TestTools.UnitTesting;

namespace EF.UnitTest
{
    [TestClass]
    public class CustomerTest
    {
        [TestMethod]
        public void CustomerOrderTest()
        {
            Database.SetInitializer<EFDbContext>(new CreateDatabaseIfNotExists<EFDbContext>());
            using (var context = new EFDbContext())
            {
                context.Database.Create();

                Customer customer = new Customer
                {
                    Name = "Raviendra",
                    Email = "[email protected]",
                    AddedDate = DateTime.Now,
                    ModifiedDate = DateTime.Now,
                    IP = "1.1.1.1",
                    Orders = new List<Order>
                    {
                        new Order
                        {
                            Quantity = 12,
                            Price = 15,
                            AddedDate = DateTime.Now,
                            ModifiedDate = DateTime.Now,
                            IP = "1.1.1.1",
                        },
                        new Order
                        {
                            Quantity = 10,
                            Price = 25,
                            AddedDate = DateTime.Now,
                            ModifiedDate = DateTime.Now,
                            IP = "1.1.1.1",
                        }
                    }
                };

                context.Entry(customer).State = EntityState.Added;
                context.SaveChanges();
            }
        }
    }
}

Now run the Test method and you get your table in the database with data. Run a select query in the database and get results like this.

SELECT [ID], [Name], [Email], [AddedDate], [ModifiedDate], [IP]
FROM [EFCodeFirst].[dbo].[Customers];

SELECT [ID], [Quantity], [Price], [CustomerId], [AddedDate], [ModifiedDate], [IP]
FROM [EFCodeFirst].[dbo].[Orders];

Figure 1.5. Customer and Order Data.

Many-to-Many Relationship

Each record in both tables can relate to any number of records (or no records) in the other table. Many-to-many relationships require a third table, known as an associate or linking table because relational systems can't directly accommodate the relationship.

To understand this relationship, consider an online course system where a single student can join many courses and a course can have many students so we define two entities, one for the student and another for the course. Let’s see the following figure for the Many-to-Many relationship.

Figure 1.6. Many-to-Many Relationship.

The Student entity is as in the following code snippet that is defined under EF. Core Project.

using System.Collections.Generic;

namespace EF.Core.Data
{
    public class Student : BaseEntity
    {
        public string Name { get; set; }
        public byte Age { get; set; }
        public bool IsCurrent { get; set; }
        public virtual ICollection<Course> Courses { get; set; }
    }
}

The Course entity is as in the following code snippet that is defined under the EF. Core Project.

using System;
using System.Collections.Generic;

namespace EF.Core.Data
{
    public class Course : BaseEntity
    {
        public string Name { get; set; }
        public Int64 MaximumStrength { get; set; }
        public virtual ICollection<Student> Students { get; set; }
    }
}

Both code snippets above have navigation properties that are collections, in other words, one entity has another entity collection.

Now create a class StudentMap class in the EF. Data project to implement a Fluent API configuration for the Student class.

using System.ComponentModel.DataAnnotations.Schema;
using System.Data.Entity.ModelConfiguration;
using EF.Core.Data;

namespace EF.Data.Mapping
{
    public class StudentMap : EntityTypeConfiguration<Student>
    {
        public StudentMap()
        {
            // Key
            HasKey(t => t.ID);

            // Properties
            Property(t => t.ID).HasDatabaseGeneratedOption(DatabaseGeneratedOption.Identity);
            Property(t => t.Name);
            Property(t => t.Age);
            Property(t => t.IsCurrent);
            Property(t => t.AddedDate).IsRequired();
            Property(t => t.ModifiedDate).IsRequired();
            Property(t => t.IP);

            // Table
            ToTable("Students");

            // Relationship
            HasMany(t => t.Courses)
                .WithMany(c => c.Students)
                .Map(t =>
                {
                    t.ToTable("StudentCourse");
                    t.MapLeftKey("StudentId");
                    t.MapRightKey("CourseId");
                });
        }
    }
}

The code snippet above shows that one student can join many courses and each course can have many students. As you know, to implement Many-to-Many relationships we need a third table named StudentCourse. The MapLeftKey() and MapRightKey() methods define the key's name in the third table otherwise the key name is automatically created with classname_Id. The Left key or first key will be that in which we are defining the relationship.

Now create a class, the CourseMap class, in the EF. Data project to implement the Fluent API configuration for the Course class.

using System.ComponentModel.DataAnnotations.Schema;
using System.Data.Entity.ModelConfiguration;
using EF.Core.Data;

namespace EF.Data.Mapping
{
    public class CourseMap : EntityTypeConfiguration<Course>
    {
        public CourseMap()
        {
            // Property
            Property(t => t.ID).HasDatabaseGeneratedOption(DatabaseGeneratedOption.Identity);
            Property(t => t.Name);
            Property(t => t.MaximumStrength);
            Property(t => t.AddedDate).IsRequired();
            Property(t => t.ModifiedDate).IsRequired();
            Property(t => t.IP);

            // Table
            ToTable("Courses");
        }
    }
}

No, we create another unit test class in the EF.UnitTest Project to test the code above. Let’s see the test method that inserts data in all three tables.

using System;
using System.Collections.Generic;
using System.Data.Entity;
using EF.Core.Data;
using EF.Data;
using Microsoft.VisualStudio.TestTools.UnitTesting;

namespace EF.UnitTest
{
    [TestClass]
    public class StudentTest
    {
        [TestMethod]
        public void StudentCourseTest()
        {
            Database.SetInitializer<EFDbContext>(new CreateDatabaseIfNotExists<EFDbContext>());
            using (var context = new EFDbContext())
            {
                context.Database.Create();
                
                Student student = new Student
                {
                    Name = "Sandeep",
                    Age = 25,
                    IsCurrent = true,
                    AddedDate = DateTime.Now,
                    ModifiedDate = DateTime.Now,
                    IP = "1.1.1.1",
                    Courses = new List<Course>
                    {
                        new Course
                        {
                            Name = "Asp.Net",
                            MaximumStrength = 12,
                            AddedDate = DateTime.Now,
                            ModifiedDate = DateTime.Now,
                            IP = "1.1.1.1"
                        },
                        new Course
                        {
                            Name = "SignalR",
                            MaximumStrength = 12,
                            AddedDate = DateTime.Now,
                            ModifiedDate = DateTime.Now,
                            IP = "1.1.1.1"
                        }
                    }
                };

                Course course = new Course
                {
                    Name = "Web API",
                    MaximumStrength = 12,
                    AddedDate = DateTime.Now,
                    ModifiedDate = DateTime.Now,
                    IP = "1.1.1.1",
                    Students = new List<Student>
                    {
                        new Student
                        {
                            Name = "Raviendra",
                            Age = 25,
                            IsCurrent = true,
                            AddedDate = DateTime.Now,
                            ModifiedDate = DateTime.Now,
                            IP = "1.1.1.1"
                        },
                        new Student
                        {
                            Name = "Pradeep",
                            Age = 25,
                            IsCurrent = true,
                            AddedDate = DateTime.Now,
                            ModifiedDate = DateTime.Now,
                            IP = "1.1.1.1"
                        }
                    }
                };

                context.Entry(student).State = EntityState.Added;
                context.Entry(course).State = EntityState.Added;
                context.SaveChanges();
            }
        }
    }
}

Now run the Test method and you get your table in the database with data. Run the select query in the database and get results like this.

-- Query to select fields from Students table
SELECT [ID], [Name], [Age], [IsCurrent], [AddedDate], [ModifiedDate], [IP]
FROM [EFCodeFirst].[dbo].[Students]

-- Query to select fields from Courses table
SELECT [ID], [Name], [MaximumStrength], [AddedDate], [ModifiedDate], [IP]
FROM [EFCodeFirst].[dbo].[Courses]

-- Query to select StudentId and CourseId from StudentCourse table
SELECT [StudentId], [CourseId]
FROM [EFCodeFirst].[dbo].[StudentCourse]

Figure 1.7. Data for Students and Courses.

Conclusion

This article introduced relationships in the Entity Framework Code First approach using the Fluent API. I didn’t use database migration here; that is why you need to delete your database before running any test method of the unit. If you have any doubts, post as a comment.