Posted on 1/22/2021 2:08:11 AM by Admin

Configure Relationship using data annotations

One-to-Zero/One Relationship

Such a relationship exists when each record of one table is related to Zero or one record of the other table.

For example, If there are two entities 'Employee' (EmployeeId, First_Name,Last_Name, Email,Mobile)and 'Address'(EmployeeAddressId, Street,City,State,ZipCode). So, each Employee can have Zero or one Address.

One-to-Zero/One Relationship
One-to-Zero/One Relationship

Technically, A one-to-zero-or-one relationship happens when a primary key of one table becomes PK & FK in another table in a relational database such as SQL Server. So, we need to configure the above entities in such a way that EF creates the Employee and Address tables in the DB and makes the EmployeeId column in Employee table as PrimaryKey (PK) and EmployeeAddressId column in the Address table as PK and ForeignKey (FK) both.

Configure One-to-Zero-or-One Relationship using Data Annotation Attributes

Here, we will apply data annotation attributes on the Employee and EmployeeAddress entities to establish a one-to-zero-or-one relationship.

Constraint 1. EmployeeId column should be the primary key of Employee table.

Solution : To make a property , a primary key column in database table , we can either use [key] attribute or name it as Id or {Class name}Id in our Entity definition , In our case the Employee entity follows the default code-first convention as it includes the EmployeeId property which will be the key property. So, we don't need to apply any attributes on it because EF will make the EmployeeId column as a PrimaryKey in the Employees table in the database.

Constraint 2. EmployeeAddressId column should be both the primary key and Foreign key for Address table.

Solution: The EmployeeAddressId property follows the default convention for primary key. So, we don't need to apply any attribute for PK. However, we also need to make it a foreign key which points to EmployeeId of the Employee entity. So, apply [ForeignKey("Employee")] on the EmployeeAddressId property which will make it a foreign key for the Employee entity.


  public class Employee
   {
        public int EmployeeId { get; set; }
        public string FirstName { get; set; }
        public string LastName { get; set; }
        public string Email { get; set; }
        public int Phone { get; set; }
        public virtual EmployeeAddress Address { get; set; }
  }
     
  public class EmployeeAddress 
   {
    [ForeignKey("Employee")]
    public int EmployeeAddressId { get; set; }    
    public string Street{ get; set; }
    public string City { get; set; }
    public int Zipcode { get; set; }
    public string State { get; set; }
    public string Country { get; set; }
    public virtual Employee Employee { get; set; }
  }

Thus, you can use data annotation attributes to configure a one-to-zero-or-one relationship between two entities.

Note: Employee includes the EmployeeAddress navigation property and EmployeeAddress includes the Employee navigation property. With the one-to-zero-or-one relationship, a Employee can be saved without EmployeeAddress but the EmployeeAddress entity cannot be saved without the Employee entity. EF will throw an exception if you try to save the EmployeeAddress entity without the Employee entity.

One-to-One Relationship

Such a relationship exists when each record of one table is related to only one record of the other table but both are required.

For example, If there are two entities ‘Person' (Id, Name, Age, Address)and ‘AadhaarCard'(AadhaarCard'_Id, AadhaarCard'_No). So, each Person can have only one AadhaarCard and each AadhaarCard belongs to only one Person , but an AadharCard is an required enetity can never be zero (optional)

 One-to-One Relationship
One-to-One Relationship

Configure a One-to-One relationship using Data Annotations

we cannot configure this type of relationship with data annotations , this is where we use Fluent API. In the next article we will learn to configure relationship using fluent API.

One-to-Many or Many-to-One Relationship

Such a relationship exists when each record of one table can be related to one or more than one record of the other table. This relationship is the most common relationship found. A one-to-many relationship can also be said as a many-to-one relationship depending upon the way we view it.

For example, If there are two entity type ‘Employee' and ‘Department' then each ‘Department' can have more than one ‘Employee' but each ‘Employee' belongs to only one ‘Department'. In this example, we can say that each Department is associated with many Employee. So, it is a one-to-many relationship. But, if we see it the other way i.e many Employee is associated with one Department then we can say that it is a many-to-one relationship.

One-to-Many
One-to-Many

Conventions for One-to-Many Relationships

There are certain conventions in Entity Framework which if followed in entity classes (domain classes) will automatically result in a one-to-many relationship between two tables in the database. You don't need to configure anything else.

Let's look at an example of all the conventions which create a one-to-many relationship.

Convention 1

We want to establish a one-to-many relationship between the Employee and Department entities where many Employees are associated with one Department.It means that each Employee entity points to a Department. This can be achieved by including a navigation property of type Department in the Employee entity class, as shown below.


 public class Employee
  {
    public int Id { get; set; }
    public string Name { get; set; }
    public Department Department { get; set; }
 }

 public class Department
 {
    public int DepartmentId { get; set; }
    public string DepartmentName { get; set; }
 }

In the above example, the Employee class includes a navigation property of Department class. So, there can be many Employees in a single Department. This will result in a one-to-many relationship between the Employees and Departments table in the database, where the Employees table includes foreign key Department_DepartmentId

