Hi - Having spent a couple of hours on this, I would be hugely grateful for any help you guys may have! I need to convert the following SQL:

select distinct vacancy.* from vacancy
LEFT OUTER JOIN dbo.[Vacancy_Option] ON ([Vacancy_Option].VacancyId = vacancy.VacancyId AND [Vacancy_Option].OptionID IN (SELECT OptionId FROM [Option] WHERE OptionTypeId='xxxxx') ) where vacancy.vacancystatusid = 1 and ([Vacancy_Option].optionid is null or [Vacancy_Option].optionid = 2)

SO far I have:

            RelationCollection _relations = new RelationCollection();
            _relations.Add(VacancyEntity.Relations.VacancyOptionEntityUsingVacancyId, JoinHint.Left).CustomFilter.Add(....;
            PredicateExpression _filter = new PredicateExpression((VacancyOptionFields.OptionId == _oe.OptionId) | (VacancyOptionFields.OptionId == DBNull.Value));
            _filter.Add(VacancyFields.VacancyStatusId == 1);
            VacancyCollection _vc = new VacancyCollection();
            _vc.GetMulti(_filter, _relations);

Essentially I got very stuck on what CustomFilter I need to use..

Many, many thanks!

The Custom filter on relations should be set this way:

IPredicateExpression customFilter = new PredicateExpression();
customFilter.Add(new FieldCompareSetPredicate(
        VacancyOptionFields.OptionId, OptionFields.OptionId , SetOperator.In,
        OptionFields.OptionTypeId == "xxxxx"));

RelationCollection _relations = new RelationCollection();
_relations.Add(VacancyEntity.Relations.VacancyOptionEntityUsingVacancyId, JoinHint.Left)
     .CustomFilter = customFilter;
...