0
down vote
favorite
1
We are trying to eager load some data using Nhibernate future queries. As you already know while calling ToList(), all the future query that we have before will get executed in one transaction.

var selectedPhysicianIds = new List<int> {1, 2};

var query = this.physicianReviewQuery.Clone()
.TransformUsing(new DistinctRootEntityResultTransformer())
.AndRestrictionOn(p => p.Id)
.IsIn(selectedPhysicianIds)
.Future();

var collectionsQuery = this.session.QueryOver<Physician>()
.AndRestrictionOn(p => p.Id)
.IsIn(selectedPhysicianIds);
collectionsQuery.Clone().Fetch(p => p.Specialties).Eager.Future();
collectionsQuery.Clone().Fetch(p => p.HospitalPhysicianBridges).Eager.Future();
collectionsQuery.Clone().Fetch(p => p.SpecialtyPhysicianBridges).Eager.Future();

selectedPhysicians.AddRange(query.ToList());
This code will generate below set of queries,

Select * from PhysicianReview where PhysicianKey in (@p0, @p1)

Select * from Physician P left outer join Specialties S on P.Specialty = S.Specialty
Where P.PhysicianKey in (@p2, @p3)

Select * from Physician P left outer join HospitalBridge HB on P.Physician = HB.Physician
Where PhysicianKey in (@p4, @p5)

Select * from Physician P left outer join SpecialtyBridge SB on P.Physican = SB.Physician
Where PhysicianKey in (@p6, @p7)
',N'@p0 int,@p1 int,@p2 int,@p3 int,@p4 int,@p5 int,@p6 int,@p7 int,
@p0=1,@p1=2,@p2=1,@p3=2,@p4=1,@p5=2,@p6=1,@p7=2
While looking into the sql queries, the same number of parameters value will be applied for all the queries with different parameter name.

Let say the selectedPhysicianIds contains array of 1000 integer(user can choose whatever they want).

var selectedPhysicianIds = new List<int> {1, 2,....998, 999, 1000};
In this case, the same number of parameter value will be applied for all the four future queries. So total number of parameter count will be (4 * 1000) 4000. But we have the below limitation in the sql server. "The incoming request has too many parameters. The server supports a maximum of 2100 parameters"

Is there any option to solve this issue with the help of Nhibernate?