LINQ to NHibernate, JQuery/jqGrid SubGrid, HQL and ICriteria

My video on this topic can be found here.

We will do 4 things in the article.

  • Add a subGrid to a JQuery/jqGrid Grid.
  • Populate the Grid using ICriteria
  • Populate the subGrid using IQuery (HQL)
  • Change the ICriteria and HQL to use LINQ to NHibernate

First, let’s open the Views/Home/Index.aspx file and add the /jqGrid declarations and source code:

[sourcecode language=”javascript” padlinenumbers=”true” autolinks=”false” gutter=”false” toolbar=”false”]
<script src=”../../Scripts/jqgrid/src/grid.subgrid.js”
type=”text/javascript”>/script>
< id=’PostList
/table>div id=’PostList_pager’>/div>
script type=’text/javascript’
jQuery(document).ready(function() {
jQuery(‘#PostList’).jqGrid({

url: ‘/Home/PostList/’,
datatype: ‘json’,
mtype: ‘GET’,
colNames: [‘Id’, ‘Title’,
‘Text’, ‘PostedAt’,
‘BlogId’, ‘UserId’],
colModel:
[
{ name: ‘Id’, index: ‘Id’,
width: 60, align: ‘left’ },
{ name: ‘Title’, index: ‘Title’,
width: 200, align: ‘left’ },
{ name: ‘Text’, index: ‘Text’,
width: 200, align: ‘left’,
sortable: false },
{ name: ‘PostedAt’, index: ‘PostedAt’,
width: 75, align: ‘left’ },
{ name: ‘BlogId’, index: ‘BlogId’,
width: 60, align: ‘left’ },
{ name: ‘UserId’, index: ‘UserId’,
width: 75, align: ‘left’ },
],
pager: jQuery(‘#PostList_pager’),
rowNum: 25,
rowList: [5, 10, 25, 50],
height: ‘auto’,
width: ‘950’,
sortname: ‘ID’,
sortorder: “ASC”,
viewrecords: true,
multiselect: false,
subGrid: true,
subGridRowExpanded:
function(subgrid_id, row_id) {
var subgrid_table_id;
subgrid_table_id = subgrid_id + “_t”;
jQuery(“#” + subgrid_id).html(”
table id='”
+ subgrid_table_id
+ “‘ class=’scroll’/table”);
jQuery(“#” + subgrid_table_id).jqGrid({
url: ‘/Home/BlogList/’ + row_id,
datatype: ‘json’,
colNames: [‘Id’, ‘Title’,
‘Subtitle’,
‘AllowsComments’,
‘CreatedAt’],
colModel:
[
{ name: ‘Id’,
index: ‘Id’,
width: 60,
align: ‘left’ },
{ name: ‘Title’,
index: ‘Title’,
width: 200,
align: ‘left’ },
{ name: ‘Subtitle’,
index: ‘Subtitle’,
width: 200,
align: ‘left’,
sortable: false },
{ name: ‘AllowsComments’,
index: ‘AllowsComments’,
width: 75,
align: ‘left’ },
{ name: ‘CreatedAt’,
index: ‘CreatedAt’,
width: 60,
align: ‘left’ },
]
});
}
})
jQuery(“#PostList”).jqGrid(‘navGrid’,
‘#PostList_pager’, { add: false,
edit: false,
del: false,
search: false })
})
</script>
[/sourcecode]

We have 2 mapped classes, not show here, that match with the Grid and SubGrid. The PostList method populates the Grid and the BlogList method populates the SubGrid.

The Controllers/HomeController.cs file contain the PostList and methods. For example reasons only, I have used the NHibernate ICriteria interface to populate the JSON object in the PostList and I implemented the IQuery interface in the BlogList. The IQuery interface utilizes HQL. See below.




The PostList method creates an instance of the NHibernate Configuration object, load the Assembly, creates the SessionFactory and Session needed to execute the ICriteria List. Once I get the result set back from NHibernate, I need to convert it to a JSON arrary so I can easily display it in the jqGrid.

[sourcecode language=”csharp” autolinks=”false” gutter=”false” toolbar=”false”]
public ActionResult PostList(string sidx, string sord, int page, int rows)
{
Configuration config = new Configuration();
config.AddAssembly(“NHPosts”);
ISessionFactory factory = config.BuildSessionFactory();
ISession session = factory.OpenSession();

#region ICriteria

ICriteria criteria = session.CreateCriteria(typeof(Posts));
criteria.SetMaxResults(25);
IList<posts> posts = criteria.List<posts>();

#endregion

var jsonData = new
{
page = page,
rows = (
from po in posts
select new
{
id = po.Id,
cell = new string[] {
po.Id.ToString(),
po.Title,
po.Text,
po.PostedAt.ToString(),
po.BlogId.ToString(),
po.UserId.ToString()}
}).ToArray()
};

return Json(jsonData, JsonRequestBehavior.AllowGet);
}
[/sourcecode]

The BlogList is similar to the PostList. In this method you see I have a query as a string and am passing it to the IQuery object so that it can be executed. Make a special note that creating an NHibernate SessionFactory is very time consuming. The way I am doing it here is not best practice, however, it is a simple way of getting the point across. I recommend that you create a Session Provider and create a SessionFactory for each domain via a singleton pattern.

[sourcecode language=”csharp” autolinks=”false” gutter=”false” toolbar=”false”]
public ActionResult BlogList(string sidx, string sord, int page, int rows, int id)
{
Configuration config = new Configuration();

config.AddAssembly(“NHPosts”);
ISessionFactory factory = config.BuildSessionFactory();
ISession session = factory.OpenSession();

#region IQuery – HQL

IQuery query = session.CreateQuery(“from Blogs where Id = :BLOGID”);
query.SetString(“BLOGID”, id.ToString());
IList<blogs> blogs = query.List<blogs>();

#endregion

var jsonData = new
{
page = page,
records = 25,
rows = (
from bl in blogs
select new
{
id = bl.Id,
cell = new string[] {
bl.Id.ToString(),
bl.Title,
bl.Subtitle,
bl.AllowsComments.ToString(),
bl.CreatedAt.ToString()}
}).ToArray()
};

return Json(jsonData, JsonRequestBehavior.AllowGet);
}
[/sourcecode]

The final part is using LINQ to NHibernate. The release of NHibernate 3.0 comes the LINQ to NHibernate capability. At the time of writing this post 3.0 is still in Beta so examples will be made using the Beta version. Before you attempt to use the LINQ functionality be sure you add the reference to the 3.0 binaries and remove the 2.0 references.

To implement the LINQ to NHibernate in the PostList method, simply comment out the code within the #region ICriteria and replace it with this code:

[sourcecode language=”csharp” autolinks=”false” gutter=”false” toolbar=”false”]
IList<posts> posts = (from p in session.Query<posts>() select p).Take(25).ToList();
[/sourcecode]

It is good practice to limit the result set, in case there happens to be a million rows. This is in regards to the .Take(2) method. This LINQ query will return 25 rows.

Similar action to be taken in the BlogList method to convert to LINQ. Comment out the code within the #region IQuery – HQL and replace it with this code:

[sourcecode language=”csharp” autolinks=”false” gutter=”false” toolbar=”false”]
IList<blogs> blogs = (from b in session.Query<blogs>()
where b.Id == id
select b).ToList();
[/sourcecode]

But wait, there is more. As a special added feature I want to show you the how Futures work. It is a very nice feature which can reduce execution time and improve network performance. What futures do is combine multiple queries together, send them together (most often across the network) to the database, executes them all and returns multiple result sets. In contrast, if a transaction requires multiple queries to complete, it would typically require multiple round trips to the database across the network.




In the PostList method, we will implement a Futures example. Add a second query to run and mark it a Future.

[sourcecode language=”csharp” autolinks=”false” gutter=”false” toolbar=”false”]
var postsCount = session.Query<posts>().ToFuture();
[/sourcecode]

Modify the LINQ to NHibernate query we added to the PostList method to include the Future method.

[sourcecode language=”csharp” autolinks=”false” gutter=”false” toolbar=”false”]
IList<posts> posts = (from p in session.Query<posts>()
select p).Take(25).ToFuture().ToList();
[/sourcecode]

And then add a records variable to the jsonData creation logic.

[sourcecode language=”csharp” autolinks=”false” gutter=”false” toolbar=”false”]
var jsonData = new
{
page = page,
records = postsCount.Count(),
rows = (
from po in posts
select new
{
id = po.Id,
cell = new string[] {
po.Id.ToString(),
po.Title,
po.Text,
po.PostedAt.ToString(),
po.BlogId.ToString(),
po.UserId.ToString()}
}).ToArray()
};[/sourcecode]

And there you have it. A ‘select count(*) from Posts’ and a ‘select * from Posts’ will be executed within a single round trip to the database.

Download the source here.



Leave a Comment

Your email address will not be published.