SQL Server Relationship With Entity Framework In Depth

2023-02-20

筆記 SQL Server Relationship 以及 Entity Framework 物件的對應關係，深入各種可能的關係篇。

SQL Server Logo

One To One

Using Data Annotation Attributes

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

    [Required]
    public virtual EmployeeInfo EmployeeInfo { get; set; }
}

public class EmployeeInfo
{
    [Key, ForeignKey("Employee")]
    public int EmployeeId { get; set; }
    public string Address { get; set; }

    public virtual Employee Employee { get; set; }
}

Using Fluent API

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

    public virtual EmployeeInfo EmployeeInfo { get; set; }
}

public class EmployeeInfo
{
    public int EmployeeInfoId { get; set; }
    public string Address { get; set; }

    public virtual Employee Employee { get; set; }
}

protected override void OnModelCreating(DbModelBuilder modelBuilder)
{
    modelBuilder.Entity<Employee>()
                .HasRequired(e => e.EmployeeInfo)
                .WithRequiredPrincipal(ei => ei.Employee);
}

SQL Script

CREATE TABLE [dbo].[Employees] (
    [EmployeeId] [int] NOT NULL IDENTITY,
    [Name] [nvarchar](max),
    CONSTRAINT [PK_dbo.Employees] PRIMARY KEY ([EmployeeId])
)

CREATE TABLE [dbo].[EmployeeInfoes] (
    [EmployeeInfoId] [int] NOT NULL IDENTITY,
    [Address] [nvarchar](max),
    CONSTRAINT [PK_dbo.EmployeeInfoes] PRIMARY KEY ([EmployeeInfoId])
)

ALTER TABLE [dbo].[EmployeeInfoes]
  ADD CONSTRAINT [FK_dbo.EmployeeInfoes_dbo.Employees_EmployeeId]
  FOREIGN KEY ([EmployeeInfoId])
  REFERENCES [dbo].[Employees] ([EmployeeId])
ALTER TABLE [dbo].[Employees]
  ADD CONSTRAINT [FK_dbo.Employees_dbo.EmployeeInfoes_EmployeeInfoId]
  FOREIGN KEY ([EmployeeId])
  REFERENCES [dbo].[EmployeeInfoes] ([EmployeeInfoId])

Another Example:

CREATE TABLE User
(
    UserId INT PRIMARY KEY,
    UserName VARCHAR(50) NOT NULL,
    UserProfileId INT FOREIGN KEY REFERENCES UserProfile(UserProfileId) UNIQUE
);

CREATE TABLE UserProfile
(
    UserProfileId INT PRIMARY KEY,
    FirstName VARCHAR(50) NOT NULL,
    LastName VARCHAR(50) NOT NULL,
    Email VARCHAR(50) UNIQUE
);

Another Example:

CREATE TABLE Person (
    PersonID int PRIMARY KEY,
    Name varchar(50) NOT NULL
);

CREATE TABLE PersonDetail (
    PersonDetailID int PRIMARY KEY,
    PersonID int UNIQUE,
    Address varchar(100),
    PhoneNumber varchar(20)
);

ALTER TABLE PersonDetail
ADD CONSTRAINT FK_PersonDetail_Person
FOREIGN KEY (PersonID) REFERENCES Person(PersonID);

One To Zero Or One

Using Data Annotation Attributes

public class User
{
    public int UserId { get; set; }
    public string Name { get; set; }

    public virtual Address Address { get; set; }
}

public class Address
{
    [Key, ForeignKey("User")]
    public int UserId { get; set; }
    public string Street { get; set; }
    public string City { get; set; }
    public string State { get; set; }
    public string Zip { get; set; }

    [Required]
    public virtual User User { get; set; }
}

Using Fluent API

modelBuilder.Entity<User>()
    .HasOptional(u => u.Address)
    .WithRequired(a => a.User);

SQL Script

CREATE TABLE Employee (
    EmployeeId int PRIMARY KEY,
    FirstName varchar(50) NOT NULL,
    LastName varchar(50) NOT NULL,
    Email varchar(50) NULL,
    PhoneNumber varchar(20) NULL
);

CREATE TABLE EmployeeAddress (
    EmployeeId int PRIMARY KEY,
    Street varchar(50) NOT NULL,
    City varchar(50) NOT NULL,
    State varchar(2) NOT NULL,
    ZipCode varchar(10) NOT NULL,
    CONSTRAINT FK_EmployeeAddress_Employee FOREIGN KEY (EmployeeId)
        REFERENCES Employee(EmployeeId)
        ON DELETE CASCADE
        ON UPDATE CASCADE
);

