How to fix "The collection to which the Single aggregate is applied must be empty or contain exactly one item" InvalidOperationException?

Question

Having this query I get an InvalidOperationException: "The collection to which the Single aggregate is applied must be empty or contain exactly one item".

List<int> olsesUsedForTaskCompletion = new List<int>();
olsesUsedForTaskCompletion.AddRange(task.OrderLineSpecifications_QtysCompleted.Select(ols => ols.Key).ToList());

var allRelatedTasks = (from t in new XPQuery<Core.Model.Task.Task>(session, true)
                       join ols in new XPQuery<OrderLineSpecification>(session, true)
                       on t.PickSpecification equals ols.PickSpecification
                       where t.PickSpecification == task.PickSpecification
                          && t.Status != TaskStatuses.Cancelled
                          && olsesUsedForTaskCompletion.Contains(ols.Oid)
                       select t).ToList();

I want that when I do the join to get only OLS which have a specific Id. What am I doing wrong?

This is the stack trace:

at DevExpress.Xpo.Helpers.InTransactionLoader.ProcessException(Exception ex)
   at DevExpress.Xpo.Helpers.InTransactionLoader.ProcessAnalyzeAndExecQuery()
   at DevExpress.Xpo.Helpers.InTransactionLoader.Process()
   at DevExpress.Xpo.Helpers.InTransactionLoader.GetObjects(ObjectsQuery[] queries)
   at DevExpress.Xpo.Helpers.InTransactionLoader.GetObjects(Session session, ObjectsQuery[] queries)
   at DevExpress.Xpo.Session.<>c__DisplayClass16.<GetObjectsInTransaction>b__14()
   at DevExpress.Xpo.Logger.LogManager.Log[T](String category, LogHandler`1 handler, MessageHandler`1 createMessageHandler)
   at DevExpress.Xpo.Session.GetObjectsInTransaction(XPClassInfo classInfo, CriteriaOperator condition, SortingCollection sorting, Int32 skipSelectedRecords, Int32 topSelectedRecords, Boolean selectDeleted)
   at DevExpress.Xpo.XPQueryBase.SessionGetObjects(XPClassInfo classInfo, CriteriaOperator condition, SortingCollection sorting, Int32 skipSelectedRecords, Int32 topSelectedRecords, Boolean selectDeleted)
   at DevExpress.Xpo.XPQueryBase.GetObjects()
   at DevExpress.Xpo.XPQueryBase.Enumerate(Type type)
   at DevExpress.Xpo.XPQuery`1.GetEnumerator()
   at DevExpress.Xpo.XPQuery`1.System.Collections.Generic.IEnumerable<T>.GetEnumerator()
   at System.Collections.Generic.List`1..ctor(IEnumerable`1 collection)
   at System.Linq.Enumerable.ToList[TSource](IEnumerable`1 source)
   at Davanti.WMS.Services.Implementation.Outbound.OrderLineSpecificationStatusService.ChangeStatusToPickedToShipToStageOrStaged(Session session, IList`1 tasks, IList`1 olsWithoutTasks) in c:\Corax\DAV_WMS\DEV\SRC\APP\WMS\Davanti.WMS.Services.Implementation\Outbound\OrderLineSpecificationStatusService.cs:line 471

Update: After some struggles here is what I've done: - came with another approach. I don't know if you can get business logic of it but I've generated first a list with OLS then from it I've generated another list with pick specifications. Later I do a simple query on Tasks.

// compose list of olses for which status will be updated
    List<OrderLineSpecification> olSpecs = (from ols in new XPQuery<OrderLineSpecification>(session, true)
                                            where ols.Status != OrderLineSpecificationStatus.Cancelled 
                                                    //...
                                                    && ols.PickSpecification == task.PickSpecification
                                                    && (olsesUsedForTaskCompletion.Count == 0
                                                        || (olsesUsedForTaskCompletion.Contains(ols.Oid) && ols.QtyOrdered == ols.QtyPicked))
                                            select ols).ToList();

    var pickSpecificationKeys = (from ols in olSpecs select ols.PickSpecification.Oid).Distinct().ToList();

    var allRelatedTasks = (from t in new XPQuery<Core.Model.Task.Task>(session, true)
                            where pickSpecificationKeys.Contains(t.PickSpecification.Oid)
                                    && t.Status != TaskStatuses.Cancelled
                            select t).ToList();

I just hope that this will work, no matter client's database structure, duble references or so... :)

It is nice of you to show your complete call stack, but if you don't give the name of the procedure of your code snippet, it is not of much use. The exception is in processException, not in ProcessAnalyzeAndExecQuery. Are you sure your code snippet is from processException? — Harald Coppoolse, Jun 16 '17 at 06:31
the code from above is in ChangeStatusToPickedToShipToStageOrStaged() method. — Alexa Adrian, Jun 16 '17 at 06:49
Not sure if it may help, but could you try declaring `var first = new XPQuery(session, true)` and `var second = new XPQuery(session, true)`, then using those in your query, e.g. `(from t in first join ols in second on ...`. — Keyur PATEL, Jun 16 '17 at 07:00
As the exception is from procedure IntransactionLeader.ProcessException the error is not in ChangeStatusToPickedToShipToStageOrStaged(). I think that there is some exception that you catch and where you decide to call ProcessException (hence the name of the procedure). In ProcessException you do something that raises a new exception. Check the code in ProcessException and let Visual Studio debugger stop when it raises this exception, so you know the exact line in ProcessException that causes the problem — Harald Coppoolse, Jun 16 '17 at 08:05
For consistency, You may need to amend your function name to ; ChangeStatusToPickedToShipToStageOrStagedorMayThrowInvalidOperationException() — uk2k05, Jun 16 '17 at 10:29
:)))) You have humor! We just couldn't find a more suitable name :P — Alexa Adrian, Jun 16 '17 at 10:35
I don't see `Single` used in that query? Are you sure you have the correct exception location? — NetMage, Jun 16 '17 at 17:58

score 1 · Answer 1 · answered Jun 19 '17 at 15:31

The Select part in your query instantiates a persistent object, which implies that each element in the result sequence is unique (a persistent object cannot be loaded twice into the same Session). That is why the query is translated into a Single aggregate function. The cause of the error is that matching of the innerKey and outerKey in the Join expression may produce duplicated entries of items selected from the outer sequence.

If duplicates are not expected in your scenario, then you need to fix data in your database or rewrite the query to take this into account. For instance, you can use the "join into" operator to group duplicated records:

from t in new XPQuery<Core.Model.Task.Task>(session, true)
   join ols in new XPQuery<OrderLineSpecification>(session, true)
   on t.PickSpecification equals ols.PickSpecification
   into tg
   where tg.key == task.PickSpecification
      && tg.Any(gi.Status != TaskStatuses.Cancelled && olsesUsedForTaskCompletion.Contains(ols.Oid))
   select t

Replacing sequences in the query may also help to avoid the error in some scenarios, but you will have duplicated object references in the result sequence then:

from ols in new XPQuery<OrderLineSpecification>(session, true)
   join t in new XPQuery<Core.Model.Task.Task>(session, true)
   on ols.PickSpecification equals t.PickSpecification
   where ols.PickSpecification == task.PickSpecification
      && t.Status != TaskStatuses.Cancelled
      && olsesUsedForTaskCompletion.Contains(ols.Oid)
   select t

If you are fine with duplicated records in the result sequence, I suggest that you simply add a projection to the Select statement, so that the query does not need to instantiate persistent objects.

from t in new XPQuery<Core.Model.Task.Task>(session, true)
   join ols in new XPQuery<OrderLineSpecification>(session, true)
   on t.PickSpecification equals ols.PickSpecification
   where t.PickSpecification == task.PickSpecification
      && t.Status != TaskStatuses.Cancelled
      && olsesUsedForTaskCompletion.Contains(ols.Oid)
   select new { SomeProperty = t.SomeProperty, AnotherProperty = t.AnotherProperty }

PS: If none of above works in your particular case, contact DevExpress Support Service directly. Your question is too specific to XPO, so you will likely obtain the qualified answer there.

Well, I've tried all 3 suggests. They did not helped me that much but at least they directed me on another approach. I will update my question with current solution... BTW: At first suggestion tg.Key is unknown ( I haven't used yet 'into' keyword in LINQ) — Alexa Adrian, Jun 20 '17 at 12:37

How to fix "The collection to which the Single aggregate is applied must be empty or contain exactly one item" InvalidOperationException?

1 Answers1