Show/Hide Toolbars

TMS Aurelius Documentation

You can add condition expressions to associations of the class being queried. For example, you can retrieve invoices filtered by the name of invoice customer.

 

To add a condition for an association, you have two options: use subcriteria or aliases.

 

Using aliases

 

You can create an alias for an association to filter by sub properties of such association.

 

Results := Manager.Find<TEstimate>
  .CreateAlias('Customer''c')
  .Where(Linq['c.Name'].Like('M%'))
  .List;

 

Calling CreateAlias does not return a new TCriteria instance, but instead it returns the original TCriteria. So the expression context is still the original class (in the example above, TEstimate). Thus, to reference a Customer property the "c" alias prefix was needed. Note that since the original TCriteria<TEstimate> object is being used, you can call List method (instead of TList<T>).

 

Just like SubCriteria calls, you can also use nested CreateAlias methods, by settings aliases for associations of associations. It's important to note that the context in the fluent interface is always the original TCriteria class:

 

Results := Manager.Find<TEstimate>
  .CreateAlias('Customer''ct')
  .CreateAlias('ct.Country''cn')
  .Where(Linq['cn.Name'] = 'United States')
  .List;

 

 

Using SubCriteria

 

You can alternatively create a sub-criteria which related to the association being filtered, using SubCriteria method of the TCriteria object itself. It returns a new TCriteria object which context is the association class, not the main class being queried.

 

Results := Manager.Find<TInvoice>
  .SubCriteria('Customer')
    .Where(Linq['Name'].Like('M%'))
    .List<TInvoice>;

 

In the example above the class TInvoice has a property Customer which is an association to the TCustomer class. The filter "Name = 'M%'" is applied to the customer, not the invoice. SubCriteria method is being called and receives "Customer" parameter, which is the name of associated property. This returns a new TCriteria object. The expressions added to it related to TCustomer class, that's why 'Name' refers to the TCustomer.Name property, not TInvoice.Name (if that ever existed).

 

Note that SubCriteria method returns a TCriteria method (the non-generic version). That's why we need to call List<TInvoice> method (not just List).

 

You can have nested SubCriteria calls, there is not a level limit for it. In the example below, the query returns all estimates for which the country of the customer is "United States".

 

Results := Manager.Find<TEstimate>
  .SubCriteria('Customer')
    .SubCriteria('Country')
      .Where(Linq['Name'] = 'United States')
      .List<TEstimate>;

 

 

 

Mixing SubCriteria and aliases

 

You can safely mix SubCriteria and CreateAlias calls in the same query:

 

Results := Manager.Find<TEstimate>
  .SubCriteria('Customer')
    .CreateAlias('Country''cn')
    .Where(Linq['cn.Name'] = 'United States')
  .List<TEstimate>;

 

Specifying Eager fetching for associations loaded as lazy by default

 

Your class mapping might have defined associations to be marked as lazy-loaded (using proxies). This means if you retrieve one hundred records and you want to access the associated object, one hundred SQL statements will be executed to retrieve such value. You can optionally override the default loading mechanism and set the association to be eager-loaded. This way Aurelius will build an extra JOIN in the SQL statement to retrieve the associated objects in a single SQL. You do this by passing TFetchMode.Eager as the third parameter of CreateAlias or second parameter of SubCriteria:

 

Results := Manager.Find<TEstimate>
  .CreateAlias('Customer''ct', TFetchMode.Eager)
  .List;

 

with the query above, even if TEstimate.Customer association is set as lazy-loading, Aurelius will create a single SQL with a JOIN between estimates and customers and retrieve all customers at once. This gives you an extra degree of flexibility when it comes to optimize your application.