Relationships in Entity Framework Core

Back to: ASP.NET Core Web API Tutorials

Relationships in Entity Framework Core with Examples

Entity Framework Core (EF Core) relationships allow us to model real-world associations between business entities. In an E-Commerce application, every entity interacts with others, customers place orders, orders contain products, products belong to categories, and so on. At the end of this session, you will understand the following pointers:

1. What is a Relationship in Entity Framework Core, and why is it important?
2. What are the three main types of relationships in EF Core: One-to-One, One-to-Many, and Many-to-Many?
3. How do you model a One-to-One, One-to-Many, and Many-to-Many relationships using default conventions in Code First?
4. What are Principal and Dependent Entities, and how do they affect relationship configuration?
5. What are Navigation Properties, and how do they enable traversal between related entities?
6. What are Primary Keys and Foreign Keys, and what role do they play in maintaining relationships?
7. What’s the difference between Required and Optional relationships in EF Core?
8. How can HasData be used to seed sample relational data across multiple related entities?

Why Do We Need Relationships in Entity Framework Core?

In a relational database, data is spread across multiple tables, and those tables must be able to “talk” to each other. Relationships ensure this happens correctly and efficiently. Every business domain has entities that are logically connected to one another. For example, in an Ecommerce application:

• A Customer places Orders.
• Each Order contains multiple Products.
• Each Product belongs to a specific Category.

These are not random objects. They are connected through Relationships that define how one entity interacts with another.

Imagine if we had a Customer table, a Product table, and an Order table, but there was no link between them. We could never tell which customer placed which order, or which products were ordered together. That’s why we use Relationships; they give meaning and connectivity to data.

What Exactly Is a Relationship in EF Core?

A relationship in Entity Framework Core defines how two or more entities are connected to each other, both in the C# object model and in the underlying database schema. It tells EF Core how one entity depends on another, how they can reference each other, and how those connections are represented through foreign keys and navigation properties.

In the C# Object Model (Code Level):

• A relationship is expressed through Navigation Properties, for example, a Customer entity having a collection of Orders, or an Order having a single Customer reference.
• These properties let you navigate between related entities directly in code (like customer.Orders or order.Customer), creating an object-oriented link between them.

In the SQL Database (Data Level):

• The same relationship is represented by primary and foreign key constraints.
• EF Core automatically creates the appropriate foreign key columns and referential integrity constraints that link records between tables.

So, in Simple Words

• A relationship in EF Core is a mapping rule that defines how two entities are connected, how they refer to each other in C#, and how they are linked through primary and foreign keys in the database.
• It allows EF Core to understand “who owns whom,” “who depends on whom,” and “how to navigate between them”.

What Are the Different Types of Relationships in EF Core?

EF Core supports three core types of relationships between entities, reflecting real-world relational database design:

1. One-to-One (1:1) Relationship
2. One-to-Many (1:M) Relationship
3. Many-to-Many (M:M) Relationship

Let’s explore each in detail with realistic business examples.

One-to-One (1:1) Relationship

A One-to-One (1:1) relationship means that each record in one entity corresponds to exactly one record in another entity, and vice versa. In simple terms, one row in Table A is linked to one and only one row in Table B.

