Issues

Select view

Select search mode

 
50 of 68

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

Not an Issue

Description

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?

Environment

None

Details

Assignee

Reporter

Components

Affects versions

Priority

Who's Looking?

Open Who's Looking?
Created June 28, 2017 at 6:09 AM
Updated June 28, 2017 at 2:19 PM
Resolved June 28, 2017 at 10:09 AM

Activity

Show:

Frédéric DelaporteJune 28, 2017 at 2:19 PM

This is not a support forum, closing.

Ramki PannerselvamJune 28, 2017 at 1:40 PM
Edited

Thanks for your reply. Also the same question is posted by me only on yesterday. I thought of asking the same question to you as well. That's why i asked it here.

We already splitting the total number of parameters into list of 2000 parameter and hitting the database to eager fetch other properties in same transaction using future. But here, the same parameter values are applied into different queries with the different parameter name. It's not reusing the same parameter name to other queries. But it's adding the new parameter name (parameter name starts with 0) for the same value in other queries.

Is there a way to reuse the same parameter name for other queries in Nhibernate?
CCing

Frédéric DelaporteJune 28, 2017 at 10:09 AM
Edited

You are hitting a database limitation unrelated to NHibernate. It is your job to split your queries execution in order to avoid hitting your database specific limit about parameters count. See NH-3006, closed for that reason (external issue).

Please by the way do not copy paste a Stack Overflow question here. This place is for reporting NHibernate bugs or asking new features. It is not for support. There is a user mailing list for support.

Who's Looking?
Loading...