Division

  -> Department

         -> Employee

 

All three types of records use a RecordStatus field to determine whether they are still Active or Inactive. I created a RdbQuery in the Division to return only the Active Divisions. However, when I check the Department these objects still contain Inactive records, and the Departments have Inactive Employees. Here is my code:

 

 

I don't understand what you want.  It looks like you want more than active divisions, because you dont like the fact that some active divisions have inactive departments and that some active departments have inactive employees.

 

Do you want:

 

(1)  Active divisions that have only active departments with active employees?

(2)  An EntityList of a mix of divisions, departments, and employees that are all fully active?

(3)  Something else?

 

 

Darren

Darren,

 

You have the right idea.  You do need a query with two subqueries.  I think the problem is with your AddClauses.  You need something like:

 

 aSubSubQuery.AddClause(Employee.StatusEntityColumn, EntityQueryOp.EQ, status.ACTIVE);

 

If you want another example of using subqueries, you can look at my example at:

 

http://www.ideablade.com/forum/forum_posts.asp?TID=818&KW=addclause - http://www.ideablade.com/forum/forum_posts.asp?TID=818&KW=addclause

 

One note on my example.  I didn't need to specify a query direction, but the example still works.

 

    public static EntityQuery GetDivisionAndChildrenByStatus(string status) {

      object mystatus = (object) status;

      EntityQuery query = new EntityQuery (typeof(Division));

      query.AddClause(Division.RecordStatusEntityColumn, EntityQueryOp.EQ, mystatus);
      query.AddOrderBy(Division.OrderEntityColumn, System.ComponentModel.ListSortDirection.Ascending);

      EntitySubquery aSubQuery = query.AddSubquery(EntityRelations.Division_Department);
      aSubQuery.AddClause(Department.RecordStatusEntityColumn, EntityQueryOp.EQ, mystatus);
     return query;
    }

 

I'm using the DevExpress grid with two views. It shows the proper records and relationships so that leads me to believe that I have the objects setup correctly, but I still get Inactive Departments with my Active Divisions. I don't understand how the code above can accurately return only the Active Divisions but not the correct Departments. I'm really under the gun here to get this completed but I have more similar things to do like this and it's not working. So, please help?

 

Darren

 

I am suspicious of the fact that you are passing in a string, converting it to an object, then comparing it to a status entity column.  I doubt that a status entity column in the database has a Datatype  of Object.

 

David

 

AddClause(EntityColumn pEntityColumn, EntityQueryOp pOperator, object pValue)

 

I've tested it both ways, using the parameter as string and an object, and either way still get the same results. I don't see anything wrong with the query compared to all the examples I've seen. It just doesn't work. What could cause this? Are there any alternatives?

 

I tried using a Predicate but I don't know how to use a Predicate on a child related object coming from the parent, when using the EntityList.ListManager. It would also be helpful to know how to take the records from the EntityList and work with the records as their original objects. Otherwise, how do you convert the EntityList of Divisions to an actual Division object? These things are probably simple to do but without any example code it isn't obvious to me how to do this. I have to get this thing working by tommorrow. Other than properly filtered lists I've got most of it working. All the queries I've done that work on the Parent level seem to work fine, so does sorting, and pass-thru queries. I'm missing something but I can't see what it is.

 

Darren

 

Division
  -> Department
         -> Employee

I'm sort of surprised that your query at the top level has to do anything more than exclude Inactive Divisions; but since you've said you need

 

     1)  Active divisions that have only active departments with active employees

 

I'm going to take your word for it for the moment.  I'm just mentioning it up front in case you may be overcomplicating your problem because of not knowing how to get the filtered lists of children.

 

In fact, let's deal with the simpler case first, because I think the answer to that is really what you need, even if the queries at each level have to be more complicated.  Suppose your problem is this:

 

1. You want to see only active Divisions;
2. For an active Division, you want to see only the active Departments (if there are any); and
3. For an active Department, you want to see only the active Employees (if there are any).

 

For the first level, your retrieval operation is something like this:

 

   RdbQuery anRbdQuery = new RdbQuery(typeof(Division), Division.RecordStatusEntityColumn,RdbQueryOp.EQ, "Active");
   EntityList<Division> activeDivisions = PM.GetEntities<Division>(anRdbQuery);

 

For the second level:  the Departments property for a given Division is simply not going to return a filtered list unless you override its definition in your Division developer class. Assuming you don't want to do that, you can get what you need as follows:

 

