LINQ Join with Multiple Conditions in EF Core 6 - Unexpected Results
I'm performance testing and I'm optimizing some code but After trying multiple solutions online, I still can't figure this out... I've been struggling with this for a few days now and could really use some help. I'm working on a project using Entity Framework Core 6 and I need to perform a join on two entities based on multiple conditions. However, I'm working with unexpected results that I need to seem to resolve. I have two tables: `Products` and `Orders`. Each `Order` can contain multiple `Products`, and I want to join these tables on both the `ProductId` and `OrderDate`. Here’s the code I've written: ```csharp var results = from product in dbContext.Products join order in dbContext.Orders on new { product.Id, order.OrderDate.Date } equals new { Id = order.ProductId, Date = order.OrderDate.Date } select new { ProductName = product.Name, OrderDate = order.OrderDate }; var productOrders = results.ToList(); ``` I'm getting an behavior stating that the join condition on `OrderDate.Date` is not being recognized correctly. The behavior message is: ``` 'Invalid operation: The property 'OrderDate' want to be used in the equality comparison because it is a complex type.' ``` I’ve tried simplifying the join condition just to `product.Id == order.ProductId` and it works as expected. However, when I attempt to add the `OrderDate.Date` to the join condition, it fails. I also checked my database schema and confirmed that `OrderDate` is indeed a nullable `DateTime`. I've considered using a separate query to filter by `OrderDate` after the join, but it feels inefficient. Can someone guide to understand why this is happening and how I can correctly implement a join with multiple conditions using LINQ in EF Core 6? My development environment is Windows. Is there a better approach? I'm working on a API that needs to handle this. Is there a better approach? This is my first time working with Csharp latest. Thanks for any help you can provide! I'm working in a CentOS environment. What am I doing wrong? Thanks in advance!