Hi there

I am retrieving data from SQL 2005 / .NET 2.0 in a fairly common scenario.

The only thing is that I am using views to filter my role based data retrieval. I am going around in circles trying to figure out the best performance strategy.

I have the following views..

vwCategoryDetails ( Contains Category + CategoryRoles joined ~ 2 tables )

vwCategoryProductDetails ( Contains many to many intersection table from Category to Product ~ Single table )

vwSkuDetails ( Contains SKU + SKURoles joined ~ 2 tables )

Now, on one of my pages I need to display a unique Category ( single entity ) and all the related Products for the Category, and all the Related SKUs for the Products.

I used to use prefetches for related entities and use the Category entity, but now it's in a view ( vwCategorydetails ) , I don't have access to get a single entity by unique contraint ( even though effectively the view will bring a unique record back using composite key = role, CategoryName ) .

I don't know if I should be using data readers, SP's with projections of Entity fetches. The whole thing would be read only.

I appreciate any advice you can give me!

thanks for your time

Kind regards Martin

Solution #1: Use EntityViews to map those views, and perform a fetch of your category using filters. Then use the CategoryID as a filter to fetch al related products from the vwCategoryProductDetails TypedView. This solution should be the fastest especially when you need your data in a Read_Only scenario

Solution #2: Map the views onto entities, and define a primary key for each entity in the LLBLGen Pro Designer, also define possible relations between those entities in the Designer. Now you can fetch the categories using it's PK (which might be defined to use the Unique Columns rather than a surrogate key). And you may use prefetchPaths to fetch related products.

thanks for the prompt response.

Solution 1 - you say use entityviews to map those views - Doesn't that use in memory filtering? Isn't that slower ? Is that what yo umeant?

The other thing is that all the views are not typed views - and there is an extra level of heirarchy. - SKU's. Would that change the architecture?

Category > CategoryProducts > SKUs \ \ \ \ Joined with Catsecurity

Solution 2 is close to what i was doing before. the issue is that the Primary key used in relations is ID - wheras the unique key used to select is CategoryName ( combined with roleName - but only 1 rolename will ever be used ) . while the categoryName exists as a unique constraint in the base table I can't see a way of using it here?

I was also wondering if Taget per entity inheritance might be used here - although I can't quite get my head around it yet.

EntityViews.........mmmm I'm sorry that was suposed to be TypedViews I meant using Typed Views.

For Solution #2:

Can you modify the 2nd View to contain the CategoryName as well, so you can pick it to be the PK for the first table and use it in the custom relation?

What I did was use adapter.fetchcollection(category,filter,prefetch)

where filter = ( effectively ) CategoryName = querystring["cname"] & RoleName IN ( '*', currentRole )

Then I used category[0] to bind to the pages.

It would be good to be able to have unique contstraints brought through from views into LLBLGENPro

Also I guess I am still wondering how much faster typed views would be if they were singleton - / cached datatables - if there would be any significant benefits ( bearing in mind that I use output caching in the pages as well ) .

It would be good to be able to have unique contstraints brought through from views into LLBLGENPro

As far as I know, there can be no constraints on database views, and so LLBLGen won't be able to read such unavailable information.

Also you can fetch one entity with its prefetchPath using the following method:

public virtual IEntity2 FetchNewEntity( 
   IEntityFactory2 entityFactoryToUse,
   IRelationPredicateBucket filterBucket,
   IPrefetchPath2 prefetchPath
)

Instead of fetching a collection and then use its first item.

As far as I know, there can be no constraints on database views, and so LLBLGen won't be able to read such unavailable information.

FetchnewEntity works well, thanks!

Also - FYI you can create unique indexes on views in SQL 2005 as long as your view has no Text,Ntext or XML columns

Also - FYI you can create unique indexes on views in SQL 2005 as long as your view has no Text,Ntext or XML columns

But to use the FetchUsingUC... methods, you need to have a Unique Constraint/Key not a Unique Index.