1. Introduction
In this tutorial, we extend the previous product and inventory management system by introducing order management. We will explore how to work with LINQ (Language Integrated Query) in C# for querying data efficiently. We'll also implement new entities, Order and OrderDetail, to track customer orders. Additionally, we’ll introduce advanced LINQ queries to manipulate and retrieve data from the Product, Inventory, and Order entities.
2. Adding New Entities: Orders and OrderDetails
The Order and OrderDetail entities represent customer orders and the products in each order. The OrderDetail entity will now have a composite primary key and establish a strong relationship with Order.
Order and OrderDetail Models
namespace ProductDomain
{
public class Order
{
private DateOnly _orderDate; // Backing field for OrderDate
public int OrderId { get; set; }
public DateOnly OrderDate
{
get => _orderDate;
set
{
if (value <= DateOnly.FromDateTime(DateTime.Now))
{
throw new ArgumentException("Order date must be greater than today's date.");
}
_orderDate = value;
}
}
public ICollection<OrderDetail> OrderDetails { get; set; }
}
public class OrderDetail
{
public int OrderId { get; set; } // Foreign key to Order
public int ProductId { get; set; } // Foreign key to Product
public Product Product { get; set; }
public int Quantity { get; set; }
public decimal Price { get; set; }
public Order Order { get; set; } // Navigation property to Order
}
}
-
Primary Key: The primary key for
OrderDetailwill be a composite key consisting ofOrderIdandProductId. -
Relationship: Each
Ordercan have multipleOrderDetails, and eachOrderDetailis linked to bothOrderandProduct.
3. Configuring the New Entities
We configure the OrderDetail entity to have a composite primary key using IEntityTypeConfiguration.
OrderConfiguration Class
namespace ProductData.EntityConfiguration
{
public class OrderConfiguration : IEntityTypeConfiguration<Order>
{
public void Configure(EntityTypeBuilder<Order> builder)
{
builder.HasKey(o => o.OrderId); // Primary key for Order
builder.Property(o => o.OrderDate).IsRequired();
// One-to-many relationship between Order and OrderDetail
builder.HasMany(o => o.OrderDetails)
.WithOne(od => od.Order)
.HasForeignKey(od => od.OrderId);
// Add shadow property for tracking last updated time
builder.Property<DateTime>("LastUpdated");
// Seed data for Orders
builder.HasData(
new { OrderId = 1, OrderDate = new DateOnly(2024, 10, 1), LastUpdated = DateTime.Now },
new { OrderId = 2, OrderDate = new DateOnly(2024, 10, 2), LastUpdated = DateTime.Now }
);
}
}
}
namespace ProductData.EntityConfiguration
{
public class OrderDetailConfiguration : IEntityTypeConfiguration<OrderDetail>
{
public void Configure(EntityTypeBuilder<OrderDetail> builder)
{
// Composite primary key consisting of OrderId and ProductId
builder.HasKey(od => new { od.OrderId, od.ProductId });
builder.Property(od => od.Quantity).IsRequired();
builder.Property(od => od.Price).HasColumnType("decimal(18,2)");
// Define the foreign key relationship with Product
builder.HasOne(od => od.Product) // Each OrderDetail has one Product
.WithMany()
.HasForeignKey(od => od.ProductId); // ProductId as foreign key
// Define the foreign key relationship with Order
builder.HasOne(od => od.Order) // Each OrderDetail belongs to one Order
.WithMany(o => o.OrderDetails) // An Order can have many OrderDetails
.HasForeignKey(od => od.OrderId); // OrderId as foreign key
// Seed data for OrderDetails
builder.HasData(
new OrderDetail { OrderId = 1, ProductId = 1, Quantity = 2, Price = 999.99M },
new OrderDetail { OrderId = 1, ProductId = 2, Quantity = 1, Price = 499.99M },
new OrderDetail { OrderId = 2, ProductId = 1, Quantity = 1, Price = 999.99M }
);
}
}
}
-
Composite Primary Key: The
OrderDetailclass has a composite key (OrderIdandProductId). -
Relationships:
- Order to OrderDetail: Defined as a one-to-many relationship where each order can have multiple order details.
-
OrderDetail to Product: Linked via the
ProductIdforeign key.
4. Updating the AppDbContext
Next, we apply the new configuration for both Order and OrderDetail in AppDbContext.
AppDbContext Update
public class AppDbContext : DbContext
{
public DbSet<Order> Orders { get; set; }
public DbSet<OrderDetail> OrderDetails { get; set; }
protected override void OnModelCreating(ModelBuilder modelBuilder)
{
modelBuilder.ApplyConfiguration(new ProductConfiguration());
modelBuilder.ApplyConfiguration(new InventoryConfiguration());
modelBuilder.ApplyConfiguration(new OrderConfiguration());
modelBuilder.ApplyConfiguration(new OrderDetailConfiguration()); // New configuration
}
}
This step ensures that our new entities (Order and OrderDetail) and their relationships are mapped correctly in the database.
5. Updating the Database: Migration and Update
As in the previous steps, we need to apply the changes to the database. First, create a migration and then update the database.
Step 1: Add Migration
Run the following command:
Add-Migration AddOrderAndOrderDetailWithCompositeKey
Step 2: Update the Database
Apply the migration to update the database schema:
Update-Database
The Orders and OrderDetails tables will now reflect the new composite key and relationships.
6. Creating the OrderService
With the Order and OrderDetail entities and their relationships set up, we can now implement the OrderService. The service will handle creating orders, retrieving orders with details, and applying LINQ queries for various operations.
OrderService
public class OrderService
{
private readonly AppDbContext _context;
public OrderService(AppDbContext context)
{
_context = context;
}
// Create a new order with order details
public void CreateOrder(List<OrderDetail> orderDetails)
{
var order = new Order
{
OrderDate = DateTime.Now,
OrderDetails = orderDetails
};
_context.Orders.Add(order);
_context.SaveChanges();
}
// Retrieve all orders with their details and products
public List<Order> GetAllOrders()
{
return _context.Orders
.Include(o => o.OrderDetails)
.ThenInclude(od => od.Product)
.ToList();
}
// Retrieve recent orders within a specific number of days
public List<Order> GetRecentOrders(int days)
{
return _context.Orders
.Where(o => o.OrderDate >= DateTime.Now.AddDays(-days))
.Include(o => o.OrderDetails)
.ThenInclude(od => od.Product)
.ToList();
}
}
Key Methods:
- CreateOrder: Allows creating an order with its associated order details.
- GetAllOrders: Retrieves all orders with order details and product information.
- GetRecentOrders: Retrieves recent orders placed within a specified number of days.
7. Using LINQ Queries in ProductService, InventoryService, and OrderService
With the OrderService in place, we can now apply LINQ queries across ProductService, InventoryService, and OrderService to handle product queries, inventory management, and order retrieval.
ProductService LINQ Queries
public List<Product> GetProductsByCategory(string categoryName)
{
return _context.Products
.Where(p => p.Category.Name == categoryName)
.Include(p => p.Inventory)
.ToList();
}
This query retrieves all products within a specific category and includes their inventory details.
InventoryService LINQ Queries
public int GetTotalStockByProduct(int productId)
{
return _context.Inventories
.Where(i => i.ProductId == productId)
.Sum(i => i.Quantity);
}
This query calculates the total stock of a product across all inventory.
OrderService LINQ Queries
public List<Order> GetRecentOrders(int days)
{
return _context.Orders
.Where(o => o.OrderDate >= DateTime.Now.AddDays(-days))
.Include(o => o.OrderDetails)
.ThenInclude(od => od.Product)
.ToList();
}
This query retrieves recent orders placed within a specific number of days.
8. Testing the Extended Functionality
We will now test the newly added features by creating orders, querying recent orders, retrieving products by category, and summing inventory stock.
Example Test in Program.cs
using (var context = new AppDbContext())
{
var orderService = new OrderService(context);
var productService = new ProductService(context);
var inventoryService = new InventoryService(context);
// Create a new order with order details
var orderDetails = new List<OrderDetail>
{
new OrderDetail { ProductId = 1, Quantity = 2, Price = 999.99M },
new OrderDetail { ProductId = 2, Quantity = 1, Price = 499.99M }
};
orderService.CreateOrder(orderDetails);
// Query recent orders (within the last 30 days)
var recentOrders = orderService.GetRecentOrders(30);
foreach (var order in recentOrders)
{
Console.WriteLine($"Order {order.OrderId}, Date: {order.OrderDate}");
}
// Retrieve products by category (e.g., "Electronics")
var electronicsProducts = productService.GetProductsByCategory("Electronics");
foreach (var product in electronicsProducts)
{
Console.WriteLine($"Product: {product.Name}, Price: {product.Price}");
}
// Sum total stock for a product (e.g., ProductId = 1)
var totalStock = inventoryService.GetTotalStockByProduct(1);
Console.WriteLine($"Total stock for product 1: {totalStock}");
}
This test demonstrates:
-
Creating an order: We create a new order with
OrderDetails. - Querying recent orders: Retrieves all orders made in the last 30 days.
-
Retrieving products by category: Fetches all products under a specific category (
"Electronics"). - Summing inventory stock: Calculates the total stock for a specific product.
9. Conclusion
In this tutorial, we expanded on the existing EF Core setup by adding Order and OrderDetail entities with a composite key, which represents orders and their details. We also extended the ProductService, InventoryService, and OrderService to include LINQ queries for querying products, managing inventory, and retrieving recent orders. By using LINQ, complex queries involving multiple entities and relationships can be handled efficiently within an EF Core-backed system.
Source Code EFCoreDemo
Top comments (0)