Using: Adapter template. LLBLGen Pro Version 2.6 Final (October 9th, 2009) .Net 3.5

Here are my tables: Parcel PropertyDescription Tract Plat BlockLot

Here are the relationships between those tables:

Parcel has many PropertyDescriptions. PropertyDescription.ParcelId is foreign key to Parcel.ParcelId

PropertyDescription has one Tract PropertyDescription.TractId is foreign key to Tract.TractId

PropertyDescription has one Plat PropertyDescription.PlatId is foreign key to Plat.PlatId

Tract has many BlockLots BlockLot.TractID is foreign key to Tract.TractID

Objective

I'd like to get all the PropertyDescriptions for a Parcel. I'd like to prefetch Tract, Plat, and BlockLot data. But I'd like to be able to sort the resulting collection using fields from those prefetched tables.

For example, I might want to sort the PropertyDescription collection by: Plat.Code, BlockLot.Block, Tract.Section, Tract.Township, Tract.Range

In the past, I'd do this by creating my own typed list in code that had the joins and the sorting. But I'm wondering if LLBLGen has a simpler way.

Hi,

Have you tried the following documentation code - which includes Prefetch with sorting:

http://www.llblgen.com/documentation/3.5/LLBLGen%20Pro%20RTF/Using%20the%20generated%20code/Adapter/gencode_prefetchpaths_adapter.htm#advancedusage

// C#
EntityCollection<EmployeeEntity> employees = new EntityCollection<EmployeeEntity>();
PrefetchPath2 prefetchPath = new PrefetchPath2(EntityType.EmployeeEntity);
SortExpression sorter = new SortExpression(OrderFields.OrderDate | SortOperator.Descending);
prefetchPath.Add(EmployeeEntity.PrefetchPathOrders, 1, null, null, sorter);
using(DataAccessAdapter adapter = new DataAccessAdapter())
{
    adapter.FetchEntityCollection(employees, null, prefetchPath);
}

// QuerySpec alternative
var employees= new EntityCollection<EmployeeEntity>();
using(DataAccessAdapter adapter = new DataAccessAdapter())
{
    var qf = new QueryFactory();
    var q = qf.Employee
        .WithPath(EmployeeEntity.PrefetchPathOrders
                .WithOrdering(OrderFields.OrderDate.Descending())
                .WithLimit(1));
    adapter.FetchQuery(q, employees);
}

I hope it helps you.

Cheers.

Yes, that is useful. Although I think you don't want to sort the related entities but the main collection sorted by related entities. This would be an approximate code:

// build the path
var path = new PrefetchPath2((int) EntityType.PropertyDescription>);
path.Add(PropertyDescriptionEntity.PrefetchPathParcel);
path.Add(PropertyDescriptionEntity.PrefetchPathTract)
    .SubPath.Add(TractEntity.PrefetchPathBlockLots);
path.Add(PropertyDescriptionEntity.PrefetchPathPlat);

// build the filter. Add the appropiate relations so you can sort on related entities
var filter = new RelationPredicateBucket();
filter.Relations.Add(PropertyEntity.Relations.ParcelEntityUsingParcelId);
filter.Relations.Add(PropertyEntity.Relations.TractEntityUsingTractId);
filter.Relations.Add(TractEntity.Relations.BlockLotEntityUsingTractId);
filter.Relations.Add(PropertyEntity.Relations.PlatEntityUsingPlatId);
// add additional predicates....
// filter.PredicateExpression.Add(...);

// build the sorter
var sorter = new SortExpression();
sorter.Add(PropertyDescriptionFields.SomeField | SortOperator.Ascending);
sorter.Add(ParcelFields.SomeField | SortOperator.Descending);
sorter.Add(BlockLotFields.SomeOtherField | SortOperator.Ascending);
// so on...

// fetch the collection
var properties = new EntityCollection<PropertyDescriptionEntity>();
using (var adapter = new DataAccessAdapter())
{
     adapter.FetchEntityCollection(properties, filter, 0, sorter, path);
}

You also could write it in LINQ2LLBL or QuerySpec, but the concept is the same. So, basically to filter or sort on related fields you should add the appropriate relations, regardless of whether or not you are prefetching some of them. So, prefetch is another thing. I talked about that in this blog post: http://www.llblgening.com/archive/2009/10/prefetchpaths-in-depth/

Yes, daelmo is correct. I want to sort my main collection by the related entities.

I'll try out your suggested solution. But I won't get a chance until next week sometime. So I'll let you know at that time how it worked out for me.

Thanks.

daelmo,

Your suggestion worked for me. Thanks! Also thanks for the link to your blog post.

clint wrote:

daelmo,

Your suggestion worked for me. Thanks! Also thanks for the link to your blog post.

Good! Thanks for the feedback