Another Example:

CREATE TABLE Orders (
    OrderID int PRIMARY KEY,
    OrderDate date,
    CustomerID int FOREIGN KEY REFERENCES Customers(CustomerID)
);

CREATE TABLE Customers (
    CustomerID int PRIMARY KEY,
    FirstName varchar(50),
    LastName varchar(50),
    OrderID int FOREIGN KEY REFERENCES Orders(OrderID)
);

Zero Or One To Zero Or One

Using Data Annotation Attributes

First Example:

public class Person
{
    public int PersonId { get; set; }
    public string Name { get; set; }
    public virtual Address Address { get; set; }
}

public class Address
{
    [Key, ForeignKey("Person")]
    public int AddressId { get; set; }
    public string Street { get; set; }
    public string City { get; set; }
    public string State { get; set; }
    public string Zip { get; set; }
    public virtual Person Person { get; set; }
}

Another Example:

public class Person
{
    public int Id { get; set; }
    public string Name { get; set; }
    public int? PassportId { get; set; }
    public virtual Passport Passport { get; set; }
}

public class Passport
{
    public int Id { get; set; }
    public string Number { get; set; }
    public int? PersonId { get; set; }
    public virtual Person Person { get; set; }
}

Using Fluent API

First Example:

protected override void OnModelCreating(DbModelBuilder modelBuilder)
{
    modelBuilder.Entity<Person>()
        .HasOptional(p => p.Address)
        .WithOptionalPrincipal(a => a.Person)
        .Map(m => m.MapKey("PersonId"));

    base.OnModelCreating(modelBuilder);
}

Another Example:

protected override void OnModelCreating(DbModelBuilder modelBuilder)
{
    modelBuilder.Entity<Person>()
        .HasOptional(p => p.Passport)
        .WithOptionalPrincipal(p => p.Person)
        .Map(m => m.MapKey("PersonId"));
}

SQL Script

CREATE TABLE Person (
    Id int PRIMARY KEY,
    Name varchar(50),
    PassportId int FOREIGN KEY REFERENCES Passport(Id)
);

CREATE TABLE Passport (
    Id int PRIMARY KEY,
    Number varchar(50),
    PersonId int UNIQUE FOREIGN KEY REFERENCES Person(Id)
);

Zero Or One To Many

Using Data Annotation Attributes

public class User
{
    public int UserId { get; set; }
    public string UserName { get; set; }
    public virtual ICollection<Address> Addresses { get; set; }
}

public class Address
{
    public int AddressId { get; set; }
    public string Street { get; set; }
    public string City { get; set; }
    public string State { get; set; }
    
    [ForeignKey("User")]
    public int? UserId { get; set; }
    public virtual User User { get; set; }
}

Using Fluent API

modelBuilder.Entity<User>()
    .HasMany(u => u.Addresses)
    .WithOptional(a => a.User)
    .HasForeignKey(a => a.UserId);

Another Example:

public class Customer
{
    public int CustomerId { get; set; }
    public string Name { get; set; }
    public string Email { get; set; }
    public ICollection<Order> Orders { get; set; }
}

public class Order
{
    public int OrderId { get; set; }
    public DateTime OrderDate { get; set; }
    public decimal TotalAmount { get; set; }
    public int? CustomerId { get; set; }
    public Customer Customer { get; set; }
}

protected override void OnModelCreating(DbModelBuilder modelBuilder)
{
    modelBuilder.Entity<Customer>()
                .HasMany(c => c.Orders)
                .WithOptional(o => o.Customer)
                .HasForeignKey(o => o.CustomerId);
}

SQL Script

CREATE TABLE [Customer] (
    CustomerId int PRIMARY KEY,
    Name varchar(50),
    Email varchar(50)
)

CREATE TABLE [Order] (
    OrderId int PRIMARY KEY,
    OrderDate date,
    TotalAmount decimal(10, 2),
    CustomerId int NULL,
    FOREIGN KEY (CustomerId) REFERENCES Customer(CustomerId)
)

One To Many

Using Data Annotation Attributes

public class ParentEntity
{
    public int Id { get; set; }
    public string Name { get; set; }

    public virtual ICollection<ChildEntity> Children { get; set; }
}

