Hi, Using LLBLgen 2.6 with latest assemblies, .NET3.5, SelfServicing and SQL2005.

I currently have a TypedList setup via the designer which includes several fields from various entities. I decided to do it this way to reduce the number of DB queries which works well. I am using the LLBLdatasouce control to load in the vehicles from the TypedList and paging them to reduce the amount of data.

My question is, I have 3 entities shown below, a vehicle can belong to many categories which are saved in the VehicleToCategoriesLink table. From the Vehicles entity, I can retrieve a collection of the categories which the vehicle belongs to via the VehicleToCategoriesLink relation which the designer has created for me. But how can I retrieve the vehicle categories for each vehicle when using a TypedList?

Vehicles VehicleCategories VehicleToCategoriesLink

I am using the TypedList with a Telerik gridview to display all the vehicles and in the list I need to show what categories they belong to. Currently I have achieved this by using the ItemDataBound event and manually loading in that Vehicle using the PK for the current row and then getting the Vehicle Categories collection from the Vehicle entity but this is causing a lot of queries.

So, can I do something in the designer to the TypedList to cater for the Vehicle Categories or can I reduce the queries somehow?

Many thanks Dan

Not easily, no. A typed list is analogous to a database view, and if you are joining vehicles to categories via a link table you are going to get the vehicle rows repeating in the typed view - once per category.

How many items are you loading into a page at a time...? If the amount is not too large you could acutally fetch the data as a graph of vehicles, each with their related categories, and do some client side processing to flatten the collection of categories into a single string which could be displayed in your grid.

Matt

Hi Matt,

Thanks for your response. The user will be given the choice of the amount of rows to show but this would be between 10 and 25. So do you think it would be better to load in the vehicles first then get each vehicles categories via a collection and merge them together into something like a datatable? How could we do this in the most efficient way in terms of the least amount of queries?

Would it be more efficient to create a storedproc and do let SQL do the processing and have it return the completed set of data? If this is an option, will it still support paging or would we need to create some logic in the SP to handle this?

Many thank Dan

Vehicles VehicleCategories VehicleToCategoriesLink

I am using the TypedList with a Telerik gridview to display all the vehicles and in the list I need to show what categories they belong to. Currently I have achieved this by using the ItemDataBound event and manually loading in that Vehicle using the PK for the current row and then getting the Vehicle Categories collection from the Vehicle entity but this is causing a lot of queries.

There are many options here:

1- You can avoid loading/fetching the Vehicle in the ItemDataBound event and just go ahead to load the VehicleCategories or even the VehicleToCategoriesLink directly using JOINS/Relations and Filters. Since you 'll have the Vehicle PK.

2- Wait till you load all rows of the grid page, collect the PKs in an array, use it as a filter to fetch all VehicleCategories in one Category. Then loop on the Grid Rows to inject the right Categories for each row using the Vehicle PK.

3- This is the option I prefer. Instead of using a TypedList, use a database View to fetch the same fields, map this View as an Entity in LLBLGen Pro, use the Designer to create a custom relation between this entity and the VehicleCategories. Use LLBLGenProDataSource to fetch a collection of the newly intoduced entity, while specifying a PrefetchPath to fetch the related VehicleCategories in one go.

Walaa wrote:

3- This is the option I prefer. Instead of using a TypedList, use a database View to fetch the same fields, map this View as an Entity in LLBLGen Pro, use the Designer to create a custom relation between this entity and the VehicleCategories. Use LLBLGenProDataSource to fetch a collection of the newly intoduced entity, while specifying a PrefetchPath to fetch the related VehicleCategories in one go.

Hi, ok i have created the view and added it to the designer but how can i create a custom relation between the view entity and the VehicleCategories? I've gone through the help and can't seem to be able to create a relation with a view.

You should edit your "Entity mapped on view". Then there's a sub-tab called "Relations". There you can add relations between entities.

If you need you could define primary key(s) (to define the relation) on that "entity mapped on view", go to the "fields mapped on database fields" sub-tab and choose the field(s) you want and mark the "Is part of primary key" checkbox".

Forgot to say thanks for the support and advice, have got it working correctly now.

Hi, i think i jumped the gun a bit... all is working just fine but i have just realised i need to also be able to filter on the VehicleCategories which is a m:n relationship via the VehicleToCategoriesLink table. I am trying to figure out how i can do this with Option 3 which Walaa suggested. Are you able to advise on how i can do this?

Many thanks Dan

Please can you post the code you are using to fetch your view based entites ?

Hi, i'm using the LLBLGen datasource control to do the fetching:

<llblgenpro:LLBLGenProDataSource ID="dsVehicles" 
            runat="server" DataContainerType="EntityCollection" 
            CacheLocation="Session" EnablePaging="True" EntityCollectionTypeName="vca_lightvans.CollectionClasses.VehicleSearchListCollection, vca_lightvans">
</llblgenpro:LLBLGenProDataSource>

and in the page load, i define a Prefetch Path:

Dim path As IPrefetchPath = New PrefetchPath(CType(EntityType.VehicleSearchListEntity, Integer))
path.Add(VehicleSearchListEntity.PrefetchPathVehicleCategoriesCollectionViaVehicleToCategoriesLink)
dsVehicles.PrefetchPathToUse = path

and also apply filters depending on what the user has selected from various drop-downs, i.e.:

If (ddlModels.SelectedValue <> "") Then
            If (CType(ddlModels.SelectedValue, Integer) > 0) Then
                filter.Add(VehicleSearchListFields.ModelPKID = ddlModels.SelectedValue)
            End If
End If

One of the filters i need to add is the VehicleCategory which is posing this problem.

In the grid's ItemDataBound event, i get the list of Vehicle Categories for each vehicle via the relationship i created in the designer:

Dim item As GridDataItem
item = e.Item
Dim objVehicle As VehicleSearchListEntity = dsVehicles.EntityCollection.Item(item.ItemIndex)
Dim colVehicleCategories As VehicleCategoriesCollection = objVehicle.VehicleCategoriesCollectionViaVehicleToCategoriesLink
For Each objVehicleCategory As VehicleCategoriesEntity In colVehicleCategories
      item("VehicleCategory").Text += objVehicleCategory.VehicleCategoryName
Next

To filter on a related entity, just specify the relation(s) to it. So if you want to filter the View based Entities returned by the dataSource, use the LLBLGenProDataSource properties FilterTouse and RelationsToUse.

In the FilterToUse add the filter based on the Field in the VehicleToCategoriesLink. And use the RelationsToUse to add the relation from the View-Entity to the VehicleCategories entity and add aother relation from VehicleCategories to VehicleToCategoriesLink.