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));

LINQ to XPO

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

why this link can be opened? 

http://expressapp.blogspot.com/2009/07/xpo-query-performance.html

It is exactly what the answer I want. 

Now I am facing the problem as below description:

I have several tables in my database as following:

(1) City:  City infomation, such as city no, city name
Table structure:
create table City
(
  City_id varchar(30),
  City_name varchar(50)
)

sample Records:
City_id          City_name
GZ                GuangZhou
SH                ShangHai
BJ                 BeiJing
WH               WuHan

(2) Employee table: employee information, including staff no, name and belong to which City etc.
Table structure:
create table Employee
(
  staff_no varchar(30),
  staff_name varchar(50),
  City_id varchar(30)
)
sample Records:
staff_no         staff_name   City_id
0001              John Tse       GZ   
0002             Simon Fung   GZ
0003             Mary yan       SH
0004             Smith Huang  BJ
0005             Tony Zeng    WH
0006             Jason Xu       GZ
0007             Sylvia Liu       BJ

(3) Leaveinfo table: when employees ask leave, they need fill out the leave information to HR department
Table structure:
create table Leaveinfo
(
  staff_no varchar(30),
  from_date datetime,
  end_date datetime
)
sample Records:
staff_no         from_date          end_date
0001       2010-01-03 08:00   2010-01-03 12:00   
0001       2010-01-04 08:00   2010-01-04 12:00  
0002       2010-01-03 08:00   2010-01-03 12:00   
0002       2010-01-04 13:00   2010-01-04 17:00  
0003       2010-01-05 08:00   2010-01-05 12:00   
0003       2010-01-06 13:00   2010-01-06 17:00 
0004       2010-01-03 08:00   2010-01-03 12:00   
0004       2010-01-07 08:00   2010-01-07 12:00  
0004       2010-01-09 08:00   2010-01-09 12:00   
0005       2010-01-04 13:00   2010-01-04 17:00  
0005       2010-01-05 08:00   2010-01-05 12:00   
0006       2010-01-08 13:00   2010-01-08 17:00  
0006       2010-01-09 08:00   2010-01-09 12:00 
0007       2010-01-10 08:00   2010-01-10 12:00

I'd like to get the records from leaveInfo table, and all Employees need belog to City_id='GZ'

Select * from LeaveInfo
where staff_no in (Select staff_no from Employee where City_id = 'GZ')

How to realize it in XPO?

 Tony, Please do not double-post, you have asked this already in the forum