Notice that the reference property is nullable, so it creates a nullable foreign key column Department_DepartmentId in the Employees table. You can configure NotNull foreign key using fluent API.

Convention 2

Another convention is to include a collection navigation property in the principal entity as shown below.


 public class Employee
  {
    public int EmployeeId { get; set; }
    public string EmployeeName { get; set; }
  }

 public class Department
 {
    public int DepartmentId { get; set; }
    public string DepartmentName { get; set; }
    public string Section { get; set; }
    public ICollection Employees { get; set; } 
  }

In the above example, the Department entity includes a collection navigation property of type ICollection. This also results in a one-to-many relationship between the Employee and Department entities. This example produces the same result in the database as convention 1.

Convention 3

Including navigation properties at both ends will also result in a one-to-many relationship, as shown below.


  public class Employee
   {
    public int Id { get; set; }
    public string Name { get; set; }
    public Department Department { get; set; }
  }

  public class Department
  {
    public int DepartmentID { get; set; }
    public string DepartmentName { get; set; }
    public string Section { get; set; }   
    public ICollection Employee { get; set; }
  }

In the above example, the Employee entity includes a reference navigation property of the Department type and the Department entity class includes a collection navigation property of the ICollection type which results in a one-to-many relationship. This example produces the same result in the database as convention 1.

Convention 4

A fully defined relationship at both ends will create a one-to-many relationship, as shown below.


   public class Employee
   {
    public int Id { get; set; }
    public string Name { get; set; }   
    public int DepartmentId { get; set; }
    public Department Department { get; set; }
 }

   public class Department
   {
    public int DepartmentId { get; set; }
    public string DepartmentName { get; set; }   
    public ICollection Employee { get; set; }
  }

In the above example, the Employee entity includes foreign key property DepartmentId with its reference property Department. This will create a one-to-many relationship with the NotNull foreign key column in the Employees table.

If the data type of DepartmentId is nullable integer, then it will create a null foreign key.


   public class Employee
   {
    public int Id { get; set; }
    public string Name { get; set; }
    public int? DepartmentId { get; set; }
    public Department Department { get; set; }
  }

The above code snippet will create a nullable DepartmentId column in the database because we have used Nullable type (? is a shortcut for Nullable)

Many-to-Many Relationship

Such a relationship exists when each record of the first table can be related to one or more than one record of the second table and a single record of the second table can be related to one or more than one record of the first table. A many-to-many relationship can be seen as a two one-to-many relationship which is linked by a 'linking table' or 'associate table'. The linking table links two tables by having fields which are the primary key of the other two tables. We can understand this with the following example.

Example: If there are two entity type ‘Customer’ and ‘Product’ then each customer can buy more than one product and a product can be bought by many different customers.

Many-to-Many Relationship
Many-to-Many Relationship

Now, to understand the concept of the linking table here, we can have the ‘Order’ entity as a linking table that links the ‘Customer’ and ‘Product’ entity. We can break this many-to-many relationship into two one-to-many relationships. First, each ‘Customer’ can have many ‘Order’ whereas each ‘Order’ is related to only one ‘Customer’. Second, each ‘Order’ is related to only one Product whereas there can many orders for the same Product.

Many-to-Many Bridge
Many-to-Many Relationship

In the above concept of linking can be understood with the help of taking into consideration all the attributes of the entities 'Customer', 'Order' and 'Product'. We can see that the primary key of both 'Customer' and 'Product' entities are included in the linking table i.e 'Order' table. These keys act as foreign keys while referring to the respective table from the 'Order' table.

EF includes default conventions for many-to-many relationships. You need to include a collection navigation property at both ends. For example, the Customer class should have a collection navigation property of Product type, and the Product class should have a collection navigation property of Customer type to create a many-to-many relationship between them without any configuration, as shown below:


  public class Customer
   {
    public Customer() 
    {
        this.Product = new HashSet< Product >();
    }

    public int CustomerId { get; set; }
    [Required]
    public string CustomerName { get; set; }
    public virtual ICollection Products { get; set; }
 }
        
  public class Product
   {
    public Product ()
    {
        this.Customer = new HashSet< Customer >();
    }

    public int ProductId { get; set; }
    public string ProductName { get; set; }
    public virtual ICollection Employees { get; set; }
  }

The following is the context class that includes the Employee and Course entities.


   public class OrganizationDBContext : DBContext
   {  
    public DbSet Customers { get; set; }
    public DbSet Products { get; set; }       
    protected override void OnModelCreating(DbModelBuilder modelBuilder)
    {
        base.OnModelCreating(modelBuilder);
    }
 }

EF API will create Customers, Products and also the joining table CustomerProducts in the database for the above example. The CustomerProducts table will include the PK (Primary Key) of both tables - Customer_CustomerId & Product_ProductId.

Note: EF automatically creates a joining table with the name of both entities and the suffix 's', but obviously we want our bridge table to be named as Order, which again can't be configured with data annotations however we can configure it using Fluent API.