Hi,

I have 4 tables in my database:

1 - ProjectFile 2 - DocumentType 3 - DocumentCategory 4 - ProjectFileVersions

ProjectFile has a relation to DocumentType, which has a relation to DocumentCategory.

Im trying to get my data organized that I can get all ProjectFiles for a specific project and sorted on:

• Document Category Identifier (this can range A - M) then within those there will be multiple types which i also want to sort on
• Document Type Identifier (this can range A01 - A07)

then also I want to sort the various FileVersions sorted on their Version.

I came up with the following code:

        public static ProjectFileCollection GetFilesForOverview(Guid ProjectID)
        {
            ProjectFileCollection ProjectFiles = new ProjectFileCollection();

            // Define sorters
            ISortExpression DocumentCategorySorter = new SortExpression();
            DocumentCategorySorter.Add(DocumentCategoryFields.Identifier | SortOperator.Ascending);

            ISortExpression DocumentTypeSorter = new SortExpression();
            DocumentTypeSorter.Add(DocumentTypeFields.Identifier | SortOperator.Ascending);

            ISortExpression FileVersionSorter = new SortExpression();
            FileVersionSorter.Add(ProjectFileVersionFields.FileVersion | SortOperator.Descending);

            IPrefetchPath prefetchPath = new PrefetchPath((int)EntityType.ProjectFileEntity);
            
            prefetchPath.Add(ProjectFileEntity.PrefetchPathDocumentType, 0, null, null, DocumentTypeSorter).SubPath.Add(DocumentTypeEntity.PrefetchPathDocumentCategory, 0, null, null, DocumentCategorySorter);
            prefetchPath.Add(ProjectFileEntity.PrefetchPathProjectFileVersion, 0, null, null, FileVersionSorter);

            IPredicateExpression filter = new PredicateExpression();
            filter.Add(ProjectFileFields.ProjectId == ProjectID);

            ProjectFiles.GetMulti(filter, prefetchPath);

            return ProjectFiles;

The Version sorting goes well, except the DocumentType sort doesnt work? Right now values like:

-A01 -A03 -A02

Am I missing something?

Best Regards, Martin

Hi Martin,

Your code looks ok. Could you please post the LLBLGen Runtime Library version (or better, update to the latest build and try again) and the SQL generated by the call?

Also, What type are the realtions (1:1, 1:n, m:1)? and Are some type of inheritance involved?

Hi David,

I'm using the latest demo, 2.6

The relations are

Proejct File -> DocumentType (m:1) DocumentType -> DocumentCategory (m:1). ProjectFile -> ProjectFileVersions (1:n).

I see 3 seperate SQL commands going to the access database, and the SQL statements look fine as far as I can see. I've been thinking about this, but I think I should use a TypedList or so? Since every entity has only 1 DocumentCategory (ViaVia), so there's no way for the framework to sort all my entities based on that relation.

If I would make a typed view I guess I could sort better on the relations?

Best Regards, Martin.

Im trying to get my data organized that I can get all ProjectFiles for a specific project and sorted on:

• Document Category Identifier (this can range A - M) then within those there will be multiple types which i also want to sort on
• Document Type Identifier (this can range A01 - A07)

then also I want to sort the various FileVersions sorted on their Version.

I understood that you want to sort the fetched ProjectFiles according to the above criteria. If that's the case, then you should create on SortExpression and pass it to the GetMulti() method, and you'd also need to pass a RelationCollection including relations from the ProjectFileEntity to various entities used in the SortExpression, And then there is no need for the prefetchPaths, if you don't want to fetch these related entities.

        public static ProjectFileCollection GetFilesForOverview(Guid ProjectID)
        {
            ProjectFileCollection ProjectFiles = new ProjectFileCollection();

            // Define sorters
            ISortExpression sorter = new SortExpression();
            sorter.Add(DocumentTypeFields.Identifier | SortOperator.Ascending);
            sorter.Add(DocumentCategoryFields.Identifier | SortOperator.Ascending);
            sorter.Add(ProjectFileVersionFields.FileVersion | SortOperator.Descending);

            RelationCollection relations = new RelationCollection();
            relations.Add(ProjectFileEntity.Relations.DocumentTypeEntityUsing...);
            relations.Add(DocumentTypeEntity.Relations.DocumentCategoryEntityUsing...);
            relations.Add(ProjectFileEntity.Relations.ProjectFileVersionEntityUsing...);

            IPredicateExpression filter = new PredicateExpression();
            filter.Add(ProjectFileFields.ProjectId == ProjectID);

            ProjectFiles.GetMulti(filter, 0, sorter, relations);

            return ProjectFiles
        }