Here is the query generated when I'm using PrefetchPaths and I just want to make sure I'm not doing something wrong because it seems like there is at least one too many joins here.

[code]declare @RegistrationId1 int set @RegistrationId1 = 154891

SELECT [smartweekly].[dbo].[cd_semester].[code] AS [Code] ,[smartweekly].[dbo].[cd_semester].[abbr] AS [Abbr] ,[smartweekly].[dbo].[cd_semester].[descr] AS [Descr]

FROM [smartweekly].[dbo].[cd_semester] WHERE ( [smartweekly].[dbo].[cd_semester].

 IN
(SELECT [smartweekly].[dbo].[stu_marks].[semester] AS [Semester]

FROM [smartweekly].[dbo].[stu_marks]

WHERE ( [smartweekly].[dbo].[stu_marks].[registration_id] IN

(SELECT [smartweekly].[dbo].[registration].[id] AS [Id]
FROM ( [smartweekly].[dbo].[registration]
INNER JOIN [smartweekly].[dbo].[stu_marks]
ON  [smartweekly].[dbo].[registration].[id]=[smartweekly].[dbo].[stu_marks].[registration_id])

WHERE ( [smartweekly].[dbo].[stu_marks].[registration_id] = @RegistrationId1)))))

...that is generated from LLBLGen. Now, here is my code to build up this query:

RegistrationEntity reg = new RegistrationEntity(registrationID);
IPrefetchPath2 regP = new PrefetchPath2((int)EntityType.RegistrationEntity);
regP.Add(RegistrationEntity.PrefetchPathStuMarks).SubPath.Add(StuMarksEntity.PrefetchPathCdSemester);
IRelationPredicateBucket smF = new RelationPredicateBucket();
smF.PredicateExpression.Add(PredicateFactory.CompareValue(StuMarksFieldIndex.RegistrationId, ComparisonOperator.Equal,registrationID));

using (DataAccessAdapter da = new DataAccessAdapter())
{
    da.FetchEntityCollection(reg.StuMarks,smF,0,null,regP);
}

...so I understand that this is the query to get the semester values from cd_semester (my code set), but why so many joins? Quick datamodel info:

1) Registration holds the set of classes a student is registered in 2) StuMarks is the set of marks he receives for a single registration (M(StuMark):1(Registration)) 3) CdSemester holds the abbreviation of the code in StuMarks. I want to show "Fall" instead of "F".

Where have I gone astray?

NickD wrote:

Here is the query generated when I'm using PrefetchPaths and I just want to make sure I'm not doing something wrong because it seems like there is at least one too many joins here.

[code]declare @RegistrationId1 int set @RegistrationId1 = 154891

SELECT [smartweekly].[dbo].[cd_semester].[code] AS [Code] ,[smartweekly].[dbo].[cd_semester].[abbr] AS [Abbr] ,[smartweekly].[dbo].[cd_semester].[descr] AS [Descr]

FROM [smartweekly].[dbo].[cd_semester] WHERE ( [smartweekly].[dbo].[cd_semester].

 IN
(SELECT [smartweekly].[dbo].[stu_marks].[semester] AS [Semester]

FROM [smartweekly].[dbo].[stu_marks]

WHERE ( [smartweekly].[dbo].[stu_marks].[registration_id] IN

(SELECT [smartweekly].[dbo].[registration].[id] AS [Id]
FROM ( [smartweekly].[dbo].[registration]
INNER JOIN [smartweekly].[dbo].[stu_marks]
ON  [smartweekly].[dbo].[registration].[id]=[smartweekly].[dbo].[stu_marks].[registration_id])

WHERE ( [smartweekly].[dbo].[stu_marks].[registration_id] = @RegistrationId1)))))

...that is generated from LLBLGen. Now, here is my code to build up this query:

RegistrationEntity reg = new RegistrationEntity(registrationID);
IPrefetchPath2 regP = new PrefetchPath2((int)EntityType.RegistrationEntity);
regP.Add(RegistrationEntity.PrefetchPathStuMarks).SubPath.Add(StuMarksEntity.PrefetchPathCdSemester);
IRelationPredicateBucket smF = new RelationPredicateBucket();
smF.PredicateExpression.Add(PredicateFactory.CompareValue(StuMarksFieldIndex.RegistrationId, ComparisonOperator.Equal,registrationID));

using (DataAccessAdapter da = new DataAccessAdapter())
{
    da.FetchEntityCollection(reg.StuMarks,smF,0,null,regP);
}

...so I understand that this is the query to get the semester values from cd_semester (my code set), but why so many joins?

There is one join, and I don't see why it's there, as the smF.RelationCollection isn't filled. (in the code snippet you posted).

The subqueries are necessary because you're fetching a graph. So it first fetches all registration objects matching the filter. Then with that filter it will fetch all StuMarks. And with the filter used to fetch all StuMarks, it fetches all CdSemester objects. It has to use the filter used to fetch the parents to fetch the childs, as only those childs have to be fetched. There is no other way to formulate the filter for the childs if you want to filter them based on the parents you fetched.

the mistery join in your query is weird. Are you sure you posted the complete code you use?

Otis wrote:

There is one join, and I don't see why it's there, as the smF.RelationCollection isn't filled. (in the code snippet you posted).

The subqueries are necessary because you're fetching a graph. So it first fetches all registration objects matching the filter. Then with that filter it will fetch all StuMarks. And with the filter used to fetch all StuMarks, it fetches all CdSemester objects. It has to use the filter used to fetch the parents to fetch the childs, as only those childs have to be fetched. There is no other way to formulate the filter for the childs if you want to filter them based on the parents you fetched.

the mistery join in your query is weird. Are you sure you posted the complete code you use?

OK, I see what you are saying about the missing relation collection, however, that is my code. I assumed that using prefetch paths would somehow create the necessary joins. Was that an incorrect assumption?

After a little playing around, I removed the PrefetchPath2 and then the query was as expected . "Select StuMarks.* from StuMarks where RegistrationID = @RegistrationID". So, the PrefetchPaths do add the joins and relations?

Since I am using an Entity (RegistrationEntity) to fetch the related collection (StuMarks), do I set up the PrefetchPath on (int)EntityType.Registration? If so, do I then add a prefetch path from RegistrationEntity.PrefetchPathStuMarks? Or is that adding in something twice?

Please ignore above - I figured out the problem. I was using a PrefetchPath from the wrong element . I assumed that I had to start with the Entity as my PrefetchPath base. However, after some working on it, I figured out that I needed to use the entity inside my collection as the PrefetchPath starting point. Once I did that, it got rid of the "mystery" join.

Amazing how once you start using it right, it does what you want it to.

NickD wrote:

Please ignore above - I figured out the problem. I was using a PrefetchPath from the wrong element . I assumed that I had to start with the Entity as my PrefetchPath base. However, after some working on it, I figured out that I needed to use the entity inside my collection as the PrefetchPath starting point. Once I did that, it got rid of the "mystery" join.

I already wondered how that ended up there

Amazing how once you start using it right, it does what you want it to.

. Prefetch paths tend to be a tough thing to grasp sometimes. It's due to the linear way you write code in lines vs. the non-linear way of an object graph, I think.