Criteria on XPCollection doesn't work when using notEqual operator on references

Renaud Bompuis

created 18 years ago

This seems to be a bug introduced in 7.1.1. As far as I know, it was worign properly in 6.3.
If I have a persistent object with a property that refers to another object of the same type (like a Staff persistent class where a Manager property would be of type Staff as well), I cannot build an XPCollection with a Criteria that excludes Staff based on who's the Manager.
The attached project example should be self-explanatory, but here are a few details in pseudo-code:
class Staff : XPObject {
string Name;
Staff Manager;
}
XPCollection<Staff> team = new XPCollection<Staff>(session);
Staff joe = session.FindObject("joe");
team.Criteria = new BinaryOperator("Manager", joe, BinaryOperatorType.NotEqual);
team.Load(); // Will produce an empty list.

XPOBug1.zip

Comments (3)

DevExpress Support Team 18 years ago

Sorry for the inconvenience, but the problem is caused by null values handling specifics in SQL.
Please try to execute this query in your database:

SQL
SELECT *  
FROM Staff  
WHERE Manager <> 2

It does not return anything.
If you execute this query:

SQL
SELECT *  
FROM Staff  
WHERE Manager <> 1

The results contain two rows where Manager=2 and don't include rows where Manager=null.
To fix the problem, please modify your code as shown below:

C#
// OLD CODE  
team.Criteria = CriteriaOperator.Parse("Oid <> ? And Manager.Oid <> ?", excludedStaff.Oid, excludedStaff.Oid);  

// NEW CODE  
team.Criteria = CriteriaOperator.Parse("Oid <> ? AND (Manager.Oid <> ? OR Manager IS NULL)", excludedStaff.Oid, excludedStaff.Oid);

Thank you,
Nick

Renaud Bompuis 18 years ago

Hi Nick,
thanks for the explanation.
Strange thing though is that I was pretty sure it worked fine in XPO6.3, but I'm probably mistaken.
XPO's abstraction sometimes detracts you too much from the behaviour of the database.
I think this kind of issue should be clearly mentionned in the documentation for Criteria as potential pitfalls when you think of your Criteria as operating on objects when they are instead SQL statements meant to querry the database directly.
Either that or Developer Express should implement those quirks to ensure that Criteria's behaviour is consistent with operations on Objects and not SQL.
Thank you,
Renaud

DevExpress Support Team 18 years ago

Hello Renaud,
You are right, version 6.3 handled these criteria in a different way. Unfortunately, the behavior of version 6.3 was not correct from the database programming point of view, and we had to fix it since we received complaints from our customers. The version 7.1 behavior is correct. The corresponding note will be added to the documentation. This task has been added to our To-Do list:
Describe the specifics of NotEqual filters when they are applied to object references
Thank you,
Nick

Criteria on XPCollection doesn't work when using notEqual operator on references

Recently viewed tickets