Queries
You can perform queries with Aurelius, just like you would do with SQL
statements. The difference is that in Aurelius you perform queries at
object level, filtering properties and associations. Most classes you
need to use for querying are declared in unit Aurelius.Criteria.Base
.
Creating Queries
Queries are represented by an instance of TCriteria object. To execute queries, you just create an instance of TCriteria object, use its methods to add filtering, ordering, projections, etc., and then call List method to execute the query and retrieve results.
Create a new query (TCriteria instance)
Use either Find<T>, CreateCriteria<T> or CreateCriteria method of a TObjectManager instance to create a new query instance. You must always define the class which you want to search objects for:
MyCriteria := Manager1.CreateCriteria(TCustomer);
or the recommended generic version, which will return a TCriteria<T> object:
MyCriteria := Manager1.Find<TCustomer>;
MyCriteria := Manager1.CreateCriteria<TCustomer>;
Memory management
One important thing you should know: the TCriteria object instance is automatically destroyed when you retrieve query results, either using List, ListValues, UniqueResult or UniqueValue methods. This is done this way so it's easier for you to use the fluent interface, so you don't need to keep instances to objects in variables and destroy them.
So be aware that you don't need to destroy the TCriteria object you created using CreateCriteria or Find, unless for some reason you don't retrieve the query results.
If you don't want this behavior to apply and you want to take full control over the TCriteria lifecycle (for example, you want to keep TCriteria alive for some time to add more filters programatically), you can set TCriteria.AutoDestroy property to false (it's true by default). This way TCriteria will not be destroyed automatically and you must destroy it at some point:
MyCriteria := Manager1.CreateCriteria(TCustomer);
MyCriteria.AutoDestroy := false;
// You MUST destroy MyCriteria eventually, even after retrieving results
Fluent Interface
The criteria objects you create implement a fluent interface. This means that most methods in the class will return an instance of the object itself. This is just a easier way to build your queries.
So instead of building the query like this:
var
Results: TObjectList<TCustomer>;
Criteria: TCriteria<TCustomer>;
Filter: TCustomCriterion;
begin
Criteria := Manager1.Find<TCustomer>;
Filter := Linq['Name'] = 'Mia Rosenbaum';
Criteria.Add(Filter);
Results := Criteria.List;
You can simply write it this way:
var
Results: TObjectList<TCustomer>;
begin
Results := Manager1.Find<TCustomer>
.Add(Linq['Name'] = 'Mia Rosenbaum')
.List;
Almost all the examples in this chapter uses the fluent interface so you can fully understand how to use it.
Retrieving Results
Usually query results are a list of objects of an specified class. You usually call List or List<T> methods to retrieve an object list, or Open to get a fetch-on-demand cursor. If you use a list, this will retrieve you a TList<T> object with all the queries objects. If you are sure your query will return a single value, use UniqueResult (or UniqueValue for projections), which will return a single instance of the object.
It's also important to know how memory management is performed with the queried objects, so you properly know when you need to destroy the retrieved results, and when you don't. Also, you don't need to destroy the query you created using CreateCriteria/Find, it's automatically destroyed when you query the results.
The following topics describe different ways of retrieving the results of a query.
Retrieving an Object List
After building your query, you can use List method to retrieve filtered/ordered objects. The method to be used depends on how you created your TCriteria object, it could be List or List<T>. The result type will always be a TList<T> where T is the class you are filtering.
If you created the criteria using non-generic Find method, you will need to call List<T> method.
var
Results: TList<TCustomer>;
MyCriteria: TCriteria;
begin
MyCriteria := ObjectManager1.Find(TCustomer);
// <snip> Build the query
// Retrieve results
Results := MyCriteria.List<TCustomer>;
If you created the generic criteria using Find<T> or CreateCriteria<T> method, just call List method and it will return the correct object list:
var
Results: TList<TCustomer>;
MyCriteria: TCriteria<TCustomer>;
begin
MyCriteria := ObjectManager1.Find<TCustomer>;
// <snip> Build the query
// Retrieve results
Results := MyCriteria.List;
Using this approach, a query will be executed, all objects will be fetched from the database, connection will be closed and a newly created TList<T> object will be returned with all fetched objects. You must later destroy the TList<T> object.
Unique Result
If you are sure your query will return a single value, use UniqueResult instead (or UniqueResult<T> for non-generic criteria). Instead of a TList<T>, it will just return an instance of TObject:
var
UniqueCustomer: TCustomer;
MyCriteria: TCriteria<TCustomer>;
begin
MyCriteria := ObjectManager1.Find<TCustomer>;
// <snip> Build the query
// Retrieve the single result
UniqueCustomer := MyCriteria.UniqueResult;
If the query returns no objects, then UniqueResult will return nil. If the query returns more than one different object, an EResultsNotUnique exception will be raised.
Note that if the query returns more than one record, but all records relate to the same object, then no exception will be raised, and the unique object will be returned.
Fetching Objects Using Cursor
Alternatively to retrieve an object list, you can get results by using a cursor. With this approach, Aurelius executes a query in the database and returns a cursor for you to fetch objects on demand. In this case, the query will remain open until you destroy the cursor.
While this approach has the advantage to keeping a database connection alive, it takes advantage of fetch-on-demand features of the underlying component set you are using, allowing you to get initial results without having to fetch all the objects returned. You don't even need to fetch all results, you can close the cursor before it. Cursor can also be used in TAureliusDataset to make it more responsive to visual controls like DB Grids.
To obtain a cursor, use the Open method:
var
MyCriteria: TCriteria<TCustomer>;
Cursor: ICriteriaCursor<TCustomer>;
FetchedCustomer: TCustomer;
begin
MyCriteria := ObjectManager1.Find<TCustomer>;
// <snip> Build the query
// Retrieve results
Cursor := MyCriteria.Open;
while Cursor.Next do
begin
FetchedCustomer := Cursor.Get;
// Do something with FetchedCustomer
end;
// No need to destroy cursor
The Open method returns an ICriteriaCursor (or ICriteriaCursor<T>) interface which is destroyed automatically by reference counting. The underlying TCriteria object (MyCriteria variable in the example above) is automatically destroyed when cursor is destroyed. Since ICriteriaCursor<T> implements GetEnumerator you can also iterate through the returned entities directly:
var
FetchedCustomer: TCustomer;
begin
for FetchedCustomer in ObjectManager1.Find<TCustomer>.Open do
begin
// Do something with FetchedCustomer
end;
The ICriteriaCursor and ICriteriaCursor<T> interfaces are declared as following.
ICriteriaCursor = interface
function Next: boolean;
function Fetch: TObject;
function BaseClass: TClass;
function ResultClass: TClass;
end;
ICriteriaCursor<T: class> = interface(ICriteriaCursor)
function Get: T;
function GetEnumerator: TEnumerator<T>;
end;
Next method increases cursor position. If result is true, then the new position is valid and there is an object to fetch. If result is false, there are no more objects to be fetched, and cursor must be destroyed. It's important to note that when the cursor is open, it remains in an undefined position. You must call Next method first, before fetching any object. If the very Next call returns false, it means the cursor has no records.
Fetch method is used to retrieve the object in the current cursor position. If Next was never called, or if the result of last Next call was false, Fetch will return unpredictable values. Never call Fetch in such situation.
Get<T> method is just a strong-typed version of Fetch method.
BaseClass method returns the base class used in the criteria query. In the example above, base class would be TCustomer.
ResultClass method returns the class of the returned objects. Usually it's the same as BaseClass, unless in specific cases like when you are using projections, for example. In this case ResultClass will be TCriteriaResult.
Results with Projections
If you added projections to your query, the results will not be entity objects anymore, but instead an special object type that holds a list of values. For example, if you use sum and grouping in your orders, you will not receive a list of TOrder objects anymore, but instead a list of values for the sum results and grouping name.
If that's the case, you should use either:
ListValues method, if you want to retrieve an object list (this is the equivalent of List method for entity objects);
UniqueValue method, if you want to retrieve an unique value (this is the equivalent of UniqueResult method for entity objects);
Open method to retrieve results using a cursor. In this case, the method is the same for either projected or non-projected queries. The only different is the type of object that will be returned.
When using queries with projections, the object returned is a TCriteriaResult object. The TCriteriaResult is an object that has a default property Values which you can use to retrieve the values using an index:
var
Results: TObjectList<TCriteriaResult>;
MyCriteria: TCriteria<TCustomer>;
FirstValueInFirstRecord: Variant;
begin
MyCriteria := ObjectManager1.Find<TCustomer>;
// <snip> Build the query and add projections to it
// Retrieve projected results
Results := MyCriteria.ListValues;
FirstValueInFirstRecord := Results[0].Values[0];
Alternatively, you can find the value by name. The name is specified by the alias of projections. If no alias is specified, an internal autonumerated name is used.
uses {...}, Aurelius.Criteria.Projections,
Aurelius.CriteriaBase, Aurelius.Criteria.Linq;
var
Results: TObjectList<TCriteriaResult>;
begin
Results := Manager.Find<TTC_Estimate>
.CreateAlias('Customer', 'c')
.Select(TProjections.ProjectionList
.Add(TProjections.Sum('EstimateNo').As_('EstimateSum'))
.Add(TProjections.Group('c.Name'))
)
.Add(Linq['c.Name'].Like('M%'))
.OrderBy('EstimateSum')
.ListValues;
EstimateSum := Results[0].Values['EstimateSum'];
CustomerName := Results[0].Values[1]; // no alias specified for c.Name
end;
If the property doesn't exist, an error is raised. TCriteriaResult also has an additional HasProp method for you to check if the specified value exists. The following code contains the TCriteriaResult public methods and properties.
TCriteriaResult = class
public
function HasProp(PropName: string): boolean;
property PropNames[Index: integer]: string read GetPropName;
property Values[Index: integer]: Variant read GetValue; default;
property Values[PropName: string]: Variant read GetPropValue; default;
property Count: integer read GetCount;
end;
It's important to note that TCriteriaResult objects are not managed by the TObjectManager, so the retrieved objects must be destroyed. When using ListValues method to retrieve the results, the returned list is a TObjectList<T> object that already has its OwnsObjects property set to true. So destroyed the list should be enough. When using UniqueValue or Open methods, you must be sure to destroy the TCriteriaResult objects.
Filtering Results
You can narrow the result of your query by adding filter expressions to your query. This is similar to the WHERE clause in an SQL statement. Any expression object descends from TCustomCriterion, and you can use Add or Where methods to add such objects to the query:
uses {...}, Aurelius.Criteria.Linq;
Results := Manager1.Find<TCustomer>
.Where(Linq['Name'] = 'Mia Rosenbaum')
.List;
You can add more than one expression to the query. The expression will be combined with an "and" operator, which means only objects which satisfies all conditions will be returned (Add and Where methods are equivalents):
Results := Manager1.Find<TCustomer>
.Add(Linq['Country'] = 'US')
.Add(Linq['Age'] = 30)
.List;
Or you can simply use logical operators directly:
Results := Manager1.Find<TCustomer>
.Where((Linq['Country'] = 'US') and (Linq['Age'] = 30))
.List;
In the topics below you will find all the advanced features for building queries in Aurelius.
Creating Expressions Using Linq
To filter results you must add TCustomCriterion objects to the query
object. The TCustomCriterion objects just represent a conditional
expression that the object must satisfy to be included in the result. To
create such objects, you can use the Linq factory. It's declared in
Aurelius.Criteria.Linq
unit:
uses Aurelius.Criteria.Linq
Linq variable is just a helper object with several methods (Equal, GreaterThan, etc.) that you can use to easily create TCustomCriterion instances. For example, the following lines produce the same object and will result in the same query:
Criterion := TSimpleExpression.Create(TPropertyProjection.Create('Age'), 30, eoGreater));
Criterion := Linq.GreaterThan('Age', 30);
Criterion := Linq['Age'] > 30;
You can always use the default indexed property passing the property name to start using queries. That will represent a property projection:
Linq[<propertyname>]
Note that in all the methods listed here, the method can receive a string (representing a property name) or a projection. See TProjections.Prop for more details.
You can use Linq to create the following conditions:
Equals
Retrieves a condition where the specified property (or projection) value must be equals to the specified value or projection. You can use Equals or Eq method, or the = operator, they all do the same.
Example - Return customers where Name property is equal to "Mia Rosenbaum".
Results := Manager.Find<TCustomer>
.Where(Linq['Name'] = 'Mia Rosenbaum')
.List;
Another way to write it:
Results := Manager.Find<TCustomer>
.Where(Linq.Eq('Name', 'Mia Rosenbaum'))
.List;
Greater Than
Retrieves a condition where the specified property (or projection) value must be greater than the specified value. You can use either GreatherThan or Gt method, or the > operator, they all do the same.
Example - Return customers where Birthday property is greater than 10-10-1981 and less than 02-02-1986.
Results := Manager.Find<TCustomer>
.Where(
(Linq['Birthday'] > EncodeDate(1981, 10, 10))
and (Linq['Birthday'] < EncodeDate(1986, 2, 2))
)
.List;
Another way to write it:
Results := Manager.Find<TCustomer>
.Add(Linq.GreaterThan('Birthday', EncodeDate(1981, 10, 10)))
.Add(Linq.LessThan('Birthday', EncodeDate(1986, 2, 2)))
.List;
Greater Than or Equals To
Retrieves a condition where the specified property (or projection) value must be greater than or equals to the specified value. You can use either GreaterOrEqual or Ge method, or >= operator, they all do the same.
Example - Return customers where Birthday property is greater than or equals to 10-10-1981 and less than or equals to 02-02-1986.
Results := Manager.Find<TCustomer>
.Where(
(Linq['Birthday'] >= EncodeDate(1981, 10, 10))
and (Linq['Birthday'] <= EncodeDate(1986, 2, 2))
)
.List;
Another way to write it:
Results := Manager.Find<TCustomer>
.Add(Linq.GreaterOrEqual('Birthday', EncodeDate(1981, 10, 10)))
.Add(Linq.LessOrEqual('Birthday', EncodeDate(1986, 2, 2)))
.List;
Less Than
Retrieves a condition where the specified property (or projection) value must be less than the specified value. You can use either LessThan or Lt method, or < operator, they all do the same.
Example - Return customers where Birthday property is greater than 10-10-1981 and less than 02-02-1986.
Results := Manager.Find<TCustomer>
.Where(
(Linq['Birthday'] > EncodeDate(1981, 10, 10))
and (Linq['Birthday'] < EncodeDate(1986, 2, 2))
)
.List;
Another way to write it:
Results := Manager.Find<TCustomer>
.Add(Linq.GreaterThan('Birthday', EncodeDate(1981, 10, 10)))
.Add(Linq.LessThan('Birthday', EncodeDate(1986, 2, 2)))
.List;
Less Than Or Equals To
Retrieves a condition where the specified property (or projection) value must be less than or equals to the specified value. You can use either LessOrEqual or Le method, or <= operator, they all do the same.
Example - Return customers where Birthday property is greater than or equals to 10-10-1981 and less than or equals to 02-02-1986.
Results := Manager.Find<TCustomer>
.Where(
(Linq['Birthday'] >= EncodeDate(1981, 10, 10))
and (Linq['Birthday'] <= EncodeDate(1986, 2, 2))
)
.List;
Another way to write it:
Results := Manager.Find<TCustomer>
.Add(Linq.GreaterOrEqual('Birthday', EncodeDate(1981, 10, 10)))
.Add(Linq.LessOrEqual('Birthday', EncodeDate(1986, 2, 2)))
.List;
Like
Retrieves a condition where the specified property (or projection) value contains the text specified. It's equivalent to the LIKE operator in SQL statements. You must specify the wildchar % in the value condition.
Example - Return customers where Sex property is not null, and Name starts with "M".
Results := Manager.Find<TCustomer>
.Where(
Linq['Sex'].IsNotNull and Linq['Name'].Like('M%')
)
.List;
Another write to write it:
Results := Manager.Find<TCustomer>
.Where(
Linq.IsNotNull('Sex') and Linq.Like('Name', 'M%')
)
.List;
ILike
Retrieves a condition where the specified property (or projection) value contains the text specified, case insensitive. It's equivalent to the ILIKE operator in SQL statements. You must specify the wildchar % in the value condition.
Example - Return customers where Sex property is not null, and Name starts with "M" (or "m", it's case insensitive).
Results := Manager.Find<TCustomer>
.Where(
Linq['Sex'].IsNotNull and Linq['Name'].ILike('M%')
)
.List;
Another write to write it:
Results := Manager.Find<TCustomer>
.Where(
Linq.IsNotNull('Sex') and Linq.ILike('Name', 'M%')
)
.List;
IsNull
Retrieves a condition where the specified property (or projection) contains a null value.
Example - Return customers where Sex property is female, or Sex property is null.
Results := Manager.Find<TCustomer>
.Where(
(Linq['Sex'] = tsFemale) or Linq['Sex'].IsNull
)
.List;
Another way to write it:
Results := Manager.Find<TCustomer>
.Where(
Linq.Eq('Sex', tsFemale) or Linq.IsNull('Sex')
)
.List;
IsNotNull
Retrieves a condition where the specified property (or projection) does not contain a null value.
Example - Return customers where Sex property is not null, and Name starts with "M".
Results := Manager.Find<TCustomer>
.Where(
Linq['Name'].Like('M%') and Linq['Sex'].IsNotNull
)
.List;
Another way to write it:
Results := Manager.Find<TCustomer>
.Where(
Linq.Like('Name', 'M%') and Linq.IsNotNull('Sex')
)
.List;
Identifier Equals
Retrieves a condition where the identifier of the specified class is equal to a value. This is very similar to using Equals, but in this case you don't need to specify the property name - Aurelius already knows that you are referring to the Id. Also, for composite id's, you can provide an array of variant for all the values of the composite id, the query will compare all table columns belonging to the composite id with all values provided in the array of variant.
Example - Return customer where identifier is equal to 1.
Customer := Manager.Find<TCustomer>
.Where(Linq.IdEq(1))
.UniqueResult;
Example - Using composite id: return person where last name is "Smith" and first name is "John" (considering that the id of this class is made of properties LastName and FirstName):
var
Id: Variant;
Person: TPerson;
begin
Id := VarArrayCreate([0, 1], varVariant);
Id[0] := 'Smith'; // last name
Id[1] := 'John'; // first name
Person := Manager.Find<TPerson>
.Where(Linq.IdEq(Id))
.UniqueResult;
Sql Expression
Creates a custom SQL expression condition. Use this for total flexibility, if you might fall into a situation where regular query filters provided by Aurelius are not enough. The SQL you provide in this expression must conform with the underlying database syntax. Aurelius doesn't perform any syntax conversion (except aliases and parameters, see below).
Example - Return customer where database column CUSTOMER_NAME is equal to "Mia Rosenbaum".
Results := Manager.Find<TCustomer>
.Where(Linq.Sql('A.CUSTOMER_NAME = ''Mia Rosenbaum'''))
.List;
Aliases
Note that since the SQL expression will be just injected in the SQL statement, you must be sure it will work. In the example above, the exact alias name ("A") and field name ("CUSTOMER_NAME") needed to be included.
In order to prevent you from knowing which alias to use (which is especially tricky when Aurelius need to use joins in SQL statement), you can use placeholders (aliases) between curly brackets. Write the name of the property inside curly brackets and Aurelius will translate it into the proper alias.fieldname format according to the SQL.
The following example does the same as the previous one, but instead of using the field name directly, you use the name of property TCustomer.Name.
Results := Manager.Find<TCustomer>
.Where(Linq.Sql('{Name} = ''Mia Rosenbaum'''))
.List;
When querying associations, you can also prefix the property name with the alias of the association (see how to query Associations):
Results := Manager.Find<TCustomer>
.CreateAlias('Country', 'c')
.Where(Linq.Sql('{c.Name} = ''United States'''))
.List;
Note that when you use subcriteria, the context of the property in curly brackets will be the subcriteria class. The following query is equivalent to the previous one:
Results := Manager.Find<TCustomer>
.SubCriteria('Country')
.Where(Linq.Sql('{Name} = ''United States'''))
.List<TCustomer>;
Parameters
You can also use parameters in the Sql projection, to avoid having to use specific database syntax for literals. For example, if you want to compare a field with a date value, you would need to specify a date literal with a syntax that is compatible with the database SQL syntax. To avoid this, Aurelius allows you to use parameters in Sql expression. You can use up to two parameters in each expression. The parameters must be indicated by a question mark ("?") and the type of parameters must be provided in a generic parameter for the Sql method.
Example - using one parameter of type TSex:
Results := Manager.Find<TCustomer>
.Where(Linq.Sql<TSex>('{Sex} IN (?)', TSex.tsFemale))
.List;
Example - using two parameters of type TDate:
Results := Manager.Find<TEstimate>
.Where(
Linq.Sql<TDate, TDate>(
'{IssueDate} IS NULL OR (({IssueDate} > ?) AND ({IssueDate} < ?))',
EncodeDate(1999, 2, 10), EncodeDate(2000, 8, 30))
)
.List;
Starts With
Retrieves a condition where the specified property (or projection) string value must start with the specified value.
Example - Return customers where Name property starts with "Mia".
Results := Manager.Find<TCustomer>
.Where(Linq['Name'].StartsWith('Mia'))
.List;
Alternative way to write it:
Results := Manager.Find<TCustomer>
.Where(Linq.StartsWith('Name', 'Mia'))
.List;
Ends With
Retrieves a condition where the specified property (or projection) string value must end with the specified value.
Example - Return customers where Name property ends with "Junior".
Results := Manager.Find<TCustomer>
.Where(Linq['Name'].EndsWith('Junior'))
.List;
Alternative way to write it:
Results := Manager.Find<TCustomer>
.Where(Linq.EndsWith('Name', 'Junior'))
.List;
Contains
Retrieves a condition where the specified property (or projection) string value must contain the specified value.
Example - Return customers where Name property contains "Walker".
Results := Manager.Find<TCustomer>
.Where(Linq['Name'].Contains('Walker'))
.List;
Alternative way to write it:
Results := Manager.Find<TCustomer>
.Where(Linq.Contains('Name', 'Walker'))
.List;
In
The actual method name is "_In". Checks if the value of a specified property (or projection) belongs to a set of predefined values. The predefined set of values can be of type string, integer or enumerated.
Example - Return invoices where Status property is either Approved or Rejected, and year of issue date is 2016 or 2014.
Results := Manager.Find<TInvoice>
.Add(Linq['Status']._In([TInvoiceStatus.Approved, TInvoiceStatus.Rejected]))
.Add(Linq['IssueDate'].Year._In([2016, 2014])
.List;
Alternative way to write it:
Results := Manager.Find<TInvoice>
.Add(Linq._In('Status', [TInvoiceStatus.Approved, TInvoiceStatus.Rejected]))
.Add(Linq._In('IssueDate', [2016, 2014]);
.List;
Comparing Projections
In most of the examples of filtering in queries, we used just the name of the property and compare it to a value. For example:
Results := Manager.Find<TCustomer>
.Where(Linq['Name'] = 'Mia')
.List;
But Aurelius query is much powerful than that. Linq['Name'] actually represents a projection, and you can use any projection in any expression you want.
This gives you great flexibility since you can create many different types of projections and compare them. For example, you can compare two projections, as follows.
Example - Return orders where cancelation date is greater than shipping date:
Results := Manager.Find<TOrder>
.Where(Linq['CancelationDate'] > Linq['ShippingDate'])
.List;
Or you can even use complex expressions. We can for example change the above query to bring all orders where the year of cancelation date is the same as the year of shipping date:
Results := Manager.Find<TOrder>
.Where(Linq['CancelationDate'].Year = Linq['ShippingDate'].Year)
.List;
Associations
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 three options: use subcriteria, aliases or rely on auto alias mechanism.
Auto alias
You can search associated objects by simply referencing their subproperties from the association property. Support you have a TEstimate
class which has a Customer
property of type TCustomer
. The customer entity, in turn, has a Name
property. You can query for all invoices which customer name starts with M
this way:
Results := Manager.Find<TEstimate>
.Where(Linq['Customer.Name'].StartsWith('M'))
.List;
Aurelius will automatically create a subcriteria with alias Customer
. Nested associations are possible by adding more of them separated by dots. To query for invoices where country of customer is United States:
Results := Manager.Find<TEstimate>
.Where(Linq['Customer.Country.Name'] = 'United States')
.List;
This feature was introduced in version 5.6 and is turned on by default.
Using aliases
Instead of using auto alias, you can explicitly 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 List<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 object (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 can that by using FetchEager
method passing the name of the association to be loaded eagerly:
Results := Manager.Find<TEstimate>
.FetchEager('Customer')
.List;
You can also use dots (.
) to provide subproperties to be loaded eagerly:
Results := Manager.Find<TEstimate>
.FetchEager('Customer.Country')
.List;
Alternatively, you can also pass TFetchMode.Eager
as the third parameter of CreateAlias
or second parameter of SubCriteria
method:
Results := Manager.Find<TEstimate>
.CreateAlias('Customer', 'ct', TFetchMode.Eager)
.List;
With either of the queries 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.
Ordering Results
You can order the results by any property of the class being query, or by a property of an association of the class. Just use either AddOrder or OrderBy methods of the TCriteria object. You must define name of the property (or projection) being ordered, and if the order is ascending or descending. See examples below.
Example - Retrieve customers ordered by Name.
Results := Manager.Find<TCustomer>
.Where(Linq['Name'].Like('M%'))
.OrderBy('Name')
.List;
Same query using AddOrder (instead of OrderBy):
Results := Manager.Find<TCustomer>
.Where(Linq['Name'].Like('M%'))
.AddOrder(TOrder.Asc('Name'))
.List;
You can also use association aliases in orderings.
Example - Retrieve all estimates which IssueDate is not null, ordered by customer name in descending order (second parameter in OrderBy specify ascending/descending - false means descending, it's true by default).
Results := Manager.Find<TEstimate>
.CreateAlias('Customer', 'c')
.Where(Linq['IssueDate'].IsNotNull)
.OrderBy('c.Name', false)
.List;
Same query using AddOrder:
Results := Manager.Find<TEstimate>
.CreateAlias('Customer', 'c')
.Add(Linq['IssueDate'].IsNotNull)
.AddOrder(TOrder.Desc('c.Name'))
.List;
If you need to order by complex expressions, it's recommended that you use a Alias projection for it. In the example below, the order refers to the EstimateSum alias, which is just an alias for the sum expression.
Results := Manager.Find<TEstimate>
.CreateAlias('Customer', 'c')
.Select(TProjections.ProjectionList
.Add(TProjections.Sum('EstimateNo').As_('EstimateSum'))
.Add(TProjections.Group('c.Name'))
)
.Where(Linq['c.Name'].Like('M%'))
.AddOrder(TOrder.Asc('EstimateSum'))
.ListValues;
Projections
You can make even more advanced queries in Aurelius by using projections. For example, instead of selecting pure object instances (TCustomer for example) you can perform grouping, select sum, average, a function that retrieves the year of a date, among others. There is a formal definition for projection, but you can think of a projection just as an expression that returns a value, for example, a call to Sum function, a literal, or the value of a property.
Usually you will use projections to return specific/calculated values instead of objects, or to perform complex condition expressions (to retrieve all customers where the year of birthday column is equal to 1999).
For example, the following query retrieves the number of invoices for the year 2013 and illustrates how to use projections in both select and where parts of the query.
uses {...}, Aurelius.Criteria.Linq, Aurelius.Criteria.Projections;
TotalInvoicesFor2013 := Manager.Find<TInvoice>
.Select(TProjections.Count('Id'))
.Where(Linq['IssueDate'].Year = 2013)
.UniqueValue;
The following topics explain in details what projections are and how you can use them.
Projections Overview
Any projection object descends from TProjection class. To make a query return projections (calculated values) instead of entities, use the SetProjections or Select method.
The example below calculates the sum of all estimates where the customer name beings with "M".
uses {...}, Aurelius.Criteria.Linq, Aurelius.Criteria.Projections;
Value := Manager.Find<TEstimate>
.Select(TProjections.Sum('EstimateNo'))
.CreateAlias('Customer', 'c')
.Where(Linq['c.Name'].Like('M%'))
.UniqueValue;
You can only have a single projection specified for the select part of the query. If you call SetProjections or Select method twice in a single query, it will replace the projection specified in the previous call. If you want to specify multiple projections, using a projection list:
Query over estimates, retrieving the sum of EstimateNo, grouped by customer name.
Results := Manager.Find<TEstimate>
.CreateAlias('Customer', 'c')
.Select(TProjections.ProjectionList
.Add(TProjections.Sum('EstimateNo'))
.Add(TProjections.Group('c.Name'))
)
.ListValues;
Note that when using projections, the query can instances of
the queried class or list of TCriteriaResult objects, which you can use to retrieve the
projection values. The result depends if you use ListValues
/UniqueValue
or List
/UniqueResult
methods
to retreive the results.
The Select method is exactly the same as the method SetProjections, it's just included as an option so it looks better in some queries.
In all the examples above, the TProjection objects added to the criteria were created using the TProjections factory class. The TProjections is just a helper class with several class methods that you can use to easily create TProjection instances.
You can also use projections in the where clause to add complex queries. Many of the condition expressions you can use in a query can compare projections, for example:
YoungCustomers := Manager.Find<TCustomer>
.Where(Linq['Birthday'].Year > 2000)
.List;
will list all customers which year of birth is greather than 2000.
Creating Projections Using TProjections
Any projection you want to use is a TProjection object. To create such
objects, you can use the TProjections factory class. It's declared in
Aurelius.Criteria.Projections
unit.
uses Aurelius.Criteria.Projections
The TProjections class is just a helper class with several class methods (Sum, Group, etc.) that you can use to easily create TProjection instances. For example, the following lines produce the same object:
Projection := TAggregateProjection.Create('sum', TPropertyProjection.Create('Total'));
Projection := TProjections.Sum('Total');
You can use TProjections to create the following projections:
Aggregated Functions
There are several methods in TProjections class that create a projection that represents an aggregated function over a property value (or a projection). Available methods are:
- Sum: Calculates the sum of values
- Min: Retrieves the minimum value
- Max: Retrieves the maximum value
- Avg: Calculates the average of all values
- Count: Retrieves the number of objects that satisfy the condition
Example - Calculates the sum of all estimates where the customer name begins with "M".
Value := Manager.Find<TEstimate>
.Select(Linq['EstimateNo'].Sum)
.CreateAlias('Customer', 'c')
.Where(Linq['c.Name'].Like('M%'))
.UniqueValue;
Alternative way to write the same query:
Value := Manager.Find<TEstimate>
.Select(TProjections.Sum('EstimateNo'))
.CreateAlias('Customer', 'c')
.Where(Linq['c.Name'].Like('M%'))
.UniqueValue;
Prop
Creates a projection that represents the value of a property. In most cases, you will use that projection transparently, because the following constructions will return such projection for you:
Linq['Name']
Linq['IssueDate']
Alternatively there are overloads for almost all methods in Linq and TProjection classes that accept a string instead of a projection. The string represents a property name and internally all it does is to create a property projection using Prop method.
The example below illustrates how Prop method can be used.
The following two queries are equivalent, both retrieve the name of the customers ordered by the Name:
Results := Manager.Find<TCustomer>
.Select(Linq['Name'])
.AddOrder(TOrder.Asc(Linq['Name']))
.ListValues;
{...}
Results := Manager.Find<TCustomer>
.Select(TProjections.Prop('Name'))
.AddOrder(TOrder.Asc(TProjections.Prop('Name')))
.ListValues;
The following three queries are also equivalent:
Results := Manager.Find<TCustomer>
.Add(Linq.Eq('Name', 'Mia Rosenbaum'))
.List;
{...}
Results := Manager.Find<TCustomer>
.Add(Linq.Eq(TProjections.Prop('Name'), 'Mia Rosenbaum'))
.List;
{...}
Results := Manager.Find<TCustomer>
.Add(Linq.Eq(Linq['Name'], 'Mia Rosenbaum'))
.List;
Limiting the selected properties
The property project is the only projection you can use and still retrieve entity objects instead of TCriteriaResult
. If your query has only property projections, then you can still use List
(or UniqueResult
) to retrieve entities:
Customers := Manager.Find<TCustomer>
.Select(TProjections.ProjectionList
.Add(Linq['Name'])
.Add(Linq['Birthday'])
)
.List;
In the above example, the query will return a list of TCustomer
objects. But they underlying SQL statement will be optimized to only include columns Name
and Birthday
, and the returned TCustomer
object will only have such properties set.
Warning
Be careful when using entities which properties are only partial set. If you use such objects to do a full update, the unreturned empty properties will be set back to the database and respective database columns will be erased.
You can also use aliased projections to specify subproperties to return, for both associations and many-valued associations
Estimates := Manager.Find<TEstimate>
.Select(TProjections.ProjectionList
.Add(Linq['EstimateNo'])
.Add(Linq['Customer'])
.Add(Linq['Customer.Name'])
.Add(Linq['Customer.Sex'])
.Add(Linq['Customer.Country'])
.Add(Linq['Customer.Country.Id'])
)
.FetchEager('Customer')
.FetchEager('Customer.Country')
.List
Group
Creates a projection that represents a group. This is similar to the GROUP BY clause in an SQL statement, but the difference is that you don't need to set a Group By anywhere - you just add a grouped projection to the projection list and Aurelius groups is automatically.
The query below retrieves the sum of EstimateNo grouped by customer name. The projected values are the EstimateNo sum, and the customer name. Since the customer name is already one of the selected projections and it's grouped, that's all you need - you don't have to add the customer name in some sort of Group By section.
Results := Manager.Find<TEstimate>
.CreateAlias('Customer', 'c')
.Select(TProjections.ProjectionList
.Add(TProjections.Sum('EstimateNo'))
.Add(TProjections.Group('c.Name'))
)
.ListValues;
Add
Adds two numeric values.
Example:
Results := Manager.Find<TInvoice>
.Select(Linq['Total'] + Linq['Additional'])
.List;
Another way to write it:
Results := Manager.Find<TInvoice>
.Select(Linq.Add(Linq['Total'], Linq['Additional']))
.List;
Subtract
Subtracts two numeric values.
Example:
Results := Manager.Find<TInvoice>
.Select(Linq['Total'] - Linq['Discount'])
.List;
Another way to write it:
Results := Manager.Find<TInvoice>
.Select(Linq.Subtract(Linq['Total'], Linq['Discount']))
.List;
Multiply
Multiplies two numeric values.
Example:
Results := Manager.Find<TInvoiceItem>
.Select((Linq['Quantity'] * Linq['UnitaryValue']).As_('TotalValue'))
.List;
Another way to write it:
Results := Manager.Find<TInvoiceItem>
.Select(Linq.Multiply(Linq['Quantity'], Linq['UnitaryValue']).As_('TotalValue'))
.List;
Divide
Divides two numeric values.
Example:
Results := Manager.Find<TInvoiceItem>
.Select((Linq['Total'] / Linq['Quantity']).As_('ItemValue'))
.List;
Another way to write it:
Results := Manager.Find<TInvoiceItem>
.Select(Linq.Multiply(Linq['Total'], Linq['Quantity']).As_('ItemValue'))
.List;
Aurelius ensures consistency among different databases. When performing division between two integer values, many databases truncate the result and return an integer, rounded value. For example, 7 / 5 results 1. Some databases do not behave that way.
In Aurelius, the division operator performs with Pascal behavior: the result is a floating point operation, even when dividing two integer values. Thus, 7 / 5 will return 1.4, as expected.
Condition
Creates a conditional projection. It works as an If..Then..Else
clause,
and it's equivalent to the "CASE..WHEN..ELSE" expression in SQL.
Example - Retrieves the customer name and a string value representing the customer sex. If sex is tsFemale, return "Female", if it's tsMale return "Male". If it's null, then return "Null".
Results := Manager.Find<TCustomer>
.Select(TProjections.ProjectionList
.Add(Linq['Name'])
.Add(TProjections.Condition(
Linq['Sex'].IsNull,
Linq.Literal<string>('Null'),
TProjections.Condition(
Linq['Sex'] = tsMale,
Linq.Literal<string>('Male'),
Linq.Literal<string>('Female')
)
)
)
)
.ListValues;
Literal<T>
Creates a constant projection. It's just a literal value of scalar type T. Aurelius automatically translates the literal into the database syntax. The Literal<T> method is different from Value<T> in the sense that literals are declared directly in the SQL statement, while values are declared as parameters and the value is set in the parameter value.
Example - Retrieves some literal values.
Results := Manager.Find<TCustomer>
.Select(TProjections.ProjectionList
.Add(Linq.Literal<string>('Test'))
.Add(Linq.Literal<Currency>(1.53))
.Add(Linq.Literal<double>(3.14e-2))
.Add(Linq.Literal<integer>(100))
.Add(Linq.Literal<TDateTime>(Date1))
)
.ListValues;
Another example using Condition projection:
Results := Manager.Find<TCustomer>
.Select(TProjections.ProjectionList
.Add(Linq['Name'])
.Add(TProjections.Condition(
Linq['Sex'].IsNull,
Linq.Literal<string>('Null'),
TProjections.Condition(
Linq['Sex'] = tsMale,
Linq.Literal<string>('Male'),
Linq.Literal<string>('Female')
)
)
)
)
.ListValues;
Value<T>
Creates a constant projection. It's just a value of scalar type T. It works similar to Literal<T> method, the difference is that literals are declared directly in the SQL statement, while values are declared as parameters and the value is set in the parameter value.
ProjectionList
Retrieves a list of projections. It's used when setting the projection of a query using Select or SetProjections method. Since only one projection is allowed per query, you define more than one projections by adding a projection list. This method returns a TProjectionList object which defines the Add method that you use to add projections to the list.
Example - Creates a projection list with two projections: Sum of EstimateNo and Customer Name.
Results := Manager.Find<TEstimate>
.CreateAlias('Customer', 'c')
.Select(TProjections.ProjectionList
.Add(TProjections.Sum('EstimateNo'))
.Add(TProjections.Group('c.Name'))
)
.ListValues;
Alias
Associates an alias to a projection so it can be referenced in other parts of criteria. Currently only orderings can refer to aliased projections. It's useful when you need to use complex expressions in the order by clause - some databases do not accept such expressions, so you can just reference an existing projection in the query, as illustrated below.
Example - Retrieve all estimates grouped by customer name, ordered by the sum of estimates for each customer.
Results := Manager.Find<TTC_Estimate>
.CreateAlias('Customer', 'c')
.Select(TProjections.ProjectionList
.Add(Linq['EstimateNo'].Sum.As_('EstimateSum'))
.Add(Linq['c.Name'].Group)
)
.Add(Linq['c.Name'].Like('M%'))
.AddOrder(TOrder.Asc('EstimateSum'))
.ListValues;
Alternatively you can create aliased projections using the TProjections.Alias method of any simple projection. This query does the same as the previous query:
Results := Manager.Find<TTC_Estimate>
.CreateAlias('Customer', 'c')
.Select(TProjections.ProjectionList
.Add(TProjections.Alias(TProjections.Sum('EstimateNo'), 'EstimateSum'))
.Add(TProjections.Group('c.Name'))
)
.Add(Linq.Like('c.Name', 'M%'))
.AddOrder(TOrder.Asc('EstimateSum'))
.ListValues;
Sql Projection
Creates a projection using a custom SQL expression. Use this for total flexibility, if you might fall into a situation where regular projections provided by Aurelius are not enough. The SQL you provide in this expression must conform with the underlying database syntax. Aurelius doesn't perform any syntax conversion (except aliases, see below).
Example - Return specific projections.
Results := Manager.Find<TCustomer>
.CreateAlias('Country', 'c')
.Select(TProjections.ProjectionList
.Add(Linq['Id'].As_('Id'))
.Add(TProjections.Sql<string>('A.CUSTOMER_NAME').As_('CustName'))
.Add(TProjections.Sql<double>('{id} * 2').As_('DoubleId'))
.Add(TProjections.Sql<integer>('{c.id} * 2').As_('DoubleCountryId'))
)
.ListValues;
Note that since the SQL expression will be just injected in the SQL statement, you must be sure it will work. In the example above, the exact alias name ("A") and field name ("CUSTOMER_NAME") needed to be included in projection "CustName".
In order to prevent you from knowing which alias to use (which is especially tricky when Aurelius need to use joins in SQL statement), you can use placeholders (aliases) between curly brackets. Write the name of the property inside curly brackets and Aurelius will translate it into the proper alias.fieldname format according to eh SQL. In the previous example, projections "DoubleId" and "DoubleCountryId" use placeholders that will be replaced by the proper "Alias.ColumnName" syntax corresponding to the referenced property. "{id}" refers to property TCustomer.Id, while "{c.Id}" refers to property TCustomer.Country.Id.
The generic parameter in the Sql method must indicate the type returned by the Sql projection.
Year
Retrieves the year of a specified date/time value.
Example:
.Where(Linq['IssueDate'].Year = 2013)
Year method creates a projection that extracts the year of a date value. Equivalent code:
.Where(Linq.Eq(TProjections.Year('IssueDate'), 2013))
Month
Retrieves the month of a specified date/time value.
Example:
.Where(Linq['IssueDate'].Month = 11)
Month method creates a projection that extracts the month of a projection with a date value. Equivalent code:
.Where(Linq.Eq(TProjections.Month('IssueDate'), 11))
Day
Retrieves the day of a specified date.
Example:
.Where(Linq['IssueDate'].Day = 31)
Day method creates a projection that extracts the day of a projection with a date value. Equivalent code:
.Where(Linq.Eq(TProjections.Day('IssueDate'), 31))
Hour
Retrieves the hour of a specified date/time value.
Example:
.Where(Linq['AppointmentTime'].Hour > 12)
Hour method creates a projection that extracts the hour of a projection with a date/time value. Equivalent code:
.Where(Linq.Gt(TProjections.Hour('AppointmentTime'), 12))
Minute
Retrieves the minute of a specified date/time value.
Example:
.Where(Linq['AppointmentTime'].Minute > 45)
Minute method creates a projection that extracts the number of minutes of a projection with a date/time value. Equivalent code:
.Where(Linq.Gt(TProjections.Minute('AppointmentTime'), 45))
Second
Retrieves the second of a specified date/time value.
Example:
.Where(Linq['AppointmentTime'].Second > 45)
Second method creates a projection that extracts the number of seconds of a projection with a date/time value. Equivalent code:
.Where(Linq.Gt(TProjections.Second('AppointmentTime'), 45))
Upper
Converts a string value to upper case.
Example:
.Where(Linq['Name'].Upper = 'JACK')
Equivalent code:
.Where(Linq.Eq(TProjections.Upper('Name'), 'JACK'))
Lower
Converts a string value to lower case.
Example:
.Where(Linq['Name'].Lower = 'jack')
Equivalent code:
.Where(Linq.Eq(TProjections.Lower('Name'), 'jack'))
Concat
Concatenates two strings.
Example:
.Select(Linq['FirstName'].Concat(' ').Concat(Linq['LastName']))
Equivalent code:
.Select(Linq.Concat(Linq.Concat(Linq['FirstName'], ' - '), Linq['LastName']))
Aurelius does not ensure cross-database consistent when it comes to null handling. Oracle treats null as empty strings, so if your expression is concatenating a null value, result will be null in all databases except Oracle, where it will concatenate the two strings normally (considering null as empty string).
Length
Returns the number of characters in a string.
Example:
// Return entities which name has less than 10 characters
.Where(Linq['Name'].Length < 10)
Equivalent code:
// Return entities which name has less than 10 characters
.Where(Linq.LessThan(TProjections.Length('Name'), 10))
ByteLength
Returns the number of bytes in a binary property.
Example:
// Return entities which Photo has less than 65536 bytes
.Where(Linq['Photo'].ByteLength < 65536)
Equivalent code:
// Return entities which Photo has less than 65536 bytes
.Where(Linq.LessThan(TProjections.ByteLength('Photo'), 65536))
Substring
Returns a substring of the specified string.
Example:
// Return the first 5 characters of the name
.Select(Linq['Name'].Substring(1, 5))
First parameter is the start index of substring, 1-based. Thus, 1 represents the first character of the string, 2 the second, etc. Second parameter is the length of substring to be returned.
Equivalent code which passes the projection/property name as the first parameter:
// Return the first 5 characters of the name
.Select(TProjections.Substring('Name', 1, 5))
Position
Returns the index value of the first character in a specified substring that occurs in a given string.
Example:
// Return entities only if the position of "@" character
// in the EMailAddress property is higher than 5
.Where(Linq['EmailAddress'].Position('@') > 5)
The parameter is the substring to be searched for. The result is the index of the first occurrence of the string, 1-based. In other words, if the substring occurs in the first character, the result is 1. If the substring is not found, result is 0.
Equivalent code which passes the projection/property name as the first parameter:
// Return entities only if the position of "@" character
// in the EMailAddress property is higher than 5
.Where(Linq.GreaterThan(TProjections.Position('@', 'EmailAddress'), 5)))
SqlFunction
Calls a custom SQL function. Aurelius provides many cross-database projection functions like Year, Upper, Concat, etc. But in case you want to call an specific database function, or create your own, you can use SqlFunction to call it.
For example, if you want to use PostgreSQL's Unaccent function:
.Where(Linq.ILike(
Linq.SqlFunction('unaccent', nil, Linq['Name']),
Linq.SqlFunction('unaccent', nil, Linq.Value<string>(SomeValue))
))
First parameter is the name of the function.
Second parameter is the value type (PTypeInfo) returned by the function. If the type of function result is the same of the type of the parameter, you can simply pass nil. In this example, Name is a string field, and unaccent also returns a string value, so you can just use nil.
If the function is not registered by default in Aurelius system (which is the case for Unaccent function), Aurelius will raise an error when trying to execute the query, informing that function could not be found. You need to register the function in the specific Dialect using RegisterFunction:
uses
{...}, Aurelius.Sql.Interfaces, Aurelius.Sql.Register, Aurelius.Sql.Functions;
TSQLGeneratorRegister.GetInstance.GetGenerator('POSTGRESQL')
.RegisterFunction('unaccent', TSimpleSQLFunction.Create('unaccent'));
Polymorphism
Since Aurelius supports inheritance using different inheritance strategies, queries are also polymorphic. It means that if you query over a specified class, you might receive objects of that class, or even descendants of that class.
For example, suppose you have a class hierarchy this way:
TAnimal = class
TBird = class(TAnimal);
TMammal = class(TAnimal);
TDog = class(TMammal);
TCat = class(TMammal);
When you perform a query like this:
Results := Manager.Find<TMammal>
.Add(Linq['Name'].Like('T%'))
.List;
You are asking for all mammals which Name begins with "T". This means all mammals, dogs and cats. So in the resulted object list, you might receive instances of TMammal, TDog or TCat classes. Aurelius does it automatically for you, regardless on the inheritance strategy, i.e. if all classes are being saved in the same table or each class is being saved in a different table. Aurelius will be sure to filter out records representing animals and birds, and retrieve only the mammals (including dogs and cats).
You can safely rely on polymorphism with Aurelius in every query, and also of course, when saving and updating objects.
Paging Results
Aurelius provides methods the allows you to limit query results at server level. It's the equivalent of "SELECT TOP" or "SELECT..LIMIT" that some databases use (note this is just an analogy, TMS Aurelius will make sure to build the proper SQL statement for each database according to the supported syntax).
You can limit the number of objects retrieved by using the Take method of TCriteria object:
Results := Manager.Find<TCustomer>
.OrderBy('Name')
.Take(50)
.List;
The previous code will retrieve the first 50 TCustomer objects, ordered by name. Using Take(0) will return an empty result. Using Take(-1) is equivalent to not using Take method at all, meaning all records will be returned. Values below -2 (including) are not allowed and might cause errors.
You can skip the first N objects retrieved by using Skip method:
Results := Manager.Find<TCustomer>
.OrderBy('Name')
.Skip(10)
.List;
The previous code will retrieve customers ordered by name, but will omit the first 10 customers from the list. Using Skip(0) is equivalent to not using Skip method at all, since it means skipping no records. Negative values are not allowed and might cause errors.
Although you can use Skip and Take methods without specifying an order, it often doesn't make sense.
Skip and Take methods are often used for paging results, i.e., returning objects belonging to an specific page. The following code exemplifies how to return objects belonging to the page PageIdx, with PageSize objects in each page:
Results := Manager.Find<TCustomer>
.OrderBy('Name')
.Skip(PageIdx * PageSize)
.Take(PageSize)
.List;
Removing Duplicated Objects
Sometimes a query might result in duplicated objects. The following query is an example of such queries:
Results := Manager.Find<TInvoice>
.CreateAlias('Items', 'i')
.Add(Linq['i.Price'] = 20)
.OrderBy('InvoiceNo')
.List;
The above criteria will look for all invoices which have any item with price equals to 20. Just like in SQL, this query is doing a "join" between the invoice and invoice items. This means that if an invoice has two or more items with price equals to 20, the same TInvoice object will be returned more than once in the result list.
If that's not what you want, and you just list all invoices matching the specified criteria, without duplicates, just use RemoveDuplicatedEntities to your criteria:
Results := Manager.Find<TInvoice>
.CreateAlias('Items', 'i')
.Add(Linq['i.Price'] = 20)
.OrderBy('InvoiceNo')
.RemovingDuplicatedEntities
.List;
And this will bring distinct invoices. This feature is usually useful when you want to filter objects by a criteria applied to many-valued associations, like in the example above, which might return duplicated results.
Please note that the removal of duplicated objects is done at client level by Aurelius framework, not at database level, so performance might be not good with queries that result too many records.
Cloning a Criteria
Aurelius TCriteria object also has a Clone method you can use to clone the criteria. This might useful when you want to reuse the criteria multiple times and maybe slightly change from the base criteria:
MyCriteria := Manager.Find<TCustomer>
.Where(Linq['Name'] = 'Mia');
ClonedCriteria := MyCriteria.Clone;
ClonedCriteria.OrderBy('Id');
MyResults := MyCriteria.List<TCustomer>;
ClonedResults := ClonedCriteria.List<TCustomer>;
Refreshing Results
When performing a query, Aurelius will keep exisiting entities in the cache. For example, if your query returns two TCustomer objects with ID's 10 and 15, if there are already instances of those objects in the manager, they will be kept in the cache with existing properties and will not be updated.
Alternatively, you can use Refreshing method when building the criteria to tell Aurelius that you want existing objects to be objects with current database values.
The query below will bring all TCustomer objects which year of birthday is 1999. If any of those customers are already in the manager, their properties will still be updated with values retrieved from the database:
MyCriteria := Manager.Find<TCustomer>
.Where(Linq['Birthday'].Year = 1999)
.Refreshing
.List;
Note that when refreshing an object that has lazy-loaded associations, the proxy is updated and not immediately loaded. When the associated object (or list) is then read, Aurelius will try to load the objects and if they are in the cache, they will not be updated. This means if you have lazy-loaded association, specially lists, and you want the list objects to be refreshed themselves, you should iterate through the list and call Refresh for each item manually.