Web API, OData, EF5, Union: The 'Distinct' operation cannot be applied to the collection ResultType of the specified argument

Question

I'm working on a Web API prototype using the OData Nuget package. I'm having some issues getting a LINQ to EF query to work.

Here are my data model. It has been highly simplified.

I'm trying to get the query to work using this DTO:

public class Product
{
    public int Id { get; set; }
    public string Name { get; set; }
    public IEnumerable<Order> Orders { get; set; }
}

public class Order
{
    public int Id { get; set; }
    public DateTime Date { get; set; }
}

The query looks like this:

[Queryable]
public IQueryable<Product> Get()
{
    var productA = _context.ProductA
                            .Select(p => new Product
                                {
                                    Id = p.id,
                                    Name = p.name,
                                    Orders = p.ProductAOrders.Select(o => new Order
                                        {
                                            Id = o.OrderId,
                                            Date = o.Orders.Date,
                                        })
                                });

    var productB = _context.ProductB
                            .Select(p => new Product
                                {
                                    Id = p.Id,
                                    Name = p.Name,
                                    Orders = p.ProductBOrders.Select(o => new Order
                                        {
                                            Id = o.OrderId,
                                            Date = o.Orders.Date,
                                        })
                                });

    return productA.Union(productB);
}

When trying to Union the two queries I get this error:

<Error><Message>An error has occurred.</Message><ExceptionMessage>The 'Distinct' operation cannot be applied to the collection ResultType of the specified argument.
Parameter name: argument</ExceptionMessage><ExceptionType>System.ArgumentException</ExceptionType><StackTrace>   at System.Data.Common.CommandTrees.ExpressionBuilder.Internal.ArgumentValidation.ValidateDistinct(DbExpression argument)
   at System.Data.Objects.ELinq.ExpressionConverter.MethodCallTranslator.UnionTranslator.TranslateBinary(ExpressionConverter parent, DbExpression left, DbExpression right)
   at System.Data.Objects.ELinq.ExpressionConverter.MethodCallTranslator.BinarySequenceMethodTranslator.Translate(ExpressionConverter parent, MethodCallExpression call)
   at System.Data.Objects.ELinq.ExpressionConverter.MethodCallTranslator.SequenceMethodTranslator.Translate(ExpressionConverter parent, MethodCallExpression call, SequenceMethod sequenceMethod)
   at System.Data.Objects.ELinq.ExpressionConverter.MethodCallTranslator.TypedTranslate(ExpressionConverter parent, MethodCallExpression linq)
   at System.Data.Objects.ELinq.ExpressionConverter.TypedTranslator`1.Translate(ExpressionConverter parent, Expression linq)
   at System.Data.Objects.ELinq.ExpressionConverter.TranslateExpression(Expression linq)
   at System.Data.Objects.ELinq.ExpressionConverter.Convert()
   at System.Data.Objects.ELinq.ELinqQueryState.GetExecutionPlan(Nullable`1 forMergeOption)
   at System.Data.Objects.ObjectQuery.ToTraceString()
   at System.Data.Entity.Internal.Linq.InternalQuery`1.ToString()
   at System.Data.Entity.Infrastructure.DbQuery`1.ToString()
   at System.Convert.ToString(Object value, IFormatProvider provider)
   at System.Web.Http.Tracing.Tracers.HttpActionDescriptorTracer.&lt;ExecuteAsync&gt;b__2(TraceRecord tr, Object value)
   at System.Web.Http.Tracing.ITraceWriterExtensions.&lt;&gt;c__DisplayClass1b`1.&lt;&gt;c__DisplayClass1f.&lt;TraceBeginEndAsync&gt;b__13(TraceRecord traceRecord)
   at System.Web.Http.Tracing.SystemDiagnosticsTraceWriter.Trace(HttpRequestMessage request, String category, TraceLevel level, Action`1 traceAction)
   at System.Web.Http.Tracing.ITraceWriterExtensions.&lt;&gt;c__DisplayClass1b`1.&lt;TraceBeginEndAsync&gt;b__12(TResult result)
   at System.Threading.Tasks.TaskHelpersExtensions.&lt;&gt;c__DisplayClass3b`2.&lt;Then&gt;b__3a(Task`1 t)
   at System.Threading.Tasks.TaskHelpersExtensions.ThenImpl[TTask,TOuterResult](TTask task, Func`2 continuation, CancellationToken cancellationToken, Boolean runSynchronously)
</StackTrace></Error>

I can return either productA or productB - but returning a Union of these 2 queries result in the distinct error above.

Any ideas to what I might be doing wrong?

Answer 1

Looks like an EF bug. I assume you are trying to get MEST (multiple entity sets of same type) working. Instead of the query that you had, you can try,

public IQueryable<Product> Get()
{
    var productA = _context.ProductA
                            .Select(p => new Product
                            {
                                Id = p.id,
                                Name = p.name,
                            });

    var productB = _context.ProductB
                            .Select(p => new Product
                            {
                                Id = p.Id,
                                Name = p.Name,
                            });

    return
        productA
        .Union(productB)
        .Select(p => new Product
        {
            Id = p.Id,
            Name = p.Name,
            Orders = _context.Orders
                    .Where(o => o.ProductA.Id == p.Id || o.ProductB.Id == p.Id)
                    .Select(o => new Order
                    {
                        Id = o.OrderId,
                        Date = o.Orders.Date,
                    })
        });
}

The idea is to take the navigation properties out of the union and add them later back. This would work only if Orders had back pointer to ProductA or ProductB.

Answer 2

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). 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);