One-to-many relationships with Entity Framework

One-to-many relationships are used when a single entity is associated with any number of other entities. For example, an Order can have many associated OrderItem, but each OrderItem is associated with only one Order.

Let's have a look at the Shop Cart example

Database design with 3 tables: Order, Product and OrderProduct With this design, I can link the product to the order. Customers can pick many products for one order

• OrderProducts table stores Order items, It links to Orders table with a foreign key OrderId and Products table with foreign key ProductId
• I use OrderId and ProductId as a Composite Primary Key for OrderProducts table

Config Entity

public class OrderEntity
{
    public int Id { get; set; }
    public decimal Total { get; set; }
    public OrderState State { get; set; }
    public DateTimeOffset CreatedDate { get; set; }
    public virtual List<OrderProductEntity> OrderProducts { get; set; } = null!;
}

public enum OrderState
{
    Create, Paid, Shipped, Done, Cancel
}

public class OrderProductEntity
{
    public int OrderId { get; init; }
    public int ProductId { get; init; }
    public int Quantity { get; init; }
    public virtual ProductEntity Product { get; init; } = null!;
}

public class ProductEntity
{
    public int Id { get; set; }
    public required string Url { get; set; }
    public required string Name { get; set; }
    public string? Description { get; set; }
    public decimal Price { get; set; }
    public DateTimeOffset CreatedDate { get; set; }
    
    public virtual ProductDetailEntity? Details { get; set; }

}

Config DbContext

// Fluent API config in DBContext
protected override void OnModelCreating(ModelBuilder modelBuilder)
{

    modelBuilder.Entity<OrderEntity>(
        e =>
        {
            e.ToTable("Orders");
            e.HasKey(x => new { x.Id }).HasName("PK_Order");
            e.Property(x => x.Id).ValueGeneratedOnAdd().UseIdentityColumn(1000);
            e.Property(x => x.Total).HasPrecision(18, 2);
            e.Property(x => x.State).HasMaxLength(50);
            e.Property(x => x.CreatedDate).HasColumnType("datetimeoffset");

            // Relationships define
            e.HasMany(order => order.OrderProducts)
                .WithOne()
                .HasForeignKey(orderProduct => orderProduct.OrderId)
                .HasConstraintName("FK_Order_Product_OrderId");
        }
    );

    modelBuilder.Entity<OrderProductEntity>(
        e =>
        {
            e.ToTable("OrderProducts");
            e.Property(x => x.OrderId);
            e.Property(x => x.ProductId);
            e.HasKey(x => new { x.OrderId, x.ProductId }).HasName("PK_OrderProduct");
            e.Property(x => x.Quantity);

            // Relationships define
            e.HasOne(orderProduct => orderProduct.Product)
                .WithOne()
                .HasForeignKey<OrderProductEntity>(orderProduct => orderProduct.ProductId)
                .HasConstraintName("FK_Order_Product_ProductId");
        }
    );
        
    modelBuilder.Entity<ProductEntity>(
        e =>
        {
            e.ToTable("Products");
            e.HasKey(x => new { x.Id }).HasName("PK_ProductId");
            e.Property(x => x.Id).ValueGeneratedOnAdd();
            e.Property(x => x.Url).HasMaxLength(255);
            e.Property(x => x.Name).HasMaxLength(255);
            e.Property(x => x.Price).HasPrecision(18, 2);
            e.Property(x => x.CreatedDate).HasColumnType("datetimeoffset");
            e.Property(x => x.Description).HasColumnType("text").HasMaxLength(255);
        }
    );
}

Query for orders

Remember to add Include() statement in your query, or the child relation will be null

List<OrderEntity> entities = await _dbContext.Orders
    .Include(order => order.OrderProducts)
    .ThenInclude(orderProduct => orderProduct.Product)
    .ToListAsync();

More details from Microsoft document: One-to-many relationships

Full project example available on Github