Subject: Bug Report:
Environment:
Library Version: Z.EntityFramework.Extensions v10.105.5
Entity Framework Version: EF 6.5.2
Database: SQL Server
Entity Type: Entities with Composite Keys (e.g., MainEntity with KeyPart1 and KeyPart2)
Description:
There is a critical incompatibility between EF 6.5.2 and UpdateFromQuery when performing updates on entities with composite keys.
When the query is translated, EF 6.5.2 generates a nested subquery structure where the innermost projection (Project1) aliases all columns to generic names (e.g., [C1], [C2], etc.). However, the outer JOIN clause attempts to reference the original column names (e.g., [KeyPart1]) against this aliased subquery, instead of using the aliases provided by the projection. This results in a SQL error: "Invalid column name 'KeyPart1'" (or whichever column is part of the composite key).
Steps to Reproduce:
Use EF 6.5.2.
Define an entity with a composite key (e.g., [KeyPart1], [KeyPart2]).
Call db.UpdateFromQuery(query, ...) for a batch update.
Observe the generated SQL and the resulting exception.
Actual Behavior (Based on real trace):
The generated SQL follows this pattern:
UPDATE A
SET A.[TargetProperty] = @p__linq__0
FROM [MainEntity] AS A
INNER JOIN (
SELECT
[Project1].[C1] AS [C1],
[Project1].[C2] AS [C2],
-- ... other aliased columns ...
[Project1].[C30] AS [C30]
FROM (
SELECT
[Extent1].[KeyPart1] AS [C1],
[Extent1].[KeyPart2] AS [C2],
-- ... other columns ...
FROM [dbo].[MainEntity] AS [Extent1]
WHERE ...
) AS [Project1]
WHERE ([Project1].[C1] IN (...)) AND ([Project1].[C1] IS NOT NULL)
) AS B ON A.[KeyPart1] = B.[KeyPart1] -- ERROR: B does not have a column named 'KeyPart1', it has 'C1'
AND A.[KeyPart2] = B.[KeyPart2] -- ERROR: B does not have a column named 'KeyPart2', it has 'C2'
Expected Behavior:
The JOIN clause should either:
Use the aliases generated in the subquery (e.g., ON A.[KeyPart1] = B.[C1]).
Or, the subquery should preserve the original column names so the outer join can resolve them.
Workaround used:
Due to the production impact, I had to bypass UpdateFromQuery and implement a manual set-based update using ExecuteSqlCommand.
Additional Context:
This issue appears to be a regression in how EF 6.5.2 handles query translation/aliasing compared to previous versions (like EF 6.2.0), which breaks the SQL generation logic in Z.EntityFramework.Extensions.
Subject: Bug Report:
Environment:
Library Version: Z.EntityFramework.Extensions v10.105.5
Entity Framework Version: EF 6.5.2
Database: SQL Server
Entity Type: Entities with Composite Keys (e.g., MainEntity with KeyPart1 and KeyPart2)
Description:
There is a critical incompatibility between EF 6.5.2 and UpdateFromQuery when performing updates on entities with composite keys.
When the query is translated, EF 6.5.2 generates a nested subquery structure where the innermost projection (Project1) aliases all columns to generic names (e.g., [C1], [C2], etc.). However, the outer JOIN clause attempts to reference the original column names (e.g., [KeyPart1]) against this aliased subquery, instead of using the aliases provided by the projection. This results in a SQL error: "Invalid column name 'KeyPart1'" (or whichever column is part of the composite key).
Steps to Reproduce:
Use EF 6.5.2.
Define an entity with a composite key (e.g., [KeyPart1], [KeyPart2]).
Call db.UpdateFromQuery(query, ...) for a batch update.
Observe the generated SQL and the resulting exception.
Actual Behavior (Based on real trace):
The generated SQL follows this pattern:
UPDATE A
SET A.[TargetProperty] = @p__linq__0
FROM [MainEntity] AS A
INNER JOIN (
SELECT
[Project1].[C1] AS [C1],
[Project1].[C2] AS [C2],
-- ... other aliased columns ...
[Project1].[C30] AS [C30]
FROM (
SELECT
[Extent1].[KeyPart1] AS [C1],
[Extent1].[KeyPart2] AS [C2],
-- ... other columns ...
FROM [dbo].[MainEntity] AS [Extent1]
WHERE ...
) AS [Project1]
WHERE ([Project1].[C1] IN (...)) AND ([Project1].[C1] IS NOT NULL)
) AS B ON A.[KeyPart1] = B.[KeyPart1] -- ERROR: B does not have a column named 'KeyPart1', it has 'C1'
AND A.[KeyPart2] = B.[KeyPart2] -- ERROR: B does not have a column named 'KeyPart2', it has 'C2'
Expected Behavior:
The JOIN clause should either:
Use the aliases generated in the subquery (e.g., ON A.[KeyPart1] = B.[C1]).
Or, the subquery should preserve the original column names so the outer join can resolve them.
Workaround used:
Due to the production impact, I had to bypass UpdateFromQuery and implement a manual set-based update using ExecuteSqlCommand.
Additional Context:
This issue appears to be a regression in how EF 6.5.2 handles query translation/aliasing compared to previous versions (like EF 6.2.0), which breaks the SQL generation logic in Z.EntityFramework.Extensions.