mohamed Tayel

Posted on Sep 16 • Updated on Sep 17

EFCore Tutorial P6: LINQ Queries

#efcore #dotnetcore #softwaredevelopment #softwareengineering

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 OrderDetail will be a composite key consisting of OrderId and ProductId.
Relationship: Each Order can have multiple OrderDetails, and each OrderDetail is linked to both Order and Product.

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 OrderDetail class has a composite key (OrderId and ProductId).
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 ProductId foreign 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

DEV Community