Filtering FetchEntityCollection based on relationship collection count

Question

I currently fetch a collection of jobs like this:

jobs = new EntityCollection<JobEntity>(new JobEntityFactory()); 

var bucket = GetJobsBucket(filter);

var tempContext = new Context();
tempContext.Add(jobs);
var sorter = new SortExpression(JobFields.Id | SortOperator.Descending);

adapter.FetchEntityCollection(jobs, bucket, maxCount, sorter, JobListPrefetchPath(false));

filter.TotalMatchesCount = adapter.GetDbCount(new JobEntityFactory().CreateFields(), bucket, null, false);
filter.ReturnedMatchesCount = jobs.Count;

tempContext.Clear();

return jobs;

where bucket contains lots of predicates e.g.

    bucket.Relations.Add(JobEntity.Relations.JobTypeEntityUsingJobTypeFk);
    bucket.Relations.Add(JobTypeEntity.Relations.JobTypeCategoryEntityUsingCategoryFk);   
    var fieldCompareValuePredicate = new FieldCompareValuePredicate(JobTypeCategoryFields.Filter, null,
     ComparisonOperator.Equal, filter.JobCategory) { CaseSensitiveCollation = true };                      
    bucket.PredicateExpression.Add(fieldCompareValuePredicate);

The Job entity has a collection of Attachments (via foreign key)

How can I filter the job list to only select jobs with one or more attachments? I know I could use a in-memory filter (AggregateSetPredicate) via a dynamic view but this will mean I will have to fetch all the jobs in order to get the correct counts, the current fetch has a maximum on the returned count.

Answer 1

Solution is to do this:

    bucket.Relations.Add(JobEntity.Relations.AttachmentEntityUsingJobFk);
FieldCompareSetPredicate filteredAttachments;
filteredAttachments = new FieldCompareSetPredicate(JobFields.Id, null,
               AttachmentFields.JobFk, null,
               SetOperator.In, null);
 bucket.PredicateExpression.Add(filteredAttachments);