Database: Sql2005 LLBLGen Pro: v2.6 Adapter using SD.TemplateBindings.SharedTemplates.NET20 .NET Framework: 3.5

We have a view which acts as a join table between our master and detail tables.

Unfortunately we have a composite key situation, and when the viewEntity is prefetched we are hitting the 2100 parameter limit on medium sized queries.

Using the designer I had added the view as a related Entity, and as this is working for smaller queries so I assume that I have setup the designer correctly.

My question is how to not perform a prefetch on this view, yet still join to the detail table.

my problem feels similar to this thread

http://llblgen.com/TinyForum/Messages.aspx?ThreadID=8610

For if I fetch the entire table (which is very small) without any where clause, and used it to join master and detail I'd be golden.

Thanks, Alex

Hi Alex,

I read your post and the related thread. However I don't fully understand yet what is your real scenario/problem. Could you please elaborate more and post some example of what you are trying to do?

Table A would be a Customer table, with 3 keys L, M, and N Table B would be a product table with 2 keys, M and O

joining Table A and B is Table C, having keys of L, M, N, O

so far all the tables are related and the designer automatically created good relationships.

The normal query pattern for us is to create Predicates to obtain specific customers(Table A), then prefetch into Table C, and subpath prefetch in to Table B

so far so good, queries are looking good.

Recently a view has been created which linked Table A to Table B. It has some logic in it to give us a unique product (Table B) for a customer (Table A) call this View D, having columns matching L, M, N, and O

so this view looks nearly identical to Table C.

Adding View D to the design as a entity, I needed to manually create a relationship from Table A to View D, and manually created a relationship from Table B to View D.

I then leave the original query in place, same predicates, and add a prefetch to View D, and a subpath prefetch to Table B.

Now everything goes sideways, the prefetch query into View D looks awful. select * from ViewD where L in ( ( L and M and N) or (L and M and N ) or..........etc) and M in ( ( L and M and N) or (L and M and N ) or..........etc) and N in ( ( L and M and N) or (L and M and N ) or..........etc)

We need to maintain the same query/predicate pattern to achieve the proper assembly of the Business objects. And currently the view satifies our need for configurable logic. So hopefully we can keep this.

Was the View not properly added to the designer? Are the relationships incorrectly created in the designer? Does something need to change with the prefetch paths for that view?

Thanks, Alex

Adding View D to the design as a entity, I needed to manually create a relationship from Table A to View D, and manually created a relationship from Table B to View D.

I have a feeling that you are using prefetchPaths while all you need is using JOINs in a query.

Did you set the ParameterisedPrefetchPathThreshold to any large value?

Would you please post the LLBLGen Pro runtime library version?

Would you please post the LLBLGen Pro runtime library version?

SD.LLBLGen.Pro.ORMSupportClasses.NET20 v2.0.50727

Yes, I tried changing the ParameterisedPrefetchPathThreshold value. I was able to get past the 2100 parameter limit, However, that query's performance was not acceptable at all.

I was unaware that the designer allows me to set specific columns as a PK on a entity mapped as a view. I am currently trying different setups down that path.

OK, adding the columns of the view entity in the designer as PK's did not help.

Walaa wrote:

I have a feeling that you are using prefetchPaths while all you need is using JOINs in a query

Could you please elaborate on the joins? basically I need all the data contained within the object graph of an EntityCollection<Table_A_Entity>.

I set the predicate on TableA, and prefetch the related data. Would I leave ViewD out of the prefetch? Or does that particular prefetch just need a predicate or RelationshipCollection when its added to the TableA prefetchpath?

SD.LLBLGen.Pro.ORMSupportClasses.NET20 v2.0.50727

That's not a valid build/version number. please check this thread: http://www.llblgen.com/TinyForum/Messages.aspx?ThreadID=7722

Also would you please attach a repro solution (better on Northwind). As I find it hard to understand what you are trying to do.

amccool wrote:

Table A would be a Customer table, with 3 keys L, M, and N Table B would be a product table with 2 keys, M and O

joining Table A and B is Table C, having keys of L, M, N, O

so far all the tables are related and the designer automatically created good relationships.

The normal query pattern for us is to create Predicates to obtain specific customers(Table A), then prefetch into Table C, and subpath prefetch in to Table B so far so good, queries are looking good.

Recently a view has been created which linked Table A to Table B. It has some logic in it to give us a unique product (Table B) for a customer (Table A) call this View D, having columns matching L, M, N, and O so this view looks nearly identical to Table C.

Adding View D to the design as a entity, I needed to manually create a relationship from Table A to View D, and manually created a relationship from Table B to View D. I then leave the original query in place, same predicates, and add a prefetch to View D, and a subpath prefetch to Table B.

Now everything goes sideways, the prefetch query into View D looks awful. select * from ViewD where L in ( ( L and M and N) or (L and M and N ) or..........etc) and M in ( ( L and M and N) or (L and M and N ) or..........etc) and N in ( ( L and M and N) or (L and M and N ) or..........etc) We need to maintain the same query/predicate pattern to achieve the proper assembly of the Business objects. And currently the view satifies our need for configurable logic. So hopefully we can keep this.

You also should see the same queries when you're fetching C instead of D (so the table instead of the view). For the logic producing the code it's not different if the target is a view or a table, it doesn't even know at that point.

Was the View not properly added to the designer? Are the relationships incorrectly created in the designer? Does something need to change with the prefetch paths for that view?

The problem with the filter is that the child set has to be filtered on the parent's set. If you have a compound PK (and thus also a compound FK), this gives a bit of a problem, because it needs to use less optimal AND/OR predicates.

It's a bit problematic as it has to define a series of tuples to which the set to fetch has a matching PK/FK in, and this is not really doable with compound PK/FK's because you can't hard-code the tuples in the filter, there's no SQL statement which can utilize these, it has to be done per-field.

As the view with the compound key is in the middle of the path, you won't see much difference by doing the path in reverse order, as the end node always has to be filtered on the fields in the pk of the entity mapped onto the view, unless the end-node currently (B) is a small set, then it could help together with the threshold to get a normal subquery .

Using a 3-field compound PK is relatively rare and often an indication that you can get rid of at least 1 field. I'm not sure if this is a legacy db and you have to work with the 3-field PK, though if you can change this, please introduce a single field PK.

Additionally, if nothing helps, you could do a per-parent 2-layer fetch, so fetch the A + D's and then for each D, fetch the B's.