In the object-oriented world (C# classes), this means one instance of an entity can be associated with exactly one instance of another entity. This type of relationship is often used when you want to split entity data into multiple tables for better organization, modularity, or performance, while still maintaining a one-to-one logical link between them.

Example: Customer ↔ Profile

In an E-Commerce application:

• Each Customer has exactly one Profile containing their extended personal information.
• Each Profile belongs to exactly one Customer, and cannot exist without it.

So, both tables, Customers and Profiles, have a one-to-one link.

Real-World Use Case

Let’s say your system has a Customer table that stores basic details like:

• Name
• Email
• Phone Number

However, as the system grows, you decide to store Additional Personal Information such as:

• Gender
• Date of Birth
• Display Name
• Profile Picture

Instead of adding these extra columns to the Customer table (which may not always be needed), create a separate Profile table and establish a One-to-One relationship between the two.

EF Core Perspective

In EF Core:

• The Customer acts as the Principal Entity; it can exist on its own.
• Profile acts as the Dependent Entity; it cannot exist without its associated customer.
• The Primary Key in the Profile table also serves as the Foreign Key referencing the Customer table.

This tells EF Core that “A Customer owns one Profile, and a Profile is tied exclusively to a single Customer.”

In Simple Words

• A One-to-One Relationship connects two entities that describe different aspects of the same logical object.
• Think of it as two parts of the same identity; the Customer is who they are, and the Profile adds personal details about them.

One-to-Many (1:M) Relationship

A One-to-Many (1:M) relationship means that a single record in the Principal Entity can be linked to multiple records in the Dependent Entity. However, each record in the dependent entity is always related to only one principal entity.

This is the most common and practical type of relationship in real-world systems, especially in E-Commerce, where customers, products, and orders naturally follow this pattern. In EF Core, such relationships are typically defined using:

• A Collection Navigation Property (ICollection<T>) on the principal side, and
• A Foreign Key Property on the dependent side with a Reference Navigation Property.

Example 1: Customer ↔ Address

• A Customer can have multiple Addresses, for example, Home, Office, Billing, or Shipping.
• Each Address belongs to exactly one Customer.

Here:

• Customer → Principal Entity
• Address → Dependent Entity (It contains a foreign key pointing to the Customer table)

Real-World Meaning: This setup allows customers to manage multiple delivery or billing addresses. It also ensures that every address is tied to a valid customer, meaning you can’t have an address without a customer.

Example 2: Category ↔ Product

• A single Category (e.g., “Electronics”) can include many Products (e.g., Laptop, Camera, TV).
• Each Product is linked to only one Category.

Here:

• Category → Principal Entity
• Product → Dependent Entity (It contains a foreign key pointing to the Category table)

Real-World Meaning: This structure helps organize products into logical groups. It enables category-based filtering, navigation, and reporting, for example, viewing all products under “Electronics” or calculating total sales per category.

Example 3: Customer ↔ Order

• A single Customer can place multiple Orders over time.
• Each Order belongs to exactly one Customer.

Here:

• Customer → Principal Entity
• Order → Dependent Entity (It contains a foreign key pointing to the Customer table)

Real-World Meaning: This relationship reflects a customer’s purchase history. It allows you to:

• Retrieve all orders placed by a particular customer, or
• Trace any order back to its owner.

It also ensures that an order cannot exist without an associated customer, maintaining clear ownership and referential consistency.

In Simple Words, a One-to-Many relationship means One parent → Many children. It models real-world scenarios like:

• One Customer has many Addresses and Orders.
• One Category containing many Products.

EF Core automatically builds and manages these links using Foreign Keys and Navigation Properties, keeping both our database and our object model perfectly aligned.

Many-to-Many (M:M) Relationship

A Many-to-Many (M:M) relationship means that multiple records in one entity can be linked to multiple records in another entity, and vice versa. In simple terms, each record in Entity A can be associated with several records in Entity B, and each record in Entity B can also be associated with several records in Entity A.

Because relational databases don’t support direct many-to-many relationships between two tables, Entity Framework Core uses a junction (bridge or joining) table, a third table that contains foreign keys from both entities. This bridge table connects the two entities and can also store additional information about their association.

Example: Order ↔ Product (via OrderItem)

In an E-Commerce system:

• A single Order can contain multiple Products.
• A single Product can appear in multiple Orders.

This is a perfect real-world case of a Many-to-Many relationship.

How EF Core Represents It

To represent this relationship, we introduce a separate entity, OrderItem, which serves as a linking table between Orders and Products. The OrderItem entity includes:

• OrderId → Foreign Key referencing Orders
• ProductId → Foreign Key referencing Products

Together, these two columns define how orders and products are connected. However, unlike a simple join table, OrderItem also carries Extra Information that describes the relationship in more detail, such as:

• Quantity: How many units of a product were ordered?
• UnitPrice: The price per item at the time of purchase (helpful in tracking historical prices).
• Discount: Optional, if your business logic includes order-level discounts.

In Simple Words

A Many-to-Many relationship connects two entities through a third entity that acts as a bridge. In our e-commerce system:

• The Order represents the customer’s purchase.
• The Product represents items for sale.
• OrderItem connects them, showing which products belong to which orders, along with their quantities and prices.

This design allows EF Core to handle complex relationships with precision, flexibility, and performance, ensuring both database normalization and real-world accuracy.

How to Implement Relationships Between Entities in EF Core

Relationships in EF Core can be established using three approaches:

1. Convention-Based (Default Approach): EF Core automatically infers relationships based on navigation properties and naming conventions.
2. Data Annotations (Attributes): You explicitly decorate properties using attributes like [ForeignKey], [Required], etc.
3. Fluent API (Programmatic Configuration): You configure relationships in OnModelCreating() for precise control.

E-Commerce Order Management API

The E-Commerce Web API, built with ASP.NET Core 8 and Entity Framework Core, is a scalable, real-world online shopping backend system. It models how customers browse, order, and purchase products using a relational SQL Server database, while maintaining clean separation of concerns through entities, relationships, and data-access abstractions.

This project demonstrates how EF Core manages different types of entity relationships (1:1, 1:M, M:M) using the EF Core Code-First Approach. It focuses on designing a domain-driven model that captures all the logical connections found in a real-life commerce environment.

Key Entities and Their Roles

• Customer → The main user of the system. Each customer can have one Profile, multiple Addresses, and multiple Orders.
• Profile → Holds personal information like gender and birth date. It has a strict 1:1 relationship with its customer.
• Address → Stores delivery and billing locations. A Customer can own many addresses (1:M).
• Category → Organizes products into logical groups such as “Electronics” or “Clothing.”
• Product → Represents sellable items, each belonging to exactly one Category but linked to many Orders via OrderItems.
• Order → Captures a customer’s purchase record and links to OrderItems, Addresses, and OrderStatus.
• OrderItem → Acts as a junction table connecting Orders and Products, supporting a Many-to-Many relationship and storing transactional details like quantity and price.
• OrderStatus → A lookup entity seeded from an enum, ensuring standardized status values like Pending, Confirmed, Shipped, Delivered, and Cancelled.
• BaseAuditableEntity → A reusable base class that provides auditing and soft-delete columns (CreatedAt, UpdatedAt, CreatedBy, IsActive), ensuring consistency across all tables.

Step 1: Create a New Project using Visual Studio

1. Open Visual Studio 2022 → Click Create a new project
2. Choose ASP.NET Core Web API → Click Next
3. Name your project → ECommerceApp
4. Choose framework → .NET 8.0 (Long-term support)
5. Click Create

Step 2: Install Required EF Core Packages

Open the Package Manager Console in Visual Studio: Tools → NuGet Package Manager → Package Manager Console. Then run the following commands:

• Install-Package Microsoft.EntityFrameworkCore
• Install-Package Microsoft.EntityFrameworkCore.SqlServer
• Install-Package Microsoft.EntityFrameworkCore.Tools

These packages provide:

• Microsoft.EntityFrameworkCore → Core EF ORM features.
• Microsoft.EntityFrameworkCore.SqlServer → SQL Server provider.
• Microsoft.EntityFrameworkCore.Tools → CLI commands like Add-Migration and Update-Database.

Step 3: Create the Folder Structure

Inside your project, create the following folders to organize your files:

• Models → Contains all entity classes that represent the database tables and define the relationships between them.
• Data → Holds the AppDbContext class and any data-related configurations like seeding or migrations.
• Enums → Stores enumerations such as OrderStatusEnum, used for predefined constant values throughout the application.
• DTOs → Contains Data Transfer Objects for handling structured request and response models between API endpoints and clients.

Step 4: Create Order Status Enum

Create a class file named OrderStatusEnum.cs within the Enums folder, then copy and paste the following code. The Enum is used in code to express allowed order states in a type-safe way and to seed the OrderStatus table, keeping domain logic readable while enforcing valid values at the database level.

namespace ECommerceApp.Enums
{
    // Enum used in application logic to represent possible order statuses.
    public enum OrderStatusEnum
    {
        Pending = 1,
        Confirmed = 2,
        Shipped = 3,
        Delivered = 4,
        Cancelled = 5
    }
}

Step 5: Create Entity Classes

We will create 9 Entities: BaseAuditableEntity, Customer, Profile, Address, Category, Product, OrderStatus, Order, and OrderItem. We will use Default Conventions only (no Data Annotations, no Fluent API). Relationships are automatically inferred from navigation properties.

Base Auditable Entity

Create a class file named BaseAuditableEntity.cs within the Models folder, then copy and paste the following code. This is a base class inherited by all entities to maintain common audit fields such as creation and update timestamps, created by, updated by, and an IsActive flag for soft deletes. It ensures consistency and reusability of audit tracking across all tables without duplicating code.

namespace ECommerceApp.Models
{
    // Base class that provides soft-delete and audit tracking.
    // Every domain entity inherits this for consistency.
    public abstract class BaseAuditableEntity
    {
        // Indicates whether the record is active (used for soft delete)
        public bool IsActive { get; set; } = true;

        // Audit Columns
        public DateTime CreatedAt { get; set; } = DateTime.UtcNow;   // Record creation timestamp
        public DateTime? UpdatedAt { get; set; }                     // Last update timestamp
        public string? CreatedBy { get; set; }                       // Optional: can be set by middleware later
        public string? UpdatedBy { get; set; }                       // Optional: for tracking modifications
    }
}

Customer Entity

Create a class file named Customer.cs within the Models folder, then copy and paste the following code. The Customer class represents a registered user in the system. It serves as a Principal Entity for related data such as Profiles, Addresses, and Orders. It holds basic customer details such as name, email, and phone number, and maintains 1:1, 1:M, and M:M relationships through navigation properties.

namespace ECommerceApp.Models
{
    // Principal for Profile, Addresses, and Orders
    public class Customer : BaseAuditableEntity
    {
        // Primary Key (use explicit name to be clear)
        public int CustomerId { get; set; }

        // Basic Customer Info (Required by default through non-nullable refs)
        public string Name { get; set; } = null!;
        public string Email { get; set; } = null!;
        public string Phone { get; set; } = null!;

        // 1:1 → A customer may have one Profile
        public virtual Profile? Profile { get; set; }

        // 1:M → A customer can have many addresses and orders
        public virtual ICollection<Address>? Addresses { get; set; }
        public virtual ICollection<Order>? Orders { get; set; }
    }
}

Profile Entity

Create a class file named Profile.cs within the Models folder, then copy and paste the following code. The Profile class stores extended customer personal details, such as gender, display name, and date of birth. It has a one-to-one (1:1) relationship with the Customer, meaning each customer has exactly one profile, and its primary key is also the foreign key referencing the customer.

using System.ComponentModel.DataAnnotations;
namespace ECommerceApp.Models
{
    // Dependent in the 1:1 relationship with Customer
    // PK = FK (same property) is the canonical required 1:1 pattern.
    public class Profile : BaseAuditableEntity
    {
        // Both Primary Key and Foreign Key to Customer
        [Key] // Keep this to make the PK explicit for readers
        public int CustomerId { get; set; }

        // Required 1:1 nav back to principal Customer
        public virtual Customer Customer { get; set; } = null!;

        // Extra profile info
        public string DisplayName { get; set; } = null!;
        public string Gender { get; set; } = null!;
        public DateTime DateOfBirth { get; set; }
    }
}

Address Entity

Create a class file named Address.cs within the Models folder, then copy and paste the following code. The Address class stores physical addresses used for billing or shipping. It has a one-to-many (1:M) relationship with the Customer; one customer can have multiple addresses, but each address belongs to a single customer. It includes address-related fields like street, city, postal code, and country.

namespace ECommerceApp.Models
{
    // Dependent in 1:M with Customer
    public class Address : BaseAuditableEntity
    {
        public int AddressId { get; set; }
        public string Line1 { get; set; } = null!;
        public string? Line2 { get; set; }
        public string Street { get; set; } = null!;
        public string City { get; set; } = null!;
        public string PostalCode { get; set; } = null!;
        public string Country { get; set; } = null!;

        // REQUIRED Relationship: Every Address must belong to a Customer
        public int CustomerId { get; set; }
        public virtual Customer Customer { get; set; } = null!;
    }
}

Category Entity

Create a class file named Category.cs within the Models folder, then copy and paste the following code. The Category class represents a logical grouping of products, such as “Electronics” or “Books.” It is a Principal Entity in a one-to-many relationship with Product. Each category can have multiple products, enabling easier organization, filtering, and reporting of items by category.

namespace ECommerceApp.Models
{
    // Principal in 1:M with Product
    public class Category : BaseAuditableEntity
    {
        public int CategoryId { get; set; }
        public string Name { get; set; } = null!;
        public string? Description { get; set; }

        // One Category → Many Products
        public virtual ICollection<Product>? Products { get; set; }
    }
}

Product Entity

Create a class file named Product.cs within the Models folder, then copy and paste the following code. The Product class represents individual items available for purchase. It belongs to one category and can appear in multiple orders through the OrderItem joining entity. It contains properties like name, price, stock, SKU, and description, making it central to the product catalog.

using System.ComponentModel.DataAnnotations.Schema;
namespace ECommerceApp.Models
{
    // Dependent in 1:M with Category, and principal for OrderItems
    public class Product : BaseAuditableEntity
    {
        public int ProductId { get; set; }
        public string Name { get; set; } = null!;

        [Column(TypeName = "decimal(18,2)")]
        public decimal Price { get; set; }
        public int Stock { get; set; }
        public string SKU { get; set; } = null!;
        public string? Description { get; set; }

        // Required FK to Category (1 Product belongs to 1 Category)
        public int CategoryId { get; set; } 
        public virtual Category Category { get; set; } = null!;

        // Many-to-Many → OrderItem acts as the bridge entity
        public virtual ICollection<OrderItem>? OrderItems { get; set; }
    }
}

OrderStatus Entity

Create a class file named OrderStatus.cs within the Models folder, then copy and paste the following code. The OrderStatus class serves as a Master Lookup Table for storing predefined order states such as Pending, Confirmed, Shipped, Delivered, and Cancelled. It maintains a one-to-many relationship with the Order entity, ensuring consistent tracking of an order’s progress throughout its lifecycle.

namespace ECommerceApp.Models
{
    // Represents a master table for all possible order statuses.
    // The enum values are seeded here for database persistence.
    public class OrderStatus : BaseAuditableEntity
    {
        public int OrderStatusId { get; set; }     // Primary Key
        public string Name { get; set; } = null!;  // e.g., "Pending", "Shipped", etc.
        public string? Description { get; set; }

        // Navigation Property (1:M) → One status can be assigned to many orders
        public virtual ICollection<Order>? Orders { get; set; }
    }
}

Order Entity

Create a class file named Order.cs within the Models folder, then copy and paste the following code. The Order class represents a single placed order. It connects customers, addresses, and products through various relationships: 1:M with OrderItems, 1:M with Customer, and M:M via OrderItem with Product. It tracks order details, including the total amount, order date, and order status.

using System.ComponentModel.DataAnnotations.Schema;
namespace ECommerceApp.Models
{
    // Dependent entity in 1:M with Customer
    // Principal in 1:M with OrderItems
    public class Order : BaseAuditableEntity
    {
        public int OrderId { get; set; }
        public DateTime OrderDate { get; set; }

        // REQUIRED: Each Order should have a valid Status
        public int OrderStatusId { get; set; }
        public virtual OrderStatus OrderStatus { get; set; } = null!;

        [Column(TypeName = "decimal(18,2)")]
        public decimal TotalAmount { get; set; }

        // Optional: Billing & Shipping addresses are Optional
        public int? ShippingAddressId { get; set; }
        public virtual Address? ShippingAddress { get; set; } 
        public int? BillingAddressId { get; set; }
        public virtual Address? BillingAddress { get; set; } 

        // REQUIRED: Who placed it
        public int CustomerId { get; set; }
        public virtual Customer Customer { get; set; } = null!;

        // 1:M → Each Order can have multiple items
        public virtual ICollection<OrderItem> OrderItems { get; set; } = null!;
    }
}

OrderItem Entity

Create a class file named OrderItem.cs within the Models folder, then copy and paste the following code. The OrderItem class is a Junction Entity between Orders and Products, enabling a many-to-many relationship. It stores item-level details, such as product ID, quantity, and unit price, for each product in an order.

using System.ComponentModel.DataAnnotations.Schema;
namespace ECommerceApp.Models
{
    // Dependent in 1:M with Order
    // Dependent in 1:M with Product
    // This is the Joining Entity enabling a Many-to-Many between Orders and Products.
    public class OrderItem : BaseAuditableEntity
    {
        public int OrderItemId { get; set; }
        public int Quantity { get; set; }

        [Column(TypeName = "decimal(18,2)")]
        public decimal UnitPrice { get; set; } // snapshot of price at purchase time

        // Foreign Keys
        // REQUIRED: each OrderItem belongs to an Order
        public int OrderId { get; set; }
        public virtual Order Order { get; set; } = null!;

        // REQUIRED: each OrderItem refers to a Product
        public int ProductId { get; set; }
        public virtual Product Product { get; set; } = null!;
    }
}

Note: All navigations are virtual, so you can later enable lazy loading if you wish; for now, we will use eager loading in controller reads.

Step 6: Create the DbContext

Create a class file named AppDbContext.cs within the Data folder, then copy and paste the following code. The AppDbContext class is the EF Core bridge to the SQL Server database. It defines all DbSet properties for entities, manages relationships automatically through conventions, and seeds initial data for Customers, Products, Categories, Orders, and Order Statuses using the HasData() method.

using ECommerceApp.Models;
using Microsoft.EntityFrameworkCore;

namespace ECommerceApp.Data
{
    public class AppDbContext : DbContext
    {
        public AppDbContext(DbContextOptions<AppDbContext> options) : base(options) { }

        public DbSet<Customer> Customers { get; set; }
        public DbSet<Profile> Profiles { get; set; }
        public DbSet<Address> Addresses { get; set; }
        public DbSet<Category> Categories { get; set; }
        public DbSet<Product> Products { get; set; }
        public DbSet<Order> Orders { get; set; }
        public DbSet<OrderItem> OrderItems { get; set; }
        public DbSet<OrderStatus> OrderStatuses { get; set; }

        protected override void OnModelCreating(ModelBuilder modelBuilder)
        {
            DateTime seedDate = new(2025, 01, 01, 10, 00, 00);

            // Order Status
            modelBuilder.Entity<OrderStatus>().HasData(
                new OrderStatus { OrderStatusId = 1, Name = "Pending", Description = "Awaiting confirmation", CreatedAt = seedDate },
                new OrderStatus { OrderStatusId = 2, Name = "Confirmed", Description = "Confirmed by admin", CreatedAt = seedDate },
                new OrderStatus { OrderStatusId = 3, Name = "Shipped", Description = "Dispatched to courier", CreatedAt = seedDate },
                new OrderStatus { OrderStatusId = 4, Name = "Delivered", Description = "Delivered successfully", CreatedAt = seedDate },
                new OrderStatus { OrderStatusId = 5, Name = "Cancelled", Description = "Cancelled by user/system", CreatedAt = seedDate }
            );

            // Customers
            modelBuilder.Entity<Customer>().HasData(
                new Customer { CustomerId = 1, Name = "Pranaya Rout", Email = "pranaya@example.com", Phone = "9876543210", CreatedAt = seedDate },
                new Customer { CustomerId = 2, Name = "Rakesh Kumar", Email = "rakesh@example.com", Phone = "9876543211", CreatedAt = seedDate }
            );

            // Profiles 
            modelBuilder.Entity<Profile>().HasData(
                new Profile { CustomerId = 1, DisplayName = "Pranaya", Gender = "Male", DateOfBirth = new(1990, 05, 10), CreatedAt = seedDate },
                new Profile { CustomerId = 2, DisplayName = "Rakesh", Gender = "Female", DateOfBirth = new(1992, 08, 22), CreatedAt = seedDate }
            );

            // Addresses
            modelBuilder.Entity<Address>().HasData(
                new Address { AddressId = 1, Line1 = "123 Market Street", Street = "Main Rd", City = "Bhubaneswar", PostalCode = "751001", Country = "India", CustomerId = 1, CreatedAt = seedDate },
                new Address { AddressId = 2, Line1 = "Tech Park Avenue", Street = "Silicon Street", City = "Cuttack", PostalCode = "753001", Country = "India", CustomerId = 1, CreatedAt = seedDate },
                new Address { AddressId = 3, Line1 = "45 Lake View", Street = "West Road", City = "Bhubaneswar", PostalCode = "751010", Country = "India", CustomerId = 2, CreatedAt = seedDate }
            );

            // Categories
            modelBuilder.Entity<Category>().HasData(
                new Category { CategoryId = 1, Name = "Electronics", Description = "Electronic Devices", CreatedAt = seedDate },
                new Category { CategoryId = 2, Name = "Books", Description = "Educational and Fiction", CreatedAt = seedDate }
            );

            // Products
            modelBuilder.Entity<Product>().HasData(
                new Product { ProductId = 1, Name = "Wireless Mouse", Price = 1200, Stock = 50, SKU = "ELEC-MOUSE-001", CategoryId = 1, CreatedAt = seedDate },
                new Product { ProductId = 2, Name = "Bluetooth Headphones", Price = 2500, Stock = 40, SKU = "ELEC-HEAD-002", CategoryId = 1, CreatedAt = seedDate },
                new Product { ProductId = 3, Name = "C# Programming Book", Price = 899, Stock = 100, SKU = "BOOK-CSPROG-003", CategoryId = 2, CreatedAt = seedDate }
            );

            // Orders
            modelBuilder.Entity<Order>().HasData(
                new Order { OrderId = 1, OrderDate = seedDate, OrderStatusId = 2, TotalAmount = 3700, ShippingAddressId = 1, BillingAddressId = 2, CustomerId = 1, CreatedAt = seedDate },
                new Order { OrderId = 2, OrderDate = seedDate.AddDays(1), OrderStatusId = 3, TotalAmount = 899, ShippingAddressId = 3, BillingAddressId = 3, CustomerId = 2, CreatedAt = seedDate }
            );

            // OrderItems
            modelBuilder.Entity<OrderItem>().HasData(
                new OrderItem { OrderItemId = 1, OrderId = 1, ProductId = 1, Quantity = 1, UnitPrice = 1200, CreatedAt = seedDate },
                new OrderItem { OrderItemId = 2, OrderId = 1, ProductId = 2, Quantity = 1, UnitPrice = 2500, CreatedAt = seedDate },
                new OrderItem { OrderItemId = 3, OrderId = 2, ProductId = 3, Quantity = 1, UnitPrice = 899, CreatedAt = seedDate }
            );
        }
    }
}

Step 7: Configure Database Connection

Please add the database connection string in the appsettings.json file as follows:

{
  "Logging": {
    "LogLevel": {
      "Default": "Information",
      "Microsoft.AspNetCore": "Warning"
    }
  },
  "AllowedHosts": "*",
  "ConnectionStrings": {
    "DefaultConnection": "Server=LAPTOP-6P5NK25R\\SQLSERVER2022DEV;Database=ECommerceDB;Trusted_Connection=True;TrustServerCertificate=True;"
  }
}

Step 8: Configure DbContext in Program.cs

Please modify the Program class as follows.

using ECommerceApp.Data;
using Microsoft.EntityFrameworkCore;
namespace ECommerceApp
{
    public class Program
    {
        public static void Main(string[] args)
        {
            var builder = WebApplication.CreateBuilder(args);

            // Add services to the container.
            builder.Services.AddControllers()
            .AddJsonOptions(options =>
            {
                // This will use the property names as defined in the C# model
                options.JsonSerializerOptions.PropertyNamingPolicy = null;
            });

            builder.Services.AddEndpointsApiExplorer();
            builder.Services.AddSwaggerGen();

            // Register DbContext and Connection String
            builder.Services.AddDbContext<AppDbContext>(options =>
                options.UseSqlServer(builder.Configuration.GetConnectionString("DefaultConnection")));

            var app = builder.Build();

            // Configure the HTTP request pipeline.
            if (app.Environment.IsDevelopment())
            {
                app.UseSwagger();
                app.UseSwaggerUI();
            }

            app.UseHttpsRedirection();

            app.UseAuthorization();

            app.MapControllers();

            app.Run();
        }
    }
}

Step 9: Creating DTOs

Let us create the DTOs for exchanging the Data.

OrderItemRequestDTO

Create a class file named OrderItemRequestDTO.cs within the DTOs folder, then copy and paste the following code. It defines individual product items within an order request.

using System.ComponentModel.DataAnnotations;
namespace ECommerceApp.DTOs
{
    // Represents an individual product item in a new order request
    public class OrderItemRequestDTO
    {
        // The product being ordered
        [Required(ErrorMessage = "ProductId is required.")]
        [Range(1, int.MaxValue, ErrorMessage = "ProductId must be a positive integer.")]
        public int ProductId { get; set; }

        // Quantity of that product
        [Required(ErrorMessage = "Quantity is required.")]
        [Range(1, 1000, ErrorMessage = "Quantity must be between 1 and 1000.")]
        public int Quantity { get; set; }
    }
}

OrderRequestDTO

Create a class file named OrderRequestDTO.cs within the DTOs folder, then copy and paste the following code. It is used for placing new orders. It includes customer ID, address IDs, and a list of ordered items.

using System.ComponentModel.DataAnnotations;
namespace ECommerceApp.DTOs
{
    // Represents the full data required to place a new order
    public class OrderRequestDTO
    {
        // The customer placing the order
        [Required(ErrorMessage = "CustomerId is required.")]
        [Range(1, int.MaxValue, ErrorMessage = "CustomerId must be a positive integer.")]
        public int CustomerId { get; set; }

        // Chosen shipping address
        [Required(ErrorMessage = "ShippingAddressId is required.")]
        [Range(1, int.MaxValue, ErrorMessage = "ShippingAddressId must be a positive integer.")]
        public int ShippingAddressId { get; set; }

        // Chosen billing address
        [Required(ErrorMessage = "BillingAddressId is required.")]
        [Range(1, int.MaxValue, ErrorMessage = "BillingAddressId must be a positive integer.")]
        public int BillingAddressId { get; set; }

        // List of products in the order
        [Required(ErrorMessage = "Order must contain at least one item.")]
        [MinLength(1, ErrorMessage = "At least one order item must be provided.")]
        public List<OrderItemRequestDTO> Items { get; set; } = new(); 
    }
}

OrderItemResponseDTO

Create a class file named OrderItemResponseDTO.cs within the DTOs folder, then copy and paste the following code. It represents each ordered product in response payloads with product and category info.

namespace ECommerceApp.DTOs
{
    // Represents an item inside an order when returning data
    public class OrderItemResponseDTO
    {
        public string ProductName { get; set; } = null!;
        public string Category { get; set; } = null!;
        public int Quantity { get; set; }
        public decimal UnitPrice { get; set; }
    }
}

CustomerResponseDTO

Create a class file named CustomerResponseDTO.cs within the DTOs folder, then copy and paste the following code. It returns summarized customer data, including name, email, and addresses.

namespace ECommerceApp.DTOs
{
    // Summarized view of the customer associated with an order
    public class CustomerResponseDTO
    {
        public int CustomerId { get; set; }
        public string Name { get; set; } = null!;
        public string Email { get; set; } = null!;
        public string? Phone { get; set; }
        public string? DisplayName { get; set; }
        public string? Gender { get; set; }
        public string? DateOfBirth { get; set; }
    }
}

OrderResponseDTO

Create a class file named OrderResponseDTO.cs within the DTOs folder, then copy and paste the following code. It wraps complete order details (customer info, order items, and addresses) for API responses.

namespace ECommerceApp.DTOs
{
    // Represents an order returned from API endpoints
    public class OrderResponseDTO
    {
        public int OrderId { get; set; }
        public string OrderDate { get; set; } = null!;
        public string Status { get; set; } = null!;
        public decimal TotalAmount { get; set; }
        public string ShippingAddress { get; set; } = null!;
        public string BillingAddress { get; set; } = null!;
        public CustomerResponseDTO Customer { get; set; } = null!;
        public List<OrderItemResponseDTO> Items { get; set; } = new();
    }
}

Step 10: Create Order API Controller

Please create a new Empty API Controller named OrderController within the Controllers folder and add the following code to it. The OrderController handles all API Endpoints related to order management, such as retrieving all orders, fetching by ID, and placing new orders. It demonstrates how EF Core relationships are utilized in queries and DTO mapping for clean data transfer. Now, we have only implemented the Place Order method; later, we will implement the other methods.

using ECommerceApp.Data;
using ECommerceApp.DTOs;
using ECommerceApp.Enums;
using ECommerceApp.Models;
using Microsoft.AspNetCore.Mvc;
using Microsoft.EntityFrameworkCore;

namespace ECommerceApp.Controllers
{
    [ApiController]
    [Route("api/[controller]")]
    public class OrderController : ControllerBase
    {
        private readonly AppDbContext _context;

        public OrderController(AppDbContext context)
        {
            _context = context;
        }

        // ===========================================================================
        // POST: api/order/place
        // PURPOSE: Places a new order with validation and business rules
        // Demonstrates: Validations, relationships, transactions, soft audit fields
        // ===========================================================================
        [HttpPost("place")]
        public async Task<IActionResult> PlaceOrder([FromBody] OrderRequestDTO request)
        {
            try
            {
                // STEP 1: Validate Customer existence
                var customer = await _context.Customers
                    .Include(c => c.Addresses)
                    .FirstOrDefaultAsync(c => c.CustomerId == request.CustomerId && c.IsActive);
                if (customer == null)
                    return BadRequest($"Customer {request.CustomerId} not found or inactive.");

                // STEP 2: Validate Shipping & Billing Addresses (must belong to the same customer)
                var shipping = await _context.Addresses
                    .FirstOrDefaultAsync(a => a.AddressId == request.ShippingAddressId && a.CustomerId == request.CustomerId);
                var billing = await _context.Addresses
                    .FirstOrDefaultAsync(a => a.AddressId == request.BillingAddressId && a.CustomerId == request.CustomerId);

                if (shipping == null || billing == null)
                    return BadRequest("Invalid Shipping or Billing Address for this customer.");

                // STEP 3: Validate Products & Stock availability
                var productIds = request.Items.Select(i => i.ProductId).ToList();
                var products = await _context.Products
                    .Where(p => productIds.Contains(p.ProductId) && p.IsActive)
                    .ToListAsync();

                // Check product existence
                if (products.Count != request.Items.Count)
                    return BadRequest("Some products are invalid or inactive.");

                // Check stock for each product
                foreach (var item in request.Items)
                {
                    var product = products.First(p => p.ProductId == item.ProductId);
                    if (product.Stock < item.Quantity)
                        return BadRequest($"Insufficient stock for '{product.Name}'. Available: {product.Stock}, Requested: {item.Quantity}");
                }

                // STEP 4: Create new Order object
                var order = new Order
                {
                    CustomerId = request.CustomerId,
                    ShippingAddressId = request.ShippingAddressId,
                    BillingAddressId = request.BillingAddressId,
                    OrderStatusId = (int)OrderStatusEnum.Pending,
                    OrderDate = DateTime.UtcNow,
                    CreatedAt = DateTime.UtcNow,
                    CreatedBy = "System",
                    IsActive = true
                };

                // STEP 5: Map Order Items and Deduct Stock
                order.OrderItems = new List<OrderItem>();

                foreach (var item in request.Items)
                {
                    var product = products.First(p => p.ProductId == item.ProductId);

                    // Reduce stock from product
                    product.Stock -= item.Quantity;
                    product.UpdatedAt = DateTime.UtcNow;
                    product.UpdatedBy = "System";

                    // Create order item record
                    order.OrderItems.Add(new OrderItem
                    {
                        ProductId = product.ProductId,
                        Quantity = item.Quantity,
                        UnitPrice = product.Price // use price from DB, not client
                    });
                }

                // STEP 6: Compute total amount
                order.TotalAmount = order.OrderItems.Sum(i => i.Quantity * i.UnitPrice);

                // STEP 7: Save changes (transactionally)
                _context.Orders.Add(order);
                await _context.SaveChangesAsync();

                // STEP 8: Return success response
                return Ok(new 
                { 
                    Message = "Order placed successfully.", 
                    OrderId = order.OrderId 
                });
            }
            catch (Exception ex)
            {
                // Global exception handler
                return StatusCode(500, new
                {
                    Message = "Unexpected error while placing the order.",
                    ErrorMessage = ex.Message
                });
            }
        }
    }
}

 

Step 11: Run Migrations

Open the Package Manager Console and execute:

• Add-Migration InitialCreate
• Update-Database

EF Core will:

• Create the database ECommerceDB.
• Create all tables.
• Insert the seed data defined in AppDbContext.

Verifying the Database:

You should see the following database with the Required database tables.

Endpoint: Place a New Order

• Method: POST
• URL: https://localhost:5001/api/order/place

Description: Creates a new order and inserts related order items.

Body (raw JSON):

{
  "CustomerId": 1,
  "ShippingAddressId": 1,
  "BillingAddressId": 1,
  "Items": [
    {
      "ProductId": 1,
      "Quantity": 2
    },
    {
      "ProductId": 2,
      "Quantity": 1
    }
  ]
}

Important: Use CustomerId, ShippingAddressId, and BillingAddressId values that actually exist in your seeded data. If ProductId is inactive or invalid, you will get a validation error.

What Are Principal and Dependent Entities?

When you define relationships between entities in Entity Framework Core (EF Core), it’s essential to understand the roles of Principal Entity and Dependent Entity. These roles tell EF Core which entity owns the relationship, where the foreign key lives, and how cascade operations (like delete) are handled internally.

Principal Entity

The Principal Entity is the entity that defines the existence of the relationship. It can exist independently, even if no related entities exist for it. In database terms, the Principal Entity is the table that is referenced by a Foreign Key (FK) in another table.

Example:

In a Category ↔ Product relationship:

• The Category is the Principal Entity because it exists independently.
• Even if there are no products, the category (like “Electronics” or “Books”) can still exist in the system.

Dependent Entity

The Dependent Entity relies on the Principal Entity for its existence. It contains the Foreign Key (FK) that refers to the Principal’s Primary Key (PK) or Unique Key (UK). If the principal entity is deleted, the dependent entity may also be deleted automatically. In simple terms, the Dependent Entity cannot logically exist without its parent (Principal).

Example:

In the Category ↔ Product relationship:

• The Product is the Dependent Entity because it depends on Category.
• A Product must be associated with one valid Category (e.g., “Laptop” must belong to “Electronics”).

What are Reference Navigation and Collection Navigation Properties?

Navigation properties in EF Core are what make entity relationships feel natural and object-oriented. They represent the Connections Between Entities in your C# classes, allowing you to traverse relationships in memory the same way we do in the database. EF Core relies on these navigation properties to Infer Relationships Automatically when using the Code First (Convention-Based) approach.

In Entity Framework Core, navigation properties enable working with related entities in an object-oriented manner. They represent the links or relationships between entities inside your C# classes, allowing you to move across related data just like navigating between tables in a database. EF Core uses these navigation properties to:

• Infer relationships automatically (when following convention-based mapping),
• Enable eager, lazy, or explicit loading of related data,

In Short, Navigation Properties connect your entities in memory the same way foreign keys connect tables in the database.

Reference Navigation Property

A Reference Navigation Property represents a single related entity. It lives on the “one” side of a relationship, meaning the current entity points to one specific related entity.

Example:

In the Product ↔ Category relationship:

• Product.Category → represents one single Category object linked to that Product.

This means that each Product instance contains a navigation property that provides access to its corresponding Category record.

Collection Navigation Property

A Collection Navigation Property represents a set or list of related entities. It exists on the “many” side of a relationship, meaning one entity can have multiple related dependents.

Example:

In the Category ↔ Product relationship:

• Category.Products → represent a collection of multiple Product objects that belong to the same Category.

This means each Category instance has a navigation property containing all related Product entities.

What are Primary Key (PK) and Foreign Key (FK)?

In relational databases, Primary Keys and Foreign Keys are the foundation of how data is uniquely identified and logically connected. They ensure data integrity, uniqueness, and consistency of relationships between entities.

Primary Key (PK)

A Primary Key is a column (or a set of columns) that uniquely identifies each record in a table. In EF Core, every entity must have a property that serves as its primary key. This property becomes the table’s Primary Key when the model is mapped to the database.

Example in Our E-Commerce Project

• In the Category entity, CategoryId uniquely identifies each record, such as “Electronics” or “Fashion”.
• Similarly, in the Product entity, ProductId uniquely identifies each product, like “Wireless Mouse” or “Bluetooth Speaker”.

That means no two products can share the same ProductId, and EF Core uses this uniqueness to track entities in memory and update them correctly.

Key Points

• Every EF Core entity must have one Primary Key (or a composite key).
• The PK acts as the Main Identity of the object, both in code and in the database.
• EF Core automatically treats a property named Id or <EntityName>Id as the primary key (by convention).

Foreign Key (FK)

A Foreign Key is a property in one entity that points to the Primary Key of another entity.
It defines a relationship link between two entities, establishing how they are connected.

Example in Our E-Commerce Project

• In the Product entity, the CategoryId property is a foreign key that references Category.CategoryId.
• This means:
  • Each Product belongs to one Category (e.g., Laptop → Electronics).
  • The CategoryId column in the Products table points to a valid record in the Categories table.
  • The database enforces referential integrity, preventing orphan records (e.g., products without valid categories).

Key Points

• A Foreign Key links dependent data (children) to principal data (parents).
• EF Core automatically creates foreign keys when we define navigation properties between entities.
• FKs ensure that relationships remain valid, for example, you can’t assign a product to a non-existent category.

What is Default Cascading Behavior in EF Core?

Cascading defines how EF Core (and the underlying database) handles related entities when a Principal Entity is deleted. EF Core automatically configures a Delete Behavior on relationships, based on whether the Foreign Key (FK) in the dependent entity is nullable or non-nullable.

Required Foreign Key (Non-Nullable FK)

A Required Relationship means that every dependent entity must be linked to a valid principal entity. In this case, the Foreign Key is non-nullable, and EF Core sets up cascade delete by default.

• Default Delete Behavior: DeleteBehavior.Cascade

In this case, when the Primary entity (Principal Entity) is deleted, all its related Child Entities (Dependent Entities, if any) are automatically deleted to maintain consistency.

Example:

• Every Product must belong to one Category.
• Product.CategoryId is non-nullable.

When a Category is deleted, all its related Products are automatically deleted to maintain consistency because a Product cannot exist without its Category. This prevents orphaned data and maintains relational integrity.

Optional Foreign Key (Nullable FK)

An Optional Relationship means a dependent entity can exist without being linked to a principal entity. Here, the Foreign Key is nullable, and EF Core uses a SetNull behavior by default.

• Default Delete Behavior: DeleteBehavior.SetNull

In this case, when the Primary entity (Principal Entity) is deleted, all its related Child Entities (Dependent Entities, if any) are set to NULL, since the Foreign Key is optional and can store NULL values.

Example:

Each Order has two address references, ShippingAddress and BillingAddress. Both FKs are declared as nullable. This means:

• An Order must exist, but
• Its associated Shipping or Billing Address can be removed later (e.g., if the user deletes the old address).
• In such cases, EF Core will set the corresponding FK to NULL rather than deleting the Order.

This ensures that historical orders are retained for auditing or reporting, even if the addresses used at the time are no longer active.

In Entity Framework Core, relationships form the foundation for how entities are connected and interact with one another. Understanding and correctly implementing these relationships ensures your database remains consistent, navigable, and aligned with real-world business rules. Once mastered, these concepts simplify complex data modeling and help build scalable, maintainable applications.