Selecting an identifier from a database where not in a large collection. Too many parameters

Question

I am working on an application that has two databases.

I have an object, let it permit , which has an id list referencing the id from the table, allows this to be called in tasks in another database.

I am trying to run the following query:

 var listOfUsedIds = select taskid from Permit_Task; Select * from task where id not in (listOfUsedIds) 

When I run this code, I get an error:

The output from the Remote Inbound Table Data Stream (TDS) (RPC) remote procedure protocol flow is incorrect.
Too many parameters were provided in this RPC request. The maximum is 2100.

I cannot run a sub-select or any thing because NHibernate will not allow me to do this on two databases.

Can someone help me in solving this problem?

Answer 1

 using (var tx = session.BeginTransaction()) { session.CreateSQLQuery("CREATE TEMP TABLE usedIds (id INT)").ExecuteUpdate(); for (int index = 0; index < ids.Length; index++) { // TODO: batch this session.CreateSQLQuery("INSERT INTO usedIds VALUES (:p" + index + ")") .SetParameter("p" + index, id) .ExecuteUpdate(); } session.CreateSQLQuery("CREATE INDEX usedIds_idx ON usedIds (id)").ExecuteUpdate(); Batch batch; while((batch.List = session.CreateSQLQuery("SELECT id FROM tasks t WHERE 1 = (SELECT COUNT(*) FROM usedIds u WHERE u.id = t.id) LIMIT 10 OFFSET " + batch.Number).List<int>()).Count > 0) { var tasks = session.QueryOver<Task>() .Where(t => t.Id.IsIn(batch)) .List(); // Do something with the tasks } tx.Commit(); } 

or

 public TaskMap() { Map(x => x.IsUsedCount).Formula("SELECT (SELECT COUNT(*) FROM usedIds u WHERE u.Id = Id)").LazyLoad(); } var tasks = session.QueryOver<Task>() .Where(t => t.IsUsedCount == 0) .List(); 

Answer 2

Just fold listOfUsedIds into smaller blocks (say 200 identifiers each), run a query for each block and .Concat() results.