Jump to content
Aerosol

Relationship in Entity Framework Using Code First Approach With Fluent API

By Aerosol, in Programare

Recommended Posts

Aerosol Newbie

Aerosol

Posted
Posted

Download source - 3.4 MB

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 the 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 on over classes. One is using simple attributes called DataAnnotations and another is using Code First's Fluent API, that 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 their 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 configuration 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.

ClassDiagram2_small.png

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 entities combination 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.

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 one-to-one relationship.

one-to-one.PNG

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 two 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 will be 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 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.

  • HasKey(): The Haskey() method configures a primary key on table.
  • 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.
  • HasDatabaseGeneratedOption: It configures how values for the property are generated by the database.
  • 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.
  • 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:

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 User entity.

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 App.config file under EF.Data project so that we can create database with the appropriate name. The connectionstring 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 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 = "sandeep.shekhawat88@test.com",  
                    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 = System.Data.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:

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:

result-1.png

Figure 1.3: Result of User and UserProfile.

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 take a look at the following figure:

one-to-many.PNG

igure 1.4 One-to-many Relationship

The customer entity is as in the following:

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 Quanatity { get; set; }  
        public Decimal Price { get; set; }  
        public Int64 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, that 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.Quanatity).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 customer. The WillCascadeOnDelete() method configures whether or not cascade delete is on for the relationship.

Now, 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 = "raviendra@test.com",  
                                         AddedDate = DateTime.Now,  
                                         ModifiedDate = DateTime.Now,  
                                         IP = "1.1.1.1",  
                                         Orders = new List<Order>{  
                                            new Order  
                                            {  
                                                Quanatity =12,  
                                                Price =15,  
                                                AddedDate = DateTime.Now,  
                                                ModifiedDate = DateTime.Now,  
                                                 IP = "1.1.1.1",  
                                            },  
                                            new Order  
                                            {  
                                                Quanatity =10,  
                                                Price =25,  
                                                AddedDate = DateTime.Now,  
                                                ModifiedDate = DateTime.Now,  
                                                 IP = "1.1.1.1",  
                                            }  
                                        }  
                                     };  
                 context.Entry(customer).State = System.Data.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:

SELECT [ID],[Name],[Email],[AddedDate],[ModifiedDate],[IP]FROM [EFCodeFirst].[dbo].[Customers]  
SELECT [ID],[Quanatity],[Price],[CustomerId],[AddedDate],[ModifiedDate],[IP]FROM [EFCodeFirst].[dbo].[Orders]

result-2.png

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.

img]http://www.codeproject.com/KB/dotnet/796540/many-to-many.PNG

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 creates 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);  

           //property  
           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")  
                                    .MapLeftKey("StudentId")  
                                    .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");            
       }  
    }  
}

Now, 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 = System.Data.EntityState.Added;  
                context.Entry(course).State = System.Data.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:

SELECT [ID],[Name],[Age],[IsCurrent],[AddedDate],[ModifiedDate],[IP] FROM [EFCodeFirst].[dbo].[Students]  
SELECT [ID],[Name],[MaximumStrength],[AddedDate],[ModifiedDate],[IP] FROM [EFCodeFirst].[dbo].[Courses]  
SELECT [StudentId],[CourseId] FROM [EFCodeFirst].[dbo].[StudentCourse]

result-3.png

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 doubt, post as a comment or directly connect through https://twitter.com/ss_shekhawat.

Source

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
Go to topic listing
×
×
  • Create New...