At TrackAbout, where I've been working for a few months now, our database has a lot of user defined functions in it. This past week, one of my tasks involved using one of these functions in conjunction with an NHibernate object.

There's a couple of ways it will work - CreateSqlQuery and CreateQuery come to mind, but I also had another requirement: Eager load a few collections. This was part of an export that includes a lot of related data, so it was important to load it all at once. The best way I could figure out eager loading was with Criteria, so I went with that that. There's probably a better way, but I'm still fumbling my way through and learning NHbernate. In fact, over the weekend, I just got my copy of NHibernate In Action.

Anyway, searching around, I found a few examples of using scalar functions, but not much on functions that return a table, which is what I was dealing with. After seeing a few hints here and there, I finally pieced together what worked for me. I've "Northwind-ized" the classes, but the gist is the same.

   1: return _session.CreateCriteria(typeof(Customer))

   2:     .Add(Expression.Sql("{alias}.Id in (select Item from dbo.ParseCsv(?))", custIdList, NHibernateUtil.String))

   3:     .SetFetchMode("Orders", FetchMode.Eager)

   4:     .SetFetchMode("Products", FetchMode.Eager)

   5:     .List<Customer>();

custIdList is a list of customer Ids coming from customer checkboxes checked on the screen. The function takes the list of customer ids and inserts them into a table, which you can then use in your queries. I tried using Exression.In, but realized that there's a limit in SQL Server of 2,100 input parameters. My particular test case happened to have 9,300 customers, so I stuck with the SQL function.

I should note that this is written against NHibernate 1.2, which is what we use right now. I'm too lazy...err...I'll leave it as an exercise for the reader to figure out if it's the same in 2.x.