Hello,
At 10 days before delivering the project to our client we are encountering a RPC parameters limit related issue when testing with production data (large data).
That's why this issue is urgent.
The problem occurs with the InOperator when the collection exceds 2100 items. The issue is very critical as we already delayed the shipping due to transactions management related problems…
Is there another way of passing this InOperator like criteria or mixing with sql ?
Please help!
Sincerely,
Catalin Manoliu
Technical Project Leader
Audilog Groupe Ericsson for Bouygues Telecom
+33675718622
cmanoliu
@
bouyguestelecom.fr
We have closed this ticket because another page addresses its subject:
How to avoid the "Too many parameters..." error when querying large amounts of persistent objects by their keys
Hello Catalin,
Thank you for your report. Unfortunately, we cannot fix this problem in our code. The exception is thrown in the SqlClient classes. Please inform Microsoft about this problem.
The immediate solution to this issue is discussed in the following Knowledge Base article:
How to obtain a collection of persistent objects by a set of their IDs
Thank you,
Nick
Thank you Nick for your reply.
I tried to use the alternate solution you've indicated but unfortunately with no success for my case.
With all the respect I think this issue is a bug or at least a hidden limitation with the InOperator even if it is due to a SQLClient limitation.
I think you should consider fixing or at least publishing this limitation as it also cames from your underlying implementation that uses SQL parameters for the IN clause (the number of SQL parameters is limited to 2100).
In our network configuration management project we've made our persistent classes aware of an "NetworkContext" that gives the visibility of an object at some moment in time.
In order to implement the awarness in our classes I've overriden the XPBaseObject's "CreateCollection<T>" adding an additional criteria (the InOperator) to the contained collections :
…
protected override XPCollection<T> CreateCollection<T>(XPMemberInfo property)
{
XPCollection<T> result = base.CreateCollection<T>(property);
if (result.GetObjectClassInfo().ClassType.GetInterface(typeof(ISerializableWithFDNKeyRefs).Name) != null)
{
CriteriaOperator skimMergingCriteria;
if (property.IsManyToMany)
skimMergingCriteria = getSkimMergingCriteria(property.Name);
else
skimMergingCriteria = getSkimMergingCriteria("OID");
if (!ReferenceEquals(skimMergingCriteria, null))
skimHelperCriteria(result, skimMergingCriteria);
}
return result;
}
private static void skimHelperCriteria(XPBaseCollection collection, CriteriaOperator mergingCriteria)
{
XPCollectionHelper helper = getXPCollectionHelper(collection);
Type helperType = helper.GetType();
FieldInfo fi_fCriteria = helperType.GetField("fCriteria", BindingFlags.NonPublic | BindingFlags.Instance);
CriteriaOperator fCriteria = (CriteriaOperator)fi_fCriteria.GetValue(helper);
CriteriaOperator newHelperCriteria = CriteriaOperator.And(fCriteria, mergingCriteria);
fi_fCriteria.SetValue(helper, newHelperCriteria);
}
private static XPCollectionHelper getXPCollectionHelper(XPBaseCollection collection)
{
Type collectionType = collection.GetType();
FieldInfo fi_Helper = collectionType.GetField("Helper", BindingFlags.NonPublic | BindingFlags.Instance);
if (fi_Helper == null)
throw new Exception("helper not found. eXpressPersistentObject's version changed ? " + Environment.NewLine +
"please contact the <skimHelperCriteria> author.");
XPCollectionHelper helper = (XPCollectionHelper)fi_Helper.GetValue(collection);
return helper;
}
…
Briefly, I need a way to return an xpcollection OWNED by a persistent object applying also this criteria on the network context.
As I said, I tried to use the solution based on XPCursor but I found no way of specifying a criteria for the OWNER join.
Inspired by the XPO sources I've tried to use :
…
ownerJoinCriteria = new BinaryOperator(new OperandProperty(property.GetAssociatedMember().Name),
new OperandValue(this),
BinaryOperatorType.Equal);
…
but it won't work with ManyToMany associations (returns runtime error with the criteria and the property path)…
Please, can you help me with the OWNER criteria ?
Thank you.
Best regards,
Catalin Manoliu
Technical Project Leader
Audilog Groupe Ericsson for Bouygues Telecom
+33675718622
cmanoliu
@
bouyguestelecom.fr
Hello Catalin,
If I correctly understand your task, you can use this code to load a filtered collection of the Network objects owned by a given Owner (the "o" variable):
using DevExpress.Xpo; using DevExpress.Data.Filtering; Owner o = XpoDefault.Session.FindObject<Owner>(null); CriteriaOperator filter = CriteriaOperator.Parse("OwnerLinks[This = ?] AND Domain = ?", o, "MyFirm"); // the same filter in Simplified Criteria Syntax: filter = new OperandProperty("OwnerLinks")[new OperandProperty("This") == new OperandValue(o)] & new OperandProperty("Domain") == "MyFirm"; XPCollection<Network> filteredNets = new XPCollection<Network>(filter); public class Owner : XPObject { [Association("Owners-Networks")] public XPCollection<Network> NetLinks { get { return GetCollection<Network>("NetLinks"); } } } public class Network : XPObject { public string Domain; [Association("Owners-Networks")] public XPCollection<Owner> OwnerLinks { get { return GetCollection<Owner>("OwnerLinks"); } } }
Thank you,
Nick
With this hint I think I shall be able to workaround the problem.
Thank you!
Best regards,
Catalin
With this hint I think I shall be able to workaround the problem.
Thank you!
Best regards,
Catalin
Hello again,
I have tried the ownership criteria and it works but unfortunately I am still unable to workaround the InOperator criteria problem…
I have overriden CreateCollection in my base persistent class in order to apply a secondary criteria (InOperator with sometimes more than 2100 items) in supplement to the "ownership" criteria.
With your link and sample for the ownership criteria, i had arrived at something like this:
(see attachement)
The reasons that doesn't working are:
- "Nested objects loading" exception when adding an new object to an existing collection;
- the automatically append to the related collection when "is many to many" does not longer exists (as implemented by the default version of CreateCollection that uses a XpRefCollectionHelperManyToMany…)
Please help me guys as I'm staring to HARDLY regret adopting XPO in our project… (we delayed the have release date for 2 months, mainly of this xpo related issues and others I haven't found time to reproduce with a simple project…)
Can't you change the way that XPO translates the InOperator to SQL without using "parameters" ?
This issue is very urgent.
Thanks for reply,
Regards,
Catalin Manoliu
Technical Project Leader
Audilog Groupe Ericsson for Bouygues Telecom
+33675718622
cmanoliu
@
bouyguestelecom.fr
Hello Catalin,
The easiest solution for you to work around this limitation is to create several criteria when number of parameters exceeds 2100: check the argument length, and split it onto several parts (less then 2000 items in each collection part). Then load collections in a loop and combine them into a single collection. Please send your code for the CreateCollection method and will help you redesign it.
Thank you,
Nick