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
I look up for that. Thank you very much.
Very good explaination related with SQL.