LLBLGenPro v4.2

            var qf = new QueryFactory();

            var query = qf.Keyword
                .OrderBy(KeywordFields.Phrase | SortOperator.Ascending)
                .Select(
                    KeywordFields.ID,
                    KeywordFields.Phrase,
                    qf.ClassificationToKeyword.CorrelatedOver(ClassificationToKeywordFields.KeywordID == KeywordFields.ID).CountRow().As("ClassificationCount")
                );

ClassificationToKeywordFields is a simple join table holding KeywordID and ClassificationID and the above query works fine.

What I now want to do is keep the above CountRow() line but add another line which also uses the "qf.ClassificationToKeyword.CorrelatedOver(ClassificationToKeywordFields.KeywordID == KeywordFields.ID)" and counts where the ClassificationToKeywordFields.ClassificationIDs returned are used in another table. I can't work out how to do it or whether it is possible.

Something like

                    qf.Resource
                        .Where(ResourceFields.ClassificationID.In(
                            qf.ClassificationToKeyword.CorrelatedOver(ClassificationToKeywordFields.KeywordID == KeywordFields.ID).Select(ResourceFields.ID))
                        ).CountRow()

This seems to work

                    qf.Resource
                        .From(QueryTarget.InnerJoin(qf.ClassificationToKeyword.CorrelatedOver(ClassificationToKeywordFields.KeywordID == KeywordFields.ID))
                            .On(ClassificationToKeywordFields.ClassificationID.SetObjectAlias("LPA_L1") == ResourceFields.ClassificationID)
                        ).CountRow()

but I had to hardcode the "LPA_L1" which I don't like to do.

Where can I specify an alias to replace the default "LPA_L1"?

Could you please try something like this?

var query = qf.Order
.OrderBy(OrderFields.OrderDate | SortOperator.Ascending)
.Select(
    OrderFields.OrderId,
    OrderFields.CustomerId,
    qf.OrderDetail.CorrelatedOver(OrderFields.OrderId == OrderDetailFields.OrderId).CountRow().As("LineItemsCount"),
    qf.OrderDetail.CorrelatedOver(OrderFields.OrderId == OrderDetailFields.OrderId).Where(
        OrderDetailFields.ProductId.In(
            qf.Create()
                .Select(ProductFields.ProductId)
                .Where(ProductFields.CategoryId.Equal(4)))
    )
    .CountRow().As("SpecialLineItemCount")
);

The second where clause doesn't look right - I'm not filtering on a constant.

Mine does work but I would just like to get rid of the constant alias name.

How would the sql look like, could you give an example of that please? CorrelatedOver simply defines a where clause for a nested query, so the COUNT(*) is on the nested query. So if the nested query should contain more fields in the projection, then you have to define a full nested query (and use TargetAs()). But it's easier if we have a bit of an idea what the sql looks like so we can give you the statements how to achieve that.

This is the SQL generated which appears to work:-

--Retrieval Query:

SELECT [Keyword].[ID], [Keyword].[Phrase], ( SELECT COUNT() AS [LPAV_] FROM [ResourceToKeyword]
WHERE ( [ResourceToKeyword].[KeywordID] = [Keyword].[ID])) AS [Count], ( SELECT COUNT() AS [LPAV] FROM [ClassificationToKeyword]
WHERE ( [ClassificationToKeyword].[KeywordID] = [Keyword].[ID])) AS [ClassificationCount], ( SELECT COUNT(*) AS [LPAV] FROM ( ( SELECT [ClassificationToKeyword].[KeywordID], [ClassificationToKeyword].[ClassificationID] FROM [ClassificationToKeyword]
WHERE ( [ClassificationToKeyword].[KeywordID] = [Keyword].[ID])) [LPA_L1] -- Where does this come from?? INNER JOIN [Resource] ON [LPA_L1].[ClassificationID] = [Resource].[ClassificationID])) AS [LLBLV_1] FROM [Keyword]
ORDER BY [Keyword].[Phrase] ASC

The second occurrence of "LPA_L1" is the one I hard-coded, the first occurrence is generated by LLBLGen.

The second where clause doesn't look right - I'm not filtering on a constant.

Mine does work but I would just like to get rid of the constant alias name.

Instead of the inner join, I'm using an IN predicate and a subquery. The constant filter is just to limit the results of the subquery, for the sake of testing the results.

Guys

I stumbled across the correct LLBLGen statements to use in message #2. It produces the SQL in message #6.

In my LLBLGen statements, I had to use ".SetObjectAlias("LPA_L1")" to match that already generated by LLBLGen.

It may well be safe to leave it like that but it would be safer if I could set the alias string on both sides. I just don't know where to do it. That is the question now.

Message #6 has '-- Where does this come from??' to indicate the LLBLGen autogenerated alias.

Ok, but what's your full code of the query with the hardcoded alias? As your 'this seems to work' fragment doesn't match with the particular part in the sql as that uses a projection.

Could you post that please?

The projection is a subquery (the one with LPA_L1) so it gets an alias automatically (it has to ). If you alias the query with 'As' you can then refer to that alias in your predicate (instead of LPA_L1), but I don't think I see the subquery which aliased as LPA_L1 in your snippets, so a full queryspec query in C# code would be great

The full code was

    [Test]
    public void TestKeywordCounts()
    {
        const string CountFieldName = "Count";
        const string ClassificationCountFieldName = "ClassificationCount";

        var qf = new QueryFactory();

        var query = qf.Keyword
            .OrderBy(KeywordFields.Phrase | SortOperator.Ascending)
            .Select(
                KeywordFields.ID,
                KeywordFields.Phrase,
                qf.ResourceToKeyword.CorrelatedOver(ResourceToKeywordFields.KeywordID == KeywordFields.ID).CountRow().As(CountFieldName),
                qf.ClassificationToKeyword.CorrelatedOver(ClassificationToKeywordFields.KeywordID == KeywordFields.ID).CountRow().As(ClassificationCountFieldName),

                qf.Resource
                    .From(QueryTarget.InnerJoin(qf.ClassificationToKeyword.CorrelatedOver(ClassificationToKeywordFields.KeywordID == KeywordFields.ID))
                        .On(ClassificationToKeywordFields.ClassificationID.SetObjectAlias("LPA_L1") == ResourceFields.ClassificationID)
                    ).CountRow()
            );

        using (var adapter = new DataAccessAdapter())
        {
            var result = adapter.FetchQuery(query);

            var xxx = result.Where(i => (int) i[3] > 0).ToArray();
            var yyy = result.Where(i => (int) i[4] > 0).ToArray();
        }
    }

I would have sworn I tried 'As' on every part of the query one a time but obviously I missed one! I have now done as you suggested and it now works (though it ignores my suggested alias name and just uses the first letter from it: "Fred" becomes "LPA_F1")

Glad it works! Yes it doesn't use aliases specified as-is, it creates its own.