Fluent NHibernate “Not Supported By” Exception

Summary

So my company has successfully switched to using Fluent NHibernate for our production systems.  So far the results are astounding.  One thing our developers have learned is that there is no “perfect” ORM.  Not yet anyway.  Each seems to have a flaw or two.  NHibernate has a couple of issues that can be worked around.  One issue, I mentioned in an earlier post regarding the 1200 parameter limit.  While unexpected, it was easy to see why it was an issue.  The issue I want to talk about today came up when one of our developers was working with a group by query involving other query operations like order by and take, etc. So I’m going to talk about one issue and show a solution.


The Setup

I’m going to use my standard sampledata database in MS SQL server with 3 tables: Product, ProductType and Store.  Here’s the ERD:



I put some seed data into these tables, it doesn’t matter what you put in the tables.  There is a SQL script in the project (named “CreateTables.txt”), you can copy the text inside this file and execute it inside a query window in MS SQL Server Management Studio.


The Ugly

Here’s the offending LINQ that I setup in my console app:

var query =
    (from p in session.Query<Product>()
     join pt in session.Query<ProductType>() 
       on p.ProductType equals pt.Id
     join s in session.Query<Store>() on p.store equals s.id
     group new { p, pt } 
     by new { pt.Id, pt.Description, p.Name } into temp
     select new
     {
      name = temp.Key.Name,
      id = temp.Key.Id,
      description = temp.Key.Description,
      count = temp.Count()
     }).OrderBy(u => u.name).ToList();

If you attempt to execute this query, you’ll get an error like this:


This error only occurred when I added the “OrderBy” onto the query.  So it appears that a combination of query parameters can cause this error to occur, or possibly there is an issue with the anonymous types generated in the “select new” part of the query.  


The Fix

I dug around for an answer to this problem and found this Stack Overflow issue:

Linq query nhibernate; not supported exception

That’s where I discovered a solution to this query.  I added the “AsEnumerable()” before the “OrderBy” to fix this problem:

var query =
    (from in session.Query<Product>()
     join pt in session.Query<ProductType>() 
       on p.ProductType equals pt.Id
     join s in session.Query<Store>() on p.store equals s.id
     group new { p, pt } 
     by new { pt.Id, pt.Description, p.Name } into temp
     select new
     {
      name = temp.Key.Name,
      id = temp.Key.Id,
      description = temp.Key.Description,
      count = temp.Count()
     }).AsEnumerable().OrderBy(u => u.name).ToList();


Now the query executes and produces the expected results.

You can download the sample project here:
FluentNHibernateGroupBlogPost.zip

 

0 thoughts on “Fluent NHibernate “Not Supported By” Exception

  1. Has ORM been a productivity boom for your development team? I'm curious about this. There has been discussion about this between developers on StackOverFlow. I'm still not sure about the benefits of ORM. Can you comment on this? Thanks.

  2. The advantages I see from an ORM is the fact that many syntax errors can be caught at compile time. I have only recently started using ORMs (since July 2013), so for me, the question still remains. Our production systems contain only a small portion of code in ORM. With that said, the results that are returned by an ORM are much more convenient to work with than a DataTable or DataRow object. Before I discovered how fast NHibernate was, I was very unsatisfied with the performance of ORMs (NHibernate seems to be close to direct query speeds).

Leave a Reply