Forums

Can XPO handle the following complex query?

Last post 4/4/2010 8:05 PM by Trevor Lane. 14 replies.
Sort Posts: Previous Next
  • Charles Graham

    Can XPO handle the following complex query?

    7/8/2009 10:13 AM
    • Not Ranked
    • Joined on 10/12/2007
    • Posts 6

    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!

  • Hans Merkl

    Re: Can XPO handle the following complex query?

    7/8/2009 10:46 AM
    • Top 150 Contributor
    • Joined on 5/4/2007
    • Posts 141

    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.

  • Tolis Bekiaris [DX-Squad]

    Re: Can XPO handle the following complex query?

    7/8/2009 10:48 AM
    • Top 50 Contributor
    • Joined on 6/21/2007
    • Posts 592

    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

  • Charles Graham

    Re: Can XPO handle the following complex query?

    7/8/2009 11:11 AM
    • Not Ranked
    • Joined on 10/12/2007
    • Posts 6

    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!

  • Robert Fuchs

    Re: Can XPO handle the following complex query?

    7/8/2009 11:42 AM
    • Top 25 Contributor
    • Joined on 5/4/2007
    • Tirol, Austria
    • Posts 2,453
    I have a similar problem. More than 50% of my user base are still on W2K.
     
    These are all corporate users, and it is difficult to convince corporations...
    Also, I don't want to recommend XP any more, because XP itself is about to be discontinued.
    Vista is out of question, they'd kill me ;-)
    And Windows 7 is still almost 4 months away from general availability.
     
    So what?
     
    regards, Robert
     
  • Charles Graham

    Re: Can XPO handle the following complex query?

    7/8/2009 12:03 PM
    • Not Ranked
    • Joined on 10/12/2007
    • Posts 6

    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.

  • Hans Merkl

    Re: Can XPO handle the following complex query?

    7/8/2009 12:11 PM
    • Top 150 Contributor
    • Joined on 5/4/2007
    • Posts 141

    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.

  • Charles Graham

    Re: Can XPO handle the following complex query?

    7/8/2009 3:07 PM
    • Not Ranked
    • Joined on 10/12/2007
    • Posts 6

    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.

  • Alex Hoffman

    Re: Can XPO handle the following complex query?

    7/9/2009 1:27 AM
    • Top 100 Contributor
    • Joined on 5/4/2007
    • Posts 244

    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

  • Charles Graham

    Re: Can XPO handle the following complex query?

    7/9/2009 12:35 PM
    • Not Ranked
    • Joined on 10/12/2007
    • Posts 6

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

  • Charles Graham

    Re: Can XPO handle the following complex query?

    7/9/2009 2:28 PM
    • Not Ranked
    • Joined on 10/12/2007
    • Posts 6

    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?

  • Alex Hoffman

    Re: Can XPO handle the following complex query?

    7/9/2009 10:18 PM
    • Top 100 Contributor
    • Joined on 5/4/2007
    • Posts 244

    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

     

  • tony zeng

    Re: Can XPO handle the following complex query?

    4/4/2010 6:50 AM
    • Not Ranked
    • Joined on 2/26/2009
    • Posts 6

    why this link can be opened? 

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

    It is exactly what the answer I want. 

  • tony zeng

    Re: Can XPO handle the following complex query?

    4/4/2010 6:53 AM
    • Not Ranked
    • Joined on 2/26/2009
    • Posts 6

    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?

  • Trevor Lane

    Re: Can XPO handle the following complex query?

    4/4/2010 8:05 PM
    • Top 75 Contributor
    • Joined on 5/12/2007
    • Posts 474

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

More from DevExpress
Live Chat
Have a pre-sales question?
Need assistance with your evaluation?
We are here to help.
Chat is one of the many ways you can contact members of the DevExpress Team. We are available Monday-Friday between 8:30am and 5:00pm Pacific Time.
If you need additional product information, require pre-sales assistance, or want help with your order, write to us at info@devexpress.com or call us at
+1 (818) 844-3383.