http://ayende.comAyende @ RahienCopyright (C) Ayende Rahien 2004 - 2021 (c) 202660Initially I called in BigIn, but that sounds... not nice. http://ayende.com/2583/nhibernates-xml-in#comment7http://ayende.com/2583/nhibernates-xml-in#comment7Tue, 26 Jun 2007 04:27:27 GMTInteresting technique and nice that you use the simpler method if less than 2100 parameters or a non-SQL2K5 database. My only complaint is the XmlIn naming. You are letting an implementation detail leak into your naming. Shouldn't this be called something like "In" so that similar hacks, though not necessarily XML-based, could be implemented for other databases? http://ayende.com/2583/nhibernates-xml-in#comment6http://ayende.com/2583/nhibernates-xml-in#comment6Tue, 26 Jun 2007 04:22:45 GMTYves, There are several reasons, the first is that it can blow up very fast, because NH is keeping cached plans. The second is the DB compiled plans cache. The third and most important is that you now opens yourself to SQL vulnerability. http://ayende.com/2583/nhibernates-xml-in#comment5http://ayende.com/2583/nhibernates-xml-in#comment5Mon, 25 Jun 2007 17:39:15 GMTWhy not use literals instead of parameters? So instead of '... IN (@P1, @P2, @P3, ....)' have it emit '... IN (213, 267, 3080, ...)'. I don't know much about NHibernate, but I would be surprised if one could not hook into its sql generation infrastructure. http://ayende.com/2583/nhibernates-xml-in#comment4http://ayende.com/2583/nhibernates-xml-in#comment4Mon, 25 Jun 2007 15:52:47 GMTI'm going to hit a similar situation this iteration.. a treeview where the user can select multiple leaves (of users), then create an aggregate report off the selected users... This is way cool! http://ayende.com/2583/nhibernates-xml-in#comment3http://ayende.com/2583/nhibernates-xml-in#comment3Fri, 22 Jun 2007 01:11:54 GMTBen, how are you going to get the data for the subquery in the first place? That is really a monster one, and I would rather not run it overmuch. I thought about using string splitting, but that seems like a hack .The XML is also a hack, but less so. http://ayende.com/2583/nhibernates-xml-in#comment2http://ayende.com/2583/nhibernates-xml-in#comment2Thu, 21 Jun 2007 14:25:01 GMTI don't see why a subquery wouldn't work there, but maybe I don't understand the problem... SELECT * FROM CustomerOrders WHERE CustomerID IN ( SELECT FriendD FROM Associations WHERE AssociationID = @x UNION SELECT ColleagueID FROM Associations WHERE AssociationID= @x UNION ..... more queries ) That would work with 2100 results from the inner query, wouldn't it? Another way would be to coalesce the different columns into a single comma separated string and perform IN on that... can't think of the query off the top of my head, but that's possible as well. http://ayende.com/2583/nhibernates-xml-in#comment1http://ayende.com/2583/nhibernates-xml-in#comment1Thu, 21 Jun 2007 13:38:47 GMT