4
Using LINQ to NHibernate
In the previous chapter, the ICritera API, a programmatic method of data retrieval, was discussed. Recall that the ICriteria interface contains the QueryOver API, which is a type-safe compile-time wrapper for the ICriteria API. It is possible to use lambda expressions in both QueryOver and LINQ to NHibernate. In this chapter, you will learn the following:
- Working with LINQ to NHibernate
- Using LINQ to NHibernate with lambda expressions
- Implementing paging using LINQ to NHibernate
- Understanding aggregate database functions with LINQ to NHibernate
INTRODUCTION
The release of NHibernate 3.0 included LINQ capabilities for use with NHibernate. LINQ is a very powerful querying tool that exists for objects, SQL, XML, and many other extensions for querying information sources. The LINQ to NHibernate provider implements most of the .NET LINQ operators found in the System.Linq namespace. Table 4-1 shows the operator types and execution method(s) provided with the LINQ to NHibernate provider.
The LINQ to NHibernate provider is deeply integrated with the .NET LINQ capabilities, and programmers can expect similar performance and functionality.
Figure 4-1 shows the numerous NHibernate classes used in the creation of LINQ to NHibernate capabilities. Recall from Chapter 1, “Getting Started with NHibernate 3,” the list of binaries required to utilize the NHibernate functionality. Prior to NHibernate 3.1, there was an additional component named Remotion.Data.Linq.dll. With NHibernate 3.1, ILMerge is used to combine two previously standalone DLLs into the NHibernate.dll. You need to know this because when the NHibernate source code is downloaded, the code for the QueryableBase<T>() class from which the NhQuerable<T> class inherits is not part of the package. The QueryableBase<T>() class is found in the Remotion.Data.Linq namespace and must be downloaded separately from the NHibernate source code. It is a very powerful library and worth downloading and studying.
Like the IQuery, ICriteria, and QueryOver capabilities, LINQ to NHibernate is simply a different API for querying your database with NHibernate. It is most similar to the syntax found in the QueryOver class, as lambda expressions are commonly used. However, there is no dependency between the QueryOver and the NHibernate LINQ provider. Listing 4-1 shows the IQuery, QueryOver, and LINQ to NHibernate methods, all of which render the same result set. Notice also that the NHibernate-generated SQL queries are the same.
LISTING 4-1: IQuery, QueryOver, and LINQ queries for comparison
NHibernate LINQ:
IList<Inventory> result = (from inv in session.Query<Inventory>()
where inv.TypeId == Id
orderby inv.Builder
select inv).ToList();
select inventory0_.ID as ID1_,
inventory0_.TYPEID as TYPEID1_,
inventory0_.BUILDER as BUILDER1_,
inventory0_.MODEL as MODEL1_,
inventory0_.QOH as QOH1_,
inventory0_.COST as COST1_,
inventory0_.PRICE as PRICE1_,
inventory0_.RECEIVED as RECEIVED1_
from INVENTORY inventory0_
where inventory0_.TYPEID=@p0
orderby inventory0_.BUILDER asc;
@p0 = e6f2a2ab-ca6d-4874-8874-6bb9baccffcb [Type: Guid (0)]
QueryOver:
IList<Inventory> result = session.QueryOver<Inventory>()
.Where(i => i.TypeId == Id)
.OrderBy(i => i.Builder).Asc
.List<Inventory>();
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 = e6f2a2ab-ca6d-4874-8874-6bb9baccffcb [Type: Guid (0)]
IQuery:
IQuery query
= session.CreateQuery(“from Inventory where TypeId = :TypeId order by Builder”)
.SetGuid(“TypeId”, Id);
select inventory0_.ID as ID1_,
inventory0_.TYPEID as TYPEID1_,
inventory0_.BUILDER as BUILDER1_,
inventory0_.MODEL as MODEL1_,
inventory0_.QOH as QOH1_,
inventory0_.COST as COST1_,
inventory0_.PRICE as PRICE1_,
inventory0_.RECEIVED as RECEIVED1_
from INVENTORY inventory0_
where inventory0_.TYPEID=@p0
orderby inventory0_.BUILDER;
@p0 = e6f2a2ab-ca6d-4874-8874-6bb9baccffcb [Type: Guid (0)]
WORKING WITH LINQ TO NHIBERNATE
The interface into LINQ to NHibernate is via the Query<T>() method, which can be found in the LinqExtentionMethods class located in the NHibernate.Linq namespace. The Query<T>() method returns an IQueraryable<T> implementation, which is an implementation of the System.Linq.IQueryProvider via the NhQueryProvider class. Note that several methods in the NhQueryProvider create and pass instances of IQuery. There is some commonality between LINQ to NHibernate and the IQuery capabilities. For example, both the NhQueryProvider.Execute() and the NhQueryProvider.ExecuteFuture() methods create and use an instance of an IQuery interface. Each of those methods passes that IQuery object to other LINQ to NHibernate methods, to be used with the IQuery CreateQuery() and SetParamter() methods. These methods help with the generation of the SQL query executed via LINQ to NHibernate.
Implementing and using LINQ to NHibernate within the GuitarStore WPF solution can be done in the same way IQuery, ICriteria, or QueryOver are implemented. In this section, you will accomplish the following:
- Create a new method named GetLINQInventory() that populates the DataGrid with data from the INVENTORY table.
- Create a new method named GetLINQFilteredInventory() that filters the result set based on a selected guitar type.
- Create a new method named SearchInventoryLINQ() that accepts a search parameter and returns the matching results.
- Modify the GetLINQInventory() method to support paging.
- Modify the GetLINQInventory() method to use Futures.
- Implement all methods into the GuitarStore WPF program.
First, open the NHibernateInventory.cs file, which is found in the NHibernate.GuitarStore project, and add the method called GetLINQInventory(). The method should resemble what is shown in Listing 4-2. Notice that the LINQ capabilities require the addition of the NHibernate.Linq using directive.
LISTING 4-2: LINQ to NHibernate query to retrieve the guitar inventory
using NHibernate.Linq;
public IList GetLINQInventory()
{
using (ITransaction transaction = Session.BeginTransaction())
{
IQueryable<Inventory> query = (from l in Session.Query<Inventory>()
select l);
return query.ToList();
}
}
Generated SQL:
select inventory0_.ID as ID1_,
inventory0_.TYPEID as TYPEID1_,
inventory0_.BUILDER as BUILDER1_,
inventory0_.MODEL as MODEL1_,
inventory0_.QOH as QOH1_,
inventory0_.COST as COST1_,
inventory0_.PRICE as PRICE1_,
inventory0_.RECEIVED as RECEIVED1_
from INVENTORY inventory0_
Next, open the MainWindow.xaml.cs file and modify the PopulateDataGrid() method so that it uses the method created in Listing 4-2. Listing 4-3 shows how it should look. Note that the GetLINQInventory() method returns a strongly typed result set; therefore, you don't need to create a DataTable before binding the result set to the DataGrid. The result of the method is bound directly to the DataGrid.
LISTING 4-3: Using LINQ to NHibernate in the GuitarStore WPF program
private void PopulateDataGrid()
{
NHibernateInventory nhi = new NHibernateInventory();
IList GuitarInventory = nhi.GetLINQInventory();
dataGridInventory.ItemsSource = GuitarInventory;
if (GuitarInventory != null)
{
dataGridInventory.Columns[0].Visibility = System.Windows.Visibility.Hidden;
dataGridInventory.Columns[1].Visibility = System.Windows.Visibility.Hidden;
dataGridInventory.Columns[9].Visibility = System.Windows.Visibility.Hidden;
}
}
Now create a new method in the NHibernateInventory class, in the NHibernate.GuitarStore project, that accepts a guitar type as a parameter and returns a list of matching data. The code segment should resemble the method shown in Listing 4-4.
LISTING 4-4: LINQ to NHibernate method with Where clause
public IList GetLINQInventory(Guid Id)
{
using (ITransaction transaction = Session.BeginTransaction())
{
try
{
IQueryable<Inventory> query = (from inv in Session.Query<Inventory>()
where inv.TypeId == Id
orderby inv.Builder
select inv);
transaction.Commit();
return query.ToList();
}
catch (Exception ex)
{
transaction.Rollback();
throw;
}
}
}
Generated SQL:
select inventory0_.ID as ID1_,
inventory0_.TYPEID as TYPEID1_,
inventory0_.BUILDER as BUILDER1_,
inventory0_.MODEL as MODEL1_,
inventory0_.QOH as QOH1_,
inventory0_.COST as COST1_,
inventory0_.PRICE as PRICE1_,
inventory0_.RECEIVED as RECEIVED1_
from INVENTORY inventory0_
where inventory0_.TYPEID=@p0
orderby inventory0_.BUILDER asc;
@p0 = 471c5b3f-19da-4fcb-8e9f-48dd17a00a3d [Type: Guid (0)]
Next, modify the comboBoxGuitarTypes_SelectionChanged() method found in the MainWindow.xaml.cs file of the GuitarStore project so that it calls the method created in Listing 4-4. Listing 4-5 provides an example of the modified SelectionChanged() method.
LISTING 4-5: Implementing the filtered LINQ to NHibernate 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();
dataGridInventory.ItemsSource = nhi.GetLINQInventory(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;
}
}
After implementing and executing the GuitarStore WPF program, selecting a guitar type from the ComboBox will result in a DataGrid being populated with a list of guitar inventory matching that guitar type. Figure 4-2 shows an example of how the GuitarStore WPF program looks.
The next requirement to implement is the search capability when using LINQ to NHibernate. The LINQ to NHibernate provider exposes an execution method called Contains()that works nicely with searches. Listing 4-6 shows how to use LINQ to retrieve all models from the INVENTORY table that match the user-entered search parameter.
LISTING 4-6: Using the Contains() method with LINQ to search
public IList SearchInventoryLINQ(string guitarType) { using (ITransaction transaction = Session.BeginTransaction()) { try { IQueryable<Inventory> query = (from inv in Session.Query<Inventory>() where inv.Model.Contains(guitarType) orderby inv.Builder select inv); transaction.Commit(); return query.ToList(); } catch (Exception ex) { transaction.Rollback(); throw; } } } Generated SQL: select inventory0_.ID as ID1_, inventory0_.TYPEID as TYPEID1_, inventory0_.BUILDER as BUILDER1_, inventory0_.MODEL as MODEL1_, inventory0_.QOH as QOH1_, inventory0_.COST as COST1_, inventory0_.PRICE as PRICE1_, inventory0_.RECEIVED as RECEIVED1_ from INVENTORY inventory0_ where inventory0_.MODEL like ('%'+@p0+'%') order by inventory0_.BUILDER asc; @p0 = '%L%' [Type: String (4000)]
The returned list is strongly typed; therefore, the properties within the IList can be accessed directly.
Adding more capabilities to a program is what makes a system feature rich and useful. LINQ provides many logical operators that support almost any program requirement. For example, Listing 4-7 includes a method that returns the inventory received in a specified time period.
LISTING 4-7: LINQ query using the AddDays() method
public IList GetRecentInventory(double daysAgo)
{
using (ITransaction transaction = Session.BeginTransaction())
{
IQueryable<Inventory> query= (from inv in Session.Query<Inventory>()
where inv.Received >
DateTime.Today.AddDays(daysAgo)
orderby inv.Builder
select inv);
return query.ToList();
}
}
Generated SQL:
select inventory0_.ID as ID1_,
inventory0_.TYPEID as TYPEID1_,
inventory0_.BUILDER as BUILDER1_,
inventory0_.MODEL as MODEL1_,
inventory0_.QOH as QOH1_,
inventory0_.COST as COST1_,
inventory0_.PRICE as PRICE1_,
inventory0_.RECEIVED as RECEIVED1_
from INVENTORY inventory0_
where inventory0_.RECEIVED>@p0
order by inventory0_.BUILDER asc;
@p0 = 5/16/2011 12:00:00 AM [Type: DateTime (0)]
By setting the daysAgo parameter to -5 and passing it to the preceding method, NHibernate generates the presented SQL query.
IMPLEMENTING PAGING
The methods used to implement paging with LINQ are Take() and Skip(). Each method takes an integer as a parameter. Take() is similar to the IQuery and ICriteria SetMaxResult() methods, while the Skip() method is equivalent to the SetFirstResult() method. Listing 4-8 shows the implementation of paging using LINQ from within the NHibernate.GuitarStore class.
LISTING 4-8: Paging with LINQ
public IList<Inventory> GetLINQInventory(int take, int skip, out int totalCount)
{
using (ITransaction transaction = Session.BeginTransaction())
{
try
{
IQueryable<Inventory> query = (from inv in Session.Query<Inventory>()
orderby inv.Builder
select inv).Take(take).Skip(skip);
IQueryable<Inventory> countResult = Session.Query<Inventory>();
totalCount = countResult.Count();
transaction.Commit();
return query.ToList<Inventory>();
}
catch (Exception ex)
{
transaction.Rollback();
throw;
}
}
}
Generated SQL using 2 database round-trips:
select cast(count(*) as INT) as col_0_0_ from INVENTORY inventory0_
select TOP (@p0)
inventory0_.ID as ID1_,
inventory0_.TYPEID as TYPEID1_,
inventory0_.BUILDER as BUILDER1_,
inventory0_.MODEL as MODEL1_,
inventory0_.QOH as QOH1_,
inventory0_.COST as COST1_,
inventory0_.PRICE as PRICE1_,
inventory0_.RECEIVED as RECEIVED1_
from INVENTORY inventory0_
order by inventory0_.BUILDER asc;
@p0 = 25 [Type: Int32 (0)]
Next, implement the GetLINQInventory() method into the GuitarStore WPF program by modifying the PopulateDataGrid() method, as shown in Listing 4-9.
LISTING 4-9: Implementing paging into the GuitarStore progam using LINQ
private void PopulateDataGrid()
{
NHibernateInventory nhi = new NHibernateInventory();
IList<Inventory> GuitarInventory = null;
int inventoryCount = nhi.GetLINQInventory(MaxResult, FirstResult,
out GuitarInventory);
dataGridInventory.ItemsSource = GuitarInventory;
if (GuitarInventory != null)
{
dataGridInventory.Columns[0].Visibility = System.Windows.Visibility.Hidden;
dataGridInventory.Columns[1].Visibility = System.Windows.Visibility.Hidden;
dataGridInventory.Columns[9].Visibility = System.Windows.Visibility.Hidden;
}
labelPaging.Content = “Retrieved ” + FirstResult.ToString() +
“ to ” + (FirstResult + GuitarInventory.Count).ToString() +
“ of ” + inventoryCount.ToString();
totalCount = inventoryCount;
}
The GuitarStore WPF program now resembles Figure 4-3. Notice that the stoplight is yellow, meaning two database round-trips were required to populate the DataGrid.
When implementing paging, it is always a good idea to batch the queries together. Although Futures can be used to load data as late in a process as possible, it is still useful to batch queries together to reduce the number of round-trips to the database. Listing 4-10 shows the GetLINQInventory() method, which now includes the ToFuture() method. The ToFuture() method batches the queries together and executes them all using a single database round-trip. Notice that when using the ToFuture() method, an IEnumerable<out T> is returned.
LISTING 4-10: Using Futures with LINQ to NHibernate
public IList<Inventory> GetLINQInventory(int take, int skip, out int totalCount)
{
using (ITransaction transaction = Session.BeginTransaction())
{
try
{
IEnumerable<Inventory> query = (from inv in Session.Query<Inventory>()
orderby inv.Builder
select inv).Take(take).Skip(skip)
.ToFuture<Inventory>();
IEnumerable<Inventory> countResult = Session.Query<Inventory>().ToFuture();
totalCount = countResult.Count();
transaction.Commit();
return query.ToList<Inventory>();
}
catch (Exception ex)
{
transaction.Rollback();
throw;
}
}
}
Generated SQL using single database round-trip and FirstResult = 26:
SELECT TOP (@p0)
ID1_,
TYPEID1_,
BUILDER1_,
MODEL1_,
QOH1_,
COST1_,
PRICE1_,
RECEIVED1_
FROM (select inventory0_.ID as ID1_,
inventory0_.TYPEID as TYPEID1_,
inventory0_.BUILDER as BUILDER1_,
inventory0_.MODEL as MODEL1_,
inventory0_.QOH as QOH1_,
inventory0_.COST as COST1_,
inventory0_.PRICE as PRICE1_,
inventory0_.RECEIVED as RECEIVED1_,
ROW_NUMBER()
OVER(ORDER BY inventory0_.BUILDER) as __hibernate_sort_row
from INVENTORY inventory0_) as query
WHERE query.__hibernate_sort_row > @p1
ORDER BY query.__hibernate_sort_row;
select inventory0_.ID as ID1_,
inventory0_.TYPEID as TYPEID1_,
inventory0_.BUILDER as BUILDER1_,
inventory0_.MODEL as MODEL1_,
inventory0_.QOH as QOH1_,
inventory0_.COST as COST1_,
inventory0_.PRICE as PRICE1_,
inventory0_.RECEIVED as RECEI;
@p0 = 25 [Type: Int32 (0)],
@p1 = 26 [Type: Int32 (0)]
The preceding method implements the Take() and Skip() methods along with the ToFuture() execution method. The ToFuture() method batches the LINQ query, which selects the page of data, together with the query for selecting the total count. Both queries are executed via a single round-trip to the database. As shown in Figure 4-4, when running the paging from the LINQ tab in the GuitarStore WPF program, the stoplight is green, indicating that the two queries are being batched together. Selecting the View SQL button reinforces this.
UNDERSTANDING LINQ TO NHIBERNATE WITH LAMBDA EXPRESSIONS
LINQ to NHibernate supports the use of lambda expressions. In the previous section, the long form of LINQ was used to retrieve the data from the INVENTORY table, to limit the results based on a selected guitar type, and to implement batching using the ToFuture() method. In this section, lambda expressions are used with the Query<T>() method to do the following:
- Populate the DataGrid with data from the INVENTORY table using a new method named GetLINQInventoryLE().
- Filter the result set based on a selected guitar type.
- Limit the result set using a search parameter and return the matching results using a new method called SearchInventoryLINQLE().
- Use paging with the ToFuture() method.
The first action to take is to add a new method named GetLINQInventoryLE() to the NHibernateInventory class found in the NHibernate.GuitarStore project. Listing 4-11 shows the code for the GetLINQInventoryLE() method.
NOTE Sometimes project names can get a little confusing, so here's a reminder: There is a GuitarStore solution, a GuitarStore WPF project, and an NHibernate.GuitarStore project. The last two are projects within the GuitarStore solution.
LISTING 4-11: Retrieving the guitar inventory using a lambda expression
using NHibernate.Linq; public IList GetLINQInventoryLE() { using (ITransaction transaction = Session.BeginTransaction()) { IQueryable<Inventory> query = Session.Query<Inventory>(); return query.ToList(); } } Generated SQL: select inventory0_.ID as ID1_, inventory0_.TYPEID as TYPEID1_, inventory0_.BUILDER as BUILDER1_, inventory0_.MODEL as MODEL1_, inventory0_.QOH as QOH1_, inventory0_.COST as COST1_, inventory0_.PRICE as PRICE1_, inventory0_.RECEIVED as RECEIVED1_ from INVENTORY inventory0_
Next, add another method to the NHibernateInventory class that accepts a search parameter and filters the result set using the provided value. Listing 4-12 provides an example of how this is achieved.
LISTING 4-12: LINQ Where clause using a lambda expression
public IList GetLINQInventoryLE(Guid Id)
{
using (ITransaction transaction = Session.BeginTransaction())
{
IQueryable<Inventory> query = Session.Query<Inventory>()
.Where(ti => ti.TypeId == Id);
return query.ToList();
}
}
Generated SQL:
select inventory0_.ID as ID1_,
inventory0_.TYPEID as TYPEID1_,
inventory0_.BUILDER as BUILDER1_,
inventory0_.MODEL as MODEL1_,
inventory0_.QOH as QOH1_,
inventory0_.COST as COST1_,
inventory0_.PRICE as PRICE1_,
inventory0_.RECEIVED as RECEIVED1_
from INVENTORY inventory0_
where inventory0_.TYPEID=@p0
orderby inventory0_.BUILDER asc;
@p0 = 471c5b3f-19da-4fcb-8e9f-48dd17a00a3d [Type: Guid (0)]
Implement the preceding method into the GuitarStore WPF program by modifying the comboBoxGuitarTypes_SelectionChanged() method. Open the MainWindow.xaml.cs file and change the SelectionChanged() method so that it reflects what is shown Listing 4-13.
LISTING 4-13: Implementing LINQ lambda expression Where clause into WPF
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.GetLINQInventoryLE(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;
}
}
The result of running the GuitarStore WPF program and selecting a guitar type from the ComboBox is shown in Figure 4-5.
Next, create the SearchInventoryLINQLE() method, which accepts a search parameter and returns a list containing the matching data results. Listing 4-14 provides an example of this method.
LISTING 4-14: Using a lambda expression to search for data
public IList SearchInventoryLINQLE(string guitarType)
{
using (ITransaction transaction = Session.BeginTransaction())
{
IQueryable<Inventory> query = Session.Query<Inventory>()
.Where(m => m.Model.Contains(guitarType));
return query.ToList();
}
}
Generated SQL:
select inventory0_.ID as ID1_,
inventory0_.TYPEID as TYPEID1_,
inventory0_.BUILDER as BUILDER1_,
inventory0_.MODEL as MODEL1_,
inventory0_.QOH as QOH1_,
inventory0_.COST as COST1_,
inventory0_.PRICE as PRICE1_,
inventory0_.RECEIVED as RECEIVED1_
from INVENTORY inventory0_
where inventory0_.MODEL like ('%'+@p0+'%'),
@p0 = '%l%' [Type: String (4000)]
Before proceeding to the implementation of the SearchInventoryLINQLE() method into the GuitarStore WPF program, it would be beneficial to test the method in the NHibernate.GuitarStore console application. To do this, open the Program.cs file found within the NHibernate.GuitarStore.Console project and add the code shown in Listing 4-15 to the Main() method.
LISTING 4-15: Testing the lambda expression from a console application
IList list52 = nhi.SearchInventoryLINQLE(“%l%”);
Console.WriteLine(“SearchInventoryLINQLE(% l %): ” +
list52.Count.ToString() + " results”);
Set the NHibernate.GuitarStore.Console application as the startup project and press F5. If the method returns the expected results, then you have some assurance that the implementation will go smoothly.
Next, implement the method shown in Listing 4-14 into the GuitarStore WPF program by modifying the buttonSearch_Click() method found in the MainWindow.xaml.cs file of the GuitarStore project so that it is identical to the code shown in Listing 4-16.
LISTING 4-16: Implementing search using lambda expressions into a WPF program
private void buttonSearch_Click(object sender, RoutedEventArgs e) { NHibernateInventory nhi = new NHibernateInventory(); IList GuitarInventory = nhi.SearchInventoryLINQLE(“%” + 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; } }
Ordering the retrieved data helps make a program more useful. Although most DataGrid controls allow users to perform this action, it is good practice to provide the data in the format required from the beginning. Two methods can be used to implement the ordering of data: OrderBy() and ThenByDecending(). Modify the GetLINQInventory() method to use these two methods with lambda expressions. Listing 4-17 provides an example of the modified methods.
LISTING 4-17: Using a lambda expression with OrderBy() and ThenByDecending()
public IList GetLINQInventoryLE()
{
using (ITransaction transaction = Session.BeginTransaction())
{
IQueryable<Inventory> query = Session.Query<Inventory>()
.OrderBy(i => i.Builder)
.ThenByDescending(i => i.Price);
return query.ToList();
}
}
Generated SQL:
select inventory0_.ID as ID1_,
inventory0_.TYPEID as TYPEID1_,
inventory0_.BUILDER as BUILDER1_,
inventory0_.MODEL as MODEL1_,
inventory0_.QOH as QOH1_,
inventory0_.COST as COST1_,
inventory0_.PRICE as PRICE1_,
inventory0_.RECEIVED as RECEIVED1_
from INVENTORY inventory0_
order by inventory0_.BUILDER asc,
inventory0_.PRICE desc
Figure 4-6 shows how the data within the DataGrid on the GuitarStore WPF window is presented using the OrderBy() and ThenByDecending() methods.
The last action to take in this section is to create a method for paging using LINQ with lambda expressions and the ToFuture() method. Listing 4-18 gives you an idea of how a method like this should look.
LISTING 4-18: LINQ with lambda expressions that implement paging
public int GetLINQInventoryLE(int take, int skip, out IList<Inventory> resultSet)
{
using (ITransaction transaction = Session.BeginTransaction())
{
try
{
IEnumerable<Inventory> query = Session.Query<Inventory>()
.Take(take).Skip(skip)
.ToFuture<Inventory>();
IEnumerable<Inventory> countResult = Session.Query<Inventory>().ToFuture();
int totalCount = countResult.Count();
resultSet = query.ToList<Inventory>();
transaction.Commit();
return totalCount;
}
catch (Exception ex)
{
transaction.Rollback();
throw;
}
}
}
Generated SQL using 1 database round-trip:
select TOP (@p0)
inventory0_.ID as ID1_,
inventory0_.TYPEID as TYPEID1_,
inventory0_.BUILDER as BUILDER1_,
inventory0_.MODEL as MODEL1_,
inventory0_.QOH as QOH1_,
inventory0_.COST as COST1_,
inventory0_.PRICE as PRICE1_,
inventory0_.RECEIVED as RECEIVED1_
from INVENTORY inventory0_;
select inventory0_.ID as ID1_,
inventory0_.TYPEID as TYPEID1_,
inventory0_.BUILDER as BUILDER1_,
inventory0_.MODEL as MODEL1_,
inventory0_.QOH as QOH1_,
inventory0_.COST as COST1_,
inventory0_.PRICE as PRICE1_,
inventory0_.RECEIVED as RECEI;
@p0 = 25 [Type: Int32 (0)]ory0_;
Implement this method into the GuitarStore WPF program by using it from within the PopulateDataGrid() method within the MainWindow.xaml.cs file located in the GuitarStore project. Listing 4-19 presents an example of the code segment.
LISTING 4-19: Implement paging using LINQ with lambda expressions in WPF
private void PopulateDataGrid()
{
NHibernateInventory nhi = new NHibernateInventory();
IList<Inventory> GuitarInventory = null;
int inventoryCount = nhi.GetLINQInventoryLE(MaxResult, FirstResult,
out GuitarInventory);
dataGridInventory.ItemsSource = GuitarInventory;
if (GuitarInventory != null)
{
dataGridInventory.Columns[0].Visibility = System.Windows.Visibility.Hidden;
dataGridInventory.Columns[1].Visibility = System.Windows.Visibility.Hidden;
dataGridInventory.Columns[9].Visibility = System.Windows.Visibility.Hidden;
}
labelPaging.Content = “Retrieved ” + FirstResult.ToString() +
“ to ” + (FirstResult + GuitarInventory.Count).ToString() +
“ of ” + inventoryCount.ToString();
totalCount = inventoryCount;
}
Figure 4-7 illustrates the GuitarStore WPF window after implementation of the preceding GetLINQInventoryLE() paging method. Notice that the stoplight is green. The green stoplight and the tool tip note confirm that the multiple methods required to support paging of the guitar inventory are being batched and executed using a single round-trip to the database.
UNDERSTANDING AGGREGATE DATABASE FUNCTIONS
Aggregates are discussed in more detail in both Chapters 2 and 3, which cover the IQuery and ICriteria interfaces, respectively. The common aggregate methods (SUM, MIN, MAX, and AVG) can be run via the LINQ to NHibernate provider. In this section, the aggregate database functions will be implemented with LINQ using a combination of the long form and lambda expressions. The following actions should be taken to implement aggregate database functions into the GuitarStore solution using LINQ to NHibernate:
- Create a new class, AggregateResults, to store the results of the aggregate database queries.
- Add SUM, MIN, MAX, and AVG methods to the NHibernateInventory class.
- Modify the Click event of the SUM, MIN, MAX, and AVG buttons created in Chapter 2.
First, add a new class named AggregateResults to the DataAccess folder of the NHibernate.GuitarStore project. Listing 4-20 provides the example code for this class.
LISTING 4-20: The AggregateResults class
namespace NHibernate.GuitarStore.DataAccess
{
public class AggregateResults
{
public string GuitarType { get; set; }
public decimal? Value { get; set; }
}
}
Next, open the NHibernateInventory.cs file located in the NHibernate.GuitarStore project and add the following methods shown in Listings 4-21 through 4-24.
LISTING 4-21: SUM LINQ aggregate function
public IList<AggregateResults> GetInventorySum()
{
using (ITransaction transaction = Session.BeginTransaction())
{
try
{
IQueryable<AggregateResults> linqSUM =
(from g in Session.Query<Guitar>()
join i in Session.Query<Inventory>() on g.Id equals i.TypeId
group i by g.Type into r
select new AggregateResults
{
GuitarType = r.Key, Value = r.Sum(i => i.Price)
});
transaction.Commit();
return linqSUM.ToList();
}
catch (Exception ex)
{
transaction.Rollback();
throw;
}
}
}
Generated SQL:
select guitar0_.TYPE as col_0_0_,
cast(sum(inventory1_.PRICE) as DECIMAL(19,5)) as col_1_0_
from GUITAR guitar0_,
INVENTORY inventory1_
where inventory1_.TYPEID=guitar0_.ID
group by guitar0_.TYPE
LISTING 4-22: MIN LINQ aggregate function
public IList<AggregateResults> GetInventoryMin()
{
using (ITransaction transaction = Session.BeginTransaction())
{
try
{
IQueryable<AggregateResults> linqMIN =
(from g in Session.Query<Guitar>()
join i in Session.Query<Inventory>() on g.Id equals i.TypeId
group i by g.Type into r
select new AggregateResults
{
GuitarType = r.Key,
Value = r.Min(i => i.Price)
});
transaction.Commit();
return linqMIN.ToList();
}
catch (Exception ex)
{
transaction.Rollback();
throw;
}
}
}
Generated SQL:
select guitar0_.TYPE as col_0_0_,
cast(min(inventory1_.PRICE) as DECIMAL(19,5)) as col_1_0_
from GUITAR guitar0_,
INVENTORY inventory1_
where inventory1_.TYPEID=guitar0_.ID
group by guitar0_.TYPE
LISTING 4-23: MAX LINQ aggregate function
public IList<AggregateResults> GetInventoryMax()
{
using (ITransaction transaction = Session.BeginTransaction())
{
try
{
IQueryable<AggregateResults> linqMAX =
(from g in Session.Query<Guitar>()
join i in Session.Query<Inventory>() on g.Id equals i.TypeId
group i by g.Type into r
select new AggregateResults
{
GuitarType = r.Key,
Value = r.Max(i => i.Price)
});
transaction.Commit();
return linqMAX.ToList();
}
catch (Exception ex)
{
transaction.Rollback();
throw;
}
}
}
Generated SQL:
select guitar0_.TYPE as col_0_0_,
cast(max(inventory1_.PRICE) as DECIMAL(19,5)) as col_1_0_
from GUITAR guitar0_,
INVENTORY inventory1_
where inventory1_.TYPEID=guitar0_.ID
group by guitar0_.TYPE
LISTING 4-24: AVG LINQ aggregate function
public IList<AggregateResults> GetInventoryAvg()
{
using (ITransaction transaction = Session.BeginTransaction())
{
try
{
IQueryable<AggregateResults> linqAVG =
(from g in Session.Query<Guitar>()
join i in Session.Query<Inventory>() on g.Id equals i.TypeId
group i by g.Type into r
select new AggregateResults
{
GuitarType = r.Key,
Value = r.Average(i => i.Price)
});
transaction.Commit();
return linqAVG.ToList();
}
catch (Exception ex)
{
transaction.Rollback();
throw;
}
}
}
Generated SQL:
select guitar0_.TYPE as col_0_0_,
cast(avg(inventory1_.PRICE) as DECIMAL(19,5)) as col_1_0_
from GUITAR guitar0_,
INVENTORY inventory1_
where inventory1_.TYPEID=guitar0_.ID
group by guitar0_.TYPE
The final step is to implement the aggregate database functions into the GuitarStore WPF program. To do this, open the MainWindow.xaml.cs file found within the GuitarStore WPF project and modify the Click event for each of the four aggregate Button controls (SUM, MIN, MAX, and AVG). Listing 4-25 presents the code required to implement the MIN aggregate database function into the buttonMinimum_Click() method within the GuitarStore WPF window.
LISTING 4-25: Implementing the MIN aggregate function into WPF
private void buttonMinimum_Click(object sender, RoutedEventArgs e) { NHibernateInventory nhi = new NHibernateInventory(); dataGridInventory.ItemsSource = nhi.GetInventoryMin(); }
Running the GuitarStore WPF program and clicking the Minimum button will render what is shown in Figure 4-8.
SUMMARY
In this chapter, you learned that the LINQ to NHibernate API supports two implementation approaches. The first is the SQL/long form method whereby the from X in Y select format is used. The second approach uses lambda expressions, as discussed in Chapter 3, “Using ICriteria,” in regard to the QueryOver API. Both LINQ to NHibernate approaches are rooted in the Query() method, which is based on the .NET LINQ libraries. In the next chapter, the primary topic is no longer the retrieval of data, which has been the focus so far, but the insertion of data and how to manage an entity's state.