I'm just going to keep asking questions until I get answers I guess.
(1) How do I alias the "root" entity in this query. I need to alias Employees, but when I do this:
IRelationPredicateBucket filter = new RelationPredicateBucket(); filter.Relations.Add(EmployeesEntity.Relations.EmployeeToDepartmentsEntityUsingEmployeeId, ,"e","f",JoinHint.Left);
I get an error of the type: "Relation at index 1 doesn't contain an entity already added to the FROM clause. Bad alias?" I don't really want to alias it this way (I don't need the "f" as far as I can tell from my SQL, but I can't figure out any other way).
I have looked at the documentation for aliases and all of the examples show how to alias the right-hand-side of a join like this; none show how to alias the root entity.
(2) The subquery syntax doesn't make any sense to me. I have this:
subQuery.Add(EmployeeToDepartmentsFields.EffectiveDate <= DateTime.Now);
subQuery.AddWithAnd(new FieldCompareValuePredicate(EmployeeToDepartmentsFields.EmployeeId, null, ComparisonOperator.Equal, EmployeesFields.EmployeeId, "e"));
And this (that I got from another thread here)
IEntityField2 field1 = EntityFieldFactory.Create(EmployeeToDepartmentsFieldIndex.EffectiveDate);
IEntityField2 field2 = EntityFieldFactory.Create(EmployeeToDepartmentsFieldIndex.EffectiveDate);
field2.AggregateFunctionToApply = AggregateFunction.Max;
IPredicate predicate1 = new FieldCompareSetPredicate(field1, null, field2, null, SetOperator.Equal, subQuery);
filter.PredicateExpression.Add(predicate1);
This doesn't make any sense. My subquery only needs to refer to "Employees e".
So far for my query I have this, and it doesn't work:
IPredicateExpression subQuery = new PredicateExpression();
subQuery.Add(EmployeeToDepartmentsFields.EffectiveDate <= DateTime.Now);
subQuery.AddWithAnd(new FieldCompareValuePredicate(EmployeeToDepartmentsFields.EmployeeId, null, ComparisonOperator.Equal, EmployeesFields.EmployeeId, "e"));
IRelationPredicateBucket filter = new RelationPredicateBucket();
filter.Relations.Add(EmployeesEntity.Relations.EmployeeToDepartmentsEntityUsingEmployeeId, JoinHint.Left);
filter.Relations.Add(EmployeeToDepartmentsEntity.Relations.DepartmentsEntityUsingDepartmentId, JoinHint.Left);
IEntityField2 field1 = EntityFieldFactory.Create(EmployeeToDepartmentsFieldIndex.EffectiveDate);
IEntityField2 field2 = EntityFieldFactory.Create(EmployeeToDepartmentsFieldIndex.EffectiveDate);
field2.AggregateFunctionToApply = AggregateFunction.Max;
IPredicate predicate1 = new FieldCompareSetPredicate(field1, null, field2, null, SetOperator.Equal, subQuery);
filter.PredicateExpression.Add(predicate1);
There has to be a way to make this query work, right? If I can do it in SQL I ought to be able to do it with LLBL.