Table: Process Columns: IDProcess (PK), ProcessName

Table: ProcessStateHistory Columns: IDStateHistory (PK), IDProcess (FK), State, CreatedOn

SELECT *
FROM Process P
WHERE (
    SELECT TOP 1 PSH.State 
    FROM ProcessStateHistory PSH 
    WHERE PSH.IDProcess = P.IDProcess 
    ORDER BY PSH.CreatedOn DESC
    ) != 'Completed'

Current code:

ISortExpression processStateHistorySorter = new SortExpression(ProcessStateHistoryFields.CreatedOn | SortOperator.Descending);

IPrefetchPath2 prefetch = new PrefetchPath2(EntityType.ProcessEntity);
prefetch.Add(ProcessEntity.PrefetchPathProcessStateHistorys, 1, null, null, processStateHistorySorter);

var bucket = new RelationPredicateBucket();
bucket.Relations.Add(ProcessStateHistoryEntity.Relations.ProcessEntityUsingIDProcess);
bucket.PredicateExpression.Add(ProcessStateHistoryFields.State != "Completed");

collection = FetchEntityColl<ProcessEntity>(bucket, prefetch);

Hello I have these two tables and I am attempting to filter the [Process]'s where the latest state inserted into [ProcessStateHistory] is not 'Completed'. I am really not sure where to go at this point. This is obviously not right because I am not doing the subquery. This is giving me any [Process] that has a [ProcessStateHistory] record without "Completed".

Any help would be greatly appreciated. Thank you!!!

Hi Daniel,

You could use an ScalarQueryExpression. Here is an example using the Low Level API: https://www.llblgen.com/tinyforum/Thread/20997#118323.

Hope that helps.

With QuerySpec it's rather straightforward:

var qf = new QueryFactory();

var q = qf.Process
        .Where(qf.ProcessStateHistory
                    .CorrelatedOver(ProcessStateHistoryFields.IDProcess.Equal(ProcessFields.IDProcess))
                    .OrderBy(ProcessStateHistoryFields.CreatedOn.Descending())
                    .Select(ProcessStateHistoryFields.State)
                    .ToScalar(forceRowLimit:true)
                    .NotEqual("Completed"))
        .WithPath(ProcessEntity.PrefetchPathProcessStateHistory);

var results = adapter.FetchQuery(q);

It's recommended to use a higher level query system (linq or queryspec) for queries like this as the low level API isn't intuitive for these kind of problems.

Thank you for both of your recommendations! I appreciate the help