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