If you have a table Order and a related table Customer, you can sort and filter this data for ASP .NET with LLBLGen pro, as mentioned in the LLBLGen Pro documentation:

// Define result entitycollection
EntityCollection<CustomerEntity> customers = new EntityCollection<CustomerEntity>();

// Define sort (sort result on column "Order.ShipCountry")
SortExpression result = new SortExpression();
result.Add(CustomerFields.Name | SortOperator.Ascending);
result[0].CaseSensitiveCollation = false;

// Define filter by adding relations (= inner join in tsql)
RelationPredicateBucket customerFilter = new RelationPredicateBucket();
customerFilter.Relations.Add(CustomerEntity.Relations.OrderEntityUsingCustomerId);

// Define filter by adding predicateexpression (= where in tsql)
customerFilter.PredicateExpression.Add(OrderFields.ShipCountry=="Brazil");

// Load for all customers fetched their orders.
PrefetchPath2 path = new PrefetchPath2(EntityType.CustomerEntity);
path.Add(CustomerEntity.PrefetchPathOrders);

// Get the data
using(DataAccessAdapter adapter = new DataAccessAdapter())
{
     adapter.FetchEntityCollection(customers, customerFilter, path);
}

If you forget to add the relations, you might get the exception:

An exception was caught during the execution of a retrieval query: The multi-part identifier "dbo.Customer.Name" could not be bound.. Check InnerException, QueryExecuted and Parameters of this exception to examine the cause of this exception.

This is because there is no inner join on Customer, so Customer.Name does not exist in the query

One Comment

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.