Fluent NHibernate Subquery Stuff

Summary

I was converting some Entity Framework Linq to use NHibernate when I came across a strange error. At first I was baffled by the error message because it was telling me that there were too many input parameters. Doing an internet search led me to articles about how to limit the number of parameters I was passing to a stored procedure, etc. etc. We’ve all run into these types of issues, the error message was not quite right. Or at least until I figured out what was really going on.

First, the Error Message

The error message I was getting was this:

“The incoming tabular data stream (TDS) remote procedure call (RPC) protocol stream is incorrect. Too many parameters were provided in this RPC request. The maximum is 2100.”

The query that produced this message had only one parameter, but I can recreate the error message (now that I know what causes it), and here’s an example of some code that can cause the error:

List<int> personList = 
    (from p in db.Query<Person>() 
     select p.id).ToList();

var departmentList =
    (from d in db.Query<Department>()
     where !personList.Contains(d.id)

     select d.name).ToList();

First, I had to create a bunch of person records, in fact I inserted over 3000 records, just to test this. I also created 4 department records, but they don’t really matter. When I checked what the second query was generating I realized that the first query was generating a list of id’s (duh) that were put into a “NOT IN” statement in the second query, which looks something like this:

select department0_.name as col_0_0_ from sampledata..department department0_ where not (department0_.id in (@p0 , @p1 , @p2 , @p3 , @p4 , @p5 …

So I did some more searching on the internet and there were some insane ideas, like looping the second query and pumping 1000 variables in at one time… Here’s an example: how-can-i-use-nhibernate-to-retrieve-data-when-the-where-in-have-thousands-o. Yikes.

Let’s face it, MS SQL server can handle a subquery with ease, so I decided to short-cut this special case (and let’s face it, subqueries are special cases only) by calling the query directly. Here’s the code I used:

var departmentList =
  db.CreateSQLQuery(@"SELECT name 
           FROM department 
           WHERE id NOT in 
           (SELECT department FROM person)")
    .SetResultTransformer(
        Transformers.AliasToBean<DepartmentNameRecord>())
    .List<DepartmentNameRecord>()
    .ToList();

The result will be the list of departments not containing any person records. If you download the code and experiment with it, you can use these query commands in MS SQL to clear your tables:

delete from department
DBCC CHECKIDENT (department, RESEED, 0)
delete from person
DBCC CHECKIDENT (person, RESEED, 0)

If you don’t have the tables setup in your sampledata database, you can use these queries to create them before running your code:

USE [sampledata]
GO
/****** Object:  Table [dbo].[person]    Script Date: 05/18/2014 11:44:14 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[person](
[id] [int] IDENTITY(1,1) NOT NULL,
[first] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[last] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[department] [int] NOT NULL,
 CONSTRAINT [PK_person] PRIMARY KEY CLUSTERED 
(
[id] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

CREATE TABLE [dbo].[department](
[id] [int] IDENTITY(1,1) NOT NULL,
[name] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
 CONSTRAINT [PK_department] PRIMARY KEY CLUSTERED 
(
[id] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO
SET ANSI_PADDING OFF

Did I also mention that the above code is blazing fast? Oh yeah. It’s always best to profile your code after you get it working. Then dump the raw SQL that your ORM is producing so you can analyze it in MS SQL management studio. If it’s running fast in the ORM, then you’re good. If there are performance problems, then maybe you need a workaround.

Download the code here: FluentNhibernate2100ErrorTest.zip

Leave a Reply