I have two tables:
Resources
ResourceId (int)
LastName (nvarchar(40)
FirstName (nvarchar(40)
Discriminator (nvarchar(50, not null)
Identifications
ResourceId (int, not null)
ValidFrom (DateTime, not null)
ValidTo (DateTime)
Value (nvarchar(max), not null)
What I want to achieve is to fetch records from Resources table ordered by active identification.
By active identification I mean the first one linked to resource which ValidFrom <= GETDATE() and (ValidTo>= GETDATE() or ValidTo is NULL).
Using SQL query it would be:
select r.ResourceID,r.LastName, r.FirstName
from core.Resources r
left join core.Identifications id on r.ResourceID = id.ResourceID
where id.ValidFrom <= GETDATE() and (id.ValidTo>= GETDATE() or id.ValidTo is null)
and r.Discriminator = 'Employee'
order by id.Value desc
What I tried to do was:
EntityCollection<EmployeeEntity> entities = new EntityCollection<EmployeeEntity>();
IPrefetchPath2 path = new PrefetchPath2(EntityType.EmployeeEntity);
path.Add(EmployeeEntity.PrefetchPathIdentifications);
PredicateExpression identificationsPredicate = new PredicateExpression();
identificationsPredicate.Add(IdentificationFields.ValidFrom <= DateTime.Now);
identificationsPredicate.Add(new FieldCompareNullPredicate(IdentificationFields.ValidTo, null) | IdentificationFields.ValidTo >= DateTime.Now);
IRelationCollection relations = new RelationCollection();
IEntityRelation relation = EmployeeEntity.Relations.IdentificationEntityUsingResourceId;
relations.Add(relation, JoinHint.Left);
ScalarQueryExpression expression = new ScalarQueryExpression(IdentificationFields.Value, identificationsPredicate, relations, true);
EntityField2 sortField = new EntityField2("sortField", expression);
ISortClause sortclause = new SortClause(sortField, null, SortOperator.Descending);
sortclause.EmitAliasForExpressionAggregateField = false;
ISortExpression sorter = new SortExpression(sortclause);
using (IDataAccessAdapter adapter = DataAccessFactory.GetAdapter())
adapter.FetchEntityCollection(entities, null, 0,sorter,path);
But unfortunately it doesn’t give me employees sorted on ‘active identification’. What am I doing wrong? Is ScalarQueryExpression the right solution for that?