I am having trouble how to reference an alias in a sub-query. Here is what I have:

                DocumentLibraryDocumentCollection dlfdc = new DocumentLibraryDocumentCollection();
                IRelationCollection relationsToUse = new RelationCollection();
                IEntityRelation Relation1 = relationsToUse.Add(DocumentLibraryDocumentEntity.Relations.DocumentLibraryFolderDocumentEntityUsingDocumentLibraryDocumentId, "a", "b", null);

                // Create subquery filter
                IPredicateExpression filter2 = new PredicateExpression();
                filter2.Add(DocumentLibraryDocumentFields.DocVersion.SetAggregateFunction(AggregateFunction.Max));
                filter2.AddWithAnd(DocumentLibraryDocumentFields.DocNumber == "a.docnumber");

                // Create main query filter
                IPredicateExpression filter = new PredicateExpression();
                filter.Add(DocumentLibraryFolderDocumentFields.DocumentLibraryFolderId == this.DocumentLibraryFolderId);
                filter.AddWithAnd(new FieldCompareSetPredicate(DocumentLibraryDocumentFields.DocVersion, DocumentLibraryDocumentFields.DocVersion, SetOperator.In, filter2));

                dlfdc.GetMulti(filter, relationsToUse);

I am pretty sure this would work if I knew how to replace the "a.docnumber" part.

Here is the query this should represent:

select distinct a.* from documentlibrarydocument a
    inner join documentlibraryfolderdocument b on
    a.documentlibrarydocumentid = b.documentlibrarydocumentid
where
    a.docversion in
        (
        select max(c.docversion) from documentlibrarydocument c
        where c.docnumber = a.docnumber
        )
           and b.documentlibrarydocumentid = @this.DocumentLibraryFolderId

If I saw another example I bet I could get it..

v.2 vs2005 C#

Hi Matthew. .SetObjectAlias("aliasString") can help you here. I've rewrote your code. I've no tested it, but I think It should work.

// collection to fill
DocumentLibraryDocumentCollection docsCollection = new DocumentLibraryDocumentCollection();

// relations to use
IRelationCollection relationsToUse = new RelationCollection();
relationsToUse.Add(DocumentLibraryDocumentEntity.Relations.DocumentLibraryFolderDocumentEntityUsingDocumentLibraryDocumentId, "docLibrary", "docLibraryFolder", null);

// Create subquery filter
IPredicateExpression subQueryFilter = new PredicateExpression();

// docLibrarySubQuery.DocNumber = docLibrary.DocNumber
subQueryFilter.Add(
    new FieldCompareExpressionPredicate(
        DocumentLibraryDocumentFields.DocNumber.SetObjectAlias("docLibrarySubQuery"),
        ComparisonOperator.Equal,
        new Expression(DocumentLibraryDocumentFields.DocNumber.SetObjectAlias("docLibrary"))));

// Create main query filter
IPredicateExpression filter = new PredicateExpression();

/// docLibrary.DocNumber IN ( select MAX(DocVersion) FROM
/// DocumentLibraryDocument docLibrarySubQuery 
/// WHERE docLibrarySubQuery.DocNumber = docLibrary.DocNumber)
filter.AddWithAnd(
    new FieldCompareSetPredicate(
            DocumentLibraryDocumentFields.DocVersion.SetObjectAlias("docLibrary"), 
            DocumentLibraryDocumentFields.DocVersion
                .SetAggregateFunction(AggregateFunction.Max)
                    .SetObjectAlias("docLibrarySubQuery"), 
            SetOperator.In, 
            subQueryFilter));


/// docLibraryFolder.DocumentLibraryFolderId = @something
filter.AddWithAnd(DocumentLibraryFolderDocumentFields.DocumentLibraryFolderId.SetObjectAlias("docLibraryFolder") == this.DocumentLibraryFolderId);

// fill
docsCollection.GetMulti(filter, relationsToUse);

Let me know if everything was ok

daelmo wrote:

Hi Matthew. .SetObjectAlias("aliasString") can help you here. I've rewrote your code. I've no tested it, but I think It should work.

Wow! You really rewrote it! Thanks much! I did later stumble upon SetObjectAlias and now it all makes sense seeing it written. I went ahead and used it the way you rewrote it since it looked better .

Your example generates this query:

SELECT DISTINCT [ClientNET].[dbo].[DocumentLibraryDocument].[DocumentLibraryDocumentID] AS [DocumentLibraryDocumentId], 
[ClientNET].[dbo].[DocumentLibraryDocument].[DocumentLibraryModuleID] AS [DocumentLibraryModuleId], 
[ClientNET].[dbo].[DocumentLibraryDocument].[RootDocumentLibraryDocumentID] AS [RootDocumentLibraryDocumentId], 
[ClientNET].[dbo].[DocumentLibraryDocument].[Title], [ClientNET].[dbo].[DocumentLibraryDocument].[Description], 
[ClientNET].[dbo].[DocumentLibraryDocument].[DocNumber], [ClientNET].[dbo].[DocumentLibraryDocument].[DocVersion], 
[ClientNET].[dbo].[DocumentLibraryDocument].[IsSecured], [ClientNET].[dbo].[DocumentLibraryDocument].[IsActive] 
FROM 
( [ClientNET].[dbo].[DocumentLibraryDocument] [LPA_d1]  
INNER JOIN [ClientNET].[dbo].[DocumentLibraryFolderDocument] [LPA_d2]  
ON  [LPA_d1].[DocumentLibraryDocumentID]=[LPA_d2].[DocumentLibraryDocumentID]) 
WHERE 
( ( [LPA_d1].[DocVersion] IN 
(SELECT MAX([docLibrarySubQuery].[DocVersion]) AS [DocVersion] 
FROM [ClientNET].[dbo].[DocumentLibraryDocument] AS [docLibrarySubQuery]  
WHERE ( [docLibrarySubQuery].[DocNumber] = [LPA_d1].[DocNumber])) 
AND [LPA_d2].[DocumentLibraryFolderID] = @DocumentLibraryFolderId1))

which produces this error:

The multi-part identifier "ClientNET.dbo.DocumentLibraryDocument.DocumentLibraryModuleID" could not be bound.

Which naturally is because the alias table LPA_d1 should be used in the select fields, not the original table name. I do not know how to fix this.

Edit: It looks like this post addresses the problem: http://www.llblgen.com/TinyForum/Messages.aspx?ThreadID=8361&HighLight=1 - I am attempting to rework the alias refs now.

Edit: Simple fix really, since the outer alias(es) aren't needed.

DocumentLibraryDocumentCollection docsCollection = new DocumentLibraryDocumentCollection();

                // relations to use
                IRelationCollection relationsToUse = new RelationCollection();
                relationsToUse.Add(DocumentLibraryDocumentEntity.Relations.DocumentLibraryFolderDocumentEntityUsingDocumentLibraryDocumentId); // , "docLibrary", "docLibraryFolder", JoinHint.Inner);

                // Create subquery filter
                IPredicateExpression subQueryFilter = new PredicateExpression();

                // docLibrarySubQuery.DocNumber = docLibrary.DocNumber
                subQueryFilter.Add(
                    new FieldCompareExpressionPredicate(
                        DocumentLibraryDocumentFields.DocNumber.SetObjectAlias("docLibrarySubQuery"),
                        ComparisonOperator.Equal,
                        new Expression(DocumentLibraryDocumentFields.DocNumber)));

                // Create main query filter
                IPredicateExpression filter = new PredicateExpression();

                /// docLibrary.DocNumber IN ( select MAX(DocVersion) FROM
                /// DocumentLibraryDocument docLibrarySubQuery 
                /// WHERE docLibrarySubQuery.DocNumber = docLibrary.DocNumber)
                filter.AddWithAnd(
                    new FieldCompareSetPredicate(
                            DocumentLibraryDocumentFields.DocVersion,
                            DocumentLibraryDocumentFields.DocVersion
                                .SetAggregateFunction(AggregateFunction.Max)
                                    .SetObjectAlias("docLibrarySubQuery"),
                            SetOperator.In,
                            subQueryFilter));

                /// docLibraryFolder.DocumentLibraryFolderId = @something
                filter.AddWithAnd(DocumentLibraryFolderDocumentFields.DocumentLibraryFolderId == this.DocumentLibraryFolderId);

                // fill
                docsCollection.GetMulti(filter, relationsToUse);

Thanks again.