I am aware of "FieldCompareSetPredicate" and have found it useful. However please explain the best practice of accomplishing the following:

Tables:

Company CompanyEmployeeRel (CompanyId, EmployeeId) Employee (has a CountryId column) Country

How would I select only companies that meet these conditions: - All companies with more than 5 employees - All Employees are from a specific country

Using prefetch paths, so we can walk through the heirarchy after the select.

Thank you

Hi rracer99,

This is an approximate code of what you want to do:

EntityCollection<CompanyEntity> companies = new EntityCollection<CompanyEntity>(new CompanyEntityFactory());

IRelationPredicateBucket filter = new RelationPredicateBucket();


// number of employees > 5
ScalarQueryExpression numberOfEmployees = new ScalarQueryExpression(    
    CompanyEmployeeRelFields.EmployeeId.SetAggregateFunction(AggregateFunction.Count),
    CompanyEmployeeRelFields.CompanyId == CompanyFields.CompanyId);

filter.PredicateExpression.Add(CompanyEmployeeRelFields.EmployeeId.SetExpression(numberOfEmployees) > 5);


// employees from certain city
filter.Relations.Add(CompnayEntity.Relations.CompanyEmployeeRelEntityUsingCompanyId);
filter.Relations.Add(CompanyEmployeeRelEntity.Relations.EmployeeEntityUsingEmployeeId);
filter.Relations.Add(EmployeeEntity.Relations.CountryEntityUsingCountryId);

filter.PredicateExpression.Add(CountryFields.Name == "Colombia");


// prefetch path
IPrefetchPath2 path = new PrefetchPath2((int)EntityType.ComapnyEntity);
path.Add(CompanyEntity.PrefetchPathCompanyEmployeeRel)
    .SubPath.Add(CompanyEmployeeRelEntity.PrefetchPathEmployee)
        .SubPath.Add(EmployeeEntity.PrefetchPathContry);


// fetch
using (DataAccessAdapter adapter = new DataAccessAdapter())
{
    adapter.FetchEntityCollection(companies, filter, path);
}