nHibernate and Table Value Parameters

nHibernate is a great tool for interacting with data. However, much of our data involves pretty huge parameters. Some of our queries can have ID counts in the thousands, and sometimes they select all of them at once and want us to do stuff with them. I was working on just this issue with a section of an app, and found a neat solution. Typically we pass large ID lists using a table valued parameter. Unfortunately nHibernate doesn't know about those. My solution was to create an XML element with all of the id's as sub elements. Then, in the named SQL I query them out of the XML into a table. 

In your method, you want to build your XML string. I didn't create an XML doc and add it that way. This is simple, so I used a string builder to create the xml by hand.

            StringBuilder sb = new StringBuilder();
            sb.Append("<StoreIds>");
            foreach (int storeId in storeIds)
            {
                sb.AppendFormat("<Id>{0}</Id>", storeId);
            }
            sb.Append("</StoreIds>");

Then I passed the xml string into my GetNamedQuery call.

                var query = Session.GetNamedQuery("MyAwesomeNamedQuery")
                    .SetParameter("userId", userId)
                    .SetParameter("condition", whereClause)
                    .SetParameter("storeIds", sb.ToString(), NHibernateUtil.StringClob);
                result = query.List<MyObject>().AsQueryable();

NOTE: The key to this is defining the third parameter of the SetParameter method as NHibernateUtil.StringClob. If you don't, nHibernate automatically truncates your string to 4,000 characters.

​The SQL statement was changed from using 

​       INSERT INTO @storeIds2

​       SELECT StoreId FROM Client.Store

​       WHERE StoreId IN (:storeIds)

To

 INSERT INTO @storeIds2
 SELECT T.x.value('.', 'int') ID
 FROM @storeIds.nodes('/StoreIds/Id') T(x)​

And that's all the SQL it takes to extract the ID's from the xml string we passed into a sql @table variable!

When I tested this code, I tested it with a db that held 11,500+ stores. All stores were passed perfectly.