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.

Sign up for GitHub

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

Jump to bottom

Query: Join flattening fails for some cases involving SelectMany #4539

Closed
maumar opened this issue Feb 12, 2016 · 3 comments
Closed

Query: Join flattening fails for some cases involving SelectMany #4539

maumar opened this issue Feb 12, 2016 · 3 comments
Labels
closed-fixed The issue has been fixed and is/will be included in the release indicated by the issue milestone. type-bug
Milestone
1.0.1

Comments

@maumar
Copy link
Contributor

maumar commented Feb 12, 2016

QueryFlattener doesn't work for specific shape of queries - e.g. Join-SelectMany

                var query = from l1 in context.LevelOne
                            join l1_Optional in context.LevelTwo on (int?)l1.Id equals EF.Property<int?>(l1_Optional, "Level1_Optional_Id") into grouping
                            from l1_Optional in grouping.DefaultIfEmpty()
                            from l2 in context.LevelTwo
                            join l2_Required_Reverse in context.LevelOne on l2.Level1_Required_Id equals l2_Required_Reverse.Id
                            select new { l1_Optional, l2_Required_Reverse };

this throws:

Additional information: Unable to cast object of type 'System.Linq.Expressions.Expression1[System.Func3[Microsoft.EntityFrameworkCore.Query.EntityQueryModelVisitor+TransparentIdentifier2[Microsoft.EntityFrameworkCore.Query.EntityQueryModelVisitor+TransparentIdentifier2[Microsoft.EntityFrameworkCore.Storage.ValueBuffer,System.Collections.Generic.IEnumerable1[Microsoft.EntityFrameworkCore.FunctionalTests.TestModels.ComplexNavigationsModel.Level2]],Microsoft.EntityFrameworkCore.FunctionalTests.TestModels.ComplexNavigationsModel.Level2],Microsoft.EntityFrameworkCore.Storage.ValueBuffer,Microsoft.EntityFrameworkCore.Query.EntityQueryModelVisitor+TransparentIdentifier2[Microsoft.EntityFrameworkCore.Query.EntityQueryModelVisitor+TransparentIdentifier2[Microsoft.EntityFrameworkCore.Query.EntityQueryModelVisitor+TransparentIdentifier2[Microsoft.EntityFrameworkCore.Storage.ValueBuffer,System.Collections.Generic.IEnumerable`1[Microsoft.EntityFrameworkCore.FunctionalTests.TestModels.ComplexNavigationsModel.Level2]],Microsoft.EntityFrameworkCore.FunctionalTests.TestModels.ComplexNavigationsModel.Level2],Microsoft.EntityFrameworkCore.Storage.ValueBuffer]]]' to type 'System.Linq.Expressions.ConstantExpression'.

at Microsoft.EntityFrameworkCore.Query.ExpressionVisitors.Internal.QueryFlattener.Flatten(MethodCallExpression methodCallExpression) in D:\k\EntityFramework\src\Microsoft.EntityFrameworkCore.Relational\Query\ExpressionVisitors\Internal\QueryFlattener.cs:line 45
at Microsoft.EntityFrameworkCore.Query.RelationalQueryModelVisitor.OptimizeJoinClause(JoinClause joinClause, QueryModel queryModel, Int32 index, Action baseVisitAction, MethodInfo operatorToFlatten, Boolean outerJoin) in D:\k\EntityFramework\src\Microsoft.EntityFrameworkCore.Relational\Query\RelationalQueryModelVisitor.cs:line 485
at Microsoft.EntityFrameworkCore.Query.RelationalQueryModelVisitor.VisitJoinClause(JoinClause joinClause, QueryModel queryModel, Int32 index) in D:\k\EntityFramework\src\Microsoft.EntityFrameworkCore.Relational\Query\RelationalQueryModelVisitor.cs:line 374
at Remotion.Linq.Clauses.JoinClause.Accept(IQueryModelVisitor visitor, QueryModel queryModel, Int32 index)
at Remotion.Linq.QueryModelVisitorBase.VisitBodyClauses(ObservableCollection`1 bodyClauses, QueryModel queryModel)
at Remotion.Linq.QueryModelVisitorBase.VisitQueryModel(QueryModel queryModel)
at Microsoft.EntityFrameworkCore.Query.EntityQueryModelVisitor.VisitQueryModel(QueryModel queryModel) in D:\k\EntityFramework\src\Microsoft.EntityFrameworkCore\Query\EntityQueryModelVisitor.cs:line 542
at Microsoft.EntityFrameworkCore.Query.RelationalQueryModelVisitor.VisitQueryModel(QueryModel queryModel) in D:\k\EntityFramework\src\Microsoft.EntityFrameworkCore.Relational\Query\RelationalQueryModelVisitor.cs:line 256
at Microsoft.EntityFrameworkCore.Query.Internal.SqlServerQueryModelVisitor.VisitQueryModel(QueryModel queryModel) in D:\k\EntityFramework\src\Microsoft.EntityFrameworkCore.SqlServer\Query\Internal\SqlServerQueryModelVisitor.cs:line 79
at Microsoft.EntityFrameworkCore.Query.EntityQueryModelVisitor.CreateQueryExecutor[TResult](QueryModel queryModel) in D:\k\EntityFramework\src\Microsoft.EntityFrameworkCore\Query\EntityQueryModelVisitor.cs:line 166
at Microsoft.EntityFrameworkCore.Storage.Database.CompileQuery[TResult](QueryModel queryModel) in D:\k\EntityFramework\src\Microsoft.EntityFrameworkCore\Storage\Database.cs:line 67
@maumar maumar changed the title Query :: flattening bug template Query :: Join flattening fails for some complex cases involving SelectMany Feb 12, 2016
@maumar
Copy link
Contributor Author

maumar commented Feb 12, 2016

added test for this case: ComplexNavigationsQueryTestBase -> Join_flattening_bug_4539 (fails for SqlServer)

@rowanmiller rowanmiller added this to the 1.0.0 milestone Feb 12, 2016
@maumar maumar mentioned this issue Feb 17, 2016
Closed
maumar added a commit that referenced this issue Feb 18, 2016
@maumar
Initial support for optional navigation translation into LEFT OUTER J…
09fba05 
…OIN.

Currently we translate all navigations into INNER JOINs, which may produce invalid results for some queries.

Fix is to translate optional navigations into LEFT OUTER JOIN. In order to do that we convert optional navigation into SelectMany-GroupJoin-DefaultIfEmpty expression, that translates to LOJ.
We also need to add null checks in various places because now the intermittent results can be null.

e.g.
from c in ctx.Customers
where c.Detail.Name == "Foo"
select c

will get translated into:

from c in ctx.Customers
join d in ctx.Detail on c.Id equals d.CustomerId into grouping
from d in grouping.DefaultIfEmpty()
where (d != null ? d.Name : null) == "Foo"
select c;

Also fixed a number of bugs uncovered by this change, due to generating significantly more complex queries in some cases.

Known issues:
- DefaultIfEmpty() cannot be translated into SQL so everything happening after it is computed on the client (filters, projections, nested navigations) (#4539),
- Optional navigations don't work with Include (#4589),
- Optional navigations don't work with some queries involving SelectMany operator (#4539),
- Optional navigations don't work for soem complex queries involving subqueries and/or navigation inside inner key selector of a Join statement (#4547)
maumar added a commit that referenced this issue Feb 18, 2016
@maumar
Initial support for optional navigation translation into LEFT OUTER J…
5284d5a 
…OIN.

Currently we translate all navigations into INNER JOINs, which may produce invalid results for some queries.

Fix is to translate optional navigations into LEFT OUTER JOIN. In order to do that we convert optional navigation into SelectMany-GroupJoin-DefaultIfEmpty expression, that translates to LOJ.
We also need to add null checks in various places because now the intermittent results can be null.

e.g.
from c in ctx.Customers
where c.Detail.Name == "Foo"
select c

will get translated into:

from c in ctx.Customers
join d in ctx.Detail on c.Id equals d.CustomerId into grouping
from d in grouping.DefaultIfEmpty()
where (d != null ? d.Name : null) == "Foo"
select c;

Also fixed a number of bugs uncovered by this change, due to generating significantly more complex queries in some cases.

Known issues:
- DefaultIfEmpty() cannot be translated into SQL so everything happening after it is computed on the client (filters, projections, nested navigations) (#4539),
- Optional navigations don't work with Include (#4589),
- Optional navigations don't work with some queries involving SelectMany operator (#4539),
- Optional navigations don't work for soem complex queries involving subqueries and/or navigation inside inner key selector of a Join statement (#4547)
maumar added a commit that referenced this issue Feb 18, 2016
@maumar
Initial support for optional navigation translation into LEFT OUTER J…
bb08227 
…OIN.

Currently we translate all navigations into INNER JOINs, which may produce invalid results for some queries.

Fix is to translate optional navigations into LEFT OUTER JOIN. In order to do that we convert optional navigation into SelectMany-GroupJoin-DefaultIfEmpty expression, that translates to LOJ.
We also need to add null checks in various places because now the intermittent results can be null.

e.g.
from c in ctx.Customers
where c.Detail.Name == "Foo"
select c

will get translated into:

from c in ctx.Customers
join d in ctx.Detail on c.Id equals d.CustomerId into grouping
from d in grouping.DefaultIfEmpty()
where (d != null ? d.Name : null) == "Foo"
select c;

Also fixed a number of bugs uncovered by this change, due to generating significantly more complex queries in some cases.

Known issues:
- DefaultIfEmpty() cannot be translated into SQL so everything happening after it is computed on the client (filters, projections, nested navigations) (#4539),
- Optional navigations don't work with Include (#4589),
- Optional navigations don't work with some queries involving SelectMany operator (#4539),
- Optional navigations don't work for soem complex queries involving subqueries and/or navigation inside inner key selector of a Join statement (#4547)
maumar added a commit that referenced this issue Feb 18, 2016
@maumar
Initial support for optional navigation translation into LEFT OUTER J…
c4e4447 
…OIN.

Currently we translate all navigations into INNER JOINs, which may produce invalid results for some queries.

Fix is to translate optional navigations into LEFT OUTER JOIN. In order to do that we convert optional navigation into SelectMany-GroupJoin-DefaultIfEmpty expression, that translates to LOJ.
We also need to add null checks in various places because now the intermittent results can be null.

e.g.
from c in ctx.Customers
where c.Detail.Name == "Foo"
select c

will get translated into:

from c in ctx.Customers
join d in ctx.Detail on c.Id equals d.CustomerId into grouping
from d in grouping.DefaultIfEmpty()
where (d != null ? d.Name : null) == "Foo"
select c;

Also fixed a number of bugs uncovered by this change, due to generating significantly more complex queries in some cases.

Known issues:
- DefaultIfEmpty() cannot be translated into SQL so everything happening after it is computed on the client (filters, projections, nested navigations) (#4539),
- Optional navigations don't work with Include (#4589),
- Optional navigations don't work with some queries involving SelectMany operator (#4539),
- Optional navigations don't work for soem complex queries involving subqueries and/or navigation inside inner key selector of a Join statement (#4547)

CR: Andrew
@maumar maumar mentioned this issue Mar 29, 2016
Closed
@maumar
Copy link
Contributor Author

maumar commented Mar 29, 2016

@anpete @divega multiple customers seem to be hitting this issue

@maumar
Copy link
Contributor Author

maumar commented Mar 29, 2016

...on the other hand there is a workaround for this particular case - break this into two separate queries.

@maumar maumar mentioned this issue Apr 19, 2016
Closed
@rowanmiller rowanmiller modified the milestones: 1.0.1, 1.0.0 May 9, 2016
@maumar maumar mentioned this issue Jun 6, 2016
Closed
anpete added a commit that referenced this issue Jun 30, 2016
@anpete
Query: Navigations: Three bug fixes.
c7518f3 
Fix: #3676 - Usage of the "let" keyword breaks grouping.
 - Fixed compiler bug in tracked, grouped queries.
Fix: #5427 - A column has been specified more than once in the order by list thrown for a complex query with orderby and navigation.
 - Fixed bug in SelectExpression.PrependToOrderBy
Fix: #4539 - Query :: Join flattening fails for some complex cases involving SelectMany
 - SelectMany after GroupJoin should not cause client-eval.

Also addressed most of the R# warnings in nav. rewriter.
anpete added a commit that referenced this issue Jun 30, 2016
@anpete
Query: Navigations: Three bug fixes.
d458e62 
Fix: #3676 - Usage of the "let" keyword breaks grouping.
 - Fixed compiler bug in tracked, grouped queries.
Fix: #5427 - A column has been specified more than once in the order by list thrown for a complex query with orderby and navigation.
 - Fixed bug in SelectExpression.PrependToOrderBy
Fix: #4539 - Query :: Join flattening fails for some complex cases involving SelectMany
 - SelectMany after GroupJoin should not cause client-eval.

Also addressed most of the R# warnings in nav. rewriter.
@anpete anpete mentioned this issue Jun 30, 2016
Closed
anpete added a commit that referenced this issue Jun 30, 2016
@anpete
Query: Navigations: Three bug fixes.
db978b9 
Fix: #3676 - Usage of the "let" keyword breaks grouping.
 - Fixed compiler bug in tracked, grouped queries.
Fix: #5427 - A column has been specified more than once in the order by list thrown for a complex query with orderby and navigation.
 - Fixed bug in SelectExpression.PrependToOrderBy
Fix: #4539 - Query :: Join flattening fails for some complex cases involving SelectMany
 - SelectMany after GroupJoin should not cause client-eval.

Also addressed most of the R# warnings in nav. rewriter.
@anpete anpete mentioned this issue Jul 1, 2016
Closed
@rowanmiller rowanmiller modified the milestones: 1.0.1, 1.1.0 Jul 1, 2016
anpete added a commit that referenced this issue Jul 1, 2016
@anpete
Query: Navigations: Three bug fixes.
12b9a63 
Fix: #3676 - Usage of the "let" keyword breaks grouping.
 - Fixed compiler bug in tracked, grouped queries.
Fix: #5427 - A column has been specified more than once in the order by list thrown for a complex query with orderby and navigation.
 - Fixed bug in SelectExpression.PrependToOrderBy
Fix: #4539 - Query :: Join flattening fails for some complex cases involving SelectMany
 - SelectMany after GroupJoin should not cause client-eval.

Also addressed most of the R# warnings in nav. rewriter.
anpete added a commit that referenced this issue Jul 3, 2016
@anpete
Query: Navigations: Three bug fixes.
8f18be6 
Fix: #3676 - Usage of the "let" keyword breaks grouping.
 - Fixed compiler bug in tracked, grouped queries.
Fix: #5427 - A column has been specified more than once in the order by list thrown for a complex query with orderby and navigation.
 - Fixed bug in SelectExpression.PrependToOrderBy
Fix: #4539 - Query :: Join flattening fails for some complex cases involving SelectMany
 - SelectMany after GroupJoin should not cause client-eval.

Also addressed most of the R# warnings in nav. rewriter.
@anpete anpete closed this as completed in bd1aa14 Jul 4, 2016
@anpete anpete added the closed-fixed The issue has been fixed and is/will be included in the release indicated by the issue milestone. label Jul 8, 2016
@anpete anpete removed their assignment Jul 8, 2016
@rowanmiller rowanmiller changed the title Query :: Join flattening fails for some complex cases involving SelectMany Query: Join flattening fails for some cases involving SelectMany Jul 21, 2016
@smitpatel smitpatel mentioned this issue Sep 10, 2016
Closed
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Assignees
No one assigned
Labels
closed-fixed The issue has been fixed and is/will be included in the release indicated by the issue milestone. type-bug
Projects
None yet
Milestone
1.0.1
Development

No branches or pull requests
4 participants
@Eilon @anpete @maumar @rowanmiller