Well - Getting my head in a muddle about this - maybe there is a better way to do this.

I have a heirarchy of data with zero to many relationships as follows.

Category > CategoryProduct > Product > Sku > SkuBasePrice

I have put one in a view - so it looks like this..

Category > vwCategoryProductDetails > vwSkuDetails

The problem is that I need to have a left join between Sku and SkuBasePrice based on a variable.

My questions are - ( code is below )

1) What is the best way to do this relation filter - should I be rethinking the strategy for this? 2) Once I have got all the data together , I want to cache it. How can I cache just the data ( read only ) and not the object ( as recommended in best practises ) .

Thanks Marty

//Code MyCategoryEntity category = new MyCategoryEntity();

//category filter string categoryName = ""Digestive"; RelationPredicateBucket categoryFilter = new RelationPredicateBucket((CategoryFields.CategoryName == categoryName));

using (DataAccessAdapter adapter = new DataAccessAdapter()) { PrefetchPath2 prefetch = new PrefetchPath2((int)EntityType.CategoryEntity);

//Filter SkuBasePrice to only current Role
String[] inRole = new String[] { "*", primaryRole };
PredicateExpression skuFilter = new PredicateExpression(new FieldCompareRangePredicate(VwSkudetailsFields.RoleName, null, inRole));

prefetch.Add(MyCategoryEntity.PrefetchPathVwCategoryProductDetails).SubPath.Add( MyVwCategoryProductDetailsEntity.PrefetchPathVwSkudetails,0);
category = (MyCategoryEntity)adapter.FetchNewEntity(new MyCategoryEntityFactory(), categoryFilter , prefetch);

}

I'm not sure I understand your question. But here is what I understand so far:

You are fetching a Category entity and filtering on Category Name. You are prefetching VwCategoryProductDetails and VwSkudetails.

If a Category entity doesn't have related rows in VwCategoryProductDetails or VwSkudetails, it will be fetched as well. That's not a Join at all.

Also if you want to prefetch VwCategoryProductDetails or VwSkudetails based on a filter, you can add a filter to the prefetchPath.Add() method. (there is an overload that accepts predicateExpression)

If I'm missing something, please post the SQL Query that you want to accomplish.

I'm not sure what you meant by...

Walaa wrote:

If a Category entity doesn't have related rows in VwCategoryProductDetails or VwSkudetails, it will be fetched as well. That's not a Join at all. ??

Walaa wrote:

Also if you want to prefetch VwCategoryProductDetails or VwSkudetails based on a filter, you can add a filter to the prefetchPath.Add() method. (there is an overload that accepts predicateExpression)

Yes I got that but the filter neds to be in the left join.

Walaa wrote:

If I'm missing something, please post the SQL Query that you want to accomplish.

Sorry - the Join is actually inside vwskudetails - so it will be ( Role Name may change each time ) .

also I want to know the best way to cache this data?

Category > vwCategoryProductDetails > Sku > SkuBasePrice

The SQL for sku > Skubaseprice will be

SELECT dbo.SKU., dbo.SKUBasePrice.Price , ISNULL(SkuBaseprice.RoleName,'') RoleName FROM dbo.SKU LEFT JOIN dbo.SKUBasePrice ON dbo.SKU.ID = dbo.SKUBasePrice.SKUID and dbo.SkuBaseprice.RoleName = 'Public'

Sorry - the Join is actually inside vwskudetails - so it will be ( Role Name may change each time ) .

The SQL for sku > Skubaseprice will be

SELECT dbo.SKU., dbo.SKUBasePrice.Price , ISNULL(SkuBaseprice.RoleName,'') RoleName FROM dbo.SKU LEFT JOIN dbo.SKUBasePrice ON dbo.SKU.ID = dbo.SKUBasePrice.SKUID and dbo.SkuBaseprice.RoleName = 'Public'

Oh, you can't pass a parameter to a database View, you may need to out it in a StoredProcedure and call it from LLBLGen Pro. Or you can simulate the entire View with LLBLGen Pro code (get rid of the database view and use LLBLGen Pro DynamicList)

also I want to know the best way to cache this data? Category > vwCategoryProductDetails > Sku > SkuBasePrice

I want to cache it. How can I cache just the data ( read only ) and not the object ( as recommended in best practises ) .

If you don't want to cache the data but not cache the objects, in what form would you like to cache the data?

Walaa wrote:

Oh, you can't pass a parameter to a database View, you may need to out it in a StoredProcedure and call it from LLBLGen Pro. Or you can simulate the entire View with LLBLGen Pro code (get rid of the database view and use LLBLGen Pro DynamicList)

OK - I will try that. Should I maybe do the whole thing as a dynamic list? ( I'm not overly familiar with them ) .

Walaa wrote:

If you don't want to cache the data but not cache the objects, in what form would you like to cache the data?

I don't know - suggestions? datatable would be ideal but maybe that's to much processing . I guess XML would work as well - just needs to be bindable. does LLBL have any good methods for getting the data out of this sort of heirarchy?

Thanks Marty

The complete hierarchy can be written to XML. Please consult the manual: Using the generated code -> XML Support (serialization, de-serialization)