GridDataExtensions ToGridData using nhibernate IQueryable

Sep 6, 2011 at 1:29 PM

Hi, not finding anything about using nhibernate with this framework, I added this discussion to maybe help other people.

        public ActionResult ListGrid(SearchModel searchModel)
            IQueryable<Criteria> criteria = _svc.FindAll();
            return Json(criteria.ToGridData(searchModel), JsonRequestBehavior.AllowGet);

--> ListGrid is an action method in the controller called by the jqGrid

-->_svc.FindAll() is a wrapper around the nhibernate session returning me a IQueryable object.

When trying to use in the controller the ToGridData extension directly on the nhibernate IQuerable it was throwing a LazyInitialization exception (Could not initialize proxy - no Session).

After some googling, it was a normal behaviour, the json serializer is trying to serialize everything, even the not loaded related entities (lazy loading), causing the error.

Two solutions:

1. load everything (no lazy loading) : I think that everyone agree that this is a 'not done' solution. You are loading things not needed and you will send out data not used in your view, opening maybe security holes to not so gently users...

2. create a new entity with only the data you need to build your jqGrid

        public ActionResult ListGrid(SearchModel searchModel)
            IQueryable<Criteria> criteria = _svc.FindAll().Select(criterion => new Criteria() { Id = criterion.Id, Modified = criterion.Modified, Remark = criterion.Remark });

            return Json(criteria.ToGridData(searchModel), JsonRequestBehavior.AllowGet);

Sep 6, 2011 at 5:33 PM

Hey evb,

First, thanks for sharing!

Second. I think even without using the MVC.Controls library, its a best practice to create a new set of classes (DTO\Model) that contain only what the View really need thus reducing the clutter of ORM proxies (be them EF or NH), and unused properties.

Sep 7, 2011 at 9:20 AM

Yes, I agree with your second thought if it will be serialized and sent over the wire, but in the other case, when only used to build the view server-side, I'm not following this practice.

In our 'ViewModel' we have of course extra view properties and the database model available. I find it an overkill to create a new set of classes with only the properties really needed in the view. In case a new database model property is needed in the view, you must adapt the view and the created view class (=recompile). If you have directly the database model available, you must just adapt the view (= no recompile).

To continue the first discussion, there is a third solution.

3. In the second solution we where reusing an existing database model class and only filling in the needed values. This solution caused me a problem with 'calculated' properties, properties constructed inside the database model class, but not residing in the database. Putting these properties also in the Select is not possible, nhibernate will complain about non existing query objects.

I didn't like the idea to be obliged to create these extra new view model classes, but what about transforming the data in an anonymous object in this case?

Fortunately, the framework is helping us, but not the documentation :-( (I know, 'Real programmers don't document' Who said that again? ;-))) )

        public ActionResult ListGrid(SearchModel searchModel)
            IQueryable<Criteria> criteria = _svc.FindAll();

            return Json(criteria.ToGridData(searchModel, Columns.CriteriaColumns), JsonRequestBehavior.AllowGet);

--> Colums is a static class describing the columns needed in the jqGrid. It is still strong typed.

--> In the method ToGridData an extra method is called UseValuesFromColumnExpressions with your columns as parameter : this method is transforming the strong typed columns to a anonymous object list.

    public static class Columns
        #region Criteria
        private static readonly GridColumnModelList<Criteria> _criteriaColumns = CreateCriteriaColumns();

        public static GridColumnModelList<Criteria> CriteriaColumns { get { return _criteriaColumns; } }

        private static GridColumnModelList<Criteria> CreateCriteriaColumns()
            GridColumnModelList<Criteria> gcl = new GridColumnModelList<Criteria>();
            gcl.Add(x => x.Id).SetAsPrimaryKey().SetHidden(true);
            gcl.Add(x => x.Modified).SetCaption(CoreResources.Strings.CreatedDate).SetCellType(GridCellType.DATE).SetFormatter("'date'").SetCustomAttributes("formatoptions:{newformat: 'ISO8601Long'}");
            gcl.Add(x => x.TranslationProxyIdName.Description).SetCaption(CoreResources.Strings.Subject);
            gcl.Add(x => x.Remark);
            gcl.Add(x => x.Weight).SetCaption(CoreResources.Strings.Weight).SetCellType(GridCellType.FLOAT);
            gcl.Add(x => x.Score).SetCaption(CoreResources.Strings.Score).SetCellType(GridCellType.FLOAT);
            return gcl;

I'm new to this framework, so this is for me a continuing learning. Correct me if what I'm producing is wrong.

Sep 7, 2011 at 10:20 AM

Not only you are correct, in the Renderers view\action in the Example project there's a sample doing just that

Sep 8, 2011 at 1:49 PM

The next episode, this time with a question.

I did use the third solution because of 'calculated' properties.

But now I have a problem when I permit users to sort on the column with the 'calculated' property.

It will go back to the server and treated in the SearchModel class as a query field.

It will be added to the modelQuery, causing nhibernate to complain about non existing query objects

        public IQueryable<T> ApplyFilters<T>(IQueryable<T> modelQuery)
            if (!string.IsNullOrEmpty(this.SortColumnName))
                res = res.OrderBy(this.SortColumnName + " " + this.SortOrder);

            return res;

For the moment I deactivated the sort possibility on this specific column, but I know that I will receive the question from users why they can't sort on it...

Do you see a solution?

Your example with the Renderers view\action is throwing an exception when clicked on the column name to sort.

Sep 8, 2011 at 2:13 PM

I can confirm that this problem is not specific to nhibernate but also exists in LINQ To Entities. I haven't explored it in depth, because I don't need to sort on my calculated fields, so I don't have a clue if the solution is difficult or trivial.

Sep 8, 2011 at 4:05 PM

Okay so I'll start by explaining why this is a problem, and then offer some ways to work around it.

The way MVC.Controls implement jqGrid search, is by translating the jqgrid search criteria (mapped to List<FilterModel> inside SearchModel) string into an Expression object which in turn is passed to the IQueryable to do the actual filtering.

What this means is, that the actual filtering is not done by MVC.Controls, but by the IQueryable QueryProvider (be it EF or NHibernate).
Since MVC.Controls doesn't know (and actually doesnt want to) which properties of the model are mapped to the database\datalayer nor can it know which properties are calculated or simple get\set,
There's no real solution to the problem.

Having said that, what can be done in a pretty neat way, is letting MVC.Controls know that certain filters or properties are to be filtered on the actual data - and not by the Query Provider.

So the question that stands (and this is an open discussion) is where should this information be:

1. Property Level - We'll define a new Attribute [ArtificialProperty] that will be applied on every property that might be filtered, but is not mapped by the data-layer, example:

public class MyModel
   public int Age {get;set;}
   public int Grade{get;set;}

  public int Calc{get{ return this.Age * this.Grade; } }

2. FilterModel Level - We'll add a boolean propery to the FilterModel class that will imply that the specified property should not be executed by the QueryProvider. example:

public ActionResult List(SearchModel searchModel) {
foreach (FilterModel filter in searchModel.Filters.Where(x => x.ColumnName == "CalculatedProperty"))
                filter.IsArtificial = true;

var result = search.ApplyFilters(DAL.GetList());


For  more quick and dirty solution, you could remove the FilterModel that corresponds to the calculated property from the SearchModel before using the ApplyFilter method, and re-apply it later.

Sep 9, 2011 at 8:07 AM

I should say, implement the two possibilities. People using attributes in their model class can use the attribute and people who don't use attributes can interact with the filtermodel.

The given quick and dirty solution will work for real filtering, but not for the sort field being also applied in the method ApplyFilters from the SearchModel class.

Sep 20, 2011 at 8:28 AM

At one of my company project we use the jqgrid and Mvc. So my partner choose the this mvc controls. I have modify some of this controls, add the UserData add the inline edit.

Now go to implement this controls. have some problem. We ues the Nhibernate and Castle proxy. Also have the problem, when the data Json to view. The error message is "A circular reference was detected while serializing an object of type 'System.Reflection.RuntimeModule'. " I have no idea about it. After follow this discussion. I think could renew the result which get from the NHB session. After it an other problem. Just a scrpit error. when us use the treegrid.js, will get the error message "b.jgrid.getAccessor(p, d.cell) is undefined", find it looks like the format data return did not have the cell model. So treegrid.js error. Could you give me some of solution? Thank you very much.

Sep 20, 2011 at 8:41 AM


Basically, if you created a new entity not bound to NHibernate (as @evb offers in the first post in this thread) the ToGridData method should work.
If you get a script error on the client, could you supply a minimal test project so I could try and recreate the error?

Sep 20, 2011 at 8:47 AM

all right. Let me build a sample js error. 

Sep 21, 2011 at 3:09 AM

Hi sternr, 

I have use the jqgrid.src.js package. And debug it. The script error disappeared.