Forum:  LLBLGen Pro Runtime Framework

Thread:  Sorting DefaultView


tprohas (User)   Posted on: 21-Aug-2006 21:32:15.
.Net 1.1
LLBLGen 2

Can anyone tell me why this code I have for sorting the EntityCollection.DefaultView doesn't work. I have tried writing this code a number of different ways now and can't make it work.

Code:

            public EntityCollection GetCollection()
            {
                ... Omitted code
                adapter.FetchEntityCollection(products, filter, prefetchPath);
                
                SortExpression sorter = new SortExpression();
                sorter.Add(new EntityProperty("Manufacturer") | SortOperator.Descending);
                sorter.Add(new EntityProperty("Model") | SortOperator.Descending);
                sorter.Add(new EntityProperty("RPPrice") | SortOperator.Descending);
                sorter.Add(new EntityProperty("MfrGrams") | SortOperator.Descending);
                products.DefaultView.Sorter = sorter;

                return products;
            }
JimHugh (User)   Posted on: 21-Aug-2006 21:52:33.
The "sorter" should go in the FetchEntityCollection call and the field names to be sorted on should come out of the HelperClasses.

You are not sorting the default view, you are sending a sort clause to the database server.

Code:

            public EntityCollection GetCollection()
            {
                SortExpression sorter = new SortExpression();
                sorter.Add(ProductsFields.Manufacturer) | SortOperator.Descending);
                sorter.Add(ProductsFields.Model) | SortOperator.Descending);
                sorter.Add(ProductsFields.RPPrice) | SortOperator.Descending);
                sorter.Add(ProductsFields.MfrGrams) | SortOperator.Descending);

                adapter.FetchEntityCollection(products, filter, 0, sorter, prefetchPath);
                return products;
            }



tprohas (User)   Posted on: 21-Aug-2006 21:59:24.
JimHugh wrote:
The "sorter" should go in the FetchEntityCollection call and the field names to be sorted on should come out of the HelperClasses.

You are not sorting the default view, you are sending a sort clause to the database server.

Code:

            public EntityCollection GetCollection()
            {
                SortExpression sorter = new SortExpression();
                sorter.Add(ProductsFields.Manufacturer) | SortOperator.Descending);
                sorter.Add(ProductsFields.Model) | SortOperator.Descending);
                sorter.Add(ProductsFields.RPPrice) | SortOperator.Descending);
                sorter.Add(ProductsFields.MfrGrams) | SortOperator.Descending);

                adapter.FetchEntityCollection(products, filter, 0, sorter, prefetchPath);
                return products;
            }



I'm actually trying to sort on the EntityCollection.DefaultView so I don't have to sort at the database level. I have done sorting in a similer way except in the other case I'm returning the DataView object to apply to the data source. In this case I want to return the sorted EntityCollection and not the DataView.
JimHugh (User)   Posted on: 21-Aug-2006 23:40:20.
Assuming you are using V2005 and LLBLGen 2.0, I think it would be something like this:

Code:

EntityCollection<ProductEntity> products = new EntityCollection<ProductEntity>(new ProductEntityFactory());
EntityView2<ProductEntity> productsView = new EntityView2<ProductEntity>(products);

SortExpression sorter = new SortExpression();
sorter.Add(ProductsFields.Manufacturer) | SortOperator.Descending);
sorter.Add(ProductsFields.Model) | SortOperator.Descending);
sorter.Add(ProductsFields.RPPrice) | SortOperator.Descending);
sorter.Add(ProductsFields.MfrGrams) | SortOperator.Descending);

productsView.Sorter = sorter;

EntityCollection<ProductsEntity> products2 = (EntityCollection<ProductsEntity>) productsView.ToEntityCollection();

return products2;


Note the "Best Practices" from the docs:
Quote:

Choose database filters over in-memory filters. If you have the choice between in-memory filters using EntityView(2) objects, or filters on the same data in the database, it's best to opt for the latter, as that means less data is pulled into memory and less processing has to be done by your application. It might be of course your application can spare the processor time and your database system can't, so at least perform research which option is the most appropriate in a given situation.


tprohas (User)   Posted on: 22-Aug-2006 00:00:42.
I don't think that the best practice advice for filtering in memory applies to sorting in memory. I not doing any in memory filtering.
JimHugh (User)   Posted on: 22-Aug-2006 00:14:04.
In my opinion, "Filtering" and sorting are both activities best suited to a good database server.

In memory sorting will not be able to take advantage of any indexes.

I feel that the "performing research" advice was an appropriate disclaimer since I'm an end user just like you are. Regular Smiley


tprohas (User)   Posted on: 22-Aug-2006 00:43:42.
Thanks for the help. The code snippet you gave me worked. The reason that I use the in-memory sorting over database sorting is that there are a few cases where I would need an extra join to do the database sort and then the extra join also causes a lot of extra rows to come back in the query.
JimHugh (User)   Posted on: 22-Aug-2006 01:09:06.
My pleasure.

Thanks for the explanation.


Otis (LLBLGen Pro Team)   Posted on: 22-Aug-2006 11:12:07.
The original question still stands: in-memory sorting should work. I answer it as I think it needs clarification and to avoid Aaron and others fall into the same trap tomorrow Wink

tprohas wrote:
.Net 1.1
LLBLGen 2

Can anyone tell me why this code I have for sorting the EntityCollection.DefaultView doesn't work. I have tried writing this code a number of different ways now and can't make it work.

Code:

            public EntityCollection GetCollection()
            {
                ... Omitted code
                adapter.FetchEntityCollection(products, filter, prefetchPath);
                
                SortExpression sorter = new SortExpression();
                sorter.Add(new EntityProperty("Manufacturer") | SortOperator.Descending);
                sorter.Add(new EntityProperty("Model") | SortOperator.Descending);
                sorter.Add(new EntityProperty("RPPrice") | SortOperator.Descending);
                sorter.Add(new EntityProperty("MfrGrams") | SortOperator.Descending);
                products.DefaultView.Sorter = sorter;

                return products;
            }

Sorting a view, sorts the view, not the entitycollection. So if you have a view, filter it, sort it etc., it affects the view, not the entity collection. So the code above does sort the defaultview, but you see the data only in a sorted way if you use the defaultview to look at the data, the entitycollection 'products' is unaffected.

(thanks Jim for the help Regular Smiley)
enerata (User)   Posted on: 09-Sep-2007 14:46:47.
Hello team Regular Smiley

in my case i need to do some sorting on aliased fields but i find it not to work. We are using
LLBLGen Pro v2.

Sample code:
Code:

HouseCollection col = new HouseCollection();

relations.Add(HouseEntity.Relations.AccountEntityUsingBuyerAccountID, "BuyerAccount", JoinHint.Left);
relations.Add(AccountEntity.Relations.DetailEntityUsingDetailID, "BuyerAccount", "BuyerDetail", JoinHint.Left);
relations.Add(HouseEntity.Relations.AccountEntityUsingSellerAccountID, "SellerAccount", JoinHint.Left);
relations.Add(AccountEntity.Relations.DetailEntityUsingDetailID, "SellerAccount", "SellerDetail", JoinHint.Left);

col.GetMulti(relations);

sorter.Add(new SortClause(DetailFields.LastName, sortOp, "BuyerDetail"));
EntityView<HouseEntity> view = col.DefaultView;
view.Sorter = sorter;
return view.ToEntityCollection();



If I sort the collection directly and not the default view it works, but in our case we need to sort the default view because we do caching.

Thank you.

Otis wrote:
The original question still stands: in-memory sorting should work. I answer it as I think it needs clarification and to avoid Aaron and others fall into the same trap tomorrow Wink

...

Sorting a view, sorts the view, not the entitycollection. So if you have a view, filter it, sort it etc., it affects the view, not the entity collection. So the code above does sort the defaultview, but you see the data only in a sorted way if you use the defaultview to look at the data, the entitycollection 'products' is unaffected.

(thanks Jim for the help Regular Smiley)



daelmo (Support Team)   Posted on: 10-Sep-2007 00:30:15.
Copied from this thread: http://www.llblgen.com/TinyForum/Messages.aspx?ThreadID=6809&StartAtMessage=0&#37502
Otis wrote:

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.


enerata (User)   Posted on: 11-Sep-2007 23:02:37.
I managed to use the EntityProperty fields but some times there were StackOverflowExceptions thrown from the getters of the extra fields that returned the fields from the related entities.

For some reason, while trying to fetch a related entity to get some field of it, it would call my custom property getter again and again until the stack was exhausted.


Walaa (Support Team)   Posted on: 12-Sep-2007 11:33:33.
Quote:
HouseCollection col = new HouseCollection();

relations.Add(HouseEntity.Relations.AccountEntityUsingBuyerAccountID, "BuyerAccount", JoinHint.Left);
relations.Add(AccountEntity.Relations.DetailEntityUsingDetailID, "BuyerAccount", "BuyerDetail", JoinHint.Left);
relations.Add(HouseEntity.Relations.AccountEntityUsingSellerAccountID, "SellerAccount", JoinHint.Left);
relations.Add(AccountEntity.Relations.DetailEntityUsingDetailID, "SellerAccount", "SellerDetail", JoinHint.Left);

col.GetMulti(relations);

sorter.Add(new SortClause(DetailFields.LastName, sortOp, "BuyerDetail"));
EntityView<HouseEntity> view = col.DefaultView;
view.Sorter = sorter;
return view.ToEntityCollection();


First of all, I think there is no overload of the GetMulti() that accepts a RelationCollection as the first and only parameter.

Second, in the above code you are specifying aliases to be used in the JOINs of the above query.
This alias is not available to be used at client side afterwards, it was only used in the generated SQL Query to perform a JOIN that may limit the rows fetched from the main entity.

So if you want to sort the returned rows/entities based on the LastName field of the BuyerDetail, then you should pass a SortExpression to the GetMulti() method.

Quote:
I managed to use the EntityProperty fields but some times there were StackOverflowExceptions thrown from the getters of the extra fields that returned the fields from the related entities.

For some reason, while trying to fetch a related entity to get some field of it, it would call my custom property getter again and again until the stack was exhausted.

Would you please post the corresponding code snippet?