Hej hej,

I am a newbie @LLBLGEN, and I am trying to fetch the entity with the last date excecuted (I'have a column lastExcecuted)

I've tried this: but got an out of sync exception

 IPredicate filter = (ImportTemplateFields.LastExecuted == ImportTemplateFields.LastExecuted.SetAggregateFunction(AggregateFunction.Max));
PrefetchPath2 pref = new PrefetchPath2(EntityType.ImportTemplateEntity);
pref.Add(ImportTemplateEntity.PrefetchPathImportColumns).Filter.Add(filter);

  EntityCollection<ImportTemplateEntity> impTemps = new EntityCollection<ImportTemplateEntity>();
         DataAdapter.FetchEntityCollection(impTemps,null,pref);

         if (impTemps[0] != null)
            .... Do something

And I tried this.

IRelationPredicateBucket bucket = new RelationPredicateBucket();
bucket.PredicateExpression(ImportTemplateFields.LastExecuted == ImportTemplateFields.LastExecuted.SetAggregateFunction(AggregateFunction.Max));

But that does not work.

How do I define that I want the entity with the MAX date?

Greetz,

Jonathan

Do you mean you want to replicate a query like

SELECT TOP 1 * FROM TemplateEntity ORDER BY LastExecuted DESC

?

You dont need any prefetch paths or filters to do this...

ISortExpression sorter = new SortExpression();
sorter.Add(TemplateEntity.Last Executed | SortOperator.Descending);

EntityCollection<ImportTemplateEntity> impTemps = new EntityCollection<ImportTemplateEntity>();
DataAdapter.FetchEntityCollection(impTemps,null,sorter);

(from memory, you'll need to check the exact overload to make sure I've got the sorter in the right place)

If not this, try posting the SQL you that replicates the query that you want to run - it makes working out how to do it much easier.

BTW - "does not work" does not tell us a lot. Exceptions, generated sql, expected results are much more useful...

Matt

hej, thank you for you're answer.

with does not work I mean that the debugger complains.

I am not really looking for a list descending because my DB table is quite big and I do not want to load the whole table.

I am looking for something like this

Select *
from SomeTable
where Date = 
  ( select max(date)
     from SomeTable)
  )

Greetz Jonathan

Option 1: Use a ScalarQueryExpression to implement the inner sub-query:

IExpression rightOperand = new ScalarQueryExpression(SomeTableFields.Date.SetAggregateFunction( AggregateFunction.Max), someFilter); 

Then use the above ScalarQueryExpression in a FieldCompareExpressionPredicate:

IPredicate filter = FieldCompareExpressionPredicate(SomeTableFields.Date, null, ExOp.Equal, rightOperand); 

Option 2: Use a FieldCompareSetPredicate to implement an IN clause.

SELECT * FROM SomeTable
WHERE Date IN (SELECT MAX(st.Date) FROM SomeTable st WHERE ....)