Hi

In my web applications I ofter find i do 2 types of sorting/filtering:

1. I get all of the records in a particular table and then filter/sort these records as I display them

2. I sort/filter the records on retrieval so that I only retrieve a subset of the data.

I have a vague idea how I coudl do no (2) with llblgen. I could use the getMulti method on an entity collection and apply a filter and sort.

How do you do no (1). I know how i could get all of the records in a table by creating an entity collection and calling getMulti(false). Once I have this collection, how would i access a subset of records in the collection and then do something like bind them to a datagrid.

Could you give me a code snippet that would illustrate how I would do this. If you imagine that I have an entity collection called orders that has all the orders in my database, how would i retrieve those orders belonging to a particular customer from that same collection i already have in memory ad bind them to a datagrid.

Thanks a lot andrea

Once I have this collection, how would i access a subset of records in the collection and then do something like bind them to a datagrid.

Using V.1 you would have to manually traverse the collection and filter the needed entities into another entity collection.

Using V.2 you should be using EntityViews (Please refer to LLBLGen Pro V.2 manual documentation "V2.0 Beta docs - > EntityView and EntityView2 classes")

Hi

Thanks but I'm still none the wiser. Please could you give me an actual example of how i would traverse the original collection and make a new collection

Andrea

andieje wrote:

Hi

Thanks but I'm still none the wiser. Please could you give me an actual example of how i would traverse the original collection and make a new collection

Andrea

CustomerCollection filteredCustomers = new CustomerCollection();
foreach(CustomerEntity customer in toFilter)
{
    if(customer.Country == "USA")
    {
        filteredCustomers.Add(customer);
    }
}

where toFilter is your original collection.

Please, language like 'none wiser' etc. isn't that nice. We all try to help people and if something isn't what you expected it to be, please post what you've tried yourself so we can perhaps point you to a fault in your own code. The routine above is pretty simple, I'm sure you could come up with such a routine yourself

In v2, now in beta, you can filter in-memory using entityviews, which allow you to filter an entity collection using a normal predicate expression, and for example you can also sort in-memory using a normal sortexpression.

Sorting an entitycollection in-memory is done through the Sort() method of the entity collection classes. See the reference manual for details on these.

Hi

I'm sorry about my choice of language. I did not mean to offend. I am grateful for your help.

I am evaluating llblgen pro with a view to buying it and I am frustrated by the lack of a sample asp.net application containing examples of common functions such as sorting/paging/editing. I appreciate that there is 300 page user manual but I don't really have time to read through all of that to decide whether I want to buy something. My frustration seems to be seeping into my posts which is very rude of me and I apologise. It is not your fault and you shouldnt bite the hand that feeds you either!

Back to your reply.

You are correct that I could come up with a routine myself. However, what I would rather see is a tried and tested way of doing something that has been devised by an expert in the area. My way may well be inefficient or convoluted. It normally is. In fact the way I was thinking of doing it isnot the way you demonstrated.

This is how i would do it:

Dim orders as OrdersCollection orders.getMulti(nothing) 'this has all orders

'To get the orders for a specific customer dim customerOrders as OrdersCollection customerOrders = orders 'copy all orders over customerOrders.getMulti(add some filter here that filters by customer)

Id your way quicker/better? I presume getMulti is just doing what you do behind the scenes anyway

Thanks a lot andrea

Hi

I'm confusing myself...

If i right, the method is suggested in my post is not very good because getMulti will actually go the database and retrieve a new set of records rather than filtering the ones that are there.

I don't know if this is defeating the object of llblgen, but if you had a collection of entities in memory and you wanted to get subsets of this collection, could you make a dataview based on the entity collection and then set the rowFilter on the dataview. I am not sure if this is possible as i dont know if an entity collection is based on a dataset.

Thanks alot andrea

andieje wrote:

Hi

I'm sorry about my choice of language. I did not mean to offend. I am grateful for your help.

I am evaluating llblgen pro with a view to buying it and I am frustrated by the lack of a sample asp.net application containing examples of common functions such as sorting/paging/editing.

The framework has a tremendous amount of functionality. Thus this leads to a thick manual, that's unavoidable. The manual is setup in such a way that with many items discussed a code snippet illustrates whats been explained. For example sorting.

We have an ASP.NET example application, called petshop, which illustrates paging for example. Sorting is explained in the manual and also in other sample applications: an entity collection isn't different in an ASP.NET application as it is in a windows forms application.

Back to your reply.

You are correct that I could come up with a routine myself. However, what I would rather see is a tried and tested way of doing something that has been devised by an expert in the area. My way may well be inefficient or convoluted. It normally is. In fact the way I was thinking of doing it isnot the way you demonstrated.

This is how i would do it:

Dim orders as OrdersCollection orders.getMulti(nothing) 'this has all orders

'To get the orders for a specific customer dim customerOrders as OrdersCollection customerOrders = orders 'copy all orders over customerOrders.getMulti(add some filter here that filters by customer)

Id your way quicker/better? I presume getMulti is just doing what you do behind the scenes anyway

The hard part of working with databases and code which targets databases is that there's no clear way to do something in all situations so it always works or always is efficient.

There are two things: working in-memory with data and working with the data in the database. Working with data in-memory is always slower per operation than working with data in the database. However reading data from the database, reading it into objects etc. also takes time. So it can be that if you have 10 orders in a collection, and you want to filter them on a customerid, it could be it's more efficient to simply walk the collection in an O(n) operation than go back to the database, fetch the entities requested, load these into new entity objects etc. That could be slower, if the table contains a lot of data.

though in general, filtering in memory is an O(n) operation, which means all objects have to be examined. So if you first fetch ALL data, then traverse ALL that data in-memory, it will be slow. If you filter on the database, fetch only the data you need, it's much faster, in most cases, but as I said: it can be it's slower if the n is small and the table is big (or for example you have to fetch the data from a webservice etc. )

Rule of thumb: always fetch AT MOST the data you need at that given moment. So don't fetch a large set of data and then traverse it to filter out what you want to use.

Your example is also inefficient because you fetch all orders which aren't used. GetMulti() works on the database: it produces a query on the db, not an in-memory filter.

Hi

Thanks for your reply.

It may make more sense if i tell you the example i am working on.

I want to display information about customers and their orders. I have a datagrid of cusotmers and then nested inside that is a datagrid of orders showing you the orders for that customer.

IN ASP.NET books, you generally implement this mater/detail relationship by getting 2 datatables: one containing all customers and one containing all orders. YOu bind the customers table to the outer datagrid. You then bind the nested datagrid by getting a dataview of the orders datatable that contains all of the orders for that customer. In this way you can get all of the orders in one retrieval operation and then split them up into subsets, rather than connecting to the database once per customer to get their orders.

Now I have explained this, I would be very grateful/interested to hear how you would handle this in llblgen pro

thanks very much andrea

Hi

Me again. Just considering your reply.

I know there are no hard and fast rules about these things, but could you give me a rough indication of table size and when it is best to filter in memory or make trips to the database.

For example, if you had a table of 100 customer records and you were showing them on one page together with their orders, would you get all of the orders for those customers in one go and filter them in memory on the webserver (to get the orders per customer) or would you make 100 round trips to the database to get the orders for each customer. At what rough table size would you switch methods.

I find this interesting!

andieje wrote:

Hi

I'm confusing myself...

If i right, the method is suggested in my post is not very good because getMulti will actually go the database and retrieve a new set of records rather than filtering the ones that are there.

Correct

I don't know if this is defeating the object of llblgen, but if you had a collection of entities in memory and you wanted to get subsets of this collection, could you make a dataview based on the entity collection and then set the rowFilter on the dataview. I am not sure if this is possible as i dont know if an entity collection is based on a dataset.

Entity collection isn't based on the dataset, it's a normal collection. In v2 of LLBLGen Pro, we've implemented entityview objects, which can be filtered using a normal predicate and which are a view on an entitycollection. v2 is currently in beta and is scheduled for release at the end of the month.

This however doesn't make what I said earlier obsolete: it's often faster to re-read data from the db.

Hi

Thanks very much for your reply.

You obviously know your stuff about databases. I am very interested what you would do if you had to display customers and their orders. I am interested in the factors ou would consider as to whether to retrieve orders in multiple trips or get all orders and filter them

Thanks a lot andrea

andieje wrote:

Hi

Thanks very much for your reply.

You obviously know your stuff about databases.

I am very interested what you would do if you had to display customers and their orders. I am interested in the factors ou would consider as to whether to retrieve orders in multiple trips or get all orders and filter them

As I said, it isn't possible to answer that for all different programs. Consider a master-detail form, where you have in the master grid all customers and in the details grid the orders of the customer selected in the master.

Now, say you have 10 customers and each customer has 10 orders. If you load everything up front, with a prefetch path, you end up with 2 queries and 110 rows read (10 customers, 10x10 orders).

That sounds efficient, just 2 queries!

But, what if the user of the application just works with 1 customer and you load the orders on the fly?, then you also end up with 2 queries but with just 20 rows read. And if the user works with 2 customers, you end up with 3 queries, but with 30 rows read. It could be it's more efficient to do that instead of loading 110 rows up front. It also could end up being slower.

Though in general, filtering in-memory is slower than filtering on the database. So loading all data and then filtering in-memory shouldn't be the first choice. UNLESS fetching data is terribly slow compared to filtering in-memory, for example when data is fetched from a webservice.

That's very interesting.

I know there is no hard and fast rule for this kind of thing but i often read that its a good idea to avoid making trips to the database but i guess that doesnt necessarily hold when you are having to filter the database records in the server memory rather than on the database server.

Thanks very much for your replies. I have gained some valuable insights