public class ChildEntity
{
    public int Id { get; set; }
    public string Name { get; set; }

    [ForeignKey("Parent")]
    public int ParentId { get; set; }
    public virtual ParentEntity Parent { get; set; }
}

Using Fluent API

modelBuilder.Entity<ParentEntity>()
    .HasMany(p => p.Children)
    .WithRequired(c => c.Parent)
    .HasForeignKey(c => c.ParentId);

SQL Script

CREATE TABLE Customers (
    CustomerID int primary key,
    FirstName varchar(50),
    LastName varchar(50)
)

CREATE TABLE Orders (
    OrderID int primary key,
    OrderDate datetime,
    CustomerID int,
    foreign key (CustomerID) references Customers(CustomerID)
)

Another Example:

CREATE TABLE Orders (
    OrderID int PRIMARY KEY,
    OrderDate datetime,
    CustomerID int,
)

CREATE TABLE OrderDetails (
    OrderDetailID int PRIMARY KEY,
    OrderID int,
    ProductID int,
    Quantity int,
    Price decimal(10,2),
    FOREIGN KEY (OrderID) REFERENCES Orders(OrderID)
)

Another Example:

CREATE TABLE Authors (
  AuthorId INT PRIMARY KEY,
  AuthorName VARCHAR(50) NOT NULL
);

CREATE TABLE Books (
  BookId INT PRIMARY KEY,
  Title VARCHAR(50) NOT NULL,
  AuthorId INT,
  CONSTRAINT FK_AuthorId FOREIGN KEY (AuthorId)
    REFERENCES Authors (AuthorId)
);

Another Example:

CREATE TABLE Departments (
    Id INT PRIMARY KEY,
    Name NVARCHAR(50) NOT NULL
);

CREATE TABLE Employees (
    Id INT PRIMARY KEY,
    Name NVARCHAR(50) NOT NULL,
    DepartmentId INT FOREIGN KEY REFERENCES Departments(Id)
);

Many To Many

Using Data Annotation Attributes

public class Student
{
    public int StudentId { get; set; }
    public string Name { get; set; }
    public virtual ICollection<Course> Courses { get; set; }
}

public class Course
{
    public int CourseId { get; set; }
    public string Name { get; set; }
    public virtual ICollection<Student> Students { get; set; }
}

// The junction table
public class Enrollment
{
    [Key]
    public int EnrollmentId { get; set; }
    public int StudentId { get; set; }
    public int CourseId { get; set; }

    public virtual Student Student { get; set; }
    public virtual Course Course { get; set; }
}

Using Fluent API

protected override void OnModelCreating(DbModelBuilder modelBuilder)
{
    modelBuilder.Entity<Student>()
        .HasMany<Course>(s => s.Courses)
        .WithMany(c => c.Students)
        .Map(cs =>
        {
            cs.MapLeftKey("StudentId");
            cs.MapRightKey("CourseId");
            cs.ToTable("Enrollment");
        });
}

SQL Script

CREATE TABLE [dbo].[Enrollment] (
    [EnrollmentId] INT           IDENTITY (1, 1) NOT NULL,
    [StudentId]    INT           NOT NULL,
    [CourseId]     INT           NOT NULL,
    CONSTRAINT [PK_dbo.Enrollment] PRIMARY KEY CLUSTERED ([EnrollmentId] ASC),
    CONSTRAINT [FK_dbo.Enrollment_dbo.Student_StudentId]
      FOREIGN KEY ([StudentId])
      REFERENCES [dbo].[Student] ([StudentId])
      ON DELETE CASCADE,
    CONSTRAINT [FK_dbo.Enrollment_dbo.Course_CourseId]
      FOREIGN KEY ([CourseId])
      REFERENCES [dbo].[Course]  ([CourseId])
      ON DELETE CASCADE
);

Another Example:

CREATE TABLE Students (
    StudentId INT PRIMARY KEY,
    StudentName VARCHAR(50) NOT NULL
);

CREATE TABLE Courses (
    CourseId INT PRIMARY KEY,
    CourseName VARCHAR(50) NOT NULL
);

CREATE TABLE StudentCourses (
    StudentId INT,
    CourseId INT,
    PRIMARY KEY (StudentId, CourseId),
    FOREIGN KEY (StudentId) REFERENCES Students(StudentId),
    FOREIGN KEY (CourseId) REFERENCES Courses(CourseId)
);
#Database #SQLServer #MVC #ASPNET #EntityFramework #Dev