Hi!

I'm looking for the correct LLBLGen code for this SQL:

SELECT * FROM customer c 
   LEFT OUTER JOIN order o ON o.customer = c.id 
   WHERE o.year = 2007 or o.id IS NULL

(Return all customer and their linked orders. Only return orders that have Year = 2007. Also return customers without orders.)

This is what I tried:

// create empty collection
           EntityCollection<CustomerEntity> customers = new EntityCollection<CustomerEntity>(new CustomerEntityFactory());

           // define prefetch path
           IPrefetchPath2 prefetchPath = new PrefetchPath2((int)EntityType.CustomerEntity);
           prefetchPath.Add(CustomerEntity.PrefetchPathOrder);

           // focus on passed businessYear
           RelationPredicateBucket bucket = new RelationPredicateBucket();
           bucket.PredicateExpression.AddWithOr(OrderFields.Year == businessYear);
           bucket.PredicateExpression.AddWithOr(OrderFields.Year == DBNull.Value);

           // load data from db
           Connection.GetDataAccessAdapter().FetchEntityCollection(customers, bucket, int.MaxValue, sorter, prefetchPath);

But this throws an exception because the resulting SQL is not as expected:

SELECT * FROM customer  
   WHERE order.year = 2007 or order.id IS NULL

That fails of course because the order table is missing.

What is the correct code in C#?

Thanks in advance!

Using an entityCollection to fetch Customers will result in the following select statement:

SELECT customer.field1, customer.field2...
FROM customer
Where (SOME FILTER EXPRESSION)

As you may notice this will only fetch the customer fields. And you can add any filtering predicate instead of the SOME FILTER EXPRESSION token.

If you wish to filter upon a field in another table, you would need to join with this table. Which can be a left join. To do this you will need to add a relation to the RelationPredicateBucket.Relations collection.

Now if you also want to fetch related Orders into the Orders property of each fetched Customer, then you should use a prefetchPath. And yet if you want to filter those prefetched entities, you can pass a filter to the PrefetchPath added. (there are overloads for the prefetchPath.Add mehtod that accept a filter).

But if you want to fetch fields from more than one table in the same select statement, then you will have to use either a TypedList, TypedView or a DynamicList.

Thanks!

A filter in the PrefetchPath did solve the problem.