SQL Server Relationship With Entity Framework In Depth

1. One To One
2. One To Zero Or One
3. Zero Or One To Zero Or One
4. Zero Or One To Many
5. One To Many
6. Many To Many

---

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

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