Create a separate EntityList, departmentsForCurrentDivision or whatever, for the filtered list, and simply use an appropriate query to retrieve the needed children into it:

 

      Division currentDivision = (Division)mDivisionsBS.Current;
      RdbQuery departmentRdbQuery = new RdbQuery(typeof(Department));
      departmentRdbQuery.AddClause(Department.DivisionIdEntityColumn, RdbQueryOp.EQ, currentDivision.Id);
      departmentRdbQuery.AddClause(Department.RecordStatusEntityColumn, RdbQueryOp.EQ, "Active");
      EntityList<Department> departmentsForCurrentDivision = PM.GetEntities<Department>(departmentRdbQuery);

 

Bind your controls to the Departments in departmentsForCurrentDivision rather than to those in currentDivision.Departments.

 

Note that you will need to run the above query whenever you move to a different Division, to obtain the list of its active Departments.  I typically do that sort of thing in a handler for the CurrentChanged event of the parent entity's BindingSource.

 

Even easier (if it's an option that's open to you), you can also immortalize the above in a custom property that you add to your Division class: call it ActiveDepartments or some such. Then you can bind to currentDivision.ActiveDepartments.

For level three and on down a chain of relations of any length: Just do more of the same, filtering the list of each entity's children by excluding the Inactive ones.

If you really do want

 

     Active divisions that have active departments with active employees

 

as distinguished from

 

     Active divisions that may or may not have active departments that may or may not have active employees

 

(which is what I just defined the logic for) then your queries at each level will be a little more involved, requiring subqueries. But you seem already to know how to write those. Just understand that a query with subqueries still only filters the target entity: it doesn't do anything to that entity's children or other descendants, other than look at them to decide how to filter the parent list.

 

Best regards,

 

Greg Dunn

IdeaBlade

 

Thanks for you reply. I think I have a better understanding of how it works now.

 

In Response to

 

"I'm sort of surprised that your query at the top level has to do anything more than exclude Inactive Divisions"

 

This all came about because I'm using the DevExpress Grid. If you're famaliar with this grid it is pretty powerful and flexible when working with related data. So, when I get an Active list of Divisions the grid recognizes that there are child relationships with the Division->Departments->Employees and will automatically create views for these records unless the "ShowDetails" properties are turned off. In my case I was using the Grid to create a "Tree" like effect that would show the Divisions down through Employees. Because the Grid is capable of displaying these records and relationships like this I wanted to do only one query against the objects and have only one bindable datasource. Of course I was trying to filter all the records to only active from the top down based on how I thought the quering worked. I could have queried each level seperately but the DevExpress grid can only bind to one source. You can have multiple members, that each have a different view, but the views can not have multiple binding sources (as far as I know they can't). So is it possible to do three EntityLists and join them into a single BindingSource? I don't know.

 

Based on you last statement

 

"Just understand that a query with subqueries still only filters the target entity: it doesn't do anything to that entity's children or other descendants, other than look at them to decide how to filter the parent list."

 

Ahh. Lights flickering. This explains the results I was getting. These subquery filters could determine what parent records I get back but CAN NOT filter the child records in any way. Glad to know how this really works. I think the "immortalize" strategy you talked about is what I will implement in the final run as this is a common view of the data that we see in several of our applications.

 

Last thing. I still had a couple of questions that maybe you can help me with. It would also be helpful to know how to take the records from the EntityList and work with the records as their original objects.

 

Otherwise, how do you convert the EntityList of Divisions to an actual Division object in code? As an example, how could I loop through the EntityList, casting the objects to the type they are, and then get to their related objects? If what I'm proposing is not recommended do you have an alternative? I can use the BindingSource to get the Current record and cast it to my object but I can't do the same with an EntityList. I figure there's got to be a way to get there but I haven't found it.

 

Thanks again for your help.

 

Darren

 

 

An EntityList<T> is a list of pointers (addresses) to entities sitting in the (PersistenceManager's) local cache. From a practical viewpoint, though, you can use the objects in the list as if they were the entities themselves. Ergo, if you have

 

     EntityList<Division> divisions = PM.GetEntities<Division>()

 

then divisions[0], divisions[1], and so forth, all Division objects. Thus you can say

 

     divisions[0].ActiveDepartments

 

to get the active Departments associated with the first Division entity in the divisions EntityList.

 

You can iterate through an EntityList<Division> as follows:

 

Incidentally, if you do add a custom ActiveDepartments property to the Division class, it should return an EntityList<Department>.  You should then be able to use the DevExpress grid's ShowDetails facility.  (I'm not familiar with it, but I assume you can say which of the "details" properties you want to display and which you don't, so that you can display ActiveDepartments and not display Departments.)

 

 

 

Excellent! Thank you. This is just what I've been looking for. Your posts have been very helpful.

 

Darren