Query: Translate querys with filter on sub-query to a single SQL statement #7122
Assignees
Labels
closed-fixed
The issue has been fixed and is/will be included in the release indicated by the issue milestone.
type-enhancement
Milestone
Comments
The model context |
DrDrDrHistoryDrStatusThe others shouldn't be important, but let me know if they're needed nonetheless. |
|
Try using FirstOrDefault instead of First. We never translate First because we are unable to properly simulate its behavior for empty collection (i.e. Throwing exception) |
|
Interesting thought, so I tried Unfortunately it's doing the same thing, albeit without the warnings. It's filtering in the query for the project ID (using |
|
Note to Triage/self: could be related to #6937 |
|
Maybe, but keep in mind the navigation link isn't optional. It used to be optional, but I changed it to just a plain int and it behaves the same in both instances. |
|
This is fixed in the current bits. We produce the following queries: main: SELECT [dr].[id], [dr].[assigned], [dr].[ata], [dr].[custsupportref], [dr].[date_required], [dr].[dateofrepro], [dr].[descr1], [dr].[descr2], [dr].[discrepancyid], [dr].[documenttypeid], [dr].[documentversion], [dr].[due_date], [dr].[flightcond], [dr].[load], [dr].[mano], [dr].[priority], [dr].[proj_dr], [dr].[project], [dr].[referencedata], [dr].[relationshipid], [dr].[relationshipto], [dr].[reprodescr], [dr].[reprodoneby], [dr].[reproducible], [dr].[reprotechnicalanalysis], [dr].[reprotrainingdevice], [dr].[softwarelog], [dr].[softwarelogoriginalname], [dr].[softwaretitle], [dr].[tags], [dr].[test_guide]
FROM [dr_dr] AS [dr]
WHERE [dr].[project] IN (1, 2, 3) AND ((
SELECT TOP(1) [d].[status]
FROM [dr_history] AS [d]
WHERE [dr].[id] = [d].[dr_num]
) = 3)
ORDER BY [dr].[proj_dr], [dr].[id]
OFFSET @__p_1 ROWS FETCH NEXT @__p_2 ROWS ONLYinclude1: SELECT [d1].[dr_num], [d1].[hist_num], [d1].[addedfilesgroup], [d1].[assigned], [d1].[attachmentID], [d1].[author], [d1].[date], [d1].[deletedfilesgroup], [d1].[descr], [d1].[enteredby], [d1].[fixedfilesgroup], [d1].[internal], [d1].[log_user_id], [d1].[status], [d1].[version], [d2].[id], [d2].[descr], [d2].[sortid], [d2].[status]
FROM [dr_history] AS [d1]
INNER JOIN (
SELECT DISTINCT [t0].*
FROM (
SELECT [dr].[proj_dr], [dr].[id]
FROM [dr_dr] AS [dr]
WHERE [dr].[project] IN (1, 2, 3) AND ((
SELECT TOP(1) [d].[status]
FROM [dr_history] AS [d]
WHERE [dr].[id] = [d].[dr_num]
) = 3)
ORDER BY [dr].[proj_dr], [dr].[id]
OFFSET @__p_1 ROWS FETCH NEXT @__p_2 ROWS ONLY
) AS [t0]
) AS [dr1] ON [d1].[dr_num] = [dr1].[id]
INNER JOIN [dr_status] AS [d2] ON [d1].[status] = [d2].[id]
ORDER BY [dr1].[proj_dr], [dr1].[id]collection include: SELECT [dr.DrHistory].[dr_num], [dr.DrHistory].[hist_num], [dr.DrHistory].[addedfilesgroup], [dr.DrHistory].[assigned], [dr.DrHistory].[attachmentID], [dr.DrHistory].[author], [dr.DrHistory].[date], [dr.DrHistory].[deletedfilesgroup], [dr.DrHistory].[descr], [dr.DrHistory].[enteredby], [dr.DrHistory].[fixedfilesgroup], [dr.DrHistory].[internal], [dr.DrHistory].[log_user_id], [dr.DrHistory].[status], [dr.DrHistory].[version]
FROM [dr_history] AS [dr.DrHistory]
INNER JOIN (
SELECT [dr0].[id], [dr0].[proj_dr]
FROM [dr_dr] AS [dr0]
WHERE [dr].[project] IN (1, 2, 3) AND ((
SELECT TOP(1) [d0].[status]
FROM [dr_history] AS [d0]
WHERE [dr0].[id] = [d0].[dr_num]
) = 3)
ORDER BY [dr0].[proj_dr], [dr0].[id]
OFFSET @__p_1 ROWS FETCH NEXT @__p_2 ROWS ONLY
) AS [t] ON [dr.DrHistory].[dr_num] = [t].[id]
ORDER BY [t].[proj_dr], [t].[id] |
maumar
added
closed-fixed
ajcvickers
changed the title
divega
changed the title
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment
Code that fails
Expectation & Result
I'd expect it to run in a single query, but what happens is hundreds of little queries to
dr_statusand one large query that filters ondr_projas expected. In fact if I remove theDrStatuspart of the filter, it all works exactly as expected with no problems.Trace
Further technical details
EF Core version: 1.1.0
Operating system: Win7 x64 SP1
Visual Studio version: VS2015 U3
Other details about my project setup: This is a vanilla ASP.NET Core 1.0.1 project, if that matters. I will add the full model classes in a comment below, they're pretty large.
The text was updated successfully, but these errors were encountered: