Geek Answers Handbook

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

I am working on a prototype Web API using the OData Nuget package. I'm having trouble getting a LINQ to EF request.

Here is my data model. This has been greatly simplified.

I am trying to get the request 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 request is as follows:

 [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 combine 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 the Union from these 2 queries will result in the distinguishing error above.

Any ideas what I can do wrong?

+4
source share
3 answers

Looks like an EF error. I assume that you are trying to get MEST (multiple sets of entities of the same type). Instead of the query 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 pull the navigation properties out of the union and add them later. This will only work if Orders returns a pointer to ProductA or ProductB.

+4
source

This is similar to design (or limitation). For set operations (UNION, INTERSECT, EXCEPT), we only allow model types and “flat” transition types (that is, row types (for example, those created for forecasts) without collection properties). The workaround here would be to make the union on the client by executing the query, i.e. instead:

 var query3 = query1.Union(query2);

do the following:

 var query3 = query1.ToList().Union(query2);
+2
source

Just tried this query ... this seems to be the only way to use navigation properties.

 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.ProductAOrders .Where(x => x.ProductAId == p.Id) .Select(o => new Order { Id = o.ProductAId, Date = o.Orders.Date }) .Union( _context.ProductBOrders .Where(x => x.ProductBId == p.Id) .Select(o => new Order { Id = o.ProductBId, Date = o.Orders.Date })) });

This will result in an error:

 <Error><Message>An error has occurred.</Message><ExceptionMessage>The type 'API.Models.Product' appears in two structurally incompatible initializations within a single LINQ to Entities query. A type can be initialized in two places in the same query, but only if the same properties are set in both places and those properties are set in the same order.</ExceptionMessage><ExceptionType>System.NotSupportedException</ExceptionType><StackTrace> at System.Data.Objects.ELinq.ExpressionConverter.ValidateInitializerMetadata(InitializerMetadata metadata) at System.Data.Objects.ELinq.ExpressionConverter.MemberInitTranslator.TypedTranslate(ExpressionConverter parent, MemberInitExpression 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.TranslateLambda(LambdaExpression lambda, DbExpression input) at System.Data.Objects.ELinq.ExpressionConverter.TranslateLambda(LambdaExpression lambda, DbExpression input, DbExpressionBinding&amp; binding) at System.Data.Objects.ELinq.ExpressionConverter.MethodCallTranslator.OneLambdaTranslator.Translate(ExpressionConverter parent, MethodCallExpression call, DbExpression&amp; source, DbExpressionBinding&amp; sourceBinding, DbExpression&amp; lambda) at System.Data.Objects.ELinq.ExpressionConverter.MethodCallTranslator.SelectTranslator.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 cannot understand why "API.Models.Product" appears in two structurally incompatible initializations in the same LINQ to Entities query.

If I replaced

  Orders = _context.ProductAOrders .Where(x => x.ProductAId == p.Id) .Select(o => new Order { Id = o.ProductAId, Date = o.Orders.Date }) .Union( _context.ProductBOrders .Where(x => x.ProductBId == p.Id) .Select(o => new Order { Id = o.ProductBId, Date = o.Orders.Date }))

With this (for simplification) I get the same error

  Orders = _context.Orders.Select(o => new Order { Id = o.Id, Date = o.Date })

LINQ to EF doesn't seem to be my best friend these days :)

-1
source

More articles:


All Articles