Background:

I have a system that tracks people and families. I have a Person table and a Household table. The Person table holds HouseholdID and the Household table holds information about the Household (address, etc.). There is a many-to-one relationship between Person and Household.

So the generated code (Adapter, VS 2005) (which I love, BTW) allows me to use:

Person.Household.Person to get an EntityCollection of people in the household.

The Problem:

I'm trying to get the list of people in the household sorted by last name, then first name. The code is:

householdMembersSorter = new SortExpression();
householdMembersSorter.Add(SortClauseFactory.Create(PersonFieldIndex.LastName, SortOperator.Ascending));
householdMembersSorter.Add(SortClauseFactory.Create(PersonFieldIndex.FirstName, SortOperator.Ascending));

prefetchPath.Add(PersonEntity.PrefetchPathHousehold).SubPath.Add(HouseholdEntity.PrefetchPathPerson, 0, null, null, householdMembersSorter);

The resulting list is not sorted as expected. Instead, the test case I tried gives back: ** Smith, John Smith, Bill Smith, Bob Smith, Elizabeth ** Any ideas what I may be doing wrong?

Thanks,

Jay

Can you turn on tracing and post what query this is generating? I don't initially see a problem with you setup.

The query seems to be correct:

exec sp_executesql N'

SELECT
   [CMS].[dbo].[Person].[PersonID] AS [PersonId],
   [CMS].[dbo].[Person].[FirstName],
   [CMS].[dbo].[Person].[LastName],

...

FROM [CMS].[dbo].[Person]
WHERE ( ( [CMS].[dbo].[Person].[HouseholdID] = @HouseholdId1))
ORDER BY
   [CMS].[dbo].[Person].[LastName] ASC,
   [CMS].[dbo].[Person].[FirstName] ASC

',N'@HouseholdId1 int',@HouseholdId1=1

What I've noticed now is that the order of the household members that I get back seems to depends on the ** "root" person** I select. So, if I

select Elizabeth, I get:

** Smith, Elizabeth Smith, Bill Smith, Bob Smith, John **

select Bill, I get:

** Smith, Bill Smith, Bob Smith, Elizabeth Smith, John **

elect Bob, I get:

** Smith, Bob Smith, Bill Smith, Elizabeth Smith, John **

select John, I get:

** Smith, John Smith, Bill Smith, Bob Smith, Elizabeth **

The "root" person is listed first and then everyone else alphabetically.

In Profiler, the query looks the same as above, but resulting list is different???

Shouldn't Person.Household.Person produce the same result regardless of the "root" person selected???

Thanks, Jay

Very strange indeed.

I am assuming that when you run the generated SQL against the DB, the results are as you expect. If this is the case then how is the result being viewed - datagrid etc...?

Yes the generated SQL produces the desired result.

I'm looking at the Entity Collection with VS 2005 in Debug mode and also by binding it to an ASP.NET Repeater.

Jay

What I've noticed now is that the order of the household members that I get back seems to depends on the "root" person I select

What do you mean by th eprevious line?

Do you fetch root entities one at a time, and they are all supposed to have the same household, and the persons collection of that house hold differs in order?

Or do you fetch them in an entity collection and when you select anyone of them at runtime and inspect the inner persons collection, you find it different in order?

Please post a complete code snippet as well as the RuntimeLibrary version you are using.

I have a page called People.aspx which displays a list of people in alphabetical order (like a phone directory). Clicking on a person in that list requests Person.aspx?id=<the person id> which displays the details for that person (name, address, work contact info, email addresses, etc.).

One of the "details" that I'm trying to display for a person is their household information (address, phone number, etc.) and a list of the members in that household (parents, children, whatever). I'm trying to get that list of household members via Person.Household.Person which returns an EntityCollection of the type PersonEntity.

The problem is that this collection is not being ordered by LastName ASC, FirstName ASC as expected (even though a trace shows that the correct query is being sent to the DB).

What I'm calling the "root" person is the person that is selected on People.aspx.

The list of household members should include the root person along with the other members in alphabetical order but what I'm seeing is the root person listed first followed by everyone else listed correctly. My previous post shows the ordering of the lists that I get back depending on the household member selected on People.aspx.

Here's the code:

