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 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()
     }).AsEnumerable().OrderBy(u => u.name).ToList();

Now the query executes and produces the expected results.

You can download the sample project here:

FluentNHibernateGroupBlogPost.zip