10

Closed

union of two select queries with navigation properties fails

description

var query1 = db.CustomersA.Include(c => c.Orders).Select(c => new { ID = c.ID, Orders = c.Orders });
var query2 = db.CustomersB.Include(c => c.Orders).Select(c => new { ID = c.ID, Orders = c.Orders });
var query3 = query1.Union(query2);

Console.WriteLine(query3.ToString());
query3 fails to get translated with the following stack trace,
Unhandled Exception: System.ArgumentException: The 'Distinct' operation cannot b
e applied to the collection ResultType of the specified argument.
Parameter name: argument
   at System.Data.Common.CommandTrees.ExpressionBuilder.Internal.ArgumentValidat
ion.ValidateDistinct(DbExpression argument)
   at System.Data.Objects.ELinq.ExpressionConverter.MethodCallTranslator.UnionTr
anslator.TranslateBinary(ExpressionConverter parent, DbExpression left, DbExpres
sion right)
   at System.Data.Objects.ELinq.ExpressionConverter.MethodCallTranslator.BinaryS
equenceMethodTranslator.Translate(ExpressionConverter parent, MethodCallExpressi
on call)
   at System.Data.Objects.ELinq.ExpressionConverter.MethodCallTranslator.Sequenc
eMethodTranslator.Translate(ExpressionConverter parent, MethodCallExpression cal
l, SequenceMethod sequenceMethod)
   at System.Data.Objects.ELinq.ExpressionConverter.MethodCallTranslator.TypedTr
anslate(ExpressionConverter parent, MethodCallExpression linq)
   at System.Data.Objects.ELinq.ExpressionConverter.TypedTranslator`1.Translate(
ExpressionConverter parent, Expression linq)
   at System.Data.Objects.ELinq.ExpressionConverter.TranslateExpression(Expressi
on linq)
   at System.Data.Objects.ELinq.ExpressionConverter.Convert()
   at System.Data.Objects.ELinq.ELinqQueryState.GetExecutionPlan(Nullable`1 forM
ergeOption)
   at System.Data.Objects.ObjectQuery.ToTraceString()
   at System.Data.Entity.Internal.Linq.InternalQuery`1.ToString()
   at System.Data.Entity.Infrastructure.DbQuery`1.ToString()
   at SOTests.Program.Main(String[] args) in c:\Users\ranadimi\Documents\Visual
Studio 2012\Projects\SOTests\SOTests\Program.cs:line 39
To get that working, I have to move the navigation properties out of the union and fetch them later like this,
var query1 = db.CustomersA.Include(c => c.Orders).Select(c => new { ID = c.ID });
var query2 = db.CustomersB.Include(c => c.Orders).Select(c => new { ID = c.ID });
var query3 = query1.Union(query2);
var query4 = query3.Select(q => new { ID = q.ID, Orders = db.Orders.Where(o => o.CustomerA.ID == q.ID || o.CustomerB.ID == q.ID) });

Console.WriteLine(query4.ToString());
Models should be self explanatory, but in case,
    public class Customer
    {
        public int ID { get; set; }

        public string Name { get; set; }

        public virtual ICollection<Order> Orders { get; set; }
    }

    public class Order
    {
        public int ID { get; set; }

        public CustomerA CustomerA { get; set; }

        public CustomerB CustomerB { get; set; }
    }

    public class CustomerA
    {
        public int ID { get; set; }

        public string Name { get; set; }

        public virtual ICollection<Order> Orders { get; set; }
    }

    public class CustomerB
    {
        public int ID { get; set; }

        public string Name { get; set; }

        public virtual ICollection<Order> Orders { get; set; }
    }

    public class CustomersContext : DbContext
    {
        public DbSet<CustomerA> CustomersA { get; set; }

        public DbSet<CustomerB> CustomersB { get; set; }

        public DbSet<Order> Orders { get; set; }
    }
Note: tried it with 6.0.0-alpha3-20320 with no success.
Closed Dec 8, 2016 at 9:41 PM by RoMiller
EF Team Triage: We are transitioning this project to GitHub (https://github.com/aspnet/EntityFramework6). As part of this transition we are bulk closing a large number of issues in order that our new issue tracker will accurately reflect the work that our team is planning to complete on the EF6.x code base.

Moving forwards, our team will be fixing bugs, implementing small improvements, and accepting community contributions to the EF6.x code base. Larger feature work and innovation will happen in the EF Core code base (https://github.com/aspnet/EntityFramework). Closing a feature request in the EF6.x project does not exclude us implementing the feature in EF Core. In fact, a number of popular feature requests for EF have already been implemented in EF Core (alternate keys, batching in SaveChanges, etc.).

This is a bulk message to indicate that this issue was closed and not ported to the new issue tracker. The reasons for not porting this particular issue to GitHub may include:
  • It was a bug report that does not contain sufficient information for us to be able to reproduce it
  • It was a question, but sufficient time has passed that it's not clear that taking the time to answer it would provide value to the person who asked it
  • It is a feature request that we are realistically not going to implement on the EF6.x code base
    Although this issue was not ported, you may still re-open it in the new issue tracker for our team to reconsider (https://github.com/aspnet/EntityFramework6/issues). We will no longer be monitoring this issue tracker for comments, so please do not reply here.

comments

moozzyk wrote Mar 21, 2013 at 6:08 PM

This seems to be by design (or a limitation). For set operations (UNION, INTERSECT, EXCEPT) we only allow model types and "flat" transient types (i.e. row types (e.g. created for projections) without collection properties) - see TypeHelpers.IsSetComparableOpType.
The workaround here would be to do the union on the client by enforcing query execution i.e. instead of doing this:

var query3 = query1.Union(query2);

do this:

var query3 = query1.ToList().Union(query2);

RoMiller wrote Mar 21, 2013 at 9:57 PM

EF Team Triage: We agree that this would be a good scenario to enable. Taking into account where we are in the EF6 release along with the size and the impact of this feature our team is not planning to implement it in EF6. Therefore, we are moving it to the Future release to reconsider in the next release.