I am using the Adapter code. I am trying to write code to return an EntityCollection of SectionEntity objects. I have the following SQL:

SELECT * FROM [Section] t1 WHERE NOT EXISTS ( SELECT * FROM [Section] WHERE WorkflowID = t1.WorkflowID AND RevisionNumber > t1.RevisionNumber )

I cannot figure out how to alias the Section table in the FROM so that I can reference it in the EXISTS query.

Any help would be appreciated.

Alex

alexcng wrote:

I am using the Adapter code. I am trying to write code to return an EntityCollection of SectionEntity objects. I have the following SQL:

SELECT * 
FROM [Section] t1
WHERE NOT EXISTS
(
    SELECT *
    FROM [Section]
    WHERE WorkflowID = t1.WorkflowID
    AND RevisionNumber > t1.RevisionNumber
)

I cannot figure out how to alias the Section table in the FROM so that I can reference it in the EXISTS query.

Tricky. The thing is, you use the same table for both queries and you want to fetch objects from the table, which means you don't specify any relation nor fields for the fetch, which thus leaves you no oppertunity to specify an alias.

I've puzzled for a while with your query, and I could rewrite it as follows:

SELECT  S1.*
FROM    [Section] S1 LEFT JOIN [Section] S2
    ON 
    S1.WorkflowID = S2.WorkflowID
    AND S1.RevisionNumber > S2.RevisionNumber
WHERE   S2.SectionID IS NULL

This you can create as follows:

// create relation object for order join.
EntityRelation sectionRelation = new EntityRelation(RelationType.OneToOne);
sectionRelation.StartEntityIsPkSide = true;
sectionRelation.AddEntityFieldPair(EntityFieldFactory.Create(SectionFieldIndex.WorkflowID), 
    EntityFieldFactory.Create(SectionFieldIndex.WorkflowID));

// Create S1.RevisionNumber > S2.RevisionNumber filter
IPredicateExpression revisionFilter = new PredicateExpression();
EntityField2 s2RevisionField = EntityFieldFactory.Create(SectionFieldIndex.RevisionNumber);
s2RevisionField.ObjectAlias = "S2";
revisionFilter.Add(PredicateFactory.CompareExpression(
    SectionFieldIndex.RevisionNumber, ComparisonOperator.GreaterThan, 
    new Expression(s2RevisionField), "S1"));

// add the relation to join to the relation collection, specify the aliasses and the left join hint.
RelationPredicateBucket filter = new RelationPredicateBucket();
filter.RelationCollection.Add(sectionRelation, "S1", "S2", JoinHint.Left).CustomFilter = revisionFilter;

// specify the IS NULL predicate
filter.PredicateExpression.Add(
    PredicateFactory.CompareNull(SectionFieldIndex.SectionID, "S2"));

// fetch.
EntityCollection sections = new EntityCollection(new SectionEntityFactory());
adapter.FetchEntityCollection(sections, filter);

I admit this is less efficient. You can also solve it by inheriting a class from DataAccessAdapter and in there, override CreateSelectDQ.

In that override check for a boolean flag you set/define yourself. If the flag isn't set, simply call the base method and return what you get from the base' method.

If the flag is set, before you call the base method, set fieldsToFetch[0].ObjectAlias to "t1", and return the query. This will make the alias appear in the FROM clause. You of course have to build the subquery's filter with the "t1" alias.

Thank you for your quick response.

I was actually able to solve the problem in a different manner. I rewrote the query to the following:

select t1.* from section t1 where t1.revisionnumber = ( select max(t2.revisionnumber) from section t2 where revisionnumber <= @revnumber and t2.workflowid = t1.workflowid )

with the following code:

// produce the filter on the item entities RelationPredicateBucket filter = new RelationPredicateBucket();

IPredicateExpression subQuery = new PredicateExpression(); subQuery.Add(PredicateFactory.CompareExpression(SectionFieldIndex.WorkflowId, ComparisonOperator.Equal, new Expression(EntityFieldFactory.Create(SectionFieldIndex.WorkflowId)), "t1")); subQuery.Add(PredicateFactory.CompareValue(SectionFieldIndex.RevisionNumber, ComparisonOperator.LessEqual, revisionNumber));

IEntityField2 field1 = EntityFieldFactory.Create(SectionFieldIndex.RevisionNumber); IEntityField2 field2 = EntityFieldFactory.Create(SectionFieldIndex.RevisionNumber); field2.ObjectAlias = "t1"; field2.AggregateFunctionToApply = AggregateFunction.Max;

IPredicate predicate1 = new FieldCompareSetPredicate(field1, null, field2, null, SetOperator.Equal, subQuery);

filter.PredicateExpression.Add(predicate1);

adapter.FetchEntityCollection(sectionCollection, filter);

It seems to work pretty well,

Thanks Alex

alexcng wrote:

Thank you for your quick response.

I was actually able to solve the problem in a different manner. I rewrote the query to the following:

select t1.* from section t1 where t1.revisionnumber = ( select max(t2.revisionnumber) from section t2 where revisionnumber <= @revnumber and t2.workflowid = t1.workflowid )

with the following code:

// produce the filter on the item entities RelationPredicateBucket filter = new RelationPredicateBucket();

IPredicateExpression subQuery = new PredicateExpression(); subQuery.Add(PredicateFactory.CompareExpression(SectionFieldIndex.WorkflowId, ComparisonOperator.Equal, new Expression(EntityFieldFactory.Create(SectionFieldIndex.WorkflowId)), "t1")); subQuery.Add(PredicateFactory.CompareValue(SectionFieldIndex.RevisionNumber, ComparisonOperator.LessEqual, revisionNumber));

IEntityField2 field1 = EntityFieldFactory.Create(SectionFieldIndex.RevisionNumber); IEntityField2 field2 = EntityFieldFactory.Create(SectionFieldIndex.RevisionNumber); field2.ObjectAlias = "t1";

Shouldn't that be 't2' ?

field2.AggregateFunctionToApply = AggregateFunction.Max;

IPredicate predicate1 = new FieldCompareSetPredicate(field1, null, field2, null, SetOperator.Equal, subQuery);

filter.PredicateExpression.Add(predicate1);

adapter.FetchEntityCollection(sectionCollection, filter); It seems to work pretty well,

Ok! I though would think the query would not work, as the filter you created will emit 't1' as the alias for the RevisionNumber in the WHERE clause, but there's no table aliassing in the FROM clause by default (and none specified).

(edit) Though I now learned that it works, if you specify the table in front of the field so: this won't work:

select  *
from    orders
where   orderdate = 
(
    select  max(o.orderdate)
    from    orders o 
    where   o.customerid = customerid
)
order by customerid

but this will

select  *
from    orders
where   orderdate = 
(
    select  max(o.orderdate)
    from    orders o 
    where   o.customerid = orders.customerid
)
order by customerid

So your query will become in SQL (I think)

SELECT *
FROM Section
WHERE Section.RevisionNumber =
(
select max(t1.revisionnumber)
from section t1
where t1.revisionnumber <= @revisionnumber
and section.workflowid = t1.workflowid
)

which indeed works