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?

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

First of all why are you using a left join? My guess is that it should be an inner join. Since you want to order on a field in this table.

Anyway, your sql query doesn't match the code you have posted. You should pass a RelationPredicateBuckate containing the PredicateExpression and the RelationCollection you have to the FetchEntityCollection() to implement the filters you have in the Where clause.

And then you won't need the ScalarQueryExpression. All you will need is a simple SortExpression.

var sorter = new SortExpression(IdentificationFields.Value | SortOperator.Descending);

Ok, so I followed you tips and did as following:

EntityCollection<T> entities = new EntityCollection<T>();

IPrefetchPath2 path = new PrefetchPath2(EntityType.EmployeeEntity);
path.Add(EmployeeEntity.PrefetchPathIdentifications);

IRelationPredicateBucket bucket = new RelationPredicateBucket();            
bucket.Relations.Add(EmployeeEntity.Relations.IdentificationEntityUsingResourceId, JoinHint.Inner);         
bucket.PredicateExpression.Add(IdentificationFields.ValidFrom <= DateTime.Now);         
bucket.PredicateExpression.Add(new FieldCompareNullPredicate(IdentificationFields.ValidTo, null) | IdentificationFields.ValidTo >= DateTime.Now);

IExpression castValueExpression = new DbFunctionCall("CAST((CASE WHEN ISNUMERIC({0})=0 THEN 0 ELSE {0} END) as INT)", new object[] { IdentificationFields.Value });
            
EntityField2 identificationField = IdentificationFields.Value.SetExpression(castValueExpression);           
            
ISortClause sortClause = new SortClause(identificationField, null, SortOperator.Descending);            
sortClause.EmitAliasForExpressionAggregateField = false;            
            
ISortExpression sorter = new SortExpression(sortClause);

using (IDataAccessAdapter adapter = DataAccessFactory.GetAdapter())
    adapter.FetchEntityCollection(entities, filter, 0, sorter, path);

The problem is that code above gives me only employees that has some 'active identifications'. I need to get all employees. Also these that don't have 'active identification' (so it would be NULL value).

I can achieve that by following sql query:

select r.*
from core.Resources r
left join (select i.ResourceId, i.Value from core.Identifications i where i.ValidFrom <= GETDATE() and (i.ValidTo is null or i.ValidTo >= GETDATE())) s on r.ResourceID = s.ResourceID 
where r.Discriminator = 'Employee'
order by CAST((CASE WHEN ISNUMERIC(s.Value)=0 THEN 0 ELSE s.Value END) as INT) desc

Is it possible to gain my goal by modifying code above or I need to use some other mechanism.? Should I consider DynamicRelation?

Does your first SQL Query also return the appropriate results?

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

... coz maybe you are complicating things in your last SQL. If you want to use your last sql then yes, you have to use a DynamicRelation.

Nope, the first query does not give proper result(there are no employees with NULL 'active identification'). Can you give me some tip how to use DynamicRelation for the second query?

The problem is that code above gives me only employees that has some 'active identifications'. I need to get all employees. Also these that don't have 'active identification' (so it would be NULL value).

Ok, so ignore the first paragraph I previously wrote and use a LeftJoin (JoinHint.Left). But keep the rest of the code as you have modified it.

Hi Walaa,

I already tried that and it does not work as I need. It turned out that my first sql query wasn't proper. That's why I changed sql query, but I don't quite know how to use DynamicRelation for that.

I think for your second sql query you will need to use a DerivedTableDefinition to generate the inner select. Have a look at the documentation here...

http://www.llblgen.com/documentation/3.1/LLBLGen%20Pro%20RTF/Using%20the%20generated%20code/gencode_derivedtabledynamicrelation.htm#DerivedTableDefinition

Matt

Problem solved. Code below:

EntityCollection<EmployeeEntity> entities = new EntityCollection<EmployeeEntity>();

ResultsetFields identificationFields = new ResultsetFields(4);
identificationFields.DefineField(IdentificationFields.ResourceId, 0);
identificationFields.DefineField(IdentificationFields.Value, 1);
identificationFields.DefineField(IdentificationFields.ValidFrom, 2);
identificationFields.DefineField(IdentificationFields.ValidTo, 3);

IPredicateExpression identificationsPredicate = new PredicateExpression(IdentificationFields.ValidFrom <= DateTime.Now);
identificationsPredicate.Add(new FieldCompareNullPredicate(IdentificationFields.ValidTo, null) | IdentificationFields.ValidTo >= DateTime.Now);

DerivedTableDefinition identificationsDefinition = new DerivedTableDefinition(identificationFields, "identifications", identificationsPredicate);

IPredicate joinPredicate = new EntityField2(IdentificationFieldIndex.ResourceId.ToString(), "identifications", typeof(int)) == EmployeeFields.Id.SetObjectAlias("resources");

DynamicRelation relationIdentifications = new DynamicRelation(identificationsDefinition, JoinHint.Right, EntityType.EmployeeEntity, "resources", joinPredicate);

IRelationPredicateBucket bucket = new RelationPredicateBucket();
bucket.Relations.Add(relationIdentifications);
bucket.SelectListAlias = "resources";

IExpression castValueExpression = new DbFunctionCall("CAST((CASE WHEN ISNUMERIC({0})=0 THEN 0 ELSE {0} END) as INT)",new object[] { IdentificationFields.Value.SetObjectAlias("identifications") });
EntityField2 fieldToSort = IdentificationFields.Value.SetExpression(castValueExpression).SetFieldAlias("identifications");

ISortClause sortClause = new SortClause(fieldToSort, null, SortOperator.Descending);
sortClause.EmitAliasForExpressionAggregateField = false;
ISortExpression sorter = new SortExpression(sortClause);

IPrefetchPath2 path = new PrefetchPath2(EntityType.EmployeeEntity);
path.Add(EmployeeEntity.PrefetchPathIdentifications);

using (IDataAccessAdapter adapter = DataAccessFactory.GetAdapter())
    adapter.FetchEntityCollection(entities, bucket, 0, sorter, path);

Thanks for help guys.