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.
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 of {classname+Id} for primary key. So, we don't need any additional configuration for the 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; }
}
In this way we use data-annotation to configure different relationship between the 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)
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
We have already discussed in previous article that One-To-Many relationship is the relationship between two tables where each record from one table can be associated with more than one record in the another table. This relationship is the most common relationship found. A One-to-Many relationship becomes the many-to-one relationship if we look at it from the opposite direction.
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.
Conventions for One-to-Many Relationships
If we follow some of the default conventions then there will be no need to explicitly configure One-to-many relationship. .
Let's learn all the default convention one by one.
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. If you want the foreign key to be not null then you will have to configure that using fluent API.
Convention 2
The next convention is to include a collection navigation property in the principal entity.
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<Employee> Employees { get; set; }
}
In the above example, the Department entity includes a collection navigation property of type ICollection<Employee>. This also results in a one-to-many relationship between the Employee and Department entities.
Convention 3
If we include navigation properties at both ends then it will also result in a one-to-many relationship
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> Employee { get; set; }
}
In the above example, the Employee entity includes a navigation property of the Department type and the Department entity class includes a collection navigation property of the ICollection<Employee> type which results in a one-to-many relationship. This example will produce the same result in the database as convention 1.
Convention 4
we can obviously also define both primary key and navigation properties at both end , this means a fully defined relationship will also produce the same result. p>
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> 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
Many-to-Many Relationship
we have discussed the Many-to-Many relationship in previous article, we know that in such relationship many record from a table can be associated with many records of another table and we need to define the bridge or associate table for maintaining the related records easily. We can understand this with the following example.
Example: In the example of customers and product, a customer can buy many products and the same product can also be bought by many other customers, so to keep track who buys what we create an orders table which acts as a bridge between customers and product.
Now let's create the bridge table which will relate both customers and orders in an efficient way, we will use fluent api to define the custom name for bridge table.
EF includes default conventions for many-to-many relationships, if we include a collection navigation property at both ends then the Entity Framework automatically creates a bridge table for us. 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();
}
public int CustomerId { get; set; }
[Required]
public string CustomerName { get; set; }
public virtual ICollection<Product> 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<Customer> Employees { get; set; }
}
The following is the context class that includes the Employee and Course entities.
public class OrganizationDBContext : DBContext
{
public DbSet<Customer> Customers { get; set; }
public DbSet<Product> 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 plus 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.