Playing around with Queries
As a first approach you can use the code from the last chapter for querying the data:
PersistenceManager pm = new PersistenceManager();
NDOQuery<Employee> q = new NDOQuery<Employee>(pm);
var l = q.Execute();
foreach (Employee e in l)
{
Console.WriteLine(e.FirstName + " " + e.LastName + ", " + e.Address.City);
foreach (Travel t in e.Travels)
{
Console.WriteLine(" Travel: " + t.Purpose);
foreach(Country country in t.Countries)
Console.WriteLine(" Country:" + country.Name);
}
}
This results in the following output:
John Doe, Dreamcity
Travel: Inhouse-Training
Country:Germany
Country:USA
Travel: TechEd 2006
Country:Germany
Press any key to continue
But you can also search for countries:
PersistenceManager pm = new PersistenceManager();
NDOQuery<Country> q = new NDOQuery<Country>(pm);
var l = q.Execute();
foreach (Country country in l)
{
Console.WriteLine(country.Name);
foreach (Travel t in country.Travels)
Console.WriteLine(" Travel: " + t.Purpose);
}
This results in the output:
Germany
Travel: Inhouse-Training
Travel: TechEd 2006
USA
Travel: Inhouse-Training
Press any key to continue
Now wouldn’t it be nice to see not only the list of travels but also who attended them? Up to now we did not provide a way to navigate from Travel objects to Employee objects. To make up for it, insert the following code into the Travel class:
// In the Travel class
[NDORelation]
Employee employee;
public Employee Employee
{
get { return employee; }
set { employee = value; }
}
Now the relation between the classes Employee and Travel becomes bidirectional. But in contrast to the relation pair between Country and Travel, in this case we have a 1:n relation combined with a 1:1 relation in the opposite direction (or, in other words: a relation with Cardinality n combined with a relation with cardinality 1). Please note that the two relations can share the same foreign key (IDEmployee), residing in the Travel table. That means that you don’t have to adapt the database for this new relation.
Now change the query code like that:
PersistenceManager pm = new PersistenceManager();
Query q = pm.NewQuery(typeof(Country));
IList l = q.Execute();
foreach (Country country in l)
{
Console.WriteLine(country.Name);
foreach (Travel t in country.Travels)
Console.WriteLine(" Employee: " + t.Employee.LastName + ", Travel: " + t.Purpose);
}
Starting the application results in this output:
Germany
Employee: Doe, Travel: Inhouse-Training
Employee: Doe, Travel: TechEd 2006
USA
Employee: Doe, Travel: Inhouse-Training
Press any key to continue
Now we want to go further into detail. Let’s query all Employees who traveled to USA. For that we create the following query:
Employee.QueryHelper qh = new Employee.QueryHelper();
Query q = pm.NewQuery(typeof(Employee), qh.travels.countries.name + " LIKE 'USA'");
Console.WriteLine(q.GeneratedQuery);
IList l = q.Execute();
foreach(Employee e in l)
Console.WriteLine(e.FirstName + " " + e.LastName);
This time we use the second parameter of NewQuery() to indicate a condition. The condition is formulated using a QueryHelper object. The nested QueryHelper classes make sure that names used in queries are checked during compilation so name changes do not cause runtime errors. Note that the condition could have been written as a string like that:
Query q = pm.NewQuery(typeof(Employee), "travels.countries.name LIKE 'USA'");
A detailed description of the QueryHelpers can be found in the chapter QueryHelper Classes. We navigate through the relation chain with the names of the member variables implementing the relations. Finally we reach the class with the persistent field to evaluate in the condition expression. Except for the names given by the QueryHelper classes, the syntax conforms to the WHERE clauses in SQL.
The property GeneratedQuery returns the resulting SQL query that is displayed on the console together with the query result. The output looks like this:
SELECT [Employee].* FROM [Employee], [Travel], [relCountryTravel], [Country] WHERE
([Travel].[IDEmployee] = [Employee].[ID] AND [relCountryTravel].[IDTravel] =
[Travel].[ID] AND [relCountryTravel].[IDCountry] = [Country].[ID]) AND [Country].[Name] LIKE
'USA'
John Doe
Press any key to continue
Such a SQL query is really not the type of code we like to type by hand – NDO makes it very easy to express queries spanning over multiple relations. Note: The SQL code given back by q.GeneratedQuery is a simplified representation of the query. An exact representation of all queries is delivered by the NDO logging capabilities.
This solution is part of the tutorial samples. You can find the tutorial source code in the Tutorial folder beneath your NDO installation folder. The code up to here is placed in the Directory NDOTravelExpenses-Step 4.
The next chapter is about inheritance.