I am using a prefetch path to load a parent entity with its child entities.

The parent entity is mapped to the child entities at the database level with an intermediary mapping table (an M:N relationship). This mapping table contains order (i.e. sequence) information on how those child entities should be sorted.

Can I use a SortExpression with the prefetch to sort the child entities in the correct order, even though the order information is on the intermediary table?

So does the graph you are loading look like

Parent -> Mapping -> Child

• ie each parent has a collection of Mappping entities, each of which has one Child entity ?

If not, how are you obtaining a single collection of all of the child entities for a parent ?

Matt

The generated parent entity hides that intermediate mapping table by exposing a field that references the mapped collection.

So the relationship is:

Course -> CourseLessonMap -> Lesson

And the CourseEntity.Lessons field exposes a LessonCollection class. By the same token, it also exposes a CourseEntity.PrefetchPathLessons field. That's how I'm loading the child entities.

This isn't custom code, it's just a standard Self-Servicing model generated from LLBLGen with minimal modification using the LLBLGen GUI.

Yes, simply specify a sort expression when you're adding the prefetch path node for Lessons to the prefetch path

Unfortunately, I am receiving an error when attempting this:

The multi-part identifier "LOCAL_IHIDB.dbo.lmsCourseLessonMap.DisplayOrder" could not be bound. Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

Exception Details: System.Data.SqlClient.SqlException: The multi-part identifier "LOCAL_IHIDB.dbo.lmsCourseLessonMap.DisplayOrder" could not be bound.

Here is the C# code I am using:

CourseCollection courses = new CourseCollection();
ISortExpression lessonSorter = new SortExpression(CourseLessonMapFields.DisplayOrder | SortOperator.Ascending);
IPrefetchPath coursePrefetch = new PrefetchPath(EntityType.CourseEntity);
IPrefetchPathElement lessonElement = coursePrefetch.Add(CourseEntity.PrefetchPathLessons, 0, null, null, lessonSorter);
courses.GetMulti(null, coursePrefetch);

To help clarify the table structure here is an extract of the sql code for these tables:

CREATE TABLE [dbo].[lmsCourse](
    [CourseGUID] [uniqueidentifier] NOT NULL,
    [Name] [varchar](100) NOT NULL,
 CONSTRAINT [PK_lmsCourse] PRIMARY KEY CLUSTERED 
(
    [CourseGUID] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]


CREATE TABLE [dbo].[lmsCourseLessonMap](
    [CourseGUID] [uniqueidentifier] NOT NULL,
    [LessonGUID] [uniqueidentifier] NOT NULL,
    [DisplayOrder] [int] NOT NULL,
 CONSTRAINT [PK_lmsCourseLessonMap] PRIMARY KEY CLUSTERED 
(
    [CourseGUID] ASC,
    [LessonGUID] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]

CREATE TABLE [dbo].[lmsLesson](
    [LessonGUID] [uniqueidentifier] NOT NULL,
    [Title] [varchar](100) NOT NULL,
 CONSTRAINT [PK_lmsLesson] PRIMARY KEY CLUSTERED 
(
    [LessonGUID] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

ALTER TABLE [dbo].[lmsCourseLessonMap]  WITH CHECK ADD  CONSTRAINT [FK_LessonGUID_lmsLesson] FOREIGN KEY([LessonGUID])
REFERENCES [dbo].[lmsLesson] ([LessonGUID])
GO

ALTER TABLE [dbo].[lmsCourseLessonMap]  WITH CHECK ADD  CONSTRAINT [FK_lmsCourseLessonMap_lmsCourse] FOREIGN KEY([CourseGUID])
REFERENCES [dbo].[lmsCourse] ([CourseGUID])
GO

Do you have suggestions on an alternate prefetch strategy to make this work?

You are trying to sort on a field in a table which is not included in the query for the pre-fetched child objects. To add this table to the query (effectivly a SQL JOIN) you need to create a RelationCollection containg the relation object between LessonMap and Lesson, and pass it as a parameter to

IPrefetchPathElement lessonElement = coursePrefetch.Add(CourseEntity.PrefetchPathLessons, 0, null, null, lessonSorter);

It'll be one of the parameters which is currently null but I can't remember which one of the top of my head.

Matt

I made the changes you recommend and now I receive:

"Relation at index 2 doesn't contain an entity already added to the FROM clause. Bad alias?"

Here's the code:

RelationCollection relations = new RelationCollection();
relations.Add(CourseEntity.Relations.CourseLessonMapEntityUsingCourseGuid);
relations.Add(CourseLessonMapEntity.Relations.LessonEntityUsingLessonGuid);

ISortExpression lessonSorter = new SortExpression(CourseLessonMapFields.DisplayOrder | SortOperator.Ascending);
IPrefetchPath coursePrefetch = new PrefetchPath(EntityType.CourseEntity);
IPrefetchPathElement lessonElement = coursePrefetch.Add(CourseEntity.PrefetchPathLessons, 0, null, relations, lessonSorter);

CourseCollection courses = new CourseCollection();
courses.GetMulti(null, coursePrefetch);

If I comment out the first relations line:

//relations.Add(CourseEntity.Relations.CourseLessonMapEntityUsingCourseGuid);

The code runs without error, but still does not sort correctly on the CourseLessonMapFields.DisplayOrder field.

I think you are adding the incorrect relation. As you are prefetching lessons, I think the relation should be:

RelationCollection relations = new RelationCollection();
relations.Add(LessonEntity.Relations.CourseLessonMapEntityUsingLessonGuid);

Final code:

RelationCollection relations = new RelationCollection();
relations.Add(LessonEntity.Relations.CourseLessonMapEntityUsingLessonGuid);

ISortExpression lessonSorter = new SortExpression(CourseLessonMapFields.DisplayOrder | SortOperator.Ascending);

IPrefetchPath coursePrefetch = new PrefetchPath(EntityType.CourseEntity);
IPrefetchPathElement lessonElement = coursePrefetch.Add(CourseEntity.PrefetchPathLessons, 0, null, relations, lessonSorter);

CourseCollection courses = new CourseCollection();
courses.GetMulti(null, coursePrefetch);

Unfortunately even with the suggested relation change, the lessons are still not sorted correctly.

I appreciate your continued efforts to help me solve this issue. Any other suggestions to try? Or any further detail I can give you to help diagnose the problem?

Could you post the queries generated (please enable DQE tracing for this) ?

Sorting of prefetch path queries isn't always enabled, as it could lead to serious performance issues in some cases.

Apologies for the long delay in responding to your request for the query trace (travel and other priorities intervened). Here is the trace:

Method Enter: CreatePagingSelectDQ
Method Enter: CreateSelectDQ
Method Enter: CreateSelectDQ
Generated Sql query: 
    Query: SELECT [LOCAL_IHIDB].[dbo].[lmsCatalog].[CatalogGUID] AS [CatalogGuid], [LOCAL_IHIDB].[dbo].[lmsCatalog].[Name], [LOCAL_IHIDB].[dbo].[lmsCatalog].[Description], [LOCAL_IHIDB].[dbo].[lmsCatalog].[StatusId] FROM [LOCAL_IHIDB].[dbo].[lmsCatalog] 

Method Exit: CreateSelectDQ
Method Exit: CreatePagingSelectDQ: no paging.
Method Enter: CreatePagingSelectDQ
Method Enter: CreateSelectDQ
Method Enter: CreateSelectDQ
Generated Sql query: 
    Query: SELECT DISTINCT [LOCAL_IHIDB].[dbo].[lmsCourse].[CourseGUID] AS [CourseGuid], [LOCAL_IHIDB].[dbo].[lmsCourse].[TopicGUID] AS [TopicGuid], [LOCAL_IHIDB].[dbo].[lmsCourse].[CourseNumber], [LOCAL_IHIDB].[dbo].[lmsCourse].[Name], [LOCAL_IHIDB].[dbo].[lmsCourse].[StatusId], [LOCAL_IHIDB].[dbo].[lmsCourse].[CreatedInCatalogGUID] AS [CreatedInCatalogGuid] FROM (( [LOCAL_IHIDB].[dbo].[lmsCatalog] [LPA_C1]  INNER JOIN [LOCAL_IHIDB].[dbo].[lmsCatalogCourseMap] [LPA_C2]  ON  [LPA_C1].[CatalogGUID]=[LPA_C2].[CatalogGUID]) INNER JOIN [LOCAL_IHIDB].[dbo].[lmsCourse]  ON  [LOCAL_IHIDB].[dbo].[lmsCourse].[CourseGUID]=[LPA_C2].[CourseGUID]) WHERE ( ( [LPA_C2].[CatalogGUID] IN (@CatalogGuid1, @CatalogGuid2, @CatalogGuid3))) ORDER BY [LOCAL_IHIDB].[dbo].[lmsCourse].[TopicGUID] ASC,[LOCAL_IHIDB].[dbo].[lmsCourse].[Name] ASC
    Parameter: @CatalogGuid1 : Guid. Length: 0. Precision: 0. Scale: 0. Direction: Input. Value: 8202cd64-cc87-4b54-9377-f67f8b513498.
    Parameter: @CatalogGuid2 : Guid. Length: 0. Precision: 0. Scale: 0. Direction: Input. Value: 6cb1c614-884b-43ef-9abd-d90849f183d4.
    Parameter: @CatalogGuid3 : Guid. Length: 0. Precision: 0. Scale: 0. Direction: Input. Value: e4137c45-faaf-4760-ad95-39f17f7c8c75.

Method Exit: CreateSelectDQ
Method Exit: CreatePagingSelectDQ: no paging.
Method Enter: CreatePagingSelectDQ
Method Enter: CreateSelectDQ
Method Enter: CreateSelectDQ
Generated Sql query: 
    Query: SELECT DISTINCT [LPA_C1].[CatalogGUID] AS [CatalogGuid0], [LOCAL_IHIDB].[dbo].[lmsCourse].[CourseGUID] AS [CourseGuid1] FROM (( [LOCAL_IHIDB].[dbo].[lmsCatalog] [LPA_C1]  INNER JOIN [LOCAL_IHIDB].[dbo].[lmsCatalogCourseMap] [LPA_C2]  ON  [LPA_C1].[CatalogGUID]=[LPA_C2].[CatalogGUID]) INNER JOIN [LOCAL_IHIDB].[dbo].[lmsCourse]  ON  [LOCAL_IHIDB].[dbo].[lmsCourse].[CourseGUID]=[LPA_C2].[CourseGUID]) WHERE ( ( ( [LPA_C2].[CatalogGUID] IN (@CatalogGuid1, @CatalogGuid2, @CatalogGuid3))))
    Parameter: @CatalogGuid1 : Guid. Length: 0. Precision: 0. Scale: 0. Direction: Input. Value: 8202cd64-cc87-4b54-9377-f67f8b513498.
    Parameter: @CatalogGuid2 : Guid. Length: 0. Precision: 0. Scale: 0. Direction: Input. Value: 6cb1c614-884b-43ef-9abd-d90849f183d4.
    Parameter: @CatalogGuid3 : Guid. Length: 0. Precision: 0. Scale: 0. Direction: Input. Value: e4137c45-faaf-4760-ad95-39f17f7c8c75.

Method Exit: CreateSelectDQ
Method Exit: CreatePagingSelectDQ: no paging.
Method Enter: CreatePagingSelectDQ
Method Enter: CreateSelectDQ
Method Enter: CreateSelectDQ
Generated Sql query: 
    Query: SELECT [LOCAL_IHIDB].[dbo].[lmsLesson].[LessonGUID] AS [LessonGuid], [LOCAL_IHIDB].[dbo].[lmsLesson].[Title], [LOCAL_IHIDB].[dbo].[lmsLesson].[StatusId], [LOCAL_IHIDB].[dbo].[lmsLesson].[CreatedInCourseGUID] AS [CreatedInCourseGuid] FROM ((( [LOCAL_IHIDB].[dbo].[lmsCourse] [LPA_C1]  INNER JOIN [LOCAL_IHIDB].[dbo].[lmsCourseLessonMap] [LPA_C2]  ON  [LPA_C1].[CourseGUID]=[LPA_C2].[CourseGUID]) INNER JOIN [LOCAL_IHIDB].[dbo].[lmsLesson]  ON  [LOCAL_IHIDB].[dbo].[lmsLesson].[LessonGUID]=[LPA_C2].[LessonGUID]) INNER JOIN [LOCAL_IHIDB].[dbo].[lmsCourseLessonMap]  ON  [LOCAL_IHIDB].[dbo].[lmsLesson].[LessonGUID]=[LOCAL_IHIDB].[dbo].[lmsCourseLessonMap].[LessonGUID]) WHERE ( ( [LPA_C2].[CourseGUID] IN (@CourseGuid1, @CourseGuid2, @CourseGuid3, @CourseGuid4, @CourseGuid5, @CourseGuid6))) ORDER BY [LOCAL_IHIDB].[dbo].[lmsCourseLessonMap].[DisplayOrder] ASC
    Parameter: @CourseGuid1 : Guid. Length: 0. Precision: 0. Scale: 0. Direction: Input. Value: e32af5d3-1d69-4ff2-8fb4-c64514525b91.
    Parameter: @CourseGuid2 : Guid. Length: 0. Precision: 0. Scale: 0. Direction: Input. Value: 450d0418-f7af-4c7e-9c7e-0301ad79ddf0.
    Parameter: @CourseGuid3 : Guid. Length: 0. Precision: 0. Scale: 0. Direction: Input. Value: c098cfbb-daa9-4d69-bb0b-dea7963a7f2a.
    Parameter: @CourseGuid4 : Guid. Length: 0. Precision: 0. Scale: 0. Direction: Input. Value: 61fd1bbe-fccb-49ba-aa13-3dcf397871d9.
    Parameter: @CourseGuid5 : Guid. Length: 0. Precision: 0. Scale: 0. Direction: Input. Value: 6e6407ca-11aa-4ba8-b5f4-9dcf691ac93c.
    Parameter: @CourseGuid6 : Guid. Length: 0. Precision: 0. Scale: 0. Direction: Input. Value: cbc2cf0d-7336-40e0-b9b0-4fee500b7cdc.

Method Exit: CreateSelectDQ
Method Exit: CreatePagingSelectDQ: no paging.
Method Enter: CreatePagingSelectDQ
Method Enter: CreateSelectDQ
Method Enter: CreateSelectDQ
Generated Sql query: 
    Query: SELECT DISTINCT [LPA_C1].[CourseGUID] AS [CourseGuid0], [LOCAL_IHIDB].[dbo].[lmsLesson].[LessonGUID] AS [LessonGuid1] FROM ((( [LOCAL_IHIDB].[dbo].[lmsCourse] [LPA_C1]  INNER JOIN [LOCAL_IHIDB].[dbo].[lmsCourseLessonMap] [LPA_C2]  ON  [LPA_C1].[CourseGUID]=[LPA_C2].[CourseGUID]) INNER JOIN [LOCAL_IHIDB].[dbo].[lmsLesson]  ON  [LOCAL_IHIDB].[dbo].[lmsLesson].[LessonGUID]=[LPA_C2].[LessonGUID]) INNER JOIN [LOCAL_IHIDB].[dbo].[lmsCourseLessonMap]  ON  [LOCAL_IHIDB].[dbo].[lmsLesson].[LessonGUID]=[LOCAL_IHIDB].[dbo].[lmsCourseLessonMap].[LessonGUID]) WHERE ( ( ( [LPA_C2].[CourseGUID] IN (@CourseGuid4, @CourseGuid5, @CourseGuid6, @CourseGuid7, @CourseGuid8, @CourseGuid9))))
    Parameter: @CourseGuid4 : Guid. Length: 0. Precision: 0. Scale: 0. Direction: Input. Value: e32af5d3-1d69-4ff2-8fb4-c64514525b91.
    Parameter: @CourseGuid5 : Guid. Length: 0. Precision: 0. Scale: 0. Direction: Input. Value: 450d0418-f7af-4c7e-9c7e-0301ad79ddf0.
    Parameter: @CourseGuid6 : Guid. Length: 0. Precision: 0. Scale: 0. Direction: Input. Value: c098cfbb-daa9-4d69-bb0b-dea7963a7f2a.
    Parameter: @CourseGuid7 : Guid. Length: 0. Precision: 0. Scale: 0. Direction: Input. Value: 61fd1bbe-fccb-49ba-aa13-3dcf397871d9.
    Parameter: @CourseGuid8 : Guid. Length: 0. Precision: 0. Scale: 0. Direction: Input. Value: 6e6407ca-11aa-4ba8-b5f4-9dcf691ac93c.
    Parameter: @CourseGuid9 : Guid. Length: 0. Precision: 0. Scale: 0. Direction: Input. Value: cbc2cf0d-7336-40e0-b9b0-4fee500b7cdc.

Method Exit: CreateSelectDQ
Method Exit: CreatePagingSelectDQ: no paging.
Method Enter: CreatePagingSelectDQ
Method Enter: CreateSelectDQ
Method Enter: CreateSelectDQ
Generated Sql query: 
    Query: SELECT [LOCAL_IHIDB].[dbo].[lmsTopic].[TopicGUID] AS [TopicGuid], [LOCAL_IHIDB].[dbo].[lmsTopic].[Name], [LOCAL_IHIDB].[dbo].[lmsTopic].[Abbreviation] FROM [LOCAL_IHIDB].[dbo].[lmsTopic]  WHERE ( ( [LOCAL_IHIDB].[dbo].[lmsTopic].[TopicGUID] IN (@TopicGuid1, @TopicGuid2, @TopicGuid3, @TopicGuid4)))
    Parameter: @TopicGuid1 : Guid. Length: 0. Precision: 0. Scale: 0. Direction: Input. Value: c63e379e-6c4b-4af8-adb4-f3f8b1213ca0.
    Parameter: @TopicGuid2 : Guid. Length: 0. Precision: 0. Scale: 0. Direction: Input. Value: dc1d16b9-e5ea-40f0-bf24-9c2a74cde6a3.
    Parameter: @TopicGuid3 : Guid. Length: 0. Precision: 0. Scale: 0. Direction: Input. Value: 7f0e2890-d0f3-4f2d-8f28-e51a4359b4fa.
    Parameter: @TopicGuid4 : Guid. Length: 0. Precision: 0. Scale: 0. Direction: Input. Value: 4449e5b8-16c3-4f6f-8958-a4ed44e21798.

Method Exit: CreateSelectDQ
Method Exit: CreatePagingSelectDQ: no paging.

If you note, the order by clause does appear to be generated in the third query from the bottom. But the second query from the bottom appears to perform a very similar query without the order by clause, so I assuming that that is causing the results to be replaced.

Any suggestions on how to remove this duplicate query? Or insure that it is properly sorted?

Please try the following code without adding relations.

string alias = "ANY_ALIAS"; //"LPA_C2";

ISortExpression lessonSorter = new SortExpression(CourseLessonMapFields.DisplayOrder.SetObjectAlias(alias) | SortOperator.Ascending);

IPrefetchPath coursePrefetch = new PrefetchPath(EntityType.CourseEntity);
IPrefetchPathElement lessonElement = coursePrefetch.Add(CourseEntity.PrefetchPathLessons, 0, null, null, lessonSorter);

CourseCollection courses = new CourseCollection();
courses.GetMulti(null, coursePrefetch);

ref: http://www.llblgen.com/TinyForum/Messages.aspx?ThreadID=5968