Nhibernate - The same in parameter value will be applied with different name to different future queries

Question

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?

score 0 · Answer 1 · answered Jul 10 '17 at 17:02

No, and I believed I have already told you in a comment. Have you deleted your question and re-posted it? This would be quite bad behavior.

Each future query is treated as a different query with its own set of parameters. NHibernate will not try to "mutualise" them, that would be overly complicated.

This is then up to you to split up your queries in order to avoid reaching the parameter count limit. Easiest way: split your id list and perform many sets of future calls, resolving the previous set before building the next set.

Otherwise forget about eager fetching and stick to lazy loading. Avoid N+1 select issues by enabling batch loading of lazy loads. This is much easier. If you need to close the session then use your entities, you will have to trigger lazy loading by calling NHibernateUtil.Initialize() on their lazily loaded properties, looping on your entities. It will not do anything on those which are already loaded thanks to lazy loading batching. An other option is to convert your entities into something like a view model before closing the session.

Nhibernate - The same in parameter value will be applied with different name to different future queries

1 Answers1