I'm an LLBLGen newbie. I've been having problems with implementing subqueries

My query looks something like this:

select * from Table1 tbl1 where -- begin subquery (select count(*) from Table1 tbl1a join tbl2 on tbl2.NUMERIC_KEY = tbl1a.EP_ATTENDANCE_PLAN where tbl2.EMPLOYEE = tbl1.EMPLOYEE -- tbl1 = outside table reference and tbl1a.ENTRY_DATE between '11/14/2007' and '11/21/2007' and isnull(tbl1a.SYSTEM_GENERATED,0) = 0) -- end subquery >= 1 -- check result of subquery is greater than or equal to 1

I'm using C# btw

Hi MetalliMyers,

You can use ScalarQueryExpression and use it in the filter.

Here is an example (using Adapter template set): I want All customers that have at least 1 order

EntityCollection<CustomerEntity> customers = new EntityCollection<CustomerEntity>(new CustomerEntityFactory());

EntityField2 numberOfOrders = new EntityField2("numberOfOrders",
    new ScalarQueryExpression(OrderFields.OrderId.SetAggregateFunction(AggregateFunction.Count),
        (CustomerFields.CustomerId == OrderFields.CustomerId)));

IRelationPredicateBucket filter = new RelationPredicateBucket();
filter.PredicateExpression.Add(numberOfOrders > 1);

using (DataAccessAdapter adapter = new DataAccessAdapter())
{
    adapter.FetchEntityCollection(customers, filter);
}

Hope helpful

How can I reference a table from the outside query in it?

in the example that daelmo wrote above the outside table is the table Orders

EntityField2 numberOfOrders = new EntityField2("numberOfOrders",
    new ScalarQueryExpression(OrderFields.OrderId.SetAggregateFunction(AggregateFunction.Count),
        (CustomerFields.CustomerId == OrderFields.CustomerId)));

plese check al the overloads of the ScalarQueryExpression class, with this class you can achieve the exact results you need.

In order to clarity I've rewroted your query:

SELECT 
    *
FROM 
    [Table1]  
WHERE   
    (
        SELECT 
            COUNT (*)
        FROM                    
            [Table1] [tbl1]  
                INNER JOIN 
                    [Table2] [tbl2]  
                        ON  ([tbl2].[Id]=[tbl2].[IdOrden])
        WHERE 
             [tbl2].[IdOrden] = [Table1].[Id]
    ) >=1

And here is the corresponding c# code:

        //collection to be fetched
        EntityCollection<Table1Entity> table1Collection = new EntityCollection<Table1Entity>(new Table1EntityFactory());



            //the aliases here is the most important part
            IRelationCollection subRelations = new RelationCollection();
            subRelations.Add(Table1Entity.Relations.Table2EntityUsingNumericKey, " tbl1", "tbl2", JoinHint.Inner); //use new EntityRelation(... if the relation doesn't exists



            IPredicateExpression subFilter = new PredicateExpression();
            subFilter.Add(new FieldCompareExpressionPredicate(Table1Fields.Employee, null, ComparisonOperator.Equal, new Expression(Table1Fields.Employee), "tbl1"));
            EntityField2 countField = new EntityField2("countField",
                new ScalarQueryExpression(Table1Fields.Id.SetAggregateFunction(AggregateFunction.Count),
                subFilter,subRelations));   


        IRelationPredicateBucket filter = new RelationPredicateBucket();

        filter.PredicateExpression.Add(new FieldCompareValuePredicate(countField,null,ComparisonOperator.GreaterEqual,1));



        using (DataAccessAdapter adapter = new DataAccessAdapter())
        {
            adapter.FetchEntityCollection(table1Collection, filter);
        }

I've left the other subFilters out, just add them in the subFilter object.

Thank you for the responses and the help.