Welcome to today’s post.
In today’s post I will be showing you how to use an Entity Framework Code First approach within a data context to declare associations between table entities.
In previous posts I showed with Entity Framework Core how to create databases and migrate schema object changes to databases. In that post I showed how to apply changes made to the field definitions of a table within an existing data context using PowerShell Entity Framework Core migration commands.
In the sections that follow, I will first show how table entity relations are defined within a typical POCO class, then how they are defined with the Fluent API. I will then show how the table entity relations are defined within the schema definitions within the SQL object explorer of the underlying SQL database.
A Typical Entity-Relation Model Mapping in Entity Framework Core
Below we have a typical Entity Relation model below showing foreign key one-many links between one parent table and two different detail tables:
The POCO class definition for the above Book table is shown below:
using System;
using System.Collections.Generic;
using System.ComponentModel.DataAnnotations;
using System.ComponentModel.DataAnnotations.Schema;
namespace BookLoan.Models
{
public class BookViewModel
{
public int ID { get; set; }
[Required]
public string Title { get; set; }
[Required]
public string Author { get; set; }
public int YearPublished { get; set; }
public string Genre { get; set; }
public string Edition { get; set; }
public string ISBN { get; set; }
[Required]
public string Location { get; set; }
public DateTime DateCreated { get; set; }
public DateTime DateUpdated { get; set; }
public virtual ICollection<LoanViewModel> Loans { get; set; }
public virtual ICollection<ReviewViewModel> Reviews { get; set; }
}
}
What you will notice is the two foreign key reference property accessors to the detail tables, Loans and Reviews are represented declaratively with the two properties:
public virtual ICollection<LoanViewModel> Loans { get; set; }
public virtual ICollection<ReviewViewModel> Reviews { get; set; }
With the next detail table, Review, the POCO class is shown below:
using System;
using System.Collections.Generic;
using System.ComponentModel.DataAnnotations;
using System.ComponentModel.DataAnnotations.Schema;
using System.Linq;
using System.Threading.Tasks;
namespace BookLoan.Models
{
public class ReviewViewModel
{
public int ID { get; set; }
public string Reviewer { get; set; }
[NotMapped]
public string Author { get; set; }
[NotMapped]
public string Title { get; set; }
public string Heading { get; set; }
public string Comment { get; set; }
public int Rating { get; set; }
public DateTime DateReviewed { get; set; }
public DateTime DateCreated { get; set; }
public DateTime DateUpdated { get; set; }
public int BookID { get; set; }
public bool IsVisible { get; set; }
public string Approver { get; set; }
public virtual BookViewModel Book { get; set; }
}
}
The foreign key reference back to the Book parent table is shown here:
public virtual BookViewModel Book { get; set; }
For the Loan table, the POCO class is shown below:
using System;
using System.Collections.Generic;
using System.ComponentModel.DataAnnotations;
using System.ComponentModel.DataAnnotations.Schema;
using System.Linq;
using System.Threading.Tasks;
namespace BookLoan.Models
{
public class LoanViewModel
{
public int ID { get; set; }
public string LoanedBy { get; set; }
public DateTime DateLoaned { get; set; }
public DateTime DateDue { get; set; }
public DateTime DateReturn { get; set; }
public bool OnShelf { get; set; }
public DateTime DateCreated { get; set; }
public DateTime DateUpdated { get; set; }
public int BookID { get; set; }
[NotMapped]
public virtual BookViewModel Book { get; set; }
public string ReturnMethod { get; set; }
}
}
The foreign key reference back to the Book parent table is shown here:
public virtual BookViewModel Book { get; set; }
In the next section, I will show how to define the table relationships using the Fluent API.
Defining the Entity Relationship with the Fluent API
With all three tables, any foreign key references properties in the POCO model are prefixed by the virtual keyword. The properties are also known as navigation properties.
To define the foreign key relationships, we can use the Fluent API, which allows us to chain together the cardinality constraint conditions.
Within the data context, after we have declared the table entities, we then define the associations between the tables, which is shown below:
public class ApplicationDbContext : DbContext
{
public ApplicationDbContext(DbContextOptions<ApplicationDbContext> options)
: base(options)
{
}
protected override void OnModelCreating(ModelBuilder builder)
{
base.OnModelCreating(builder);
builder.Entity<BookViewModel>().ToTable("Books");
builder.Entity<LoanViewModel>().ToTable("Loans");
builder.Entity<ReviewViewModel>().ToTable("Reviews");
…
builder.Entity<LoanViewModel>().
HasOne(p => p.Book).
WithMany(q => q.Loans).
HasForeignKey(p => p.BookID).HasConstraintName("FK_Book_Loan");
builder.Entity<ReviewViewModel>().
HasOne(p => p.Book).
WithMany(q => q.Reviews).
HasForeignKey(p => p.BookID).HasConstraintName("FK_Book_Review");
}
public DbSet<LoanViewModel> Loans { get; set; }
public DbSet<ReviewViewModel> Reviews { get; set; }
public DbSet<BookViewModel> Books { get; set; }
}
I will explain how the Loans and Books tables are associated.
Each Loan record is associated with exactly one Book record.
In turn, each Book record is associated with many Loan records.
This is expressed with the Fluent API below:
builder.Entity<LoanViewModel>().
HasOne(p => p.Book).
WithMany(q => q.Loans).
HasForeignKey(p => p.BookID).HasConstraintName("FK_Book_Loan");
In the next section, I will show how the above table relations look within the table definitions within the SQL database in the SQL Object Explorer.
Viewing the Table Relationships within the SQL Object Explorer
In SSMS, we can see the foreign key definition for the Loan table below:
The foreign key relationship FK_Loan_Book is shown below:
The lookup detail table Loans, its field BookID, which is a foreign key, links to the base table, Book and its primary key ID.
The definition between the keys is shown below:
The Fluent API HasOne() method describes the one-one part of the relationship between the two tables. The HasMany() method describes the one-many part of the relationship between the two tables.
We can use the Fluent API to provide navigation of properties within entities that are accessed using Entity Framework Core LINQ to Entities. Using one of the data entity loading patterns, which I discussed in my previous posts on Lazy Loading, Eager Loading, or with Explicit Loading, we can control the loading of detail data in a data context and control the performance of the application.
With applications that have tables contained within the same database, the use of foreign key constraints can be useful in enforcing referential integrity within data access methods within application web API services. Where the respective tables are hosted within separate databases, such as with a Microservice architecture, the use of foreign key constraints is not applicable, and we would then require key validation being enforced with external web API methods or through data synchronization.
That is all for today’s post.
I hope you found this post useful and informative.
Andrew Halil is a blogger, author and software developer with expertise of many areas in the information technology industry including full-stack web and native cloud based development, test driven development and Devops.
