I am using LLBLGen Pro Version 2.0.0.0 December 6, 2006 Final - adapter - MsSql 2000

My goal was to find the total Orders placed by "Customers" during 2006. "Customers" is one of many titles in the users table: Here is the C# code I used:

    static void Main(string[] args)
    {
        DateTime sdate=new DateTime(2006,1,1);
        DateTime edate=new DateTime(2007,1,1);
        DateTime tdate = new DateTime();

        EntityCollection<OrdersEntity> torders = 
            new EntityCollection<OrdersEntity>(new OrdersEntityFactory());
        IPrefetchPath2 tfetch = 
            new PrefetchPath2((int)EntityType.OrdersEntity);
        tfetch.Add(OrdersEntity.PrefetchPathUsers);
        IRelationPredicateBucket tfilter = new RelationPredicateBucket();
        tfilter.PredicateExpression.Add(OrdersFields.OrderDate>=sdate);
        tfilter.PredicateExpression.AddWithAnd(OrdersFields.OrderDate<=edate);

        DataAccessAdapter tadapt = new DataAccessAdapter();
        tadapt.FetchEntityCollection(torders, tfilter,tfetch);
        double tcusttotal=0,tconstotal=0;
        string ttitle="";
        foreach (OrdersEntity aorder in torders)
        {
            ttitle = aorder.Users.Title.Trim();
            if (ttitle.Equals("Customer"))
            {
                tcusttotal += (double)aorder.Totalorder.Value;
            }
            else
            {
                tconstotal += (double)aorder.Totalorder.Value;
            }
            tdate = aorder.OrderDate;
        }
        Console.WriteLine("Customer Total 2006: " + tcusttotal.ToString());
        Console.WriteLine("Consultant Total 2006: " + tconstotal.ToString());
        Console.ReadLine();

    }

I got the Customer totals I needed by iterating through the fetched orders and adding the total of the customer totals.

My question is: What strategy could I have used to prefetch just the orders placed by "Customers". The title field is in the Users table - in Sql I would have done an inner join on users to orders and used a where clause for the orderdates field in the orders table and the title of "Customer" in the Users table. I was able to filter the fetching of the orders by orderdates above, but could not see how to filter the fetching of the orders placed during 2006 on a user title of "Customer". The relationship between users and orders is a PK of userid on the users table to an FK of customerid on the orders table.

Thanks

Something like:

            IRelationPredicateBucket tfilter = new RelationPredicateBucket();
            tfilter.Relations.Add(OrdersEntity.Relations.UsersEntityUsingUserID);
            tfilter.PredicateExpression.Add(OrdersFields.OrderDate>=sdate);
            tfilter.PredicateExpression.AddWithAnd(OrdersFields.OrderDate<=edate);
            tfilter.PredicateExpression.AddWithAnd(UsersFields.Title == "Customer");

Adding the relation is like doing the inner join

(ps, you don't need the prefetch path unless you actualy want to populate the Users objects).

Thanks - that did it!