Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Left Join and Group by is client evaluated #12522

Closed
sirJimmy1 opened this issue Jul 2, 2018 · 1 comment · Fixed by #18283
Closed

Left Join and Group by is client evaluated #12522

sirJimmy1 opened this issue Jul 2, 2018 · 1 comment · Fixed by #18283
Labels
closed-fixed The issue has been fixed and is/will be included in the release indicated by the issue milestone. customer-reported type-enhancement
Milestone

Comments

@sirJimmy1
Copy link

sirJimmy1 commented Jul 2, 2018

Hi, I am working with version EF core 2.1.1 and it seems to me that left join with group by not working.

Please see simple example:

var query = from table1 in _dbContext.Table1
join table2 in _dbContext.Table2 on table1.Table2_Id equals table2.Id
//join published in _dbContext.Table3Publish on table1.Id equals published.Table1Id into publishedGroup  
//  from pub in publishedGroup.DefaultIfEmpty()
                           
group table1 by new { table1.Column1, table1.Column2} into grp
select new { grp.Key.Column1, grp.Key.Column2 };

With commented left join part it is translated correctly,
unfortunately with left join it is translated as:

SELECT [All collumns from all joined tables]
FROM [table1] AS [table1_0]
INNER JOIN [table2] AS [table2_0] ON [table1_0].[Table2_Id] = [table2].[Id]
LEFT JOIN [Table3Publish] AS [published0] ON [table1_0].[Id] = [published0].[table1_Id]
ORDER BY [table1_0].[Id]

and it's grouped by in memory.

I found this issue has been resolved in #11755
but with EF core 2.1.1 it's not working again.

Further technical details
EF Core version: 2.1.1
Database Provider: Microsoft.EntityFrameworkCore.SqlServer
Operating system: Windows 10
IDE: Visual Studio 2017 15.7.3

@sirJimmy1 sirJimmy1 changed the title Left Join and Group by is not translated correctly in SQL [2.1.1] Left Join and Group by is not translated correctly in SQL Jul 2, 2018
@ajcvickers ajcvickers changed the title [2.1.1] Left Join and Group by is not translated correctly in SQL Left Join and Group by is not translated correctly in SQL Jul 2, 2018
@ajcvickers ajcvickers added this to the 3.0.0 milestone Jul 6, 2018
@ajcvickers ajcvickers modified the milestones: 3.0.0, Backlog Aug 3, 2018
@ajcvickers ajcvickers changed the title Left Join and Group by is not translated correctly in SQL Left Join and Group by is client evaluated Aug 3, 2018
@smitpatel smitpatel removed their assignment Sep 1, 2018
@cccvvv2012
Copy link

cccvvv2012 commented Feb 13, 2019

You may try two possible solutions for core 2.1.1

1
force select at first
var query = from table1 in _dbContext.Table1
join table2 in _dbContext.Table2 on table1.Table2_Id equals table2.Id
join published in _dbContext.Table3Publish on table1.Id equals published.Table1Id into publishedGroup
from pub in publishedGroup.DefaultIfEmpty()
select table1 into stable1
group stable1 by new { stable1.Column1, stable1.Column2} into grp
select new { grp.Key.Column1, grp.Key.Column2 };

2
use group by later
var query = from table1 in _dbContext.Table1
join table2 in _dbContext.Table2 on table1.Table2_Id equals table2.Id
join published in _dbContext.Table3Publish on table1.Id equals published.Table1Id into publishedGroup
from pub in publishedGroup.DefaultIfEmpty()
select table1;
query.GroupBy(t=> new { t.Column1, t.Column2}).Select(grp => new { grp.Key.Column1, grp.Key.Column2 });

@smitpatel smitpatel added the verify-fixed This issue is likely fixed in new query pipeline. label Jul 2, 2019
@ajcvickers ajcvickers modified the milestones: Backlog, 3.1.0 Sep 4, 2019
@maumar maumar added closed-fixed The issue has been fixed and is/will be included in the release indicated by the issue milestone. and removed verify-fixed This issue is likely fixed in new query pipeline. labels Oct 7, 2019
@maumar maumar modified the milestones: 3.1.0, 3.0.0 Oct 7, 2019
maumar added a commit that referenced this issue Oct 8, 2019
- added regression tests for #12289
- added regression tests for #15279
- added regression tests for #15938
- added regression tests for #18267

- resolves #12522
- resolves #12805
- resolves #13231
- resolves #13594
- resolves #13754
- resolves #14851
- resolves #15103
- resolves #15669
- resolves #15853
maumar added a commit that referenced this issue Oct 8, 2019
- added regression tests for #12289
- added regression tests for #15279
- added regression tests for #15938
- added regression tests for #18267

- resolves #12522
- resolves #12805
- resolves #13231
- resolves #13594
- resolves #13754
- resolves #14851
- resolves #15103
- resolves #15669
- resolves #15853
maumar added a commit that referenced this issue Oct 8, 2019
- added regression tests for #12289
- added regression tests for #15279
- added regression tests for #15938
- added regression tests for #18267

- resolves #12522
- resolves #12805
- resolves #13231
- resolves #13594
- resolves #13754
- resolves #14851
- resolves #15103
- resolves #15669
- resolves #15853
maumar added a commit that referenced this issue Oct 8, 2019
- added regression tests for #12289
- added regression tests for #15279
- added regression tests for #15938
- added regression tests for #18267

- resolves #12522
- resolves #12805
- resolves #13231
- resolves #13594
- resolves #13754
- resolves #14851
- resolves #15103
- resolves #15669
- resolves #15853
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Labels
closed-fixed The issue has been fixed and is/will be included in the release indicated by the issue milestone. customer-reported type-enhancement
Projects
None yet
Development

Successfully merging a pull request may close this issue.

5 participants