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:
SQLselect * 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);
}
}
}
}
Hello Thomas.
The information you provided is insufficient to research the issue. We cannot build the code snippets you provided and cannot replicate the issue. Please provide us with a small sample project demonstrating the issue in action that we can build and debug locally. Please also describe the task you are trying to implement in detail. In fact, it looks like you are using undocumented parts of the XPO framework not intended to be used in this context.
I'm receiving the same error. It seems that it throws the exception in DevExpress.Xpo.Generators.DeleteQueryGenerator.GenerateDelete(classinfo, criteriaSet, batchWideData);
and it's when the criteria set contains criteria that involves a join.
I realize that DeleteQueryGenerator is intended for internal use, but it's almost irresistible because it's just the ticket for physically deleting large amounts of data without having to craft SQL statements. Anyway the code I used came from this post: https://www.devexpress.com/Support/Center/Question/Details/Q536728
I've attached a very small sample application that should replicate the issue.
Thanks,
John Allen
@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.
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.