public PersonEntity GetPerson(int PersonID)
{
   PersonEntity person;
   IPrefetchPath2 prefetchPath = null;
   SortExpression householdMembersSorter;

   person = new PersonEntity(PersonID);

   householdMembersSorter = new SortExpression();
   householdMembersSorter.Add(SortClauseFactory.Create(PersonFieldIndex.LastName, SortOperator.Ascending));
   householdMembersSorter.Add(SortClauseFactory.Create(PersonFieldIndex.FirstName, SortOperator.Ascending));

   prefetchPath.Add(PersonEntity.PrefetchPathHousehold).SubPath.Add(HouseholdEntity.PrefetchPathPerson, 0, null, null, householdMembersSorter);

   DataAccessAdapter adapter = new DataAccessAdapter();

   try
   {
      adapter.FetchEntity(person, prefetchPath);
   }

   catch (Exception e)
   {
      throw e;
   }

   finally
   {
      adapter.Dispose();
   }

   return person;
}

When I bind the EntityCollection returned from Person.Household.Person to a Repeater, I see the lists displayed in my earlier post.

Hope that helps. Thanks for looking at this.

Jay

Oops, forgot the version info:

I was using 1.0.2005.1 with the original October .NET 2.0 runtime libraries. I just downloaded the 4/26/2006 libraries and rebuilt... same problem.

Jay

That seems to be odd behavior. It may be a bug, in the meantime as a workaround you may want to use client side sorting of the collection instead.

person.Household.Person.Sort((int)PersonFieldIndex.FirstName, ListSortDirection.Ascending);

I've already looked at client-side sorting.

It would work fine for households in which everyone has the same last name. However, there are many households in which the members have different last names and, unfortunately, client-side sorting can only be done on one field at a time.

Would you please set a breakpoint before the binding and inspect the fetched collection to see if it's correctly sorted or not?

A side note: while this might be a bug, I think it might be in your favour, as you may not want to display the main person in his household persons. So you may blindly remove the first entity in the collection before binding it, rather than finding it first then removing it.

I believe I'm running into a similar problem: using prefetch path sorting the generated SQL looks good but the final collection is not sorted correctly. I can't work around my problem either with client-side sorting as the sort field is in a related entity, not the entity collection being sorted.

However, I am not using databinding.

Jeff...

<EDIT> Hmmm, I think this might be my fault...ignore for now...

I've examined the collection in the debugger and it is not sorted correctly.

Should this be moved over to the Bugs and Issues forum to get more visibility?

It's on my list for further investigation. I hope to have more results later today.

Great! Thanks.

It indeed looks strange. The subpath node should have sorted results, which are then merged in THAT order with the root. I'll setup a testcase and look into it. This will likely be tomorrow morning (wednesday CET time).

Waiting patiently...

With northwind, when I fetch order - customer - orders, with order 10254, I indeed can reproduce this behavior. Everything seems to be sorted OK, though the first row is indeed the one of the root collection.

This is caused by the following: - you fetch person P. - you fetch P's household H - you fetch that H's persons.

When H is fetched, and assigned to P.HouseHold, P is added to H, as: person.HouseHold = myHouseHold; also does: myHouseHold.Persons.Add(person);

and if you do instead: myHouseHold.Persons.Add(person); this is also done: person.HouseHold = myHouseHold;

So, after H is fetched, P is already in H.Persons. Then H's persons are fetched and merged with H.Persons. The person objects in that fetch again contain P. When P is to be merged with H.Persons, it's already there. This means that the addition is a no-op and P stays at the top of the list.

I cant fix it in v1. In v2 it's different, as some architecture has changed so this was possible to fix. My test fails there too so it still contains a bug in v2 (I can get the proper list if I add everything to a context, which doesn't fix it in v1 though).

To work around this use the following: you've to fetch the list separately. THis is ok, it's for databinding in a read-only fashion anyway.

EntityCollection persons = new EntityCollection(new PersonEntityFactory());
RelationPredicateBucket filter = new RelationPredicateBucket();
filter.PredicateExpression.Add(new FieldCompareSetPredicate(
        PersonFields.HouseHoldId, null,
        PersonFields.HouseHoldId, null,
        SetOperator.Equal, (PersonFields.PersonId ==PersonID), null, string.Empty,1, null));

using(DataAccessAdapter adapter = new DataAccessAdapter())
{
    adapter.FetchEntityCollection(persons, filter, 0, sorter);
}

It produces the query:

SELECT  Person.A, Person.B.... 
FROM    Person
WHERE   HouseHoldID =
(
    SELECT  TOP 1 HouseHoldID
    FROM    Person
    WHERE   PersonID = @personID
)
ORDER BY ... 

OK, the work around is working...

Thanks for taking the time to look at this.

Jay