This is an interesting one. Let’s take a very standard scenario as an example. Say we have a one-to-many relation (customer-order) between two tables (diagram made using QuickDBD):

And say we want to fetch all the customers that haven’t made a single order yet. It’s sort of the opposite of filtering by nested entities which was covered in an earlier post of the series. In this case we want to fetch all the customers that have a zero count of children entities. To do this, we’ll have to use JoinHint and DBNull LLBLGen constructs. Here is how:

// Fetch all customers with no orders
var customers = new EntityCollection<CustomerEntity>();

var filter = new RelationPredicateBucket();
filter.Relations.Add(CustomerEntity.Relations.OrderEntityUsingCustomerId, JoinHint.Left);
filter.PredicateExpression.Add(OrderFields.OrderId == DBNull.Value);

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

If you have any further questions, please leave them below. Cheers!