I'm trying to sort an entity on the fields of a related entity and can't seem to figure out how to make this work.

What I have is two tables, Product and ProductVersion which have a one to many relationship. I have been fetching Product entities and then prefetching the ProductVersion entities with a multi column sort of ProductVersionFields.Price and ProductVersionFields.MfrGrams and I want to change this. This query is slow because the join between the two tables returns more records then I want for the Product Entity.

I was hoping I could use the new EntityView2 class to sort on the ProductVersionFields.Price and ProductVersionFields.MfrGrams, but it's not working. As far as I can tell the code below is simply ignored. Is it possible to make this work?

EntityView2 view = new EntityView2(productCollection);
ISortExpression sorter = new SortExpression(ProductVersionFields.RPPrice | SortOperator.Ascending);
sorter.Add(ProductVersionFields.MfrGrams | SortOperator.Ascending);
sorter.Add(ProductFields.Material | SortOperator.Ascending);
view.Sorter = sorter;

?

EntityView2 view = new EntityView2(productCollection); ISortExpression sorter = new SortExpression(ProductVersionFields.RPPrice | SortOperator.Ascending); sorter.Add(ProductVersionFields.MfrGrams | SortOperator.Ascending); sorter.Add(ProductFields.Material | SortOperator.Ascending); view.Sorter = sorter;

I don't understand whether you are trying to sort the ProductVersion entities prefetched with each Product, or you want to sort the Product Entities based on fields on the related ProductVersion entities. (which might not make sense as each Product has a collection of ProductVersions which means multiple values of Price field for instance)

Walaa wrote:

?

EntityView2 view = new EntityView2(productCollection); ISortExpression sorter = new SortExpression(ProductVersionFields.RPPrice | SortOperator.Ascending); sorter.Add(ProductVersionFields.MfrGrams | SortOperator.Ascending); sorter.Add(ProductFields.Material | SortOperator.Ascending); view.Sorter = sorter;

I don't understand whether you are trying to sort the ProductVersion entities prefetched with each Product, or you want to sort the Product Entities based on fields on the related ProductVersion entities. (which might not make sense as each Product has a collection of ProductVersions which means multiple values of Price field for instance)

The second option is exactly what I'm trying to do. The reason that it does make sense is that I want to sort the Product on the smallest price from the ProductVersion entity. I need to do this because the product could have many prices and I need to display the product with it's lowest price.

Here is some of the code I'm using now.

RelationPredicateBucket filter = new RelationPredicateBucket();
filter.Relations.Add(ProductEntity.Relations.FrameEntityUsingFrameID);
filter.Relations.Add(ProductEntity.Relations.RidingStyleEntityUsingStyleCode);
filter.Relations.Add(ProductEntity.Relations.ManufacturerEntityUsingManufacturerID);
filter.Relations.Add(ManufacturerEntity.Relations.CompanyEntityUsingManufacturerID);
filter.Relations.Add(ProductEntity.Relations.ProductVersionEntityUsingProductID);

// Lots of filtering being done here ...

ISortExpression sortVersions = new SortExpression();
sortVersions.Add(ProductVersionFields.RPPrice | SortOperator.Ascending);
sortVersions.Add(ProductVersionFields.MfrGrams | SortOperator.Ascending);
IPrefetchPathElement2 productVersionNode = prefetchPath.Add(ProductEntity.PrefetchPathProductVersion, 1, filter.PredicateExpression, filter.Relations, sortVersions);

ISortExpression sort = new SortExpression();
sort.Add(ProductVersionFields.RPPrice | SortOperator.Descending);

adapter.FetchEntityCollection(collection, filter, 0, sort, prefetchPath);

If I pass the sort on ProductVersionFields.RPPrice into the FetchEntityCollection method then the query returns a row from the Product tabel for each row of the ProductVersion table. If I don't pass the sort then the query produced by LLBLGen uses DISTINCT and a much smaller number of rows are returned. This makes the query much faster.

I was hoping that I would be able to solve this problem using the EntityView2 class when v2 was released, but now that I have it I'm still not able to solve this problem because EntityView2 will only sort on the fields from the Fields collection. I can't use EntityView2 to sort the Product entityies on the ProductVersion entity fields. I also thought that I might be able to create a property on the Product entity which returned the lowest price from the ProductVersion entity and then sort on this property, but this won't work either.

Any suggestions would be appreciated.

1) you can't sort on fields in a related entity 2) however, you can sort on fields mapped onto related fields. Use an EntityProperty() field for that.

Otis,

Thank you again. This is great!

Here is the code from the entity.

// Custom Entity Property
public decimal Price
{
    get
    {
        if ( this.ProductVersion.Count > 0 )
            return ((ProductVersionEntity)this.ProductVersion[0]).Price;
        return 0;
    }
}

// Custom Entity Property
public decimal Grams
{
    get
    {
        if ( this.ProductVersion.Count > 0 )
            return ((ProductVersionEntity)this.ProductVersion[0]).Price;
        return 0;
    }
}

ASP.NET codebehind file.

EntityView2 view = new EntityView2(entityCollection);
ISortExpression sorter = new SortExpression(new EntityProperty("Price") | SortOperator.Descending);
sorter.Add(new EntityProperty("Grams") | SortOperator.Descending);
view.Sorter = sorter;

.Net Version: 1.1 ORMSupportClasses.NET11: 2.0.0.0 Template: Adapter C#

I just started having problems with this code again. I just wanted to add in another sort field and it threw the whole sort off. This sort works prefectly until I add in the sort for ModelYear. This ModelYear field is a char (4) data type that contains values like '2004', '2005'. etc.

EntityView2 view = new EntityView2(frames);
ISortExpression sorter = new SortExpression(new EntityProperty("CompanyName") | SortOperator.Ascending);
sorter.Add(ProductFields.ModelYear | SortOperator.Descending);
sorter.Add(new EntityProperty("RPPrice") | SortOperator.Descending);
sorter.Add(new EntityProperty("MfrGrams") | SortOperator.Descending);
view.Sorter = sorter;

this.framesList.DataSource = view;
this.framesList.DataBind();

The sorting of CompanyName in this works, but the ModelYear, RPPrice and MfrGrams are not in order. The odd part of this problem is that this sorting does work in one place where I fetch product data, but it doesn't work in another place where I fetch product data using a different predicate expression. So this leads me to the qustion, does the sorting of the data from the database affect how the in-memory sort will work?

The CompanyName, RPPrice, and MfrGrams fields are all fields from a related entity. The ModelYear field is on the entity that I'm sorting. Does anyone have any idea why the ModelYear field would throw the sort off? Does it have anything to do with the fact that it is a char column?

Thanks.

Can you post some examples of the queries that are being generated when you don't have the model year included and then an example when you do include the model year?

Unless I'm not understanding your request. There aren't any queries generated as this is an in-memory sort where the sort works perfectly without the ModelYear then doesn't work once the ModelYear is added into the sort.

To add a little more information to this. The CompanyName field is a property of a related entity and the RPPrice and MfrGrams are both custom properties. The ModelYear is the only property here that is native to the entity.

tprohas wrote:

Unless I'm not understanding your request. There aren't any queries generated as this is an in-memory sort where the sort works perfectly without the ModelYear then doesn't work once the ModelYear is added into the sort.

To add a little more information to this. The CompanyName field is a property of a related entity and the RPPrice and MfrGrams are both custom properties. The ModelYear is the only property here that is native to the entity.

It shouldn't make a difference, so it's odd that it doesn't work. Well, 'doesn't work' is perhaps a bit of a vague statement, so I'd like you to provide a small snippet of the data you have, what you expected and what you got.

Sorting is straight forward, till there are multiple instances of the same value to sort. What to do? The sorter inside the code sorts sets of equal values with the next sort clause and so on. So only equal companynames in the set will get sorted on ModelYear. I'm uncertain if this is indeed what you're seeing or not.

All sorting is done by reading values using the field or entityproperty provided. These objects all implement IEntityFieldCoreInterpret, which provides values for in-memory interpretations of them. It performs a case insensitve sort unless you've specified CaseSensitiveCollation on the sortclause object. It then performs an ArrayList.Sort() operation, which is a quicksort call.