Hi,

I have a view which I want to sort by some related data. It's a view in a DispatchCollection

IEntityView view = dispatchCollection.DefaultView;

The relation is: Dispatch - Address - ZipCode - Country I want to sort the view by Country.Name, is that possible?

I think the following thread was discussing the same issue: http://www.llblgen.com/TinyForum/Messages.aspx?ThreadID=9288

I'll get back later, but I'm still a bit "lost"

The threads you link to is about sorting in a datagrid, but I can't find anything about sorting a view. My problem is that I get a DispatchCollection from DB. This collectin is split in 5 collections and one of these collections should be sorted difrently than the other 4 (they use the sortign from the GetMulti). Since I can't sort the DispatchCollection I have to sort the View of the DispatchCollection, but, as I wrote earlier, I want to sort using related fields. I have read the thread you linked to but I'm afraid I need more help.

This is the code that gets the DispatchCollection

IPredicateExpression filter = new PredicateExpression();
            ISortExpression sort = new SortExpression();
            IPrefetchPath prefetch = new PrefetchPath((int)EntityType.DispatchEntity);
            PrefetchPathElement prefetchDestinationAddressZipCode = (PrefetchPathElement)AddressEntity.PrefetchPathZipCode;
            PrefetchPathElement prefetchPickupAddressZipCode = (PrefetchPathElement)AddressEntity.PrefetchPathZipCode;

            filter.Add(DispatchFields.CompletedDate == DBNull.Value);
            if (dispatchPickupDateFrom.HasValue)
                filter.AddWithAnd(DispatchFields.PickupDate >= dispatchPickupDateFrom.Value);
            if (dispatchPickupDateTo.HasValue)
                filter.AddWithAnd(DispatchFields.PickupDate <= dispatchPickupDateTo.Value);

            prefetchDestinationAddressZipCode.SubPath.Add(ZipCodeEntity.PrefetchPathAreaDefinition ).SubPath.Add(AreaDefinitionEntity.PrefetchPathRegion);
            prefetchDestinationAddressZipCode.SubPath.Add(ZipCodeEntity.PrefetchPathCountry);
            prefetch.Add(DispatchEntity.PrefetchPathAddressDestination).SubPath.Add(prefetchDestinationAddressZipCode);
            prefetchPickupAddressZipCode.SubPath.Add(ZipCodeEntity.PrefetchPathAreaDefinition ).SubPath.Add(AreaDefinitionEntity.PrefetchPathRegion);
            prefetchPickupAddressZipCode.SubPath.Add(ZipCodeEntity.PrefetchPathCountry);
            prefetch.Add(DispatchEntity.PrefetchPathAddressPickup).SubPath.Add(prefetchPickupAddressZipCode);
            prefetch.Add(DispatchEntity.PrefetchPathCar).SubPath.Add(CarEntity.PrefetchPathCarModel ).SubPath.Add(CarModelEntity.PrefetchPathCarManufacturer);
            prefetch.Add(DispatchEntity.PrefetchPathDispatchList).SubPath.Add(DispatchListEntity.PrefetchPathDriver);

            sort.Add(DispatchFields.PickupDate | SortOperator.Ascending);

            DispatchCollection result = new DispatchCollection();
            result.GetMulti(filter, 0, sort, null, prefetch);
            return result;

This is where I sort the DispatchCollecting (after splitting the "original" collection into 5 collections)

public static DispatchCollection SortAbroadDispatchList(DispatchCollection dispatchCollection)
        {
            IEntityView view = dispatchCollection.DefaultView;

            ISortExpression sort = new SortExpression();
            sort.Add(new SortClause(ZipCodeFields.ZipCode, SortOperator.Ascending));
            view.Sorter = sort;

            DispatchCollection result = new DispatchCollection();

            foreach (DispatchEntity entity in view)
            {
                result.Add(entity);
            }
            return result;
        }

As I wrote in message #1 the relation is Dispatch - Address - ZipCode - Country. Each dispatch has 2 relations to Address (PickupAddress ad DestinationAddress)

The threads you link to is about sorting in a datagrid, but I can't find anything about sorting a view.

Both are the same since client-side sorting of a grid, is actually using the DefaultView sorting mechanism of the binded entityCollection.

The following is qouted from the thread:

That's client-side sorting and I don't think it's gonna work with fields defined in related entities. (That's where a TypedList/DynamicList would have been of much use)

In another words, the following is true for client-side sorting an entity collection without re-fetching and doing the sort on the database: 1) You can't sort on fields in a related entity 2) however, you can sort on fields mapped onto related fields("Fields on related Fields"). Use an EntityProperty() field for that. And these are just one level deep.

Ref: http://www.llblgen.com/TinyForum/Messages.aspx?ThreadID=6809

Ok, so I have to use EntityProperty(). Just for testing I tried to sort my view by Address1 (collumn in Address table)

sort.Add(new EntityProperty("Address1") | SortOperator.Ascending);

but I got a null ref. exception. Hov do I get "access" to the related fields?

Say you want to sort on Address.StreetName field. Then you should use the LLBLGen Pro Designer to map the Address.StreetName field into a new field on the Dispatch entity, using the "Fields On Related Fields" tab.

Then to populate this new field at runtime you should prefetchPath the Address entity when fetching the Dispatch entity.

And if I want to use Address.ZipCode.City?

What you can do here is manually create a custom property inside the entity in hand. This customProperty should read the value from the underlying "Address.ZipCode.City". Needless to say that this chain should be prefetchPathed.

How do I add the CustomProperties and set the value to the value of Address.ZipCode.City?

And my next Q is how to sort the view by the value of the custom property.

How do I add the CustomProperties and set the value to the value of Address.ZipCode.City?

Just open the entityClass in the Visual Studio and add a property with 'Get' only you won't need a 'Set', and return Address.ZipCode.City Just make sure you add it in a User code region, in order not to be overwritten when re-generating the code. ref: LLBLGen Pro manual "Using the generated code -> Adding your own code to the generated classes"

And my next Q is how to sort the view by the value of the custom property.

Like any property of the entityClass which is not an entityField:

sort.Add(new EntityProperty("Address1") | SortOperator.Ascending);

Ok, I thougt maby it was possible to add it in the designer

I now have the folowing code:

        public static DispatchCollection SortAbroadDispatchList(DispatchCollection dispatchCollection)
        {
            IEntityView view = dispatchCollection.DefaultView;

            ISortExpression sort = new SortExpression();
            sort.Add(new EntityProperty("ImportExport") | SortOperator.Ascending);
            sort.Add(new EntityProperty("PickupCountry") | SortOperator.Ascending);
            sort.Add(new EntityProperty("PickupZipCode") | SortOperator.Ascending);
            view.Sorter = sort;

            DispatchCollection result = new DispatchCollection();

            foreach (DispatchEntity entity in view)
            {
                result.Add(entity);
            }
            return result;
        }

The sorting on PickupZipCode doesn't look right if all 3 clauses is added. But if I uncomment one of the clauses (ie. PickupCountry) is looks as it should. Any idears why that is?

The sorting on PickupZipCode doesn't look right if all 3 clauses is added. But if I uncomment one of the clauses (ie. PickupCountry) is looks as it should.

How should the data look when sorting with three clauses?

Export Albania AlbaniaCountry1 AlbaniaCountry1_0001 ... Export Albania AlbaniaCountry1 AlbaniaCountry1_0002 ... Export Albania AlbaniaCountry1 AlbaniaCountry1_0003 ... Export Albania AlbaniaCountry2 AlbaniaCountry2_0010 ... Export Albania AlbaniaCountry2 AlbaniaCountry2_0020 ... Export Belgium BelgiumCountry1 BelgiumCountry1_0001 ... Import Albania AlbaniaCountry1 AlbaniaCountry1_0001 ... Import Albania AlbaniaCountry1 AlbaniaCountry1_0002 ... Import Albania AlbaniaCountry1 AlbaniaCountry1_0003 ... Import Albania AlbaniaCountry2 AlbaniaCountry2_0010 ... Import Albania AlbaniaCountry2 AlbaniaCountry2_0020 ... Import Belgium BelgiumCountry1 BelgiumCountry1_0001 ...

??

Could you provide the Generated SQL for this data retrieval. (LLBLGenPro Help - Using the generated code - Troubleshooting and debugging)

Yes the data should look like you "quoted". If all 3 sort clauses is used then zipcodes are not sorted.

Seems to be working now, don't now what did the trick but thanks for your help.