I have a PatientDocument which has a FK to a DocumentEntity.

I want to fetch a filtered collection of PatientDocuments, and in this result I want to order them by a property of the DocumentEntity.

I prefer a single query to be generated, but unfortunately I can't get it working.

While settling for less, I tried to fetch all the entities and do the sorting in-memory.

this is one of my attempts:

PatientDocumentCollection patientDocuments = new PatientDocumentCollection();
            IPredicateExpression filter = new PredicateExpression(PatientDocumentFields.FolderID == 1);         
RelationCollection relations = new RelationCollection();
             relations.Add(PatientDocumentEntity.Relations.DocumentEntityUsingDocumentID);

IPrefetchPath prefetchPath = new PrefetchPath(EntityType.PatientDocumentEntity);
            prefetchPath.Add(PatientDocumentEntity.PrefetchPathDocument, 1, null, relations);
            patientDocuments.GetMulti(filter, prefetchPath);

ISortExpression sorter = new SortExpression(new SortClause(DocumentFields.IsDeleted, SortOperator.Descending));
IEntityView entityView = patientDocuments.CreateView(null, sorter);

I use the prefetchPath to get the FK property in the result, to enable sorting, but this doesn't work either... Any tips would be appreciated!

Hi Jowen,

As you don't need the documents to be prefetched (at lest that is what I understand), you don't need a prefetchPath, yo only need to pass the sorter:

// the filter
IPredicateExpression filter = new PredicateExpression(PatientDocumentFields.FolderID == 1);         

// the relation that allows the filter possible
RelationCollection relations = new RelationCollection();
relations.Add(PatientDocumentEntity.Relations.DocumentEntityUsingDocumentID);

// the sorter
ISortExpression sorter = new SortExpression(new SortClause(DocumentFields.IsDeleted, SortOperator.Descending));

// fetch
PatientDocumentCollection patientDocuments = new PatientDocumentCollection();
patientDocuments.GetMulti(filter, 0, sorter);

Now, if you want the PatientDocuments to be prefetched, you also need to pass the prefetchPath, and that will result in a second query.

daelmo wrote:

Hi Jowen,

As you don't need the documents to be prefetched (at lest that is what I understand), you don't need a prefetchPath, yo only need to pass the sorter:

// the filter
IPredicateExpression filter = new PredicateExpression(PatientDocumentFields.FolderID == 1);         

// the relation that allows the filter possible
RelationCollection relations = new RelationCollection();
relations.Add(PatientDocumentEntity.Relations.DocumentEntityUsingDocumentID);

// the sorter
ISortExpression sorter = new SortExpression(new SortClause(DocumentFields.IsDeleted, SortOperator.Descending));

// fetch
PatientDocumentCollection patientDocuments = new PatientDocumentCollection();
patientDocuments.GetMulti(filter, 0, sorter);

Now, if you want the PatientDocuments to be prefetched, you also need to pass the prefetchPath, and that will result in a second query.

Hi daelmo,

tnx for your reply, but unfortunately it doesnt work. I get the error: Unknown column 'document.IsDeleted' in 'order clause'

You're not doing anything with the relations collection, so the field from the documents table is not being included in the query. one of the overloads of GetMuli should accept the relations collection as a parameter.

Matt

MTrinder wrote:

You're not doing anything with the relations collection, so the field from the documents table is not being included in the query. one of the overloads of GetMuli should accept the relations collection as a parameter.

Matt

hmm. It looks too simple to be true, but it works!

I would have sworn that this was one of my first options, but ok.... it works

Tnx!