Recently, I upgraded my project to use Entity Framework Core instead of Entity Framework. I have a query in my project that has recently started timing out.

When I checked the profiler, I noticed that the query is being executed differently than before:

This is the query:

var orders = context.Orders.AsQueryable();
var orderItems = context.OrderItems.AsQueryable();
var customers = context.Customers.AsQueryable();
var statusCodes = statuses.Cast<short>();
var paymentData = context.PaymentData.AsQueryable();

query = 
    from order in orders
    where order.CustomerId == customerId && statusCodes.Contains(order.StatusCode)
    join item in orderItems on order.OrderId equals item.OrderId into itemList
    from customer in customers
    where order.CustomerId == customer.CustomerId &&
          order.Currency == customer.Currency &&
          order.ProductType == customer.ProductType &&
          order.PaymentMethodId == customer.PaymentMethodId
    join payment in context.Payments.AsQueryable() on new
    { OrderId = order.OrderId, ProductType = order.ProductType.Value } equals
    new { OrderId = (int)payment.OrderId, payment.ProductType } into payments
    from paymentResult in payments.DefaultIfEmpty()
    join paymentDetail in paymentData
    on order.OrderId equals paymentDetail.OrderId into paymentDetails
    select new OrderResponse
    {
        OrderId = order.OrderId,
        CustomerId = order.CustomerId,
        CustomerName = order.CustomerName,
        OrderDate = order.OrderDate,
        //...
    };

Relationships:

• Orders to OrderItems: one-to-many
• Orders to Customers: many-to-one
• Orders to Payments: one-to-many (Left Join)
• Orders to PaymentData: one-to-many

The profiler shows me this result:

exec sp_executesql N'SELECT ...
FROM [Sales].[Orders] AS [order]
LEFT JOIN [Sales].[OrderItems] AS [item] ON [order].[OrderId] = [item].[OrderId]
WHERE ([order].[CustomerId] = @__customerId_0) AND [order].[StatusCode] IN (CAST(1 AS smallint), CAST(2 AS smallint), CAST(3 AS smallint), CAST(4 AS smallint), CAST(5 AS smallint), CAST(6 AS smallint), CAST(7 AS smallint), CAST(8 AS smallint), CAST(9 AS smallint), CAST(81 AS smallint), CAST(82 AS smallint), CAST(89 AS smallint), CAST(90 AS smallint), CAST(91 AS smallint), CAST(99 AS smallint))
ORDER BY [OrderId0]',N'@__customerId_0 varchar(50)',@__customerId_0='62325234'

SELECT [customer].[CustomerId], [customer].[Currency], [customer].[ProductType]
FROM [Sales].[Customers] AS [customer]

SELECT [payment].[OrderId], [payment].[ProductType], [payment].[CollectionDueDate]
FROM [Sales].[Payments] AS [payment]

SELECT [paymentDetail].[OrderId], [paymentDetail].[AccountHolderName], [paymentDetail].[BankAccountNumber], [paymentDetail].[BankName], [paymentDetail].[IachId], [paymentDetail].[Routing]
FROM [Sales].[PaymentData] AS [paymentDetail]

It retrieves the last three tables — all of them without any filters — and since I have a lot of data, running the query has started to take a long time.

Does anyone know how to help me resolve this issue? How can I modify my query to perform a join instead of separate queries for each table?