I'm fairly new to LLBLGen and I'm trying to setup a dynamic list that involves a query on the same table:

           ResultsetFields fields = new ResultsetFields(6);
            fields.DefineField(CategoryFields.CategoryID, 0);
            fields.DefineField(CategoryFields.CategoryName, 1);
            fields.DefineField(CategoryFields.CategoryDescription, 2);
            fields.DefineField(CategoryFields.OrderIndex, 3);

             fields.DefineField(new EntityField("AmountSubcats",
                    new ScalarQueryExpression(CategoryFields.CategoryID.SetAggregateFunction(AggregateFunction.Count),
                        (CategoryFields.ParentID == CategoryFields.CategoryID))), 4);

This query/sub-query I really want is this: select c.CategoryName, (select count(CategoryID) from Category where ParentID = c.CategoryID) from Category c

The above DefineField doesn't use aliases and so it doesn't work.

I couldn't quite find the right solution in the docs and forums using aliases to solve this easily.

Thanks in advance.

You can use the SetObjectAlias on the entityfields to relate to the correct field/alias. So use e.g.

CategoryFields.ParentID.SetObjectAlias("SubQuery") == CategoryFields.CategoryID

in the ScalarQueryExpression. I think you should then also use the SetObjectAlias on the CategoryFields.CategoryID.SetAggregateFunction(AggregateFunction.Count) statement.

DvK wrote:

You can use the SetObjectAlias on the entityfields to relate to the correct field/alias. So use e.g.

CategoryFields.ParentID.SetObjectAlias("SubQuery") == CategoryFields.CategoryID

in the ScalarQueryExpression. I think you should then also use the SetObjectAlias on the CategoryFields.CategoryID.SetAggregateFunction(AggregateFunction.Count) statement.

You're absolutely correct. Thanks!

Here's the final code which worked as expected:

ResultsetFields fields = new ResultsetFields(5);
fields.DefineField(CategoryFields.CategoryID, 0);
fields.DefineField(CategoryFields.CategoryName, 1);
fields.DefineField(CategoryFields.CategoryDescription, 2);
fields.DefineField(CategoryFields.OrderIndex, 3);
fields.DefineField(new EntityField("AmountSubcats",
        new ScalarQueryExpression(CategoryFields.CategoryID.SetAggregateFunction(AggregateFunction.Count).SetObjectAlias("SubQuery"),
            (CategoryFields.ParentID.SetObjectAlias("SubQuery") == CategoryFields.CategoryID))), 4);