Okay I have a simple 3 table structure.. I have the following tables... Template, Question and TemplateQuestion. Template (pk TemplateID), Question (QuestionID) - TemplateQuestion (TemplateID, QuestionID). When I add these all to the LLBL IDE - all the proper relationships are mapped. I generate the code and all seems fine.. but when I create a new template and try to load up all the questions through the m:n I get an error. Here is my code.. not using Adapter and v2.0...

DataAccessAdapter adapter = new DataAccessAdapter(); ReviewEntity currentReview = new ReviewEntity(1); adapter.FetchEntity(currentReview);

currentReview.Template = (TemplateEntity)adapter.FetchNewEntity(new TemplateEntityFactory(), currentReview.GetRelationInfoTemplate()); adapter.FetchEntityCollection(currentReview.Template.QuestionCollectionViaTemplateQuestion, currentReview.Template.GetRelationInfoTemplateQuestion());

When the query runs here is what i get from the sql trace... Seems the join to templateQuestion is not being created.. any idea what I'm dong wrong? Thanks!

exec sp_executesql N'SELECT [LCGReview].[dbo].[Question].[Question_ID] AS [QuestionId], [LCGReview].[dbo].[Question].[Title], [LCGReview].[dbo].[Question].[QuestionText], [LCGReview].[dbo].[Question].[Active] FROM [LCGReview].[dbo].[Question] WHERE ( ( [LCGReview].[dbo].[TemplateQuestion].[Template_ID] = @TemplateId1))',N'@TemplateId1 int',@TemplateId1=1

Msg 4104, Level 16, State 1, Line 1 The multi-part identifier "COReview.dbo.TemplateQuestion.Template_ID" could not be bound.

adapter.FetchEntityCollection(currentReview.Template.QuestionCollectionViaTemplateQuestion, currentReview.Template.GetRelationInfoTemplateQuestion());

The GetRelationInfoTemplateQuestion() will get the relation (join) between Template and TemplateQuestion, while you are fetching a collection of Questions not TemplateQuestions.

It's better and easier to use one fetch to the Templates with prefetchPaths to TemplateQuestions and SubPaths to Questions.

Or even one fetch to the CurrentReview with the Templates, TemplateQuestions and Questions fetched by prefetchPaths.

thanks for the reply. I tried a couple different ways to use prefetched paths, but I could only get a prefetched path to the first releation of template. Could you show me an example of how to use a prefetch to this chain of objects? I guess I'm confused as to how to add multiple prefetched paths to an expression. - Thanks!

Hi,

that should look like:

IPrefetchPath2 prefetchPath = new PrefetchPath2((int)EntityType.ReviewEntity);
prefetchPath.Add(ReviewEntity.PrefetchPathTemplate).SubPath.Add(TemplateEntity.PrefetchPathQuestionCollectionViaTemplateQuestion);

Now if you want to add parallel prefetch branches (not the case here, just a chain as you said), you've got to retrieve the PrefetchPathElement2 into intermediate variables to perform multiple additions.

Cheers

Wow..great.. sorry I totally missed that in the documentation!!