3
Using ICriteria
In the previous chapter you learned how the Hibernate Query Language (HQL) works and how to implement most of the APIs that make up the library. Most important, you learned how to combine queries so that fewer round-trips can be made to the database, thereby improving the program's performance. This chapter covers using ICriteria, which is a more method-based query API versus the SQL-like queries of IQuery. In this chapter, you learn about the following:
- CreateCriteria, CreateMultiCriteria, DetachedCriteria, QueryOver, and Futures
- Implementing paging
- Lambda expressions
- FetchMode, Restrictions, and data transfer objects (DTOs)
INTRODUCTION
The ICriteria interface is a feature-rich, method-based query API. When I first started using NHibernate, I chose to use ICriteria. As a programmer, the implementation of ICritera seemed more programmatic than HQL — meaning that instead of using dot (.) notation in a string, ICriteria provides methods, classes, and properties for creating the query. A large number of ICriteria methods are accessible via the Session to execute a query against the database, and pulling the methods and properties together seems almost intuitive.
Figure 3-1 provides a graphical representation of the methods and their corresponding execution methods for using the ICriteria interface, which implements the same functionality described in the previous chapter for IQuery. This chapter describes these methods and provides examples demonstrating how to use them.
Figure 3-2 shows the ICriteria, CriteriaImpl, and DetachedCriteria class diagram. ICriteria is the interface that is used to manage and create your query objects. When the Session.CreateCriteria() method is called, it returns an implementation of the ICriteria interface. The methods used to execute an attached query can be found in the NHibernate.Impl namespace within the CriteriaImpl.cs file. The DetachedCriteria class is found in the NHibernate.Criterion namespace.
The GuitarStore solution used in this book does not require the creation of complex SQL queries. The query shown in Listing 3-1 indicates that it is a little more complicated to create a joined query using ICritera versus IQuery. That's because it is required to programmatically create aliases prior to execution. Whenever you have to dynamically build and then execute a query that joins many tables together, it requires some relatively sophisticated code.
LISTING 3-1: Creating an ICriteria and HQL JOIN query
ICriteria:
ICriteria criteria = Session.CreateCriteria<Inventory>(“i”)
.CreateAlias(“Guitar”, “g”)
.SetProjection(Projections.ProjectionList()
.Add(Projections.Property(“i.Builder”))
.Add(Projections.Property(“i.Model”))
.Add(Projections.Property(“i.Price”))
.Add(Projections.Property(“i.Id”))
.Add(Projections.Property(“g.Type”)));
IQuery/HQL:
select i.Builder, i.Model, i.Price, i.Id, g.Type
from Inventory i, Guitar g
where i.TypeId = g.Id
Creating the JOIN using ICritera requires a number of different methods and classes to build the query. On the other hand, an HQL query is very similar to the way an SQL query is written. Notice that, as shown in Listing 3-2, ICriteria uses an inner JOIN, whereas IQuery uses a standard equijoin.
LISTING 3-2: ICriteria vs. HQL JOIN SQL
ICriteria:
SELECT this_.BUILDER as y0_,
this_.MODEL as y1_,
this_.PRICE as y2_,
this_.ID as y3_,
g1_.TYPE as y4_
FROM INVENTORY this_ inner JOIN GUITAR g1_ on this_.TYPEID=g1_.ID
IQuery/HQL:
select inventory0_.BUILDER as col_0_0_,
inventory0_.MODEL as col_1_0_,
inventory0_.PRICE as col_2_0_,
inventory0_.ID as col_3_0_,
guitar1_.TYPE as col_4_0_
from INVENTORY inventory0_, GUITAR guitar1_
where inventory0_.TYPEID=guitar1_.ID
In both of the preceding SQL queries, the data returned is identical and there is no noticeable difference in performance. Nonetheless, there are some things about the ICriteria interface that are worth mentioning:
- It is less vulnerable to SQL injection.
- Parameter data types are validated using the type value within the mapping file.
- Data can be retrieved dynamically or strongly typed.
- You can use it when the exact structure of the query is not known at design time (i.e., you are not sure what is needed until runtime).
- Methods are used to create data queries, meaning you can't actually see the query.
- It implements fetch strategies by default.
- You can define parameters explicitly using methods such as Restrictions.Eq(“propertyName”, value).
Defining parameters explicitly in a query using ICriteria is very straightforward. Simply add a Restrictions class, in combination with a property name and the value, and the NHibernate-generated SQL query will include a WHERE clause containing the restriction. The Restrictions class is found in the NHibernate.Criterion namespace. An example is shown in Listing 3-3.
LISTING 3-3: Restrictions Class example
public IList CriteriaInventoryList(string builder)
{
using (ITransaction transaction = Session.BeginTransaction())
{
try
{
ICriteria criteria = Session.CreateCriteria<Inventory>()
.SetProjection(Projections.ProjectionList()
.Add(Projections.Property(“Builder”))
.Add(Projections.Property(“Model”))
.Add(Projections.Property(“Price”))
.Add(Projections.Property(“Id”)))
.Add(Restrictions.Eq(“Builder", builder));
transaction.Commit();
return criteria.List();
}
catch (Exception ex)
{
transaction.Rollback();
throw;
}
}
}
Generated SQL:
SELECT this_.BUILDER as y0_,
this_.MODEL as y1_,
this_.PRICE as y2_,
this_.ID as y3_
FROM INVENTORY this_
WHERE this_.BUILDER = @p0;
@p0 = ‘FENDER’ [Type: String (4000)]
The CriteriaInventoryList() method found within the NHibernateInventory class received “FENDER” as a parameter. The parameter was added to the CreateCriteria() method via the Add() method and Restrictions class. The NHibernate-generated SQL query contains a WHERE clause that restricts the results of Inventory where the Builder equals “FENDER”.
Another interesting aspect of ICriteria is that it implements fetch strategies by default, whereas using HQL via the IQuery interface employs lazy loading of data by default. Fetching strategies are put into action on collections initialized within a solution's mapping and class files — for example, the IList<Inventory> Inventory collection within the Guitar class. Placing a fetch=”JOIN” attribute into the bag element of the Guitar.hbm.xml file will result in the eager fetching of the Inventory collection. Listing 3-4 shows the Guitar.hbm.xml file containing the fetch attribute.
LISTING 3-4: Eager loading Inventory mapping example using fetch=JOIN
<bag name=“Inventory” table=“INVENTORY” fetch=“JOIN”> <key column=“TYPEID” /> <one-to-many class=“NHibernate.GuitarStore.Common.Inventory” /> </bag>
Then, if the method in Listing 3-5 retrieves the Guitar class, the resulting NHibernate-generated SQL query joins the GUITAR and INVENTORY tables together in a single statement that loads the data from both tables into the Guitar class.
LISTING 3-5: Eager loading the Guitar object using ICriteria
public IList<T> ExecuteCriteria<T>() where T : class
{
using (ITransaction transaction = Session.BeginTransaction())
{
try
{
ICriteria criteria = Session.CreateCriteria<T>();
transaction.Commit();
return criteria.List<T>();
}
catch (Exception ex)
{
transaction.Rollback();
throw;
}
}
}
Generated SQL:
SELECT this_.ID as ID0_1_,
this_.TYPE as TYPE0_1_,
inventory2_.TYPEID as TYPEID3_,
inventory2_.ID as ID3_,
inventory2_.ID as ID1_0_,
inventory2_.TYPEID as TYPEID1_0_,
inventory2_.BUILDER as BUILDER1_0_,
inventory2_.MODEL as MODEL1_0_,
inventory2_.QOH as QOH1_0_,
inventory2_.COST as COST1_0_,
inventory2_.PRICE as PRICE1_0_,
inventory2_.RECEIVED as RECEIVED1_0_
FROM GUITAR this_
left outer JOIN INVENTORY inventory2_ on
this_.ID=inventory2_.TYPEID
Figure 3-3 shows the List<Guitar> collection, which contains the eagerly loaded Inventory data. No additional database query is needed to access all Inventory for the guitar type.
However, HQL ignores the fetch attribute's value and loads everything lazy by default. Therefore, when executing the HQL query with the Guitar class, shown next in Listing 3-6, only the Guitar is retrieved, not the IList<Inventory> Inventory collection, regardless of the fetch value. HQL will wait until the data is accessed before retrieving the collection.
LISTING 3-6: HQL ignoring fetch=”JOIN” and using lazy loading instead
public IList<T> ExecuteHQL<T>(string hqlQuery)
{
using (ITransaction transaction = Session.BeginTransaction())
{
IQuery query = Session.CreateQuery(hqlQuery);
return query.List<T>();
}
}
Generated SQL with fetch=JOIN:
select guitar0_.ID as ID0_,
guitar0_.TYPE as TYPE0_
from GUITAR guitar0_
If at a later point the Inventory collection within the Guitar class is requested, an additional SELECT statement is generated to retrieve the Inventory for the specific guitar type.
Note that in two of the preceding listings, Listing 3-1 and Listing 3-3, no “HQL-like” or “SQL-like” query is created when using ICriteria. This may be a positive for programmers who are not strong in SQL-like languages. If this is the case, then using methods, instead of joining tables and forming string-based queries, may be the best option. However, be aware that when using this method-based query API, the overview and visibility of the data being retrieved can easily be lost when the queries become complex, for example, if you need to JOIN several tables, using restrictions, aliases, and projections that use the relationships built within the mapping files.
NOTE All NHibernate query constructions are stated in terms of the persistent object model, rather than the database. This means that restrictions are always based on the property values in the mapped object, never in terms of the database column name.
Understanding the Stateless Session
In Chapter 2, “Using HQL,” the ISession interface was used to manage the state of the entities loaded from the database. In this chapter, the IStatelessSession interface is used when data is loaded for display purposes only. If a result set is only used to retrieve data for display in a DataGrid, i.e. read-only, then the IStatelessSession can be used to control memory consumption. It is more difficult to recover from an exception that occurs using a stateful Session, which uses the built-in change-tracking capabilities of NHibernate, than when using the IStatelessSession (stateless) interface. Recovering from an exception when using the IStatelessSession requires that you close the stateless Session and reopen a new one using the OpenStatelessSession() method found in the SessionFactory class. Before implementing this interface, however, you should consider the following two limitations:
- Caching is not utilized.
- Lazy loading is not supported.
A majority, but not all, of the methods in this chapter use the StatelessSession interface to show that it exposes many of the same methods as the statefull Session. In most cases you would use the statefull Session, as that is where most of NHibernate's power exists. Use the StatelessSession interface for simple display-only or for bulk data manipulation.
WORKING WITH CREATECRITERIA
Six different CreateCriteria methods are available within the ISession interface, as shown in Listing 3-7, each one having a different definition. Two of them support generics; the others expect an alias, a System.Type, or the name of the entity as a string.
LISTING 3-7: CreateCriteria methods
ICriteria CreateCriteria<T>() where T : class; ICriteria CreateCriteria<T>(string alias) where T : class; ICriteria CreateCriteria(System.Type persistentClass); ICriteria CreateCriteria<T>( System.Type persistentClass, string alias); ICriteria CreateCriteria(string entityName); ICriteria CreateCriteria(string entityName, string alias);
In this section, the CreateCriteria<T>() method is used in most of the examples. To implement the CreateCriteria() method in the NHibernate.GuitarStore class library and the GuitarStore WPF project, you will perform the following:
- Program a CreateCriteria() method that returns a dynamic list of all Inventory.
- Reuse the BuildDataTable() method created in Chapter 2 to convert the dynamic result set into a DataTable.
- Bind the result from the BuildDataTable() to a DataGrid.
- Create a CreateCriteria() method that accepts and uses a guitar type as a parameter to constrain the result set.
- Implement paging.
Open the GuitarStore solution and within the NHibernate.GuitarStore project, double-click the NHibernateInventory.cs file. Add the method shown in Listing 3-8, which returns a dynamic list of all Inventory using the CreateCriteria() method.
LISTING 3-8: CreateCriteria method returning project inventory
public IList GetDynamicInventoryList()
{
using (ITransaction transaction = StatelessSession.BeginTransaction())
{
try
{
ICriteria criteria = StatelessSession.CreateCriteria<Inventory>()
.SetProjection(Projections.ProjectionList()
.Add(Projections.Property(“Builder”))
.Add(Projections.Property(“Model”))
.Add(Projections.Property(“Price”))
.Add(Projections.Property(“Id”)))
.AddOrder(Order.Asc(“Builder”));
transaction.Commit();
return criteria.List();
}
catch (Exception ex)
{
transaction.Rollback();
throw;
}
}
}
Generated SQL:
SELECT this_.BUILDER as y0_,
this_.MODEL as y1_,
this_.PRICE as y2_,
this_.ID as y3_
FROM INVENTORY this_
ORDER BY this_.BUILDER asc
Next, in the GuitarStore WPF project, open the MainWindow.xml.cs file and review the BuildDataTable() method created in Chapter 2, found in Listing 2-4.
To retrieve the Inventory data from the database, use the GetDynamicInventoryList() method created in Listing 3-8. Then bind the result of the BuildDataTable() method to the DataGrid, as shown in Listing 3-9. Place the code found in Listing 3-9 into the PopulateDataGrid() method created in Chapter 2.
LISTING 3-9: Retrieving a dynamic result set and binding it to the DataGrid
private void PopulateDataGrid() { NHibernateInventory nhi = new NHibernateInventory(); List<string> fields = new List<string> { “Builder”, “Model”, “Price”, “Id” }; IList GuitarInventory = nhi.GetDynamicInventoryList(); dataGridInventory.ItemsSource = BuildDataTable(fields, GuitarInventory).DefaultView; if (GuitarInventory != null) { dataGridInventory.Columns[3].Visibility = System.Windows.Visibility.Hidden; } }
The preceding method simply selects the entire inventory from the INVENTORY table and populates the DataGrid with the data. Next, add a new method to the NHibernateInventory class within the NHibernate.GuitarStore project that accepts a guitar type as a parameter and returns only the guitars of that type. Listing 3-10 shows the method that accepts a parameter and returns the matching data.
LISTING 3-10: CreateCriteria returning inventory by guitar Type
public IList GetDynamicInventoryListByType(Guid Id)
{
using (ITransaction transaction = StatelessSession.BeginTransaction())
{
try
{
ICriteria criteria = StatelessSession.CreateCriteria<Inventory>()
.SetProjection(Projections.ProjectionList()
.Add(Projections.Property(“Builder”))
.Add(Projections.Property(“Model”))
.Add(Projections.Property(“Price”))
.Add(Projections.Property(“Id”)))
.Add(Restrictions.Eq(“TypeId”, Id))
.AddOrder(Order.Asc(“Builder”));
transaction.Commit();
return criteria.List();
}
catch (Exception ex)
{
transaction.Rollback();
throw;
}
}
}
Generate SQL:
SELECT this_.BUILDER as y0_,
this_.MODEL as y1_,
this_.PRICE as y2_,
this_.ID as y3_
FROM INVENTORY this_
WHERE this_.TYPEID = @p0
ORDER BY this_.BUILDER asc;
@p0 = 471c5b3f-19da-4fcb-8e9f-48dd17a00a3d [Type: Guid (0)]
Finally, modify the comboBoxGuitarTypes_SelectionChanged() method to use the preceding method when a guitar type is selected from the ComboBox found on the GuitarStore WPF window. The code shown in Listing 3-11 captures the selected guitar type from the ComboBox and passes the Id to the GetDynamicInventoryListByType() method, builds the DataTable with the results, and populates the DataGrid.
LISTING 3-11: CreateCriteria method from the SelectionChanged method
private void comboBoxGuitarTypes_SelectionChanged(object sender,
SelectionChangedEventArgs e)
{
try
{
dataGridInventory.ItemsSource = null;
Guitar guitar = (Guitar)comboBoxGuitarTypes.SelectedItem;
Guid guitarType = new Guid(guitar.Id.ToString());
NHibernateInventory nhi = new NHibernateInventory();
IList GuitarInventory = nhi.GetDynamicInventoryListByType(guitarType);
List<string> fields = new List<string>
{
“Builder”, “Model”, “Pricey”, “Id”
};
dataGridInventory.ItemsSource =
BuildDataTable(fields, GuitarInventory).DefaultView;
if (GuitarInventory != null)
{
dataGridInventory.Columns[3].Visibility = System.Windows.Visibility.Hidden;
}
}
catch (Exception ex)
{
labelMessage.Content = ex.Message;
}
}
Set the GuitarStore project as the startup project and run it. When the guitar type is selected from the ComboBox, the resulting list in the DataGrid will be restricted to the selection.
Implementing Paging
It is good practice to reduce the number of rows returned from the database, especially if the table being selected from has hundreds of thousands of rows. The current implementation selects all the rows from the INVENTORY table, which is okay in this situation because there are relatively few rows to retrieve at this early stage in this program's life cycle.
The ICriteria interface provides two methods for implementing paging. The names are identical to those in the IQuery interface: SetMaxResults() and SetFirstResult(). The SetMaxResults() method accepts an integer as a parameter that determines the maximum number of rows to be returned with the associated query.
The SetFirstResult() method also accepts an integer as a parameter and is used to determine the first row to be returned from the query. For example, if the value for SetFirstResult() is 500 and the value for SetMaxResults() is 50, then rows 500 to 550 will be returned when the query is executed.
NOTE When used with ICriteria, the SetFirstResult() method expects a 1 as the first row. However, when used with IQuery, the value is expected to be 0.
A method that retrieves the data from the INVENTORY table has already been implemented, as shown previously in Listing 3-8. To implement paging, create a new method in the NHibernateInventory.cs file of the NHibernate.Guitar project that accepts two integer parameters, one for max result and one for first result. Then associate the SetFirstResult() and SetMaxResults() methods to the CreateCriteria method. Listing 3-12 shows how this method would look once completed.
LISTING 3-12: ICriteria paging method
public IList GetDynamicInventoryList(int maxResult, int firstResult)
{
using (ITransaction transaction = StatelessSession.BeginTransaction())
{
try
{
ICriteria criteria = StatelessSession.CreateCriteria<Inventory>()
.SetProjection(Projections.ProjectionList()
.Add(Projections.Property(“Builder”))
.Add(Projections.Property(“Model”))
.Add(Projections.Property(“Price”))
.Add(Projections.Property(“Id”)))
.AddOrder(Order.Asc(“Builder”))
.SetMaxResults(maxResult)
.SetFirstResult(firstResult);
transaction.Commit();
return criteria.List();
}
catch (Exception ex)
{
transaction.Rollback();
throw;
}
}
}
Generate SQL: (where max=25 and first=1)
SELECT TOP (@p0)
y0_,
y1_,
y2_,
y3_
FROM (SELECT this_.BUILDER as y0_,
this_.MODEL as y1_,
this_.PRICE as y2_,
this_.ID as y3_,
ROW_NUMBER()
OVER(ORDER BY this_.BUILDER) as __hibernate_sort_row
FROM INVENTORY this_) as query
WHERE query.__hibernate_sort_row > @p1
ORDER BY query.__hibernate_sort_row;
@p0 = 25 [Type: Int32 (0)],
@p1 = 1 [Type: Int32 (0)]
Next, create a method that selects a count of all retrievable records from the INVENTORY table. The row count provides a context that indicates where the data contained in the current DataGrid is in relation to all data stored on the table. Add the contents of Listing 3-13, to the NHibernateInventory.cs file of the NHibernate.GuitarStore project.
LISTING 3-13: CriteriaRowCount() method
public int CriteriaRowCount<T>() where T : class
{
using (ITransaction transaction = StatelessSession.BeginTransaction())
{
try
{
ICriteria rowCount = StatelessSession.CreateCriteria<T>();
return Convert.ToInt32(
rowCount.SetProjection(
Projections.RowCount()).UniqueResult());
}
catch (Exception ex)
{
transaction.Rollback();
throw;
}
}
}
Generated SQL:
SELECT count(*) as y0_
FROM INVENTORY this_
Before implementing the code in Listing 3-12 and Listing 3-13 in the GuitarStore WPF project, add the code segment shown in Listing 3-14 to the Main() method of the NHibernate.GuitarStore console application and test to ensure that the expected results are returned.
LISTING 3-14: Testing CreateCriteria paging and row count from the console
IList list25 = nhi.GetDynamicInventoryList(25, 1);
System.Console.WriteLine(“GetDynamicInventoryList(25, 1): ” +
list25.Count.ToString() + “ results”);
int count26 = nhi.CriteriaRowCount<Inventory>();
System.Console.WriteLine(“CriteriaRowCount<Inventory>(): ” +
count26.ToString() + “ results”);
Now open the MainWindow.xaml.cs file in the GuitarStore WPF project and modify the existing PopulateDataGrid() method with the following code in Listing 3-15.
LISTING 3-15: PopulateDataGrid() using ICriteria paging functionality
public int FirstResult = 1;
public int MaxResult = 25;
public int totalCount = 0;
private void PopulateDataGrid()
{
NHibernateInventory nhi = new NHibernateInventory();
List<string> fields = new List<string>
{
“Builder”, “Model”, “Price”, “Id”
};
IList GuitarInventory = nhi.GetDynamicInventoryList(MaxResult, FirstResult);
int inventoryCount = nhi.CriteriaRowCount<Inventory>();
dataGridInventory.ItemsSource =
BuildDataTable(fields, GuitarInventory).DefaultView;
if (GuitarInventory != null)
{
dataGridInventory.Columns[3].Visibility = System.Windows.Visibility.Hidden;
}
labelPaging.Content = “Retrieved ” + FirstResult.ToString() +
“ to ” + (FirstResult + GuitarInventory.Count).ToString() +
“ of ” + inventoryCount.ToString();
totalCount = inventoryCount;
}
Notice that the stoplight in the following figure, Figure 3-4, is red. This is because three queries are executed using three round-trips to the database during the loading of the GuitarStore WPF window. The first loads the guitar types into the ComboBox, the second is the CreateCriteria() query that retrieves the Inventory for the DataGrid, and the third retrieves the total row count on the INVENTORY table, for paging.
For many programs, three queries to load a window are acceptable. The implementation of the QueryCounter, which exists in the Utils class of the NHibernate.GuitarStore project (where 1 = green, 2 = yellow, and 3 = red), is used only as an example. The acceptable number of queries per process or transaction will vary according to the program, of course. In this case, three query executions are equated with red.
In the next section, where the CreateMultiCriteria() method is implemented, the Inventory retrieval and the row count are combined into a single database round-trip; therefore, the stoplight will be yellow instead of red after the GuitarStore WPF window is loaded.
IMPLEMENTING CREATEMULTICRITERIA
One of the many nice features found in NHibernate is the capability to batch multiple queries together and execute them using a single trip to the database. Consider that one of the more expensive actions in regard to a database transaction is the time it takes to get from the computer or server where the program is hosted to the database server and back. If you limit the number of round-trips required, you can realize a noticeable performance improvement.
In this section, the focus is on batching two queries together using the CreateMultiCriteria() method. To implement the CreateMultiCriteria() method in the NHibernate.GuitarStore class library and the GuitarStore WPF project, you need to perform the following:
- Program a CreateMultiCriteria() method that returns the row count as an integer and a dynamic list of all Inventory as an out parameter.
- Reuse the BuildDataTable() method created in Chapter 2 to convert the dynamic result set into a DataTable.
- Bind the result from the BuildDataTable() method to a DataGrid.
- Create a CreateMultiCriteria()method that accepts and uses a guitar type as a parameter to constrain the result set.
- Implement paging.
Open the GuitarStore solution and within the NHibernate.GuitarStore project, double-click the NHibernateInventory.cs file. The CreateMultiCriteria() method provides the capability to batch queries together. Add the method shown in Listing 3-16, which batches together two queries using the CreateMultiCriteria()method. Note that the ISession is used here. The IStatelessSession interface does not expose an instance of the CreateMultiCriteria() method.
LISTING 3-16: Example of the CreateMultiCriteria() method
public IList GetDynamicInventoryList(int maxResult, int firstResult,
out int totalCount)
{
using (ITransaction transaction = Session.BeginTransaction())
{
try
{
ICriteria criteria = Session.CreateCriteria<Inventory>()
.SetProjection(Projections.ProjectionList()
.Add(Projections.Property(“Builder”))
.Add(Projections.Property(“Model”))
.Add(Projections.Property(“Price”))
.Add(Projections.Property(“Id”)))
.AddOrder(Order.Asc(“Builder”))
.SetMaxResults(maxResult)
.SetFirstResult(firstResult);
ICriteria rowCount = Session.CreateCriteria(typeof(Inventory))
.SetProjection(Projections.Count(Projections.Id()));
IMultiCriteria mCriteria = Session.CreateMultiCriteria()
.Add(“criteria”, criteria)
.Add(“count”, rowCount);
IList countResult = (IList)mCriteria.GetResult(“count”);
totalCount = Convert.ToInt32(countResult[0]);
return (IList)mCriteria.GetResult(“criteria”);
}
catch (Exception ex)
{
transaction.Rollback();
throw;
}
}
}
Generated SQL:
select count(*) as col_0_0_
from INVENTORY inventory0_;
SELECT TOP (@p0)
col_0_0_,
col_1_0_,
col_2_0_,
col_3_0_
FROM (select inventory0_.BUILDER as col_0_0_,
inventory0_.MODEL as col_1_0_,
inventory0_.PRICE as col_2_0_,
inventory0_.ID as col_3_0_,
ROW_NUMBER()
OVER(ORDER BY inventory0_.BUILDER)as __hibernate_sort_row
from INVENTORY inventory0_) as query
WHERE query.__hiber;
@p0 = 25 [Type: Int32 (0)],
@p1 = 1 [Type: Int32 (0)];
The preceding method implements two CreateCriteria() methods. The first method creates the query to return the projected dataset from the INVENTORY table. The second selects the total number of rows that exist on the table. This count value is displayed in the paging label to identify where the user is in relation to the total number of available records.
The two CreateCriteria() methods are bound together using the Add() method of the CreateMultiCriteria() method, which returns an IMultiCriteria interface. A call to the GetResult() execution method results in the two generated CreateCriteria() SQL queries being batched and executed via a single round-trip to the database.
Next, open the MainWindow.xaml.cs file in the GuitarStore WPF project and implement the GetDynamicInventoryList() method, which uses the CreateMultiCriteria() NHibernate method. Listing 3-17 is an example of how it can be implemented using the PopulateDataGrid() method.
LISTING 3-17: Using CreateMultiCriteria() from populateGridData()
private void PopulateDataGrid()
{
NHibernateInventory nhi = new NHibernateInventory();
List<string> fields = new List<string>
{
“Builder”, “Model”, “Price”, “Id”
};
IList GuitarInventory = null;
int inventoryCount =
nhi.GetDynamicInventoryList(MaxResult, FirstResult, out GuitarInventory);
dataGridInventory.ItemsSource =
BuildDataTable(fields, GuitarInventory).DefaultView;
if (GuitarInventory != null)
{
dataGridInventory.Columns[3].Visibility = System.Windows.Visibility.Hidden;
}
labelPaging.Content = “Retrieved ” + FirstResult.ToString() +
“ to ” + (FirstResult + GuitarInventory.Count).ToString() +
“ of ” + inventoryCount.ToString();
totalCount = inventoryCount;
}
Run the GuitarStore WPF program. As shown in Figure 3-5, the stoplight is yellow, reflecting the query to populate the ComboBox with guitar types and the batched query containing the inventory data and the row count.
This is one of the ways to confirm that the queries have been batched together and executed using a single round-trip to the database. The other way is to select the View SQL button, which enables you to look at the most recently executed NHibernate-generated SQL query.
As already mentioned, not all DBMSs support the batching and execution of queries together. Check the NHibernate.Driver namespace for your driver and look for the SupportMultipleQueries() method. If you find it, chances are good that the DBMS you currently use supports this capability. If it is not in the driver class, then most likely you will not be able to implement this technology in your programs.
UNDERSTANDING DETACHEDCRITERIA
Sometimes a requirement dictates that a query be built dynamically. There are many ways this could be accomplished. One possibility is to implement the DetachedCriteria class, which is found in the NHibernate.Criterion namespace. This class allows the passing of a DetachedCriteria object between different methods, each of which could perform some actions and modify the object based on the business requirements.
For example, the method in Listing 3-18 is used to search for a specific model of guitar that may exist in the inventory.
LISTING 3-18: A DetachedCriteria example
using NHibernate.Criterion;
public IList DetachedSearch(string searchParameter)
{
using (ITransaction transaction = StatelessSession.BeginTransaction())
{
DetachedCriteria detachedCriteria= DetachedCriteria.For<Inventory>()
.Add(Restrictions.Like(“Model”, searchParameter));
return detachedCriteria.GetExecutableCriteriaS(StatelessSession).List();
}
}
Generated SQL:
SELECT this_.ID as ID1_0_,
this_.TYPEID as TYPEID1_0_,
this_.BUILDER as BUILDER1_0_,
this_.MODEL as MODEL1_0_,
this_.QOH as QOH1_0_,
this_.COST as COST1_0_,
this_.PRICE as PRICE1_0_,
this_.RECEIVED as RECEIVED1_0_
FROM INVENTORY this_
WHERE this_.MODEL like @p0;
@p0 = ‘%L%’ [Type: String (4000)]
To implement the preceding method in the GuitarStore WPF program, you need to perform the following:
- Reuse the search TextBox and Button controls added in Chapter 2.
- Modify the buttonSearch_Click() method to use the DetachedSearch() method.
Open the MainWindow.xaml.cs file and modify the buttonSearch_Click() method so that it resembles the code in Listing 3-19.
LISTING 3-19: Implementing DetachedCriteria in the GuitarStore WPF program
private void buttonSearch_Click(object sender, RoutedEventArgs e) { NHibernateInventory nhi = new NHibernateInventory(); IList GuitarInventory = nhi.DetachedSearch(“%” + textBoxSearch.Text + “%”); dataGridInventory.ItemsSource = GuitarInventory; if (GuitarInventory != null && GuitarInventory.Count > 0) { dataGridInventory.Columns[0].Visibility = System.Windows.Visibility.Hidden; dataGridInventory.Columns[1].Visibility = System.Windows.Visibility.Hidden; dataGridInventory.Columns[8].Visibility = System.Windows.Visibility.Hidden; dataGridInventory.Columns[9].Visibility = System.Windows.Visibility.Hidden; } labelPaging.Content = “”; buttonNext.IsEnabled = false; }
The preceding method will return a list of guitar inventory based on the value entered in the TextBox control.
Next, to dynamically modify the DetachedCriteria, you want to add a method named SellFirst() that returns the list of inventory ordered by Received and where the Cost is greater than €1000. To do this, open the NHibernateInventory.cs file found in the NHibernate.GuitarStore project and add the method shown in Listing 3-20.
LISTING 3-20: SellFirst() to modify a DetachedCriteria
public static void SellFirst(DetachedCriteria criteria)
{
decimal Amount = 1000;
criteria.Add(Restrictions.Ge(“Cost”, Amount));
criteria.AddOrder(Order.Asc(“Received”));
}
Then modify the DetachedSearch() method to call the SellFirst() method, which passes the detached criteria instance for modification. The code is shown in Listing 3-21.
LISTING 3-21: Modifying the DetachedCriteria
public IList DetachedSearch(string searchParameter)
{
using (ITransaction transaction = StatelessSession.BeginTransaction())
{
DetachedCriteria detachedCriteria = DetachedCriteria.For<Inventory>()
.Add(Restrictions.Like(“Model”, searchParameter));
SellFirst(detachedCriteria);
return detachedCriteria.GetExecutableCriteria(StatelessSession).List();
}
}
Generated SQL:
SELECT this_.ID as ID1_0_,
this_.TYPEID as TYPEID1_0_,
this_.BUILDER as BUILDER1_0_,
this_.MODEL as MODEL1_0_,
this_.QOH as QOH1_0_,
this_.COST as COST1_0_,
this_.PRICE as PRICE1_0_,
this_.RECEIVED as RECEIVED1_0_
FROM INVENTORY this_
WHERE this_.MODEL like @p0 and
this_.COST >= @p1
ORDER BY this_.RECEIVEDasc;
@p0 = ‘%L%’ [Type: String (4000)],
@p1 = 1000 [Type: Decimal (0)]
Associating the restrictions with the DetachedCriteria() method resulted in the usage of the SQL keyword LIKE and the ≥ symbol.
Now, when the inventory is searched the result set will be restricted to guitars with a Cost of more than €1000 and ordered by Received. The GuitarStore WPF program now resembles Figure 3-6.
WORKING WITH QUERYOVER
The driving motivation for the QueryOver API was to provide a type-safe (compile-time) wrapper for the Criteria API. The Criteria API is dependent on fragile string literals that could fail at runtime, so adding this wrapper eliminates that dependency. Figure 3-7 shows the IQueryOver, IQueryOver<TRoot>, and the QueryOver<TRoot, TSubType> class diagram. The NHibernate.Criterion.QueryOver class is an abstract class. It is implemented by the QueryOver<TRoot, TSubType> class found within the same file. QueryOver is built on top of both the ICriteria interface and the CriteriaImpl class. Therefore, the QueryOver capabilities can be considered a lambda expression–based interface into ICriteria. I find the implementation a little more simple and straightforward than using the Criteria API.
Listing 3-22 shows an example that compares an ICriteria implementation versus an exact replicated implementation using QueryOver.
LISTING 3-22: Comparison of an ICriteria and queryOver API with generated SQL
ICriteria:
IList<Guitar> result = session.CreateCriteria<Guitar>()
.List<Guitar>();
QueryOver:
IList<Guitar> result = session.QueryOver<Guitar>().List();
Generated SQL for the 2 are identical
SELECT this_.ID as ID0_0_,
this_.TYPE as TYPE0_0_
FROM GUITAR this_
Using QueryOver in the GuitarStore WPF project requires the following actions:
- Create a method named GetInventory() that returns a list of a strongly typed Inventory class.
- Bind the method to the DataGrid on the GuitarStore WPF window.
- Create another GetInventory() method that accepts a guitar type and limit the results on that parameter.
- Implement paging.
First, create a method that uses QueryOver in the NHibernateInventory class found in the NHibernate.GuitarStore project. This method needs to return a result set that is used to populate the GuitarStore WPF DataGrid. Listing 3-23 contains the GetInventory() method.
LISTING 3-23: GetInventory() using QueryOver
public IList<Inventory> GetInventory()
{
using (ITransaction transaction = StatelessSession.BeginTransaction())
{
try
{
IQueryOver<Inventory> result = StatelessSession.QueryOver<Inventory>()
.OrderBy(i => i.Builder).Asc;
transaction.Commit();
return result.List<Inventory>();
}
catch (Exception ex)
{
transaction.Rollback();
throw;
}
}
}
Generated SQL:
SELECT this_.ID as ID1_0_,
this_.TYPEID as TYPEID1_0_,
this_.BUILDER as BUILDER1_0_,
this_.MODEL as MODEL1_0_,
this_.QOH as QOH1_0_,
this_.COST as COST1_0_,
this_.PRICE as PRICE1_0_,
this_.RECEIVED as RECEIVED1_0_
FROM INVENTORY this_
ORDER BY this_.BUILDER asc
Next, open the MainWindow.xaml.cs file and modify the PopulateDataGrid() method so that is uses the GetInventory() method, which utilizes the QueryOver API. Listing 3-24 contains the description of the PopulateDataGrid() method.
LISTING 3-24: PopulateDataGrid() Using QueryOver
private void PopulateDataGrid() { NHibernateInventory nhi = new NHibernateInventory(); dataGridInventory.ItemsSource = nhi.GetInventory(); dataGridInventory.Columns[0].Visibility = System.Windows.Visibility.Hidden; dataGridInventory.Columns[1].Visibility = System.Windows.Visibility.Hidden; dataGridInventory.Columns[9].Visibility = System.Windows.Visibility.Hidden; }
Next, create another the GetInventory() method that is called when a guitar type is selected from the ComboBox on the GuitarStore WPF window. The method is added to the NHibernateInventory class found within the NHibernate.GuitarStore project. Listing 3-25 contains this new method and shows how to use the Where and OrderBy methods in a lambda expression.
LISTING 3-25: GetInventory() to filter based on guitar type
public IList<Inventory> GetInventory(Guid Id)
{
using (ITransaction transaction = StatelessSession.BeginTransaction())
{
try
{
IQueryOver<Inventory> result = StatelessSession.QueryOver<Inventory>()
.Where(i => i.TypeId == Id)
.OrderBy(i => i.Builder).Asc;
transaction.Commit();
return result.List<Inventory>();
}
catch (Exception ex)
{
transaction.Rollback();
throw;
}
}
}
Generated SQL:
SELECT this_.ID as ID1_0_,
this_.TYPEID as TYPEID1_0_,
this_.BUILDER as BUILDER1_0_,
this_.MODEL as MODEL1_0_,
this_.QOH as QOH1_0_,
this_.COST as COST1_0_,
this_.PRICE as PRICE1_0_,
this_.RECEIVED as RECEIVED1_0_
FROM INVENTORY this_
WHERE this_.TYPEID = @p0
ORDER BY this_.BUILDER asc;
@p0 = 471c5b3f-19da-4fcb-8e9f-48dd17a00a3d [Type: Guid (0)]
Within the MainWindow.xaml.cs file, modify the comboBoxGuitarTypes_SelectionChanged() method to use the newly created GetInventory() method, as shown in Listing 3-26.
LISTING 3-26: Using QueryOver in the GuitarStore WPF program
private void comboBoxGuitarTypes_SelectionChanged(object sender,
SelectionChangedEventArgs e)
{
try
{
dataGridInventory.ItemsSource = null;
Guitar guitar = (Guitar)comboBoxGuitarTypes.SelectedItem;
Guid guitarType = new Guid(guitar.Id.ToString());
NHibernateInventory nhi = new NHibernateInventory();
dataGridInventory.ItemsSource = nhi.GetInventory(guitarType);
dataGridInventory.Columns[0].Visibility =
System.Windows.Visibility.Hidden;
dataGridInventory.Columns[1].Visibility =
System.Windows.Visibility.Hidden;
dataGridInventory.Columns[9].Visibility =
System.Windows.Visibility.Hidden;
}
catch (Exception ex)
{
labelMessage.Content = ex.Message;
}
}
Running the GuitarStore WPF program and selecting a guitar type from the ComboBox results in a window similar to the one shown in Figure 3-8.
Lastly, to implement paging using QueryOver, add the following method in Listing 3-27 to the NHibernateInventory class found within the NHibernate.GuitarStore project. Note the use of Futures and the ISession interface. Use a statefull Session with Futures, as Futures queries are not supported for a stateless Session.
LISTING 3-27: QueryOver method implementing paging and using Futures
public IEnumerable<Inventory> GetInventory(int maxResult, int firstResult,
out int totalCount)
{
using (ITransaction transaction = Session.BeginTransaction())
{
try
{
IQueryOver<Inventory> rowCount =
Session.QueryOver<Inventory>().ToRowCountQuery();
IQueryOver<Inventory> result = Session.QueryOver<Inventory>()
.OrderBy(i => i.Builder).Asc
.Take(maxResult)
.Skip(firstResult);
totalCount = rowCount.FutureValue<int>().Value;
transaction.Commit();
return result.Future<Inventory>();
}
catch (Exception ex)
{
transaction.Rollback();
throw;
}
}
}
Generated SQL:
SELECT TOP (@p0)
this_.ID as ID1_0_,
this_.TYPEID as TYPEID1_0_,
this_.BUILDER as BUILDER1_0_,
this_.MODEL as MODEL1_0_,
this_.QOH as QOH1_0_,
this_.COST as COST1_0_,
this_.PRICE as PRICE1_0_,
this_.RECEIVED as RECEIVED1_0_
FROM INVENTORY this_
ORDER BY this_.BUILDER asc;
;@p0 = 25 [Type: Int32 (0)]
INVENTORY this_;
Before implementing the preceding code in the GuitarStore WPF program, now would be a good time to use the NHibernate.GuitarStore.Console application to test the GetInventory() method. The code to perform this test is contained in Listing 3-28 and should be located within the Main() method in the Program.cs file.
LISTING 3-28: Testing the Paging GetInventory() method from the console
IEnumerable<Inventory> invListQO31 = null;
int countQO31 = nhi.GetInventory(25, 0, out invListQO31);
Console.WriteLine(“GetInventory(25, 0, out invListQO31): ” +
invListQO31.Count().ToString() + “ results”);
The result will confirm that the Take() method applied to the QueryOver API returns the number of rows passed via the maxResult parameter. Also, if the show_sql parameter is enabled within the NHibernate configuration settings, then the SQL can be viewed on the console.
To implement paging using the QueryOver API, modify the PopulateDataGrid() method within the MainWindow.xaml.cs file found in the GuitarStore WPF project. The code is shown in Listing 3-29. Don't forget to set the FirstResult back to 0; it was changed to 1 when paging was implemented using the CreateCriteria() method earlier in this chapter.
LISTING 3-29: Paging within PopulateDataGrid() using the QueryOver API
public int FirstResult = 0;
private void PopulateDataGrid()
{
NHibernateInventory nhi = new NHibernateInventory();
IEnumerable<Inventory> GuitarInventory = null;
int inventoryCount = nhi.GetInventory(MaxResult, FirstResult,
out GuitarInventory);
DataTable dt = new DataTable();
dt.Columns.Add(“Builder”, typeof(string));
dt.Columns.Add(“Model”, typeof(string));
dt.Columns.Add(“Price”, typeof(string));
dt.Columns.Add(“Id”, typeof(string));
foreach (var item in GuitarInventory)
{
dt.Rows.Add(item.Builder, item.Model, item.Price.ToString(), item.Id);
}
dataGridInventory.ItemsSource = dt.DefaultView;
labelPaging.Content = “Retrieved ” + FirstResult.ToString() +
“ to ” + (FirstResult + GuitarInventory.Count()).ToString() +
“ of ” + inventoryCount.ToString();
totalCount = inventoryCount;
}
Futures have been implemented with paging from the start using the QueryOver API. Therefore, when the GuitarStore WPF program is run, the inventory is populated into the DataGrid and the row count displayed using a single round-trip to the database. Notice that the stoplight is green in Figure 3-9.
USING LAMBDA EXPRESSIONS
As with LINQ to NHibernate, discussed in the next chapter, the QueryOver class found in the NHibernate.Criterion namespace supports the use of lambda expressions. Lambda expressions are synonymous in many ways with anonymous functions in that they don't support assignment to an implicitly typed variable. Lambda expressions simplify the syntax used in anonymous methods by removing the requirement to use the delegate keyword.
Listing 3-30 shows the difference between using an anonymous method versus a lambda expression. When the CalculateProfit delegate is accessed, it will return the decimal value Profit. Profit is the result of subtracting the cost of the guitar from the price for which it was sold.
LISTING 3-30: Anonymous method versus lambda expression
Anonymous method:
CalculateProfit cProfit = delegate(decimal Profit)
{ return (decimal)inventory.Price - (decimal)inventory.Cost; };
Lambda expression:
CalculateProfit cProfit = (Profit) =>
{ return (decimal)inventory.Price - (decimal)inventory.Cost; };
Within NHibernate, the lambda expression capability is used primarily to limit the amount of data returned from a query. Lambda expressions also make the code more readable and user friendly. This enables a better understanding of what the code is doing and makes it easier to modify and maintain. One of the primary motivations for the introduction of lambda expressions was to use the System.Linq.Expression constructs for type-safe static reflection. Therefore, you will find many of the common LINQ commands within the QueryOver API, such as And, Where, WhereNot, Select, Skip, Take, and so on, all of which are used to retrieve a more precise result set. Nonetheless, however similar LINQ is to lambda expressions, do not make the mistake of thinking they are the same.
UNDERSTANDING FUTURES
The source of the Future() methods can be found in the NHibernate.Impl namespace within the CriteriaImpl class. A more canonical use of Future() methods is the retrieval of data as late in a process or transaction as possible, whereby the data is only retrieved if it is actually needed. Future() methods also enable the batching of queries so that round-trips to the database for data retrieval can be minimized. For example, if it is known at the beginning of a transaction that the average cost of a guitar type is needed, and then later the minimum price, Future() methods could be an option for retrieving both in a single database round-trip, rather than multiple trips. As discussed earlier, it is efficient to batch the queries for paging, whereby the retrieval of the current page of data is combined with the count of all rows on the table.
The Future<T>() source code, found in the CreateImpl class, is interesting. You can see it in Listing 3-31.
LISTING 3-31: The ICriteria Future<T>() method
public IEnumeralbe<T> Future<t>()
{
if(!session.Factory.ConnectionProvider.Driver.SupportsMultipleQueries)
{
return List<T>();
}
session.FutureCriteriaBatch.Add<T>(this);
return session.FutureCriteriaBatch.GetEnumerator<T>();
}
Notice that the if statement checks to confirm that the DMBS being used supports the batching of SQL queries before trying to execute it. If batching is not supported, the method is intelligent enough to return result sets as a List<t> instead of an error. This is not the case when accessing the CreateMultiCriteria() method directly, whereby an error is returned instead of a result set.
TIP Use Futures when you want to batch SQL queries together to reduce database round-trips or when you want to delay the retrieval of the data for as long as possible.
Future() methods have already been used in this chapter to implement paging with the QueryOver and Criteria APIs. Using Future() methods also allows the program to wait as long as possible to execute the query so that it can be batched with other queries that potentially need execution. The Future() method is an alternative for the List() method, which will execute the query immediately. The Future() method can also be used in a context other than with paging. For example, the loading of the ComboBox and the DataGrid are executed from two separate methods using a combination of the List() method of the CreateCriteria() and the Future() method of the QueryOver API. They should instead be combined into a single method using the Future() method. Listing 3-32 provides an example of a method called Load() located in the NHibernateInventory class of the NHibernate.GuitarStore project. This method combines all database queries required to load the GuitarStore WPF program into a single round-trip to the database.
LISTING 3-32: Method using Future() methods to load all GuitarStore queries
public int Load(int maxResult, int firstResult,
out IEnumerable<Inventory> resultSetInv,
out IEnumerable<Guitar> resultSetGuitar)
{
using (ITransaction transaction = Session.BeginTransaction())
{
try
{
IQueryOver<Inventory> rowCount =
Session.QueryOver<Inventory>().ToRowCountQuery();
IQueryOver<Inventory> result = Session.QueryOver<Inventory>()
.OrderBy(i => i.Builder).Asc
.Take(maxResult)
.Skip(firstResult);
ICriteria criteriaGuitar = Session.CreateCriteria<Guitar>();
resultSetInv = result.Future<Inventory>();
resultSetGuitar = criteriaGuitar.Future<Guitar>();
transaction.Commit();
return rowCount.FutureValue<int>().Value;
}
catch (Exception ex)
{
transaction.Rollback();
throw;
}
}
}
Generated SQL:
SELECT TOP (@p0)
this_.ID as ID1_0_,
this_.TYPEID as TYPEID1_0_,
this_.BUILDER as BUILDER1_0_,
this_.MODEL as MODEL1_0_,
this_.QOH as QOH1_0_,
this_.COST as COST1_0_,
this_.PRICE as PRICE1_0_,
this_.RECEIVED as RECEIVED1_0_
FROM INVENTORY this_
ORDER BY this_.BUILDER asc;
SELECT this_.ID as ID0_0_,
this_.TYPE as TYPE0_0_
FROM GUITAR this_;
;@p0 = 25 [Type: Int32 (0)]
INVENTORY this_;
Implementing this into the GuitarStore WPF program requires that the code within the MainWindow.xaml.cs file that populates the ComboBox is removed and then the PopulateDataGrid() method is modified to use the previously created Load() method. Listing 3-33 contains the code for the PopulateDataGrid() method.
LISTING 3-33: Loading all GuitarStore controls with a single database round-trip
private void PopulateDataGrid()
{
NHibernateInventory nhi = new NHibernateInventory();
IEnumerable<Inventory> GuitarInventory = null;
IEnumerable<Guitar> GuitarTypes = null;
int inventoryCount = nhi.Load(MaxResult, FirstResult,
out GuitarInventory, out GuitarTypes);
foreach (var item in GuitarTypes)
{
Guitar guitar = new Guitar(item.Id, item.Type);
comboBoxGuitarTypes.DisplayMemberPath = “Type”;
comboBoxGuitarTypes.SelectedValuePath = “Id”;
comboBoxGuitarTypes.Items.Add(guitar);
}
DataTable dt = new DataTable();
dt.Columns.Add(“Builder”, typeof(string));
dt.Columns.Add(“Model”, typeof(string));
dt.Columns.Add(“Price”, typeof(string));
dt.Columns.Add(“Id”, typeof(string));
foreach (var item in GuitarInventory)
{
dt.Rows.Add(item.Builder, item.Model, item.Price.ToString(),
item.Id);
}
dataGridInventory.ItemsSource = dt.DefaultView;
labelPaging.Content = “Retrieved ” + FirstResult.ToString() +
“ to ” + (FirstResult + GuitarInventory.Count()).ToString() +
“ of ” + inventoryCount.ToString();
totalCount = inventoryCount;
}
Finally, notice that in Figure 3-10 the stoplight is now green at the initial startup. This means that all queries are being executed using a single round-trip to the database.
Selecting the View SQL button on the GuitarStore WPF window will display the most recently NHibernate-generated SQL queries, as shown in Figure 3-11.
USING FETCHMODE
How the data will be retrieved within a program is an important decision that needs to be made during the design phase. The data can be lazy loaded, meaning that it's retrieved only when accessed; or eagerly loaded, meaning the data and corresponding mapped entities are all loaded when the query is executed. The data retrieval strategy may need to be made on a per-query basis or perhaps at a higher level, such as for specific groups of functionality. The ICriteria interface provides designers and programmers with a mechanism to define such strategies within the program. This mechanism is referred to as FetchMode. The FetchMode enumerator can be found in the NHibernate namespace and it is implemented using the SetFetchMode() method within the NHibernate.Impl.CriteriaImpl class.
NOTE HQL ignores the fetch attribute's value and loads everything lazy by default. If no FetchMode is set, ICriteria will default to lazy loading.
FetchMode can be configured either in the mapping files or during the ICriteria query configuration.
Configuring FetchMode in the Mapping Files
The default setting for a data collection, i.e., set, bag, list, and so on, is lazy=”true”; however, if the fetch value is set to JOIN within the collection, then it is similar to setting lazy=”false”. Listing 3-34 shows the fetch attribute added to the Guitar.hbm.xml mapping file.
LISTING 3-34: Fetch attribute example
<bag name=“Inventory” table=“INVENTORY” fetch=“JOIN” > <key column=“TYPEID” /> <one-to-many class=“NHibernate.GuitarStore.Common.Inventory” /> </bag>
When the code in Listing 3-35 is executed, it may not be obvious that all data from both the GUITAR and the INVENTORY tables has been retrieved from the database and loaded into memory. However, when looking at the generated SQL query, it is clear that a JOIN was performed between the tables and that all the data from both tables was selected.
LISTING 3-35: Loading the Guitar class with fetch=JOIN
ICriteria criteria = Session.CreateCriteria<Guitar>(); return criteria.List<Guitar>(); Generated SQL: SELECT this_.ID as ID0_1_, this_.TYPE as TYPE0_1_, inventory2_.TYPEID as TYPEID3_, inventory2_.ID as ID3_, inventory2_.ID as ID1_0_, inventory2_.TYPEID as TYPEID1_0_, inventory2_.BUILDER as BUILDER1_0_, inventory2_.MODEL as MODEL1_0_, inventory2_.QOH as QOH1_0_, inventory2_.COST as COST1_0_, inventory2_.PRICE as PRICE1_0_, inventory2_.RECEIVED as RECEIVED1_0_ FROM GUITAR this_ left outer JOIN INVENTORY inventory2_ on this_.ID=inventory2_.TYPEID
Configuring FetchMode programmatically
Setting the FetchMode programmatically, as shown in Listing 3-36, is required for each mapped relationship, just like in the mapping files. There is no global way to set the FetchMode for all relationships within the class. Not being able to set the FetchMode globally may seem like a hindrance initially, but developers soon realize that they can set the FetchMode for each relationship within each class as appropriate. Some requirements may necessitate the eager loading of related classes, while other classes only need to be loaded when referred to within the code. This gives you the flexibility and functionality to support a system's needs in the most optimal way.
LISTING 3-36: Setting FetchMode programmatically
public IList<Guitar> GetGuitar()
{
using (ITransaction transaction = Session.BeginTransaction())
{
try
{
ICriteria criteria = Session.CreateCriteria<Guitar>()
.SetFetchMode(“Inventory”, FetchMode.Join);
transaction.Commit();
return criteria.List<Guitar>();
}
catch (Exception ex)
{
transaction.Rollback();
throw;
}
}
}
Generated SQL:
SELECT this_.ID as ID0_1_,
this_.TYPE as TYPE0_1_,
inventory2_.TYPEID as TYPEID3_,
inventory2_.ID as ID3_,
inventory2_.ID as ID1_0_,
inventory2_.TYPEID as TYPEID1_0_,
inventory2_.BUILDER as BUILDER1_0_,
inventory2_.MODEL as MODEL1_0_,
inventory2_.QOH as QOH1_0_,
inventory2_.COST as COST1_0_,
inventory2_.PRICE as PRICE1_0_,
inventory2_.RECEIVED as RECEIVED1_0_
FROM GUITAR this_ left outer JOIN INVENTORY inventory2_
on this_.ID=inventory2_.TYPEID
The preceding code generates the same SQL query you would get when the fetch mode is set from within the mapping file. Table 3-1 describes the different fetch mode settings.
| VALUE | DESCRIPTION |
| FetchMode.Default | Uses the settings in the.hbm.xml mapping file |
| FetchMode.Eager | Loads mapped relationships as if lazy=false, using a left outer JOIN |
| FetchMode.Join | Loads mapped relationships as if lazy=false, using a left outer JOIN |
| FetchMode.Lazy | Loads mapped relationships as if lazy=true |
| FetchMode.Select | Loads mapped relationships as if lazy=true |
A review of the NHibernate.FetchMode source code shows that Eager = Join and Lazy = Select. Therefore, one can rightly assume that because they are set as equal, as shown in Listing 3-37, they will act the same. This is probably the reason why IntelliSense, when configuring the Fetch in the mapping file, only displays select and JOIN as options.
LISTING 3-37: NHibernate FetchMode enum source code
namespace NHibernate
{
[Serializable]
public enum FetchMode
{
/// <summary>
/// Default to the setting configured in the mapping file.
/// </summary>
Default = 0,
/// <summary>
/// Fetch eagerly, using a separate select. Equivalent to
/// <c>fetch=“select”</c> (and <c>outer-JOIN=“false”</c>)
/// </summary>
Select = 1,
/// <summary>
/// Fetch using an outer JOIN. Equivalent to
/// <c>fetch=“JOIN”</c> (and <c>outer-JOIN=“true”</c>)
/// </summary>
Join = 2,
Lazy = Select,
Eager = Join
}
}
NOTE Avoid using eager and lazy FetchMode values, as they exist only for backward compatibility with older NHibernate-dependent projects.
Lastly, recall from Listing 2-6 in Chapter 2 where IQuery is covered how parameters were set: IQuery exposes a large set of methods that specifically define the value type of the parameter being passed to the query. ICriteria does not have this capability. Instead, ICriteria uses the types defined in the mapping files to confirm that the value being supplied matches the database type. Listing 3-38 shows a comparison between setting a parameter in IQuery vs. ICriteria.
LISTING 3-38: Setting Query Parameters — HQL vs. ICriteria
HQL- uses reflection to validate data type of Id:
IQuery query = session.CreateQuery(HQLQuery)
.SetGuid(“TypeId”, Id);
ICriteria- uses mapping file type to validate data type of Id:
ICriteria criteria = session.CreateCriteria(typeof(Inventory))
.Add(Restrictions.Eq(“TypeId”, Id));
IMPLEMENTING AGGREGATE DATABASE FUNCTIONS
Aggregate database methods provide the programmer with a quick and simple way to average, count, or find the minimum or maximum values in a database table. Not all database aggregates are supported by NHibernate. A review of the Projection class, found in the NHibernate.Criterion namespace, shows that the functions described in Table 3-2 are supported.
TABLE 3-2:Supported Aggregate Functions
To implement aggregate database functions using ICriteria in the GuitarStore WPF program, you need to perform the following:
- Add Avg, Min, Max, and Count methods to the NHibernateInventory class.
- Modify the Click event of the AVG, MIN, MAX, and COUNT buttons created in Chapter 2.
Open the NHibernateInventory.cs file located in the NHibernate.GuitarStore project and add the following methods shown in Listings 3-39 through 3-42.
LISTING 3-39: Avg ICriteria aggregate function
public IList GetInventoryAveragePrice()
{
using (ITransaction transaction = Session.BeginTransaction())
{
try
{
ICriteria criteria = Session.CreateCriteria<Guitar>(“g”)
.CreateAlias(“Inventory”, “i”)
.SetProjection(Projections.ProjectionList()
.Add(Projections.GroupProperty(“g.Type”))
.Add(Projections.Avg(“i.Price”)));
transaction.Commit();
return criteria.List();
}
catch (Exception ex)
{
transaction.Rollback();
throw;
}
}
}
Generated SQL:
SELECT this_.TYPE as y0_,
avg(cast(i1_.PRICE as DOUBLE PRECISION)) as y1_
FROM GUITAR this_ inner JOIN INVENTORY i1_
on this_.ID=i1_.TYPEID
GROUP BY this_.TYPE
The preceding code calculates the average Price by guitar type and returns a list grouped by guitar types.
LISTING 3-40: Min ICriteria aggregate function
public IList GetInventoryMinimumPrice()
{
using (ITransaction transaction = Session.BeginTransaction())
{
try
{
ICriteria criteria = Session.CreateCriteria<Guitar>(“g”)
.CreateAlias(“Inventory”, “i”)
.SetProjection(Projections.ProjectionList()
.Add(Projections.GroupProperty(“g.Type”))
.Add(Projections.Min(“i.Price”)));
transaction.Commit();
return criteria.List();
}
catch (Exception ex)
{
transaction.Rollback();
throw;
}
}
}
Generated SQL:
SELECT this_.TYPE as y0_,
min(i1_.PRICE) as y1_
FROM GUITAR this_ inner JOIN INVENTORY i1_
on this_.ID=i1_.TYPEID
GROUP BY this_.TYPE
The preceding code returns the guitar with the lowest Price by guitar type and a list grouped by guitar type.
LISTING 3-41: Max ICriteria aggregate function
public IList GetInventoryMaximumPrice() { using (ITransaction transaction = Session.BeginTransaction()) { try { ICriteria criteria = Session.CreateCriteria<Guitar>(“g”) .CreateAlias(“Inventory”, “i”) .SetProjection(Projections.ProjectionList() .Add(Projections.GroupProperty(“g.Type”)) .Add(Projections.Max(“i.Price”))); transaction.Commit(); return criteria.List(); } catch (Exception ex) { transaction.Rollback(); throw; } } } Generated SQL: SELECT this_.TYPE as y0_, max(i1_.PRICE) as y1_ FROM GUITAR this_ inner JOIN INVENTORY i1_ on this_.ID=i1_.TYPEID GROUP BY this_.TYPE
The preceding list returns the guitar with the highest Price by guitar type and a list grouped by guitar type.
LISTING 3-42: Count ICriteria aggregate function
public IList GetInventoryCountADF()
{
using (ITransaction transaction = Session.BeginTransaction())
{
try
{
ICriteria criteria = Session.CreateCriteria<Guitar>(“g”)
.CreateAlias(“Inventory”, “i”)
.SetProjection(Projections.ProjectionList()
.Add(Projections.GroupProperty(“g.Type”))
.Add(Projections.RowCount()));
transaction.Commit();
return criteria.List();
}
catch (Exception ex)
{
transaction.Rollback();
throw;
}
}
}
Generated SQL:
SELECT this_.TYPE as y0_,
count(*) as y1_
FROM GUITAR this_ inner JOIN INVENTORY i1_
on this_.ID=i1_.TYPEID
GROUP BY this_.TYPE
The preceding list returns the total number of guitars by guitar type and a list grouped by guitar type.
NOTE Both IQuery and ICriteria support the use of aggregates.
The next step is to change the existing code for the buttonCount_Click() method, which is called when the Count button is clicked from the GuitarStore WPF window. An example of the COUNT implementation is shown in Listing 3-43.
LISTING 3-43: Using the ICriteria Count Button from the GuitarStore WPF
private void buttonCount_Click(object sender, RoutedEventArgs e)
{
NHibernateInventory nhi = new NHibernateInventory();
List<string> fields = new List<string>
{
“Guitar Type”, “Total Count”
};
IList GuitarInventory = nhi.GetInventoryCountADF();
dataGridInventory.ItemsSource =
BuildDataTable(fields, GuitarInventory).DefaultView;
}
Starting the GuitarStore WPF program and clicking the Count button will display a WPF window like the one shown in Figure 3-12.
To implement the remaining aggregate database functions using the ICriteria API, simply modify the code in the MainWindow.xaml.cs file to use the newly created aggregate database methods.
UNDERSTANDING RESTRICTIONS AND EXPRESSIONS
The Restrictions and Expression classes can be found in the NHibernate.Criterion namespace. These classes hold an abundant number of methods that support the implementation of projection and value comparison logic. Projection reduces the amount of data returned from a query and is implemented with SQL by using the WHERE clause, or with the ICriteria API by using the Restrictions class. Conversely, value comparison logic uses operators, such as ≤, ≠, or ≥, for example, to limit the data result. Most of the value comparison operators and terms are supported via the Restrictions class. Table 3-3 describes the restriction methods and comparison operators/terms found within the Restrictions class.
TABLE 3-3:Restrictions Methods and Operators
It is very common for a data-driven program to need to constrain the queries that return information used for decision making. You will find that the Restrictions class plays a significant role in the data retrieval process and provides the programmer with the capabilities necessary for creating such data-driven programs.
NOTE The Expression class inherits from the Restrictions class. Avoid using Expressions, as the class is marked as semi-deprecated and exists only for backward compatibility.
By adding query restrictions to a CreateCriteria or DetachedCriteria method, the query will be constrained and return only the data that complies with the restriction. The following actions are performed in this section to implement the Between, In, Not, and Or Restrictions into the GuitarStore WPF program.
- Create four new methods within the NHibernateInventory class found in the NHibernate.GuitarStore project: one method for each restriction implemented.
- Enhance the search capabilities of the GuitarStore WPF program to utilize the new search capabilities.
- Populate the search results in the DataGrid.
First, open the NHibernateInventory class found within the NHibernate.GuitarStore project and add the four methods shown in Listing 3-44, 3-45, 3-47, and 3-48. Listing 3-44 will return a list of guitars whose price is between the low and high values passed to the method.
LISTING 3-44: Between restriction using the ICriteria restrictions class
public IList GetGuitarBetween(decimal low, decimal high)
{
using (ITransaction transaction = StatelessSession.BeginTransaction())
{
try
{
ICriteria criteria = StatelessSession.CreateCriteria<Inventory>()
.SetProjection(Projections.ProjectionList()
.Add(Projections.Property(“Builder”))
.Add(Projections.Property(“Model”))
.Add(Projections.Property(“Price”))
.Add(Projections.Property(“Id”)))
.AddOrder(Order.Asc(“Builder”))
.Add(Restrictions.Between(“Cost”, low, high));
transaction.Commit();
return criteria.List();
}
catch (Exception ex)
{
transaction.Rollback();
throw;
}
}
}
Generated SQL:
SELECT this_.BUILDER as y0_,
this_.MODEL as y1_,
this_.PRICE as y2_,
this_.ID as y3_
FROM INVENTORY this_
WHERE this_.COST between @p0 and @p1
ORDER BY this_.BUILDER asc;
@p0 = 1000 [Type: Decimal (0)],
@p1 = 2000 [Type: Decimal (0)]
Listing 3-45 provides an example of how to implement the In restriction using the ICriteria API. The query returns all guitars in the inventory that are built by Fender, Ibanez, and Takamine.
LISTING 3-45: In restriction using the ICriteria Restrictions class
public IList GetGuitarIn(object[] Builders) { using (ITransaction transaction = StatelessSession.BeginTransaction()) { try { ICriteria criteria = StatelessSession.CreateCriteria<Inventory>() .SetProjection(Projections.ProjectionList() .Add(Projections.Property(“Builder”)) .Add(Projections.Property(“Model”)) .Add(Projections.Property(“Price”)) .Add(Projections.Property(“Id”))) .AddOrder(Order.Asc(“Builder”)) .Add(Restrictions.In(“Builder”, Builders)); transaction.Commit(); return criteria.List(); } catch (Exception ex) { transaction.Rollback(); throw; } } } Generated SQL: SELECT this_.BUILDER as y0_, this_.MODEL as y1_, this_.PRICE as y2_, this_.ID as y3_ FROM INVENTORY this_ WHERE this_.BUILDER in (@p0, @p1, @p2) ORDER BY this_.BUILDER asc; @p0 = ‘FENDER’ [Type: String (4000)], @p1 = ‘IBANEZ” [Type: String (4000)], @p2 = ‘TAKAMINE” [Type: String (4000)]
Now would be a good time to test the preceding method using the NHibernate.GuitarStore.Console application. By doing this you can be certain that the method works prior to attempting its implementation in the GuitarStore WPF program. It will also help you to understand the format of the parameter data required to utilize the method. Add the following code in Listing 3-46 to the Main() method of the Program class found in the NHibernate.GuitarStore.Console application. Then set the project as the startup project, run it, and confirm it runs as expected.
LISTING 3-46: Testing the In ICriteria restriction
object[] Builders40 = new object[] { “FENDER”, “IBANEZ”, “TAKAMINE” }; IList list40 = nhi.GetGuitarIn(Builders40); Console.WriteLine(“GetGuitarIn(Builders40): ” + list40.Count.ToString() + “ results”);
Next, add the method that uses the Not restriction. This method, shown in Listing 3-47, returns a list of guitars that are not built by Fender, Ibanez, or Takamine.
LISTING 3-47: Not restriction using the ICriteria restrictions class
public IList GetGuitarNot(object[] Builders)
{
using (ITransaction transaction = StatelessSession.BeginTransaction())
{
try
{
ICriteria criteria = StatelessSession.CreateCriteria<Inventory>()
.SetProjection(Projections.ProjectionList()
.Add(Projections.Property(“Builder”))
.Add(Projections.Property(“Model”))
.Add(Projections.Property(“Price”))
.Add(Projections.Property(“Id”)))
.AddOrder(Order.Asc(“Builder”))
.Add(Restrictions.Not(Restrictions.In(“Builder”, Builders)));
transaction.Commit();
return criteria.List();
}
catch (Exception ex)
{
transaction.Rollback();
throw;
}
}
}
Generated SQL:
SELECT this_.BUILDER as y0_,
this_.MODEL as y1_,
this_.PRICE as y2_,
this_.ID as y3_
FROM INVENTORY this_
WHERE not (this_.BUILDER in (@p0, @p1, @p2))
ORDER BY this_.BUILDER asc;
@p0 = ‘FENDER’ [Type: String (4000)],
@p1 = ‘IBANEZ’ [Type: String (4000)],
@p2 = ‘TAKAMINE’ [Type: String (4000)]
The last method that implements the Or restriction is shown in Listing 3-48. This query will result in a list of guitars that are built by Fender, Ibanez, or Takamine or whose price is between the low and high value passed as the parameter.
LISTING 3-48: Or restriction using the ICriteria restrictions class
public IList GetGuitarOr(object[] Builders, decimal low, decimal high)
{
using (ITransaction transaction = StatelessSession.BeginTransaction())
{
try
{
ICriteria criteria = StatelessSession.CreateCriteria<Inventory>()
.SetProjection(Projections.ProjectionList()
.Add(Projections.Property(“Builder”))
.Add(Projections.Property(“Model”))
.Add(Projections.Property(“Price”))
.Add(Projections.Property(“Id”)))
.AddOrder(Order.Asc(“Builder”))
.Add(Restrictions.Or(Restrictions.In(“Builder”, Builders),
Restrictions.Between(“Cost”, low, high)));
transaction.Commit();
return criteria.List();
}
catch (Exception ex)
{
transaction.Rollback();
throw;
}
}
}
Generated SQL:
SELECT this_.BUILDER as y0_,
this_.MODEL as y1_,
this_.PRICE as y2_,
this_.ID as y3_
FROM INVENTORY this_
WHERE (this_.BUILDER in (@p0, @p1, @p2)
or this_.COST between @p3 and @p4)
ORDER BY this_.BUILDER asc;
@p0 = ‘FENDER’ [Type: String (4000)],
@p1 = ‘IBANEZ’ [Type: String (4000)],
@p2 = ‘TAKAMINE’ [Type:String (4000)],
@p3 = 1000 [Type: Decimal (0)],
@p4 = 2000 [Type: Decimal (0)]
To implement the Between restriction shown earlier in Listing 3-44 in the GuitarStore WPF program, open the MainWindow.xaml file within the GuitarStore project and add two Label controls and two TextBox controls. Figure 3-13 shows the result. The XAML code is shown in Listing 3-49.
LISTING 3-49: XAML Code for Between Restriction WPF implementation
<Label Content=“Between:” Height=“28” HorizontalAlignment=“Left”
Margin=“77,24,0,0” Name=“labelBetween” VerticalAlignment=“Top” />
<TextBox Height=“23” HorizontalAlignment=“Left” Margin=“131,21,0,0”
Name=“textBoxLow” VerticalAlignment=“Top” Width=“38” />
<Label Content=“and” Height=“28” HorizontalAlignment=“Left”
Margin=“174,24,0,0” Name=“labelAnd” VerticalAlignment=“Top” />
<TextBox Height=“23” HorizontalAlignment=“Left” Margin=“200,21,0,0”
Name=“textBoxHigh” VerticalAlignment=“Top” Width=“38” />
Now open the MainWindow.xaml.cs file and modify the buttonSearch_Click() method so that is resembles what is shown in Listing 3-50.
LISTING 3-50: Between restriction in the GuitarStore WPF program
private void buttonSearch_Click(object sender, RoutedEventArgs e)
{
NHibernateInventory nhi = new NHibernateInventory();
IList GuitarInventory = nhi.GetGuitarBetween(Convert.ToDecimal(textBoxLow.Text),
Convert.ToDecimal(textBoxHigh.Text));
List<string> fields = new List<string>
{
“Builder”, “Model”, “Price”, “Id”
};
dataGridInventory.ItemsSource =
BuildDataTable(fields, GuitarInventory).DefaultView;
}
Lastly, to implement the Or restriction into the GuitarStore WPF program, add three CheckBox controls to the MainWindow.xaml window. Listing 3-51 provides the XAML code.
LISTING 3-51: XAML code for Or restriction WPF implementation
<CheckBox Content=“Fender” Height=“16” HorizontalAlignment=“Left”
Margin=“142,54,0,0” Name=“checkBoxFender” VerticalAlignment=“Top” />
<CheckBox Content=“Ibanez” Height=“16” HorizontalAlignment=“Left”
Margin=“205,54,0,0” Name=“checkBoxIbanez” VerticalAlignment=“Top” />
<CheckBox Content=“Takamine” Height=“16” HorizontalAlignment=“Left”
Margin=“267,54,0,0” Name=“checkBoxTakamine” VerticalAlignment=“Top” />
Then modify the buttonSearch_Click() method so that it resembles the code shown in Listing 3-52.
LISTING 3-52: Or restriction in the GuitarStore WPF program
private void buttonSearch_Click(object sender, RoutedEventArgs e)
{
NHibernateInventory nhi = new NHibernateInventory();
object[] Builders = new object[3];
if (checkBoxFender.IsChecked == true)
{
Builders[0] = “FENDER”;
}
if (checkBoxIbanez.IsChecked == true)
{
Builders[1] = “IBANEZ”;
}
if (checkBoxTakamine.IsChecked == true)
{
Builders[2] = “TAKAMINE";
}
IList GuitarInventory = nhi.GetGuitarOr(Builders,
Convert.ToDecimal(textBoxLow.Text),
Convert.ToDecimal(textBoxHigh.Text));
List<string> fields = new List<string>
{
“Builder”, “Model”, “Price”, “Id”
};
dataGridInventory.ItemsSource =
BuildDataTable(fields, GuitarInventory).DefaultView;
}
When the GuitarStore WPF program is run after €4000 is entered as the low Cost, €5000 is entered as the high Cost, and Fender and Ibanez are checked, the window shown in Figure 3-14 is produced. The DataGrid displays all Fender and Ibanez guitars, plus guitars that have a Cost between €4000 and €5000.
WORKING WITH DATA TRANSFER OBJECTS
There is a very powerful feature in NHibernate that supports the conversion of any result set to a business object-like class or data transfer object (DTO). This capability is supported through the Transformers class, found within the NHibernate.Transform namespace. Although this capability is available via the IQuery interface, it is easier to leverage when accessed via ICriteria because of the continued use of methods to build the query.
In all the examples within the GuitarStore solution up to now, when projection has been implemented to reduce the number of columns returned from a query, the fact that the result set was dynamic has not been an issue. This is because in every case, the result set has been immediately added to a DataTable and then bound to a DataGrid.
In other programs, it may be an absolute requirement that all result sets be strongly typed. As shown in Listings 2-18 and 2-19 in Chapter 2 and Listings 3-23 and 3-25 in this chapter, when the result set is strongly typed as a class, accessing the properties within the class is much easier.
NHibernate provides programmers with the capability to transform a dynamically retrieved dataset into a strongly typed class. This is accomplished by using the AliasToBean() method, found within the Transformers class, in the NHibernate.Transform namespace.
NOTE Both IQuery and ICriteria support the transformation of a dynamically retrieved result set to a strongly typed class.
To implement a DTO to store the projected result set into the GuitarStore WPF program, you need to do the following:
- Create a new class named InventoryDTO.
- Create a new mapping file named InventoryDTO.hbm.xml.
- Add a method named GetInventoryDTO() to the NHibernateInventory class that retrieves a projected result set and then converts it to the InventoryDTO class.
- Implement the GetInventoryDTO() method into the GuitarStore WPF program.
To create a new class in the NHibernate.GuitarStore project, right-click the Common folder, and select Add 
Class. Then add InventoryDTO.cs as the name of the class and select OK. Modify the InventoryDTO class so that it resembles the code shown in Listing 3-53.
LISTING 3-53: Inventory DTO class description
namespace NHibernate.GuitarStore.Common
{
public class InventoryDTO
{
public InventoryDTO() { }
public virtual Guid Id { get; set; }
public virtual string Builder { get; set; }
public virtual string Model { get; set; }
public virtual decimal? Price { get; set; }
public virtual string Type { get; set; }
}
}
The InventoryDTO class is created just like any other class that is used to store retrieved data via NHibernate. The mapping file is a little different from all the previous mapping files, as shown in Listing 3-54. As there is no associated database table, the properties do not need mappings. To add the file to the NHibernate.GuitarStore project, right-click the Mapping directory and select Add New Item. Select XML File and enter InventoryDTO.hbm.xml as the name.
LISTING 3-54: Inventory DTO mapping file example
<?xml version=“1.0” encoding=“utf-8” ?> <hibernate-mapping xmlns=“urn:nhibernate-mapping-2.2”> <import class=“NHibernate.GuitarStore.Common. InventoryDTO, NHibernate.GuitarStore” /> </hibernate-mapping>
There is no database table directly related to this class and mapping file. Therefore, no actual SQL query is generated to populate this object. These files are used simply as storage containers for the dynamically generated result set. The import element in the mapping file makes NHibernate aware of the type, enabling NHibernate to manipulate it.
NOTE It is required to set all .hbm.xml files in your project to Embedded Resource via the Properties window or place them within the program working directory; otherwise, you are likely to get an exception at compile time or your program simply will not display any data.
The GuitarStore solution should now resemble Figure 3-15.
Next, add a method named GetInventoryDTO() to the NHibernateInventory class found within the NHibernate.GuitarStore project. The code segment should resemble Listing 3-55. Notice that the NHibernate.Transform directive has been added to the NHibernateInventory class.
LISTING 3-55: GetInventory DTO() using AliasToBean()
using NHibernate.Transform;
public IList<InventoryDTO> GetInventoryDTO()
{
using (ITransaction transaction = StatelessSession.BeginTransaction())
{
try
{
ICriteria criteria = StatelessSession.CreateCriteria<Guitar>(“g”)
.CreateAlias(“Inventory”, “i”)
.SetProjection(Projections.ProjectionList()
.Add(Projections.Property(“i.Id”), “Id”)
.Add(Projections.Property(“i.Builder”), “Builder”)
.Add(Projections.Property(“i.Model”), “Model”)
.Add(Projections.Property(“i.Price”), “Price”)
.Add(Projections.Property(“g.Type”), “Type”))
.AddOrder(Order.Asc(“g.Type”));
criteria.SetResultTransformer(Transformers.AliasToBean<InventoryDTO>());
return criteria.List<InventoryDTO>();
}
catch (Exception ex)
{
transaction.Rollback();
throw;
}
}
}
Generated SQL:
SELECT i1_.ID as y0_,
i1_.BUILDER as y1_,
i1_.MODEL as y2_,
i1_.PRICE as y3_,
this_.TYPE as y4_
FROM GUITAR this_ inner JOIN INVENTORY i1_
on this_.ID=i1_.TYPEID
ORDER BY this_.TYPE asc
The result is a strongly typed dataset that provides the programmer with direct access to the properties of the class. To implement the DTO into the GuitarStore WPF program, the PopulateDataGrid() method within the MainWindow.xaml.cs file should resemble what is shown in Listing 3-56.
LISTING 3-56: Implementing a DTO into the GuitarStore WPF program
private void PopulateDataGrid()
{
NHibernateInventory nhi = new NHibernateInventory();
IList<InventoryDTO> GuitarInventory = nhi.GetInventoryDTO();
dataGridInventory.ItemsSource = GuitarInventory;
if (GuitarInventory != null)
{
dataGridInventory.Columns[0].Visibility =
System.Windows.Visibility.Hidden;
}
}
When you run the GuitarStore WPF program, the window produced should look like the one shown in Figure 3-16.
SUMMARY
Choosing which interface to use with an NHibernate implementation can be difficult. Both Chapter 1 and Chapter 2 have demonstrated that the ICriteria and the IQuery APIs contain powerful features, and each has the capabilities and functionality to support a vast majority of common database activities. My recommendation is simply to choose the interface that matches your programming philosophy and stick with it throughout the lifetime of your project. As you have seen in this chapter, ICriteria is as feature-rich as IQuery, but it also includes the QueryOver API, which utilizes lambda expressions. The ICriteria API has a very strong projection library that can be used to reduce the amount of data retrieved and to more precisely retrieve the data from a data source, by using the methods found within the Restrictions class. The next chapter covers another NHibernate Query API, LINQ to NHibernate, which has been available since the release of NHibernate version 3.0.