Open
Description
openedon Apr 24, 2024
Current situation
Currently I have an EF Query built like this:
var query = GetOffersQuery(identities, loggedUser)
.AsNoTracking();
var dtoQuery = query.Select(x => new OfferDTO
{
Status = x.Status,
CalculationDate = x.CalculationDate,
CreatedDate = x.CreatedDate,
ProductNames = x.Components.Select(c => c.ProductName),
Quotations = x.Components.Select(c => c.Header.SalesDoc),
BatteryTypes = x.Components.Select(c => c.BatteryType),
ApproverName = x.Approver!.DisplayName,
CustomerName = x.Customer.Name,
Id = x.Id,
SalesmanName = x.Salesman!.DisplayName,
UploaderName = x.Uploader!.DisplayName,
OpportunityNumber = x.OpportunityNumber,
CountryCode = x.CountryCode
})
.AsSingleQuery();Notice that ProductNames, Quotations and BatteryTypes all refers to the Components entity. The Components entity is alread Included inside GetOffersQuery.
This results in the following SQL Server query being generated:
SELECT [t].[Status], [t].[CalculationDate], [t].[CreatedDate], [t].[Id], [u].[CountryCode], [u].[UserPrincipalName], [u0].[CountryCode], [u0].[UserPrincipalName], [u1].[CountryCode], [u1].[UserPrincipalName], [o1].[ProductName], [o1].[Id], [o2].[Header_SalesDoc], [o2].[Id], [o3].[BatteryType], [o3].[Id], [u].[DisplayName], [t].[Customer_Name], [u0].[DisplayName], [u1].[DisplayName], [t].[OpportunityNumber], [t].[CountryCode]
FROM (
SELECT TOP(@__p_1) [o].[Id], [o].[ApprovedBy], [o].[CalculationDate], [o].[CountryCode], [o].[CreatedBy], [o].[CreatedDate], [o].[OpportunityNumber], [o].[SalesEmployee], [o].[Status], [o].[Customer_Name]
FROM [OfferHeader] AS [o]
WHERE ([o].[CountryCode] IN (...) OR EXISTS (
SELECT 1
FROM [OfferGrantedUsers] AS [o0]
WHERE [o0].[OfferId] = [o].[Id] AND [o0].[UserPrincipalName] = @__loggedUser_0)) AND [o].[Deleted] = CAST(0 AS bit)
ORDER BY [o].[CalculationDate] DESC, [o].[Id] DESC
) AS [t]
LEFT JOIN [UserHierarchyRoles] AS [u] ON [t].[CountryCode] = [u].[CountryCode] AND [t].[ApprovedBy] = [u].[UserPrincipalName]
INNER JOIN [UserHierarchyRoles] AS [u0] ON [t].[CountryCode] = [u0].[CountryCode] AND [t].[SalesEmployee] = [u0].[UserPrincipalName]
INNER JOIN [UserHierarchyRoles] AS [u1] ON [t].[CountryCode] = [u1].[CountryCode] AND [t].[CreatedBy] = [u1].[UserPrincipalName]
LEFT JOIN [OfferComponent] AS [o1] ON [t].[Id] = [o1].[OfferId]
LEFT JOIN [OfferComponent] AS [o2] ON [t].[Id] = [o2].[OfferId]
LEFT JOIN [OfferComponent] AS [o3] ON [t].[Id] = [o3].[OfferId]
ORDER BY [t].[CalculationDate] DESC, [t].[Id] DESC, [u].[CountryCode], [u].[UserPrincipalName], [u0].[CountryCode], [u0].[UserPrincipalName], [u1].[CountryCode], [u1].[UserPrincipalName], [o1].[Id], [o2].[Id]Notice that OfferComponent table gets added once for every Select. In fact, if I remove one or more Selects inside the projection, the number of joins adjust accordingly.
Expected result
I would expect EF (or SqlClient) to only join the table once, since all 3 Selects are based on the same entity. The query I expect is the following:
SELECT [t].[Status], [t].[CalculationDate], [t].[CreatedDate], [t].[Id], [u].[CountryCode], [u].[UserPrincipalName], [u0].[CountryCode], [u0].[UserPrincipalName], [u1].[CountryCode], [u1].[UserPrincipalName], [o1].[ProductName], [o1].[Id], [o1].[Header_SalesDoc], [o1].[BatteryType], [u].[DisplayName], [t].[Customer_Name], [u0].[DisplayName], [u1].[DisplayName], [t].[OpportunityNumber], [t].[CountryCode]
FROM (
SELECT TOP(@__p_1) [o].[Id], [o].[ApprovedBy], [o].[CalculationDate], [o].[CountryCode], [o].[CreatedBy], [o].[CreatedDate], [o].[OpportunityNumber], [o].[SalesEmployee], [o].[Status], [o].[Customer_Name]
FROM [OfferHeader] AS [o]
WHERE ([o].[CountryCode] IN (...) OR EXISTS (
SELECT 1
FROM [OfferGrantedUsers] AS [o0]
WHERE [o0].[OfferId] = [o].[Id] AND [o0].[UserPrincipalName] = @__loggedUser_0)) AND [o].[Deleted] = CAST(0 AS bit)
ORDER BY [o].[CalculationDate] DESC, [o].[Id] DESC
) AS [t]
LEFT JOIN [UserHierarchyRoles] AS [u] ON [t].[CountryCode] = [u].[CountryCode] AND [t].[ApprovedBy] = [u].[UserPrincipalName]
INNER JOIN [UserHierarchyRoles] AS [u0] ON [t].[CountryCode] = [u0].[CountryCode] AND [t].[SalesEmployee] = [u0].[UserPrincipalName]
INNER JOIN [UserHierarchyRoles] AS [u1] ON [t].[CountryCode] = [u1].[CountryCode] AND [t].[CreatedBy] = [u1].[UserPrincipalName]
LEFT JOIN [OfferComponent] AS [o1] ON [t].[Id] = [o1].[OfferId]
ORDER BY [t].[CalculationDate] DESC, [t].[Id] DESC, [u].[CountryCode], [u].[UserPrincipalName], [u0].[CountryCode], [u0].[UserPrincipalName], [u1].[CountryCode], [u1].[UserPrincipalName], [o1].[Id]This can benefit by reducing the number of reduntant joins.
I know that one way to fix it would be to project the 3 properties as a single DTO with 3 properties, to enumerate Components once, but this changes the output object definition (and in my case the objective of the DTO projection is exactly to have a flat object)
All tests have been done on:
- EF Core 8.0.4
- Microsoft.Data.SqlClient 5.1.5
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
