I have an application that uses an object datalayer that I created myself, but I am interested in trying out XPO.  Before I get invest any time more time in it though, I need to know if it can handle the following scenario:

Consider the following Tables: Employee, Department, Permission, User

The Permission table specifies which Departments a User has access too, and all Employees belong to a Department.  How would the following query be represented in XPO:

SELECT * FROM EMPLOYEE

WHERE DepartmentId IN (SELECT DepartmentId FROM Permission WHERE Username = 'someuser')

Thanks in advance!

You may want to take a look at InOperator http://www.devexpress.com/Help/?document=XtraData/DevExpressDataFilteringInOperator_ctortopic.htm . I haven't used it myself but it looks like it should handle this.

Or you can use Linq

List<int> queryable = new XPQuery<Permission>(Session.DefaultSession).Where(permission => permission.UserName == "someuser").

                Select(permission => permission.DepartmentId).ToList();

            IQueryable<Employee> employees = new XPQuery<Employee>(Session.DefaultSession).Where(employee => queryable.Contains(employee.DepartmentId));

http://www.devexpress.com/Help/?document=XPO/CustomDocument4060.htm

Linq would work great, unfortunately though I would have deploy .NET 3.5 to around 300 users which is a task I'm not very interested in.

I'll look at the In Operator.

Thanks!

My client is a local government, with an inconsistent networking scheme... there are actually 4 different active directory domains, none of which are trusts.  So group policy is out.  Some of the users are on state owned computers, so that makes installing new software even harder.

The datalayer/entity framework i created uses a custom query object to get data, and I guess that is what I'm looking to see if XPO can do.  for Instance, for this scenario I would write this:

SelectQuery permissions = new SelectQuery();

permissions.Tables.Add(Permission.TableName);

permissions.Columns.Add(Permission.Columns.DepartmentId);

permissions.Where.AddCondition(Permission.Columns.Username, CriteriaOperator.Equals, "someuser");

SelectQuery employees = new SelectQuery();

employees.Tables.Add(Employee.TableName);

employees.Where.AddCondition(Employee.Columns.DepartmentId, CriteriaOperator.In, permissions);

return employees.GetList();

Now obviously that is alot of code to write, which is why i'm interested in XPO.

I'd like to second that. I also have lots of users who are happily using Windows 2000 and will not upgrade any time soon.

Ok I played around a bit with the InOperator and this is what I came up with:

XPCollection<Permission> permissions = new XPCollection<Permission>(CriteriaOperator.Parse("Username = 'someuser'"));
List<string> departments = new List<string>();
foreach (Permission item in permissions)
{
     departments.Add(item.DepartmentId.DepartmentId);
}
InOperator op = new InOperator("DepartmentId", departments);
XPCollection<Employee> list = new XPCollection<Employee>(op);

This works, but it seems a bit kludgy to me; does anyone have a more streamlined method? The primary problem i'm seeing with this method is the number of data retrievals required.  First it has to fetch the collection of permissions, then fetch Department object of each item in that collection to access the DepartmentId property.  And finally you fetch the list of employees. So it is a minimum of 3 database queries to get the list of employees.

Charles,

I've written a few thoughts on this here:-

http://expressapp.blogspot.com/2009/07/xpo-thinking-in-objects.html

-- Alex Hoffman
expressapp.blogspot.com

Thanks for the response Alex, I understand what you are saying and will give it a try.

That worked perfectly, thanks again Alex.

Next question is there a way to increase the performance of this operation?  I'm assuming that the CriteriaOperator.Parse() method uses reflection; is there a way to do it without using reflection?

Charles Graham:

... is there a way to increase the performance of this operation?  I'm assuming that the CriteriaOperator.Parse() method uses reflection; is there a way to do it without using reflection?

Charles,

I've written something on the question of query performance here.

-- Alex Hoffman
expressapp.blogspot.com