Using results from sproc in Criteria Restrictions.In

Question

I have this detached criteria:

DetachedCriteria students = DetachedCriteria.For(typeof(Submission)) 
.SetProjection(Projections.Property("ID"))
.Add(Restrictions.In("JicsStudent.HostID", visiblestudents));

visiblestudents is a List<string>. It works well enough until I run into the 2100 parameter restriction. visiblestudents is populated by another query that has some crazy business logic in a sproc. The return from the sproc is then linq filtered down to a list<string>

So my question is is there some way to replace the list<string> with the sproc to prevent the SQL error? Everything I have found so far on stackoverflow suggests that I should create a temp table with the list and then use a join, but my lead would prefer that I didnt use a temp table.

There is some good information and discussion on using sproc's with NHibernate [here.](http://stackoverflow.com/questions/1091521/how-do-i-call-a-stored-procedure-from-nhibernate-that-has-no-result) — Handprint, Aug 14 '12 at 13:08

score 0 · Answer 1 · answered Aug 21 '12 at 14:39

We found a solution that seems to work as we wanted;

var n = new object[2];
var o = new IType[2];

DetachedCriteria students = DetachedCriteria.For(typeof (Submission))
.SetProjection(Projections.Property("ID"))
.Add(Expression.Sql("{alias}.StudentUserID in (select UserId from RMS_udfVisibleStudents(?, ?))", n, o));

Using results from sproc in Criteria Restrictions.In

1 Answers1