Advanced filter usage

This section builds upon the previous sections about filtering and shows you how to use the introduced concepts and classes in more advanced topics and situations.

Negative predicates

Every predicate object can be negated, i.e. will be true when the predicate itself is not true. This is accomplished by setting the IPredicate's Negate property to true. Negate is false by default.

To negate a existing predicate object, use the Functions.Not method as shown in the example below.

var myPredicate = OrderFields.EmployeeID.Equal(2);

// predicate needs to be negated. Sets Negate on myPredicate, 
// it doesn't make a copy.
myPredicate = Functions.Not(myPredicate);

Filtering on entity type

To filter on a particular entity type which is in an inheritance hierarchy, use the following general mechanism. Say you want to limit a fetch to only BoardMember entities, which is a subtype of Manager, which is a subtype of Employee. The given filter is build in the object filter in the following example:

// Obtain the predicate
var boardMemberFilter = BoardMemberEntity.GetEntityTypeFilter();

The method GetEntityTypeFilter(), which is available in all entities which are part of an inheritance hierarchy, produces an IPredicateExpression object which filters on the entity type you call the method on, so in our example on BoardMember.

All subtypes of the type you're filtering on will also match the filter, as they're also of the type you're filtering on. (BoardMember is-a Manager is-a Employee).

Multi-entity filters

Sometimes you may want to filter on values in related entities. This is achieved by specifying the relationships to create the proper joins additionally to the required predicates.

For example, suppose you wanted to retrieve all customers who bought a product from any supplier in France. This requires a filter on Country, but Country is not part of the Customer entity, it is part of the Supplier entity. Here's how to achieve this in code, using QuerySpec.

var qf = new QueryFactory();
var q = qf.Customer
    .From(QueryTarget
        .InnerJoin(CustomerEntity.Relations.OrderEntityUsingCustomerId)
        .InnerJoin(OrderEntity.Relations.OrderDetailEntityUsingOrderId)
        .InnerJoin(OrderDetailEntity.Relations.ProductEntityUsingProductId)
        .InnerJoin(ProductEntity.Relations.SupplierEntityUsingSupplierId))
    .Where(SupplierFields.Country.Equal("France"));
var customers = new EntityCollection<CustomerEntity>();
using(var adapter = new DataAccessAdapter())
{
    adapter.FetchQuery(q, customers);
}
var qf = new QueryFactory();
var q = qf.Customer
    .From(QueryTarget
        .InnerJoin(CustomerEntity.Relations.OrderEntityUsingCustomerId)
        .InnerJoin(OrderEntity.Relations.OrderDetailEntityUsingOrderId)
        .InnerJoin(OrderDetailEntity.Relations.ProductEntityUsingProductId)
        .InnerJoin(ProductEntity.Relations.SupplierEntityUsingSupplierId))
    .Where(SupplierFields.Country.Equal("France"));
var customers = new CustomerCollection();
customers.GetMulti(q);

To specify the right joins, we start with the target entity, in this case Customer, and work your way down to the entity you want to filter on. In this case the SupplierEntity. Each entity, on both sides of 'Relations' is included in the complete scope of the query, thus ProductEntity.Relations.SupplierEntityUsingSupplierId will include both Product and Supplier and thus you can filter on fields in either or both of these entities.

After the relationships are specified, the predicate, a FieldCompareValuePredicate is specified in the .Where() call, which compares Supplier.Country with the value "France". Now all the objects are ready to be used and the query is passed to the appropriate fetch method. This will retrieve all Customer objects meeting the requirements of the filter we just defined.

In the example, all entities in the relations are added once. If you want to filter on an entity twice, or if you use an entity twice in two, different relations, you have to specify aliasses for the entities in the relations. See Advanced filtering below for more information. 

Custom filters for EntityRelations

In the section above, Multi-entity filters, it was described how relationships could be specified to construct a JOIN path. The JOIN clauses themselves are determined from the relationship objects, thus FK-PK compares which result in the ON clause. Sometimes it is important to specify additional predicates in this ON clause.

You can do this by simply specifying the On clause manually in the QuerySpec query, or, alternatively, you can specify a PredicateExpression instance for the relationship used, by setting the relationship's CustomFilter property of the to a RelationCollection. For both an example is given below.

In the example below we add a custom predicate to the EntityRelation object of the relation Customer-Order and which filters on Order.ShipCountry="Mexico". It uses the example of Multi-entity filters above.

var customFilter = new PredicateExpression();
customFilter.Add(OrderFields.ShipCountry.Equal("Mexico"));
// ... 
relationsToUse.Add(CustomerEntity.Relations.OrderEntityUsingCustomerID)
                .CustomFilter = customFilter;
// ...

// QuerySpec alternative (qf is QueryFactory instance). It lets you 
// define the ON clause in full.
...
.From(QueryTarget.InnerJoin(qf.Order)
    .On((CustomerFields.CustomerId==OrderFields.CustomerId)
        .And(OrderFields.ShipCountry=="Mexico")));

Advanced filtering

The predicates and relations discussed up till now can do a fair amount of filtering for you. However sometimes you need more advanced filtering, for example when an entity has to be joined multiple times to the join list, using aliases and you want to filter with one predicate on the fields of one alias and in another predicate on another alias.

An example would be: get all Customers who have a visiting address in Amsterdam and a billing address in Rotterdam. Customer has two relations with Address: Customer.VisitingAddressID - Address.AddressID and Customer.BillingAddressID - Address.AddressID.

Simply adding the relation CustomerEntity.Relations.AddressUsingVisitingAddressID to the RelationCollection will work, but when you add the relation CustomerEntity.Relations.AddressUsingBillingAddressID, you have two times the Address entity in the join list, how are you going to target one of them in a predicate?

To solve that, specify aliases for the entities joined: The address related over VisitingAddresss is aliased as VA, the address related over BillingAddress is aliased as BA.

Important!

The aliases specified have to be valid aliases in SQL

var qf = new QueryFactory();
var q = qf.Customer
            .From(QueryTarget
                .InnerJoin(qf.Address.As("BA"))
                    .On(CustomerFields.BillingAddressId
                        .Equal(AddressFields.AddressId.Source("BA")))
                .InnerJoin(qf.Address.As("VA"))
                    .On(CustomerFields.VisitingAddressId
                        .Equal(AddressFields.AddressId.Source("VA")))
            .Where(AddressFields.City.Source("VA").Equal("Amsterdam")
                    .And(AddressFields.City.Source("BA").Equal("Rotterdam")));

// fetch using adapter:
var customers = new EntityCollection<CustomerEntity>();
using(var adapter = new DataAccessAdapter())
{
    adapter.FetchQuery(q, customers);
}

// or using SelfServicing:
var customers = new CustomerCollection();
customers.GetMulti(q);