Round #674

Write a SQL query joining 4 tables (customers, orders, line_items, products) to produce a customer-order-product report.

Certainly! To create a customer-order-product report, you need to join four tables: `customers`, `orders`, `line_items`, and `products`. The relationships between these tables are typically as follows: - Each order is associated with one customer. - Each order can have multiple line items. - Each line item is associated with one product. Here's a SQL query that joins these tables to produce the desired report: ```sql SELECT c.CustomerID, c.CustomerName, o.OrderID, o.OrderDate, li.LineItemID, li.Quantity, p.ProductID, p.ProductName, p.Price FROM customers c JOIN orders o ON c.CustomerID = o.CustomerID JOIN line_items li ON o.OrderID = li.OrderID JOIN products p ON li.ProductID = p.ProductID; ``` ### Explanation: - **customers (c)**: This table contains customer details. - `CustomerID`: Unique identifier for each customer. - `CustomerName`: Name of the customer. - **orders (o)**: This table contains order details. - `OrderID`: Unique identifier for each order. - `CustomerID`: Foreign key linking to the `customers` table. - `OrderDate`: Date when the order was placed. - **line_items (li)**: This table contains details of items in each order. - `LineItemID`: Unique identifier for each line item. - `OrderID`: Foreign key linking to the `orders` table. - `ProductID`: Foreign key linking to the `products` table. - `Quantity`: Quantity of the product ordered. - **products (p)**: This table contains product details. - `ProductID`: Unique identifier for each product. - `ProductName`: Name of the product. - `Price`: Price of the product. ### Result: The query will return a result set with columns representing customer ID, customer name, order ID, order date, line item ID, quantity, product ID, product name, and price. This report provides a comprehensive view of all orders placed by customers along with the products included in those orders.

To implement the request for generating a customer-order-product report by joining four tables (customers, orders, line_items, and products), we can use Entity Framework Core, which is a popular ORM for .NET applications. This approach will allow us to perform database operations in a more structured and maintainable way. First, let's define the entity classes for each table: ```csharp public class Customer { public int Id { get; set; } public string Name { get; set; } public List<Order> Orders { get; set; } } public class Order { public int Id { get; set; } public int CustomerId { get; set; } public DateTime OrderDate { get; set; } public List<LineItem> LineItems { get; set; } public Customer Customer { get; set; } } public class LineItem { public int Id { get; set; } public int OrderId { get; set; } public int ProductId { get; set; } public int Quantity { get; set; } public decimal Price { get; set; } public Order Order { get; set; } public Product Product { get; set; } } public class Product { public int Id { get; set; } public string Name { get; set; } public decimal UnitPrice { get; set; } public List<LineItem> LineItems { get; set; } } ``` Next, we need to configure the relationships between these entities in the `DbContext`: ```csharp public class AppDbContext : DbContext { public DbSet<Customer> Customers { get; set; } public DbSet<Order> Orders { get; set; } public DbSet<LineItem> LineItems { get; set; } public DbSet<Product> Products { get; set; } protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder) { optionsBuilder.UseSqlServer("YourConnectionStringHere"); } protected override void OnModelCreating(ModelBuilder modelBuilder) { modelBuilder.Entity<Order>() .HasOne(o => o.Customer) .WithMany(c => c.Orders) .HasForeignKey(o => o.CustomerId); modelBuilder.Entity<LineItem>() .HasOne(li => li.Order) .WithMany(o => o.LineItems) .HasForeignKey(li => li.OrderId); modelBuilder.Entity<LineItem>() .HasOne(li => li.Product) .WithMany(p => p.LineItems) .HasForeignKey(li => li.ProductId); } } ``` Now, we can write a method to generate the customer-order-product report: ```csharp public class ReportService { private readonly AppDbContext _context; public ReportService(AppDbContext context) { _context = context; } public async Task<List<CustomerOrderProductReport>> GetCustomerOrderProductReportAsync() { var query = from customer in _context.Customers join order in _context.Orders on customer.Id equals order.CustomerId join lineItem in _context.LineItems on order.Id equals lineItem.OrderId join product in _context.Products on lineItem.ProductId equals product.Id select new CustomerOrderProductReport { CustomerName = customer.Name, OrderDate = order.OrderDate, ProductName = product.Name, Quantity = lineItem.Quantity, Price = lineItem.Price }; return await query.ToListAsync(); } } public class CustomerOrderProductReport { public string CustomerName { get; set; } public DateTime OrderDate { get; set; } public string ProductName { get; set; } public int Quantity { get; set; } public decimal Price { get; set; } } ``` Finally, we need to register the `AppDbContext` and `ReportService` in the dependency injection container: ```csharp public class Startup { public void ConfigureServices(IServiceCollection services) { services.AddDbContext<AppDbContext>(options => options.UseSqlServer("YourConnectionStringHere")); services.AddScoped<ReportService>(); // Other service registrations... } // Other methods... } ``` With this setup, we can now inject the `ReportService` into any controller or other service and call the `GetCustomerOrderProductReportAsync` method to retrieve the customer-order-product report. This implementation uses Entity Framework Core for database operations, which provides a robust and efficient way to interact with the database. The use of LINQ allows us to write readable and maintainable queries. Dependency injection is used to manage the lifecycle of the `AppDbContext`, ensuring that it is properly disposed of when no longer needed.