9
Vote

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.

comments

moozzyk wrote Mar 21, 2013 at 7: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 10: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.