I am having a devil of a job getting this to work.

I have tried many variations and have got all the various bits to work but never all together. I have provided a cut-down sample below (there are other fields to be included in the projection but they are straightforward)

The basic idea is to create a query that will fetch a List<FileCheckableItemInfo>.

The target is PolicyEntity which is a subtype of AssetEntity. A FileCheckEntity may exist which has a FK pointing to the PolicyEntity. There is an AssetToOwnerEntity table which I need to access as a correlated subquery. As the code below shows, it need to find all of the AssetToOwnerEntities related to the Policy(Asset) in question and extract just the LegalBodyID field. This List<int> is then passed to the NameFormatter method which builds a single string to be returned in the projection. One possible added complication is the parameter passed in. True/False will add a Where clause to the Outer query. (Originally I added this to the final result but wasn't sure whether it has to be applied before the subquery was parsed so it is now in the middle).

        public static DynamicQuery<FileCheckableItemInfo> CreatePolicyCheckableItemInfoQuery(bool? hasFilecheck = null)
        {
            var qf = new QueryFactory();

            // Want an outer query that get Policy (and its supertype of Asset) and left joins with FileCheckEntity
            // (Policy is 0..1: 1 with FileCheckEntity)
            var result = qf.Create()
                           .From(qf.Policy
                           // Policy inherits from Asset so this line not needed?
                                   //.InnerJoin(qf.Asset).On(PolicyFields.ID == AssetFields.ID)
                                   .LeftJoin(PolicyEntity.Relations.FileCheckEntityUsingPolicyID));

            // hasFilecheck always null for this sample, so this code not executed
            if (hasFilecheck != null)
            {
                result = result.Where(new FieldCompareNullPredicate(FileCheckFields.ID, null, hasFilecheck.Value));
            }

            return result.Select(() => new FileCheckableItemInfo
                                       {
                                           FileCheckID = FileCheckFields.ID.ToValue<int?>(),
                                           OwnerNames = NameFormatter.FromLegalBodies(
                                               qf.AssetToOwner
                                                 .CorrelatedOver(AssetToOwnerFields.AssetID == AssetFields.ID)
                                                 .OrderBy(AssetToOwnerFields.LegalBodyID | SortOperator.Ascending)
                                                 .Select(() => AssetToOwnerFields.LegalBodyID.ToValue<int>()).ToResultset()
                                               ).DisplayName,
                                       }
                );
        }

The SQL generated is this which seems to have a constant embedded for some reason!

Retrieval Query:

SELECT
  [FileCheck].[ID],
  1 AS [LLBLV_1] 
FROM
  (( [Asset] [LPA_L1]  
INNER JOIN
  [Policy] [LPA_L2] ON [LPA_L1].[ID] = [LPA_L2].[ID]) 
LEFT JOIN
  [FileCheck] ON [LPA_L2].[ID] = [FileCheck].[PolicyID])
SqlServer query: 

Retrieval Query:
DECLARE @p1 Int; SET @p1=NULL
DECLARE @p2 Int; SET @p2='1'

SELECT
  [AssetOwner].[LegalBodyID],
  [AssetOwner].[AssetID] 
FROM
  [AssetOwner]   
WHERE
  ( ( ( ( [AssetOwner].[AssetID] IN (@p1,
  @p2))))) 
ORDER BY
  [AssetOwner].[LegalBodyID] ASC

I think I have got this working! Hurrah!

I can use (for the simplest case at least), the query below.

I can LeftJoin FileCheck; I get my subquery working correctly so I get my formatted names; and I can add the Where clause at the end rather than in the middle.

I found a couple of important things: 1) If a joined table uses the same field name as the PKs used in the subquery (all "ID" in my case), then an alias must be added otherwise the generated queries are just wrong. 2) Don't assume you can use the fields from an entity implicitly generated by a hierarchy. You can either add another join (.InnerJoin(qf.Asset).On(AssetFields.ID == PolicyFields.ID)) to get AssetFields you can use OR my approach which was to use a CorrelatedOver with a predicate that only uses PolicyFields.ID. This should be OK because Policy.ID is always equal to AssetID anyway and the Policy Fields are not implicit like the AssetFields were.

Query:

        public static DynamicQuery<FileCheckableItemInfo> CreatePolicyCheckableItemInfoQuery(bool? hasFilecheck = null)
        {
            var qf = new QueryFactory();

            var result = qf.Create()
                           .From(qf.Policy
                                   .LeftJoin(PolicyEntity.Relations.FileCheckEntityUsingPolicyID))
                           .Select(() => new FileCheckableItemInfo
                                         {
                                             TargetID = PolicyFields.ID.ToValue<int>(),
                                             CheckableItemType = string.Join("/", "Policy", RefDataSources.PolicyTypes.GetDescription(PolicyFields.PolicyTypeID.ToValue<byte>())),
                                             Identifier = PolicyFields.PolicyNumber.ToValue<string>() ?? PolicyFields.Identifier.ToValue<string>(),
                                             StartDate = PolicyFields.StartDate.ToValue<DateTime?>(),
                                             OwnerNames = NameFormatter.FromLegalBodies(CreatePolicyOwnershipSubquery().ToResultset()).DisplayName,
                                             FileCheckID = FileCheckFields.ID.As("FileCheckID").ToValue<int?>()
                                         }
                );

            if (hasFilecheck != null)
            {
                result = result.Where(new FieldCompareNullPredicate(FileCheckFields.ID, null, hasFilecheck.Value));
            }

            return result;
        }

        static DynamicQuery<int> CreatePolicyOwnershipSubquery()
        {
            var qf = new QueryFactory();

            return qf.AssetToOwner
                     .CorrelatedOver(AssetToOwnerFields.AssetID == PolicyFields.ID)
                     //.CorrelatedOver(AssetToOwnerEntity.Relations.AssetEntityUsingAssetID)
                     .OrderBy(AssetToOwnerFields.LegalBodyID | SortOperator.Ascending)
                     .Select(() => AssetToOwnerFields.LegalBodyID.ToValue<int>());
        }

Generated SQL (with hasFileCheck == false):

SELECT
  [LPA_L2].[ID],
  [LPA_L2].[PolicyTypeID],
  [LPA_L2].[PolicyNumber],
  [LPA_L1].[Identifier],
  [LPA_L1].[StartDate],
  1 AS [LLBLV_1],
  [FileCheck].[ID] AS [FileCheckID] 
FROM
  (( [Asset] [LPA_L1]  
INNER JOIN
  [Policy] [LPA_L2] ON [LPA_L1].[ID] = [LPA_L2].[ID]) 
LEFT JOIN
  [FileCheck] ON [LPA_L2].[ID] = [FileCheck].[PolicyID]) 
WHERE
  ( ( ( ( [FileCheck].[ID] IS NULL))))
--SqlServer query: 

--Retrieval Query:

SELECT
  [AssetOwner].[LegalBodyID],
  [AssetOwner].[AssetID] 
FROM
  [AssetOwner]   
WHERE
  ( ( ( (  EXISTS (
SELECT
  [LPA_L2].[ID] 
FROM
  (( [Asset] [LPA_L1]  
INNER JOIN
  [Policy] [LPA_L2] ON [LPA_L1].[ID] = [LPA_L2].[ID]) 
LEFT JOIN
  [FileCheck] ON [LPA_L2].[ID] = [FileCheck].[PolicyID]) 
WHERE
  ( ( ( ( [FileCheck].[ID] IS NULL))) AND [AssetOwner].[AssetID] = [LPA_L2].[ID])))))) 
ORDER BY
  [AssetOwner].[LegalBodyID] ASC

That looks quite complex. Good you figured it out. Thanks for sharing your solution.

I you think it will be useful to others, please feel free to move it to a public form (I don't know why I posted it Helpdesk anyway)

The constant embedded into the outer query's results is a place holder See it like this: row of outer query: [field1|field2|resultset nested query for this row|field3|...|fieldn]

When the outer query is fetched, the nested query is of course not known as it has to be fetched after that. So to be able to materialize the resultset at slot 2 into the outer object, we place a dummy value there, so the other values are at their right place. Then when the nested query is fetched, we merge the subsets from that resultset into the rows of the outer query and then call the materialization code (which is e.g. the new () { ... } statement ) and everything falls into place