Hi,

I am using App.NET with self servicing model. The requirement is:

• I have a gridview with some filters (drop down, text box, etc) above it.

• Depending on whether a filter is applied or not, I want to dynamically add relation from the main entity to the other entity on which filter needs to be applied.

• Server side paging is a must as there could be lots and lots of data on the server.

• Ideally I want to handle this using datasource control events

I would like to stay away from TypedList because I want to add joins to the query on if they are necessary i.e. only if filters are applied. This is obviously for efficiency reasons.

Is it possible to achieve this? If not, can you give me some ideas or even code for the best possible approach.

Many Thanks!

If you want to use the LLBLGenProDataSource then here are your options:

1- If you are displaying fields from one entity, then use an entityCollection. And you can easily use the dataSource's FilterToUse and RelationsToUse properties for filtering.

2- If you are displaying fields from more than one entity, then you may use a TypedList.

You should set Live Persistence to false and do something like the following in the PerformSelect event:

OrderCustomerTypedList orderCustomer = new OrderCustomerTypedList();
DataAccessAdapter adapter = new DataAccessAdapter();

IRelationPredicateBucket bucket = orderCustomer.GetRelationInfo();

bucket.PredicateExpression.Add(....);  <--------
bucket.Relations.Add(....)                   <--------

adapter.FetchTypedList(orderCustomer.GetFieldsInfo(), orderCustomer, bucket, 0, null, true);

If you are displaying fields from one entity, then use an entityCollection

I am displaying fields from more than one entity. So I cant use entity collection.

If you are displaying fields from more than one entity, then you may use a TypedList

Creating Typed list would mean that I will be creating all possible joins in the typed list and then applying filters on that. This is not desirable due to performance reasons because many joins would mean more processing by SQL Server. I need to join only when the filter is applied.

As an example - I need to show all invoices where one of the currencies in the line item tax records in Euro. In the grid, I do not need to show anything from line items or the tax records. But depending on the filter applied, I may have to make a join.

Is there no way we can use dynamic list for this? and still allow of grid view features like paging and sorting?

Creating Typed list would mean that I will be creating all possible joins in the typed list and then applying filters on that. This is not desirable due to performance reasons because many joins would mean more processing by SQL Server. I need to join only when the filter is applied.

No you don't need to do that.

Only create the Joins enough to display the fields you need. Then at runtime you can additional Joins (relations) as your filtering requires.

Actually the piece of code I've posted before is the same one used with DynamicLists. The only diffrenece is that with a TypedList you already have the fields defined at design time, you don't need to define them in code.

Thanks Walaa for the help. It does show me the right direction.

I am using self servicing model. I guess IRelationPredicateBucket works only with adapter model.

I tried using LLBLGenDataSource.RelationsToUse, but it is null. So I cannot make use of it.

I am unable to find any suitable example in the help files.

Please try the following in the preformSelect event: (code not tested)

MyTypedList myTypedList = new MyTypedList();

RelationCollection relations = myTypedList.GetRelationsInfo(); relations.Add(...); <---------------------------- // add relations as needed

PredicateExpression filter = new PredicateExpression(); filter.Add(...); <-------------------------// Also add predicates as needed

TypedListDAO dao = new TypedListDAO();

dao.GetMultiAsDataTable(myTypedList.getFieldsInfo(), e.ContainedTypedList, 0, null, filter, relations, true, null, null, 0, 0);

The myTypedList.GetRelationsInfo() method does not exist. Would that be - myTypedList.BuildRelationSet() ?

Also, I could not find myTypedList.getFieldsInfo(). I tried with myTypedList.BuildResultset() but that did not work either.

The myTypedList.GetRelationsInfo() method does not exist. Would that be - myTypedList.BuildRelationSet() ?

Also, I could not find myTypedList.getFieldsInfo(). I tried with myTypedList.BuildResultset()

Oh, I'm sorry that's my mistake. You are correct, since you are using SelfServicing, then you would find BuildRelationSet() & BuildResultset() instead of the methods I used (I was using Adapter methods).

but that did not work either

Would you please post a sample repro solution of what you were trying to do, so I can debug it. (please use Northwind database for easiness)

Hi Walaa,

I am attaching a solution targeting Northwind database. It has 2 projects - the LLBLGen generated code and the web project. There is a TypedList called CustomerOrdersTypedList created in it which contains fields from 2 tables - customers and orders.

Can we take a simple case like -- showing all records from the typedlist where OrderDetails.UnitPrice > 50

Thanks for the help!

I tested it in VS 2005. The following piece of code works just fine.

Provided that in the LLBLGenProDataSource EnablePaging="False"

        protected void LLBLGenProDataSource1_PerformGetDbCount(object sender, SD.LLBLGen.Pro.ORMSupportClasses.PerformGetDbCountEventArgs e)
        {
            //e.DbCount = e.ContainedCollection.GetDbCount();
        }
        protected void LLBLGenProDataSource1_PerformSelect(object sender, SD.LLBLGen.Pro.ORMSupportClasses.PerformSelectEventArgs e)
        {
            CustomerOrdersTypedList myTypedList = new CustomerOrdersTypedList();

            IRelationCollection relations = myTypedList.BuildRelationSet();
            relations.Add(OrdersEntity.Relations.OrderDetailsEntityUsingOrderId);

            PredicateExpression filter = new PredicateExpression();
            filter.Add(OrderDetailsFields.UnitPrice > 100);

            TypedListDAO dao = new TypedListDAO();

            dao.GetMultiAsDataTable(myTypedList.BuildResultset(), (DataTable)e.ContainedTypedList, 0, null, filter, relations, true, null, null, 0, 0);

        }

**Now if you want to enable paging in the dataSource you should appropriatly set the e.DbCount in the PerformGetDbCount event.

And appropriatly set the last 2 parameters of the GetMultiAsDataTable method to specify the page size and page number**

Thats great Walaa! Thanks! It works very well.

Cheers !!