Ticket T131050
Visible to All Users

DeleteQueryGenerator.GenerateDelete throws an exception when a complex criteria is used

created 11 years ago (modified 9 years ago)

Hi I want to delete some objects from my database. The class (DeriviedClass) is derived from another class. So in the database there are three tables:

BaseClass - Only has a id an data (No information about the owner!)
DerivedClass- Some more information and Id of its owner

to get data in sql in can use this query:

SQL
select * from PartnerAccountAmount paa join BaseAmount ba on paa.Id = ba.Id

The owner on the other hand has another owner. This would result to an delete-query like this:

C#
internal void Remove(IEnumerable<SegmentStatement> statements) { var a = statements.Select(s => string.Format("Owner.Owner.Id = '{0}'", s.Id)); var b = string.Join(" OR ", a); Session.Remove<TInterface>(b); //Session.Remove<TInterface>(Session.FindObjects<TInterface>(criteria, parameters)); }

The method commented out works fine.
But when executed, this results to an exception on this operation
'DeleteQueryGenerator.GenerateDelete(classInfo, generatorCriteriaSet, batchWideData);'. Please see below.

Can we make this operation work? I like to change the way of deleting, because

C#
Session.Remove<TInterface>(Session.FindObjects<TInterface>(criteria, parameters));

is to slow for big databases.
Thank you very much.

C#
Test method CP.ConsTest.BusinessLogic.Helper.StatementRemoverStrategyTest.TestRemoveBookingConsolidatedInFuture threw exception: System.ArgumentNullException: Value cannot be null. Parameter name: key at System.Collections.Generic.Dictionary`2.FindEntry(TKey key) at System.Collections.Generic.Dictionary`2.TryGetValue(TKey key, ref TValue value) at DevExpress.Xpo.Generators.BaseQueryGenerator.AppendJoinNode(XPMemberInfo property, JoinNode prevnode, JoinType type) at DevExpress.Xpo.Generators.BaseQueryGenerator.GetPropertyNode(MemberInfoCollection propertyPath, JoinType type) at DevExpress.Xpo.Generators.BaseQueryGenerator.<>c__DisplayClass1.b__0(String pn) at DevExpress.Xpo.Generators.BaseQueryGenerator.ExecuteWithPropertyNameDiving(String propertyName, ExecuteWithPropertyNameDivingHandler worker) at DevExpress.Xpo.Generators.BaseQueryGenerator.GetPropertyNode(OperandProperty property, JoinType type) at DevExpress.Xpo.Generators.BaseQueryGenerator.DevExpress.Data.Filtering.IClientCriteriaVisitor.Visit(OperandProperty theOperand) at DevExpress.Data.Filtering.OperandProperty.Accept(ICriteriaVisitor visitor) at DevExpress.Xpo.Generators.BaseQueryGenerator.DevExpress.Data.Filtering.ICriteriaVisitor.Visit(BinaryOperator theOperator) at DevExpress.Data.Filtering.BinaryOperator.Accept(ICriteriaVisitor visitor) at DevExpress.Xpo.Generators.BaseQueryGenerator.ProcessLogical(CriteriaOperator operand) at DevExpress.Xpo.Generators.BaseQueryGenerator.DevExpress.Data.Filtering.ICriteriaVisitor.Visit(GroupOperator theOperator) at DevExpress.Data.Filtering.GroupOperator.Accept(ICriteriaVisitor visitor) at DevExpress.Xpo.Generators.BaseQueryGenerator.ProcessLogical(CriteriaOperator operand) at DevExpress.Xpo.Generators.DeleteQueryGenerator.InternalGenerateSql(CriteriaOperator criteria) at DevExpress.Xpo.Generators.BaseQueryGenerator.GenerateSql(CriteriaOperator criteria) at DevExpress.Xpo.Generators.BaseObjectQueryGenerator.GenerateSql(ObjectGeneratorCriteriaSet criteriaSet, MemberInfoCollection properties, Boolean reverse) at DevExpress.Xpo.Generators.DeleteQueryGenerator.GenerateDelete(XPClassInfo classInfo, ObjectGeneratorCriteriaSet criteriaSet, BatchWideDataHolder4Modification batchWideData) at CP.Cons.Persistence.SessionXPO.Remove(String criteria, Object[] parameters) in SessionXPO.cs: line 329 at CP.Cons.BusinessLogic.PersistenceManager`4.Remove(String criteria, Object[] parameters) in PersistenceManager.cs: line 293 at CP.Cons.BusinessLogic.SegmentModule.PartnerSegmentAmountManager.Remove(IEnumerable`1 statements) in PartnerSegmentAmountManager.cs: line 34

Here is the implementation of Remove and FindObjects:

C#
/// <summary> /// Deletes the specified session. /// </summary> /// <typeparam name="TInterface">The type of the interface.</typeparam> /// <param name="criteria">The criteria.</param> /// <param name="parameters">The parameters.</param> public void Remove<TInterface>(string criteria, params object[] parameters) { CriteriaOperator criteriaOperator = !string.IsNullOrEmpty(criteria) ? CriteriaOperator.Parse(criteria, parameters) : CriteriaOperator.Parse("True"); Type interfaceType = typeof(TInterface); Type xpoType = InterfaceToXpoMap.Instance[interfaceType]; XPClassInfo classInfo = unitOfWork.GetClassInfo(xpoType); var batchWideData = new BatchWideDataHolder4Modification(unitOfWork); int recordsAffected = (int)unitOfWork.Evaluate(classInfo, CriteriaOperator.Parse("Count()"), criteriaOperator); ObjectGeneratorCriteriaSet generatorCriteriaSet = ObjectGeneratorCriteriaSet.GetCommonCriteriaSet(criteriaOperator); List<ModificationStatement> collection = DeleteQueryGenerator.GenerateDelete(classInfo, generatorCriteriaSet, batchWideData); foreach (ModificationStatement item in collection) item.RecordsAffected = recordsAffected; ModificationStatement[] collectionToArray = collection.ToArray<ModificationStatement>(); unitOfWork.DataLayer.ModifyData(collectionToArray); } /// <summary> /// Finds the objects. /// </summary> /// <param name="criteria">The criteria.</param> /// <param name="parameters">The parameters.</param> /// <returns></returns> public IEnumerable<TInterface> FindObjects<TInterface>(string criteria, params object[] parameters) { Type interfaceType = typeof(TInterface); Tuple<Type, string, object[], bool> cacheKey = null; if (IsReadOnly) { cacheKey = Tuple.Create(interfaceType, criteria, parameters, true); object cacheValue; if (complexKeyCache.TryGetValue(cacheKey, out cacheValue)) return (IEnumerable<TInterface>)cacheValue; } Type xpoType = InterfaceToXpoMap.Instance[interfaceType]; CriteriaOperator criteriaOperator = null; if (!string.IsNullOrEmpty(criteria)) criteriaOperator = CriteriaOperator.Parse(criteria, parameters); var collection = new XPCollection(EvaluationBehavior, unitOfWork, unitOfWork.GetClassInfo(xpoType), criteriaOperator, false); if (interfaceType == typeof(IStatementItem)) collection.PreFetch("Children", "AssignedAccounts", "CrossReferences"); if (interfaceType == typeof(IReportingPeriod)) collection.PreFetch("TimeSteps"); IEnumerable<TInterface> result = collection.OfType<TInterface>(); if (cacheKey != null) complexKeyCache.Add(cacheKey, result); return result; }
C#
/// /// Deletes a collection of items. /// /// The list. public void Remove(IEnumerable list) { RemoveCore(list, false, true); } private void RemoveCore(IEnumerable list, bool removeReferencingObjects, bool removeSelf) { List objectsToRemove = list.ToList(); if (objectsToRemove.Count > 0) { //Benutzer benachrichtigen das auch Abhängigkeiten gelöscht werden bool continueDeletion = true; if (Session.ClientAccessor.HasValue) { var eventArgs = new RemovingObjectsEventArgs(Session.ClientId, objectsToRemove.ToDictionary(dalObject => GetId(dalObject.PersistenceObject), dalObject => dalObject.GetType())); var userSessionLogic = ServiceLocatorFactory.Default.GetInstance(); Masterdata client; if (userSessionLogic.TryFind(Session.ClientAccessor.Value, out client)) client.EventLogic.GetEvent().Publish(eventArgs); continueDeletion = !eventArgs.Cancel; } if (continueDeletion) { foreach (TObject item in objectsToRemove) OnRemove(item); //und dann die eigentliche Objekte. logic.Remove(Session, objectsToRemove.Select(o => o.PersistenceObject).ToArray(), removeReferencingObjects, removeSelf); foreach (TObject item in objectsToRemove) { OnRemoved(item); ClearFields(item); } } } }
Show previous comments (2)
DevExpress Support Team 9 years ago

    @John: Thanks for the project. I have replicated the exception. We need additional time to research it. I will get back once we have any result.

    DevExpress Support Team 9 years ago

      Hello Thomas.

      I regret to inform you that DeleteQueryGenerator doesn't support complex queries that require joins with other tables. It would require a lot of code to extend DeleteQueryGenerator to support this scenario. I'm afraid we don't have immediate plans for such an improvement since it fits internal XPO requirements perfectly.

        I understand.  Thanks for looking into it.

        Disclaimer: The information provided on DevExpress.com and affiliated web properties (including the DevExpress Support Center) is provided "as is" without warranty of any kind. Developer Express Inc disclaims all warranties, either express or implied, including the warranties of merchantability and fitness for a particular purpose. Please refer to the DevExpress.com Website Terms of Use for more information in this regard.

        Confidential Information: Developer Express Inc does not wish to receive, will not act to procure, nor will it solicit, confidential or proprietary materials and information from you through the DevExpress Support Center or its web properties. Any and all materials or information divulged during chats, email communications, online discussions, Support Center tickets, or made available to Developer Express Inc in any manner will be deemed NOT to be confidential by Developer Express Inc. Please refer to the DevExpress.com Website Terms of Use for more information in this regard.