Generated code - Advanced filter usage, Adapter

Preface

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.

Re-using filters

A predicate (expression) object is an object that is used to construct a complete WHERE clause. However, a predicate expression object can be re-used for filtering on, for example, other values for a particular field without recreating the predicate objects. Say we want to filter on a given order ID in our Typed View Invoices, by using this expression:

// [C#]
PredicateExpression invoicesFilter = new PredicateExpression(InvoicesFields.OrderID > 11000);

' [VB.NET]
Dim invoicesFilter As New PredicateExpression(InvoicesFields.OrderID > 11000)

The value used to compare with is 11000. This is passed as a parameter value to the query for the parameter that is used to compare with Invoices.OrderID. However, if a new query on Invoices has to be executed after the query using the above filter, the predicate can be re-used, the value just has to be altered. First we have to cast the predicate object to the right type to alter the value later:

// [C#]
IPredicateExpression invoicesFilter = new PredicateExpression();
FieldCompareValuePredicate filterElement = (FieldCompareValuePredicate)(InvoicesFields.OrderID > 11000);
invoicesFilter.Add(filterElement);
' [VB.NET]
Dim invoicesFilter As New PredicateExpression()
Dim filterElement As New FieldCompareValuePredicate( _
	InvoicesFields.OrderID, Nothing, ComparisonOperator.GreaterThan, 11000)
invoicesFilter.Add(filterElement)

We can now set the Value for this predicate to another value to compare with:

// [C#]
// ... invoiceFilter is used in code
// Re-use the filter with a value of 10000. Set the value to 10000 to be able to do that
filterElement.Value = 10000;
' [VB.NET]
' ... invoiceFilter is used in code
' Re-use the filter with a value of 10000. Set the value to 10000 to be able to do that
filterElement.Value = 10000

And the next time you specify the invoiceFilter as a filter, the value to compare the field OrderID with will be 10000.

Negative predicates

Every predicate object can be negated, i.e. will be true when the predicate itself is not true. This is accomplished by specifying 'true' for negated when using the predicate class constructors. Negated is false by default. Each predicate type knows for itself where to place the NOT statement, so this is being taken care of by the predicate itself. You can negate a predicate or predicate expression by setting its Negate property to true.

Using the native language filter construction methods, you can also negate a predicate by simply prefixing it with '!' (C#) or 'Not' (without the quotes), as shown in the following example, which filters on employeeid not equal to 2. (note that you also could have used a '!=' (C#) or '<>' (VB.NET) operator instead of the equation operator.)

// C#
bucket.PredicateExpression.Add(!(OrderFields.EmployeeID==2));

// QuerySpec extension methods:
.Where(Functions.Not(OrderFields.Employee==2));
' VB.NET
bucket.PredicateExpression.Add(Not (OrderFields.EmployeeID=2))

' QuerySpec extension methods:
.Where(Functions.Not(OrderFields.Employee=2))

Filtering on entity type

In the situation where you want to fetch entities of a particular type (and all subtypes of that particular type), if the entity type to fetch is in an inheritance hierarchy, it can be cumbersome to formulate the exact filter, after all the predicate classes don't supply you with a proper filter to filter on a type.

To filter on a particular type, 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:

// C#
// add a filter which filters on boardmembers
filter.PredicateExpression.Add(BoardMemberEntity.GetEntityTypeFilter());
'VB.NET
' add a filter which filters on boardmembers
filter.PredicateExpression.Add(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).

note Note:
For filtering entity instances on type in memory, the DelegatePredicate is recommended with a delegate which filters using .NET native type filtering using Type.IsAssignableFrom().

Multi-entity filters

Sometimes you may want to filter on values in related entities. This is achieved by creating a RelationPredicateBucket and adding the required Relations. The RelationPredicateBucket is also used to hold additional filters; these filters are added to the PredicateExpression property of the RelationPredicateBucket. 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:

// [C#]
EntityCollection<CustomerEntity> customers = new EntityCollection<CustomerEntity>();
RelationPredicateBucket bucket = new RelationPredicateBucket();
bucket.Relations.Add(CustomerEntity.Relations.OrderEntityUsingCustomerID);
bucket.Relations.Add(OrderEntity.Relations.OrderDetailsEntityUsingOrderID);
bucket.Relations.Add(OrderDetailsEntity.Relations.ProductEntityUsingProductID);
bucket.Relations.Add(ProductEntity.Relations.SupplierEntityUsingSupplierID);
bucket.PredicateExpression.Add(SupplierFields.Country == "France");
using(DataAccessAdapter adapter = new DataAccessAdapter())
{
	adapter.FetchEntityCollection(customers, bucket);
}

// QuerySpec alternative
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 == "France");
var customers = new EntityCollection<CustomerEntity>();
using(var adapter = new DataAccessAdapter())
{
	adapter.FetchQuery(q, customers);
}
' [VB.NET]
Dim customers As New EntityCollection(Of CustomerEntity)()
Dim bucket As New RelationPredicateBucket()
bucket.Relations.Add(CustomerEntity.Relations.OrderEntityUsingCustomerID)
bucket.Relations.Add(OrderEntity.Relations.OrderDetailsEntityUsingOrderID)
bucket.Relations.Add(OrderDetailsEntity.Relations.ProductEntityUsingProductID)
bucket.Relations.Add(ProductEntity.Relations.SupplierEntityUsingSupplierID)
bucket.PredicateExpression.Add(SupplierFields.Country = "France")
Using adapter As New DataAccessAdapter()
	adapter.FetchEntityCollection(customers, bucket)
End Using

' QuerySpec alternative
Dim qf As New QueryFactory()
Dim q = qf.Customer _
	.From(QueryTarget _
		.InnerJoin(CustomerEntity.Relations.OrderEntityUsingCustomerId) _
		.InnerJoin(OrderEntity.Relations.OrderDetailEntityUsingOrderId) _
		.InnerJoin(OrderDetailEntity.Relations.ProductEntityUsingProductId) _
		.InnerJoin(ProductEntity.Relations.SupplierEntityUsingSupplierId)) _
	.Where(SupplierFields.Country == "France")
Dim customers As new EntityCollection(Of CustomerEntity)()
Using adapter As New DataAccessAdapter()
	adapter.FetchQuery(q, customers)
End Using

First, the EntityCollection object is created which will contain the entities that are retrieved from the persistent storage. Next, a new RelationPredicateBucket is created to hold the RelationCollection and the PredicateExpression objects we want to use in our filter. We now add each relation in the correct order. 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 relations are added to the RelationCollection property of our bucket we add the search filter to the PredicateExpression property of our bucket. We do this by adding one predicate, a FieldCompareValuePredicate, which compares Supplier.Country with the value "France". Now all the objects are ready to be used and are passed as parameters to the FetchEntityCollection method of the created DataAccessAdapter object. This will retrieve all Customer objects meeting the requirements of the filter we just defined.

There is no limit to the number of relations you can add to the RelationCollection of our bucket, however keep in mind that each added relation will result in an INNER JOIN statement,  unless a JoinHint has been specified using a different overload of Add().

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 relations could be specified to construct a JOIN path. The JOIN clauses themselves are determined from the relation 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 specifying a PredicateExpression instance for the CustomFilter property of the EntityRelation you add to a RelationCollection. 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. 

// C#
IPredicateExpression customFilter = new PredicateExpression();
customFilter.Add(OrderFields.ShipCountry == "Mexico"));
// ... 
relationsToUse.Add(CustomerEntity.Relations.OrderEntityUsingCustomerID).CustomFilter = customFilter;
// ...

// QuerySpec alternative (qf is QueryFactory instance). It lets you define the ON clause in full.
...
.From(qf.Customer.InnerJoin(qf.Order)
	.On((CustomerFields.CustomerId==OrderFields.CustomerId)
	    .And(OrderFields.ShipCountry=="Mexico")));
' VB.NET
Dim customFilter As IPredicateExpression = New PredicateExpression()
customFilter.Add(OrderFields.ShipCountry = "Mexico")
' ... 
relationsToUse.Add(CustomerEntity.Relations.OrderEntityUsingCustomerID).CustomFilter = customFilter
' ...

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

Please pay special attention to the flag EntityRelation.CustomFilterReplacesOnClause. If this flag is set to true, it will make the join construction logic to use the value of CustomFilter as the ON clause instead of appending it with AND to the field relation clause.

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?

The solution is to alias the entities in the relation added to the RelationCollection, and also to use the same alias in a predicate. If you omit an alias, it is considered not aliased and if you have aliased an entity in an earlier added relation to the same RelationCollection, it will be considered a different entity in the join list. So aliassing Customer to "C" in the first relation and in the second relation you do not specify an alias for Customer, you'll get 2 times a Customer entity in the join list.

Our example of Customer and the two Address entities with the two City predicates will result in the following code. Notice the alias usage in the predicates as well.

// C#
RelationPredicateBucket bucket = new RelationPredicateBucket();
bucket.Relations.Add(CustomerEntity.Relations.AddressEntityUsingVisitingAddressID, "VisitingAddress");
bucket.Relations.Add(CustomerEntity.Relations.AddressEntityUsingBillingAddressID, "BillingAddress");
bucket.PredicateExpression.Add((AddressFields.City.SetObjectAlias("VisitingAddress")=="Amsterdam") &
	 (AddressFields.City.SetObjectAlias("BillingAddress")=="Rotterdam"));
EntityCollection<CustomerEntity> customers = new EntityCollection<CustomerEntity>();
using(DataAccessAdapter adapter = new DataAccessAdapter())
{
	adapter.FetchEntityCollection(customers, bucket);
}
' VB.NET
Dim bucket As New RelationPredicateBucket()
bucket.Relations.Add(CustomerEntity.Relations.AddressEntityUsingVisitingAddressID, "VisitingAddress")
bucket.Relations.Add(CustomerEntity.Relations.AddressEntityUsingBillingAddressID, "BillingAddress")
bucket.PredicateExpression.Add( _
	((AddressFields.City.SetObjectAlias("VisitingAddress")="Amsterdam") And
	 (AddressFields.City.SetObjectAlias("BillingAddress")="Rotterdam"))
Dim customers As New EntityCollection(Of CustomerEntity)()
Using adapter As New DataAccessAdapter()
	adapter.FetchEntityCollection(customers, bucket)
End Using

note Note:
The aliases specified have to be valid aliases in SQL

LLBLGen Pro Runtime Framework v3.5 documentation. ©2012 Solutions Design bv