Query: Support in-memory processing of INTERSECT,CONCAT,EXCEPT,UNION

[PrashanthIBM]

Hi ,

I am using EF7BETA6 and trying to run simple set operators and getting an exception.
I think the issue is similar to "Problem with Union Operator #3459 "

Sample Program :

var query = context.ORDERS.Where(o => o.SHIPCOUNTRY == "Mexico").Select(o => o.Customer).
Except(context.ORDERS.Where(o => o.SHIPCOUNTRY == "Canada").Select(o => o.Customer));

        foreach (var val in query) // getting exception at this point.

---

var mexico = context.ORDERSS.Where(o => o.SHIPCOUNTRY == "Mexico").Select(o => o);
        var canada = context.ORDERSS.Where(o => o.SHIPCOUNTRY == "Canada").Select(o => o);

        var query = mexico.Concat(canada);

foreach (var val in query) // getting exception at this point.

---

Similarly for Intersect and Union.

Stack trace :
at Microsoft.Data.Entity.Query.ResultOperatorHandler.HandleResultOperator(EntityQueryModelVisitor entityQueryModelVisitor, ResultOperatorBase resultOperator, QueryModel queryModel) in C:\Users\praskarn\Desktop\EF7\EntityFramework-7.0.0-beta6\src\EntityFramework.Core\Query\ResultOperatorHandler.cs:line 58
at Microsoft.Data.Entity.Query.RelationalResultOperatorHandler.HandlerContext.get_EvalOnClient() in C:\Users\praskarn\Desktop\EF7\EntityFramework-7.0.0-beta6\src\EntityFramework.Relational\Query\RelationalResultOperatorHandler.cs:line 59
at Microsoft.Data.Entity.Query.RelationalResultOperatorHandler.HandleResultOperator(EntityQueryModelVisitor entityQueryModelVisitor, ResultOperatorBase resultOperator, QueryModel queryModel) in C:\Users\praskarn\Desktop\EF7\EntityFramework-7.0.0-beta6\src\EntityFramework.Relational\Query\RelationalResultOperatorHandler.cs:line 117
at Microsoft.Data.Entity.Query.EntityQueryModelVisitor.VisitResultOperator(ResultOperatorBase resultOperator, QueryModel queryModel, Int32 index) in

Please let me know if the issue is fixed in new beta releases.

Thanks and Regards,
Prashanth.

[rowanmiller]

Please try against the latest release (Beta 8) and let us know if this still happens (we've made a lot of progress on query since Beta6)

[PrashanthIBM]

Hi Rowan,

Seems like set operators are not working with RC1 too.

Refer #3675

Thanks ,
Prashanth.

[rowanmiller]

Have you tried it on RC1, #3675 is a different issue

[PrashanthIBM]

Since the driver code is based on beta6 and it will take some time for me to upgrade to latest, i had asked @AlekseyMartynov in 3675 and he validated that set operators are not working with RC1 .

Excerpt from #3675
"
Are you able to use set operators like UNION, INTERSECT, EXCEPT, CONCAT .... in rc1 ?

No, they fail too.
"

Thanks and Regards,
Prashanth.

[leus]

Using beta8:

            var qry = (from u in db.Unidades
                       select new { Id = u.Id, ParentId = u.AreaId, u.Nombre }).Union(
                        from a in db.Areas
                        select new { Id = a.Id, ParentId = a.GerenciaId, a.Nombre }).Union(
                        from g in db.Gerencias
                        select new { Id = g.Id, ParentId = (int?)0, g.Nombre });

Error thrown:

    NotImplementedException: Remotion.Linq.Clauses.ResultOperators.UnionResultOperator

Stack trace:

Microsoft.Data.Entity.Query.ResultOperatorHandler.HandleResultOperator(EntityQueryModelVisitor entityQueryModelVisitor, ResultOperatorBase resultOperator, QueryModel queryModel)
Microsoft.Data.Entity.Query.Internal.RelationalResultOperatorHandler.HandlerContext.EvalOnClient(Boolean requiresClientResultOperator)
Microsoft.Data.Entity.Query.Internal.RelationalResultOperatorHandler.HandleResultOperator(EntityQueryModelVisitor entityQueryModelVisitor, ResultOperatorBase resultOperator, QueryModel queryModel)
Microsoft.Data.Entity.Query.EntityQueryModelVisitor.VisitResultOperator(ResultOperatorBase resultOperator, QueryModel queryModel, Int32 index)
Microsoft.Data.Entity.Query.RelationalQueryModelVisitor.VisitResultOperator(ResultOperatorBase resultOperator, QueryModel queryModel, Int32 index)
Remotion.Linq.Clauses.ResultOperatorBase.Accept(IQueryModelVisitor visitor, QueryModel queryModel, Int32 index)
Remotion.Linq.QueryModelVisitorBase.VisitResultOperators(ObservableCollection`1 resultOperators, QueryModel queryModel)
Remotion.Linq.QueryModelVisitorBase.VisitQueryModel(QueryModel queryModel)
Microsoft.Data.Entity.Query.EntityQueryModelVisitor.VisitQueryModel(QueryModel queryModel)
Microsoft.Data.Entity.Query.RelationalQueryModelVisitor.VisitQueryModel(QueryModel queryModel)
Microsoft.Data.Entity.Query.Internal.SqlServerQueryModelVisitor.VisitQueryModel(QueryModel queryModel)
Microsoft.Data.Entity.Query.ExpressionVisitors.RelationalEntityQueryableExpressionVisitor.VisitSubQuery(SubQueryExpression subQueryExpression)
Remotion.Linq.Clauses.Expressions.SubQueryExpression.Accept(ExpressionVisitor visitor)
System.Linq.Expressions.ExpressionVisitor.Visit(Expression node)
Microsoft.Data.Entity.Query.ExpressionVisitors.ExpressionVisitorBase.Visit(Expression expression)
Microsoft.Data.Entity.Query.EntityQueryModelVisitor.ReplaceClauseReferences(Expression expression, IQuerySource querySource)
Microsoft.Data.Entity.Query.EntityQueryModelVisitor.CompileMainFromClauseExpression(MainFromClause mainFromClause, QueryModel queryModel)
Microsoft.Data.Entity.Query.RelationalQueryModelVisitor.CompileMainFromClauseExpression(MainFromClause mainFromClause, QueryModel queryModel)
Microsoft.Data.Entity.Query.EntityQueryModelVisitor.VisitMainFromClause(MainFromClause fromClause, QueryModel queryModel)
Remotion.Linq.Clauses.MainFromClause.Accept(IQueryModelVisitor visitor, QueryModel queryModel)
Remotion.Linq.QueryModelVisitorBase.VisitQueryModel(QueryModel queryModel)
Microsoft.Data.Entity.Query.EntityQueryModelVisitor.VisitQueryModel(QueryModel queryModel)
Microsoft.Data.Entity.Query.RelationalQueryModelVisitor.VisitQueryModel(QueryModel queryModel)
Microsoft.Data.Entity.Query.Internal.SqlServerQueryModelVisitor.VisitQueryModel(QueryModel queryModel)
Microsoft.Data.Entity.Query.EntityQueryModelVisitor.CreateQueryExecutor[TResult](QueryModel queryModel)
Microsoft.Data.Entity.Storage.Database.CompileQuery[TResult](QueryModel queryModel)
--- End of stack trace from previous location where exception was thrown ---
System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()
Microsoft.Data.Entity.Query.QueryCompiler.<>c__DisplayClass16_0`1.<CompileQuery>b__0()
Microsoft.Data.Entity.Query.Internal.CompiledQueryCache.GetOrAddQuery[TResult](Object cacheKey, Func`1 compiler)
Microsoft.Data.Entity.Query.QueryCompiler.CompileQuery[TResult](Expression query)
Microsoft.Data.Entity.Query.QueryCompiler.Execute[TResult](Expression query)
Microsoft.Data.Entity.Query.Internal.EntityQueryProvider.Execute[TResult](Expression expression)
Remotion.Linq.QueryableBase`1.GetEnumerator()
System.Collections.Generic.List`1..ctor(IEnumerable`1 collection)
System.Linq.Enumerable.ToList[TSource](IEnumerable`1 source)

[divega]

@anpete this looks like something @smitpatel could look at, with the premise that this should work in memory. Do you agree?

[anpete]

@divega Sounds good.

[capesean]

Is this the same issue as I've asked about here:
http://stackoverflow.com/questions/34790543/entity-framework-7-union-query-error?noredirect=1

[divega]

Yes, it looks like it is the same issue.

[smitpatel]

We have not included set operators in supported ResultOperators. Hence this does not work even for InMemory.
This list defines supported result operators in EF7.
https://github.com/aspnet/EntityFramework/blob/dev/src/EntityFramework.Core/Query/ResultOperatorHandler.cs#L23

[divega]

Thanks @smitpatel. I have cleared up the milestone so that we can re-triage. Is it fine to assume that adding these so that they always resolve in memory would be very easy?

[smitpatel]

I am not certain about it. Probably @anpete can answer that.