The problem: Nhibernate parses each value in the "WHERE IN()" sql as parameters and MS SQL server doesn't support enough parameters (over 2000).
I am using Nhibernate with Linq to retrive my data from the SQL server and I need to load alot of entities based on already known ID's.
My code looks something like this:
int[] knownIds = GetIDsFromFile();
var loadedEntities = _Repository.GetAll()
.Where(x => knownIds.Contains(x.ID))
.ToList();
Which give an sql like this:
SELECT id, name FROM MyTable
WHERE id IN (1 /* @p0 */,2 /* @p1 */,3 /* @p2 */,4 /* @p3 */, 5 /* @p4 */)
If there is too many values in knownIds, then this code will throw an Exception because of the many parameters that NHibernate uses.
I think the best solution would be if I could make NHibernate use only 1 parameter for the whole "WHERE IN()", but I don't know how to do this:
SELECT id, name FROM MyTable WHERE id IN (1, 2, 3, 4, 5 /* @p0 */)
I'll be glad to hear any ideas of how to solve this - either by extending the LINQ provider or by other means. One solution is to simple do the query x times (knownIds.Count / 1000), but I rather want a generic solution which would work for all my entities.
I have tried looking at extending the LINQ provider by searching google and Stackoverflow, however I can't find a solution and I dont have any experience with either HQL or the treebuilder. Here are a few of the sites I have been at: