KB Article A2404
Visible to All Users

How to use the ContainsOperator for objects in a many-to-many relationship

Description:
Suppose we have the following classes:

C#
public class User : XPObject { string name; public User() : base() {} public User(Session session) : base(session) {} public string Name { get { return name; }set { name = value; }} [Association("User-Role", typeof(Role))] public XPCollection Roles { get { return GetCollection("Roles"); }} } public class Role : XPObject { public Role() : base () {} public Role(Session session) : base(session) {} string roleName; public string RoleName { get { return roleName; } set { roleName = value; } } [Association("User-Role", typeof(User))] public XPCollection Users { get { return GetCollection("Users"); } } }

and there are two roles: "manager" and "programmer". How to obtain a list of users by their role?

Answer:
You can get all the users:

C#
XPCollection c = new XPCollection(typeof(User));

Or just the programmers:

C#
XPCollection c = new XPCollection(typeof(User), new ContainsOperator("Roles", new BinaryOperator("This", programmer)));

Or just the managers:

C#
XPCollection c = new XPCollection(typeof(User), new ContainsOperator("Roles", new BinaryOperator("This", manager)));

Or just programmers whose names contain "Smith":

C#
XPCollection c = new XPCollection(typeof(User), new GroupOperator(GroupOperatorType.And, new CriteriaOperator[] { new ContainsOperator("Roles", new BinaryOperator("This", programmer)), new BinaryOperator("Name", "%Smith%", BinaryOperatorType.Like)}) );

Note, XPO just translates the "BinaryOperatorType.Like" to the underlying database's "LIKE" operator and you should use the appropriate patterns ("%", "_", etc) to select the desired objects.
If you are using MS SQL Server you should be aware of the Case Sensitivity setting of your database server to make sure strings are compared in the right way (case sensitive or insensitive).
See Also:
How to filter a collection by an associated objects' properties
How to implement the "like" operator in XPO criteria
How to construct complex queries
How to create a criterion, which returns an intersection of two many-to-many sets

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.