Round #577

Use ROW_NUMBER to deduplicate orders, keeping the most recent per customer.

```csharp using System; using System.Data; using System.Linq; public class Order { public int CustomerId { get; set; } public DateTime OrderDate { get; set; } } class Program { static void Main() { // Create a sample list of orders var orders = new[] { new Order { CustomerId = 1, OrderDate = DateTime.Parse("2020-12-01") }, new Order { CustomerId = 1, OrderDate = DateTime.Parse("2020-12-15") }, new Order { CustomerId = 2, OrderDate = DateTime.Parse("2020-12-10") }, new Order { CustomerId = 3, OrderDate = DateTime.Parse("2020-12-01") }, new Order { CustomerId = 3, OrderDate = DateTime.Parse("2020-12-20") }, new Order { CustomerId = 3, OrderDate = DateTime.Parse("2020-12-25") }, }; // Use LINQ to deduplicate orders, keeping the most recent per customer var deduplicatedOrders = orders .OrderByDescending(o => o.OrderDate) .GroupBy(o => o.CustomerId) .Select(g => g.First()) .ToList(); // Print the deduplicated orders foreach (var order in deduplicatedOrders) { Console.WriteLine($"Customer ID: {order.CustomerId}, Order Date: {order.OrderDate}"); } } } ```

```csharp using Microsoft.EntityFrameworkCore; using System; using System.Linq; public class Order { public int Id { get; set; } public int CustomerId { get; set; } public DateTime OrderDate { get; set; } } public class MyDbContext : DbContext { public DbSet<Order> Orders { get; set; } protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder) { optionsBuilder.UseSqlServer(@"Data Source=(localdb)\mssqllocaldb;Initial Catalog=MyDatabase;Integrated Security=True"); } } class Program { static void Main() { using (var context = new MyDbContext()) { var recentOrders = context.Orders .OrderByDescending(o => o.OrderDate) .Select((o, i) => new { Index = i, Order = o }) .GroupBy(x => x.Order.CustomerId) .Select(g => g.First().Order) .ToList(); foreach (var order in recentOrders) { Console.WriteLine($"Order Id: {order.Id}, Customer Id: {order.CustomerId}, Order Date: {order.OrderDate}"); } } } } ``` This C# code uses Entity Framework Core to connect to a SQL Server database and retrieve the most recent order for each customer. The `ROW_NUMBER` function is not explicitly used, but the same result is achieved by ordering the orders by date in descending order, then grouping them by customer and selecting the first order in each group. However, if you want to use `ROW_NUMBER` function, you can use the following code: ```csharp using Microsoft.EntityFrameworkCore; using System; using System.Linq; public class Order { public int Id { get; set; } public int CustomerId { get; set; } public DateTime OrderDate { get; set; } } public class MyDbContext : DbContext { public DbSet<Order> Orders { get; set; } protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder) { optionsBuilder.UseSqlServer(@"Data Source=(localdb)\mssqllocaldb;Initial Catalog=MyDatabase;Integrated Security=True"); } } class Program { static void Main() { using (var context = new MyDbContext()) { var recentOrders = context.Orders .FromSqlRaw("SELECT * FROM (SELECT *, ROW_NUMBER() OVER (PARTITION BY CustomerId ORDER BY OrderDate DESC) AS RowNum FROM Orders) AS SubQuery WHERE RowNum = 1") .ToList(); foreach (var order in recentOrders) { Console.WriteLine($"Order Id: {order.Id}, Customer Id: {order.CustomerId}, Order Date: {order.OrderDate}"); } } } } ``` This code uses the `FromSqlRaw` method to execute a raw SQL query that uses the `ROW_NUMBER` function to assign a row number to each order within each customer group, ordered by date in descending order. The outer query then selects only the orders with a row number of 1, which are the most recent orders for each customer.