NHibernate JoinQueryOver and Lazy Loading
I have a query
var query = _session.QueryOver<TEntity>().JoinQueryOver<PropertyMultTable>(p => p.Properties).Where(propertyPredicate).List();
this query generates the next SQL
select this_.BaseEntity_id as BaseId0_1_ ,
this_1_.Label as Label0_1_ ,
this_1_.Description as Descript3_0_1_ ,
this_1_.CreatedDate as CreatedD4_0_1_ ,
this_.Width as Width2_1_ ,
this_.Height as Height2_1_ ,
this_.Duration as Duration2_1_ ,
propertymu1_.id as id4_0_ ,
propertymu1_.Name as Name4_0_ ,
propertymu1_1_.DateTimeValue as DateTime2_5_0_ ,
propertymu1_2_.IntegerValue as IntegerV2_6_0_ ,
propertymu1_3_.DecimalValue as DecimalV2_7_0_ ,
propertymu1_4_.StringValue as StringVa2_8_0_
from [Video] this_
inner join [BaseEntity] this_1_ on this_.BaseEntity_id = this_1_.BaseId
inner join [PropertyMultTable] propertymu1_ on this_.BaseEntity_id = propertymu1_.BaseEntity_id
left outer join DateTimeValues propertymu1_1_ on propertymu1_.id = propertymu1_1_.PropertyMultTable_id
left outer join IntegerValues propertymu1_2_ on propertymu1_.id = propertymu1_2_.PropertyMultTable_id
left outer join DecimalValues propertymu1_3_ on propertymu1_.id = propertymu1_3_.PropertyMultTable_id
left outer join StringValues propertymu1_4_ on propertymu1_.id = propertymu1_4_.PropertyMultTable_id
where ( propertymu1_2_.IntegerValue >= 459144
and propertymu1_2_.IntegerValue <= 691982
)
but I want to get only the Entity, without the properties. So, I need SQL like this:
select this_.BaseEntity_id as BaseId0_1_ ,
this_1_.Label as Label0_1_ ,
this_1_.Description as Descript开发者_运维技巧3_0_1_ ,
this_1_.CreatedDate as CreatedD4_0_1_ ,
this_.Width as Width2_1_ ,
this_.Height as Height2_1_ ,
this_.Duration as Duration2_1_
from [Video] this_
inner join [BaseEntity] this_1_ on this_.BaseEntity_id = this_1_.BaseId
inner join [PropertyMultTable] propertymu1_ on this_.BaseEntity_id = propertymu1_.BaseEntity_id
left outer join DateTimeValues propertymu1_1_ on propertymu1_.id = propertymu1_1_.PropertyMultTable_id
left outer join IntegerValues propertymu1_2_ on propertymu1_.id = propertymu1_2_.PropertyMultTable_id
left outer join DecimalValues propertymu1_3_ on propertymu1_.id = propertymu1_3_.PropertyMultTable_id
left outer join StringValues propertymu1_4_ on propertymu1_.id = propertymu1_4_.PropertyMultTable_id
where ( propertymu1_2_.IntegerValue >= 459144
and propertymu1_2_.IntegerValue <= 691982
)
or, even much better, like this:
select distinct this_.BaseEntity_id as BaseId0_1_ ,
this_1_.Label as Label0_1_ ,
this_1_.Description as Descript3_0_1_ ,
this_1_.CreatedDate as CreatedD4_0_1_ ,
this_.Width as Width2_1_ ,
this_.Height as Height2_1_ ,
this_.Duration as Duration2_1_
from [Video] this_
inner join [BaseEntity] this_1_ on this_.BaseEntity_id = this_1_.BaseId
inner join [PropertyMultTable] propertymu1_ on this_.BaseEntity_id = propertymu1_.BaseEntity_id
left outer join IntegerValues propertymu1_2_ on propertymu1_.id = propertymu1_2_.PropertyMultTable_id
where ( propertymu1_2_.IntegerValue >= 459144
and propertymu1_2_.IntegerValue <= 691982
)
Can I do this with Fluent NHibernate? Thanks for the responses.
here is how you can do it using HQL.
var hqlQuery=string.Format( "select v from Video as v inner join v.BaseEntity
as be inner join v.PropertyMultTable as pmt left outer join pmt.IntegerValues
as iv where be.BaseEntity_id=v.BaseId and be.BaseEntity_id=pmt.BaseEntity_id and
pmt.Id=iv.PropertyMultTable_id and iv.IntegerValue >={0} and iv.IntegerValue
<={1}", 459144,691982);
note here when inner join is done Im assuming the property names are the same as mentioned in the query above. they should be the exact same thing as mentione din your property or you will get an nhibernate exception.
If it doesnt work post the entire class diagram.
you can run this query as follows:
session.CreateQuery(hqlquery).List<Video>();
Maybe using Future()
:
var query =
_session.QueryOver<TEntity>()
.JoinQueryOver<PropertyMultTable>(p => p.Properties)
.Future()
.Where(propertyPredicate).List();
精彩评论