在这里,我获得了代理商可以访问的代理机构列表。
var agencies = _session.QueryOver<AgencyRep>()
.JoinQueryOver<AgencyRepsSecondaryAgency>(r => r.AgencyRepsSecondaryAgencies)
.Where(a => a.AgencyRepId == user.Id)
.List();
获得了我想进行SQL搜索的代理商列表后,该值在代理商中。ID列。
var data = _session.QueryOver<Staging.Case>()
.Where(x => x.AgencyId ???? agencies)
.List();
不确定如何使用流利的NHIBERNATE实现这样的事情。任何帮助将不胜感激。
尝试以下:
var data = _session.QueryOver<Staging.Case>()
.WhereRestrictionOn(x => x.AgencyId).IsIn(agencies.Select(a => a.Id).ToArray())
.List();
编辑:如果您之后不需要代理商 - 您可以通过进行一些优化:
var agencyIds = _session.QueryOver<AgencyRep>()
.JoinQueryOver<AgencyRepsSecondaryAgency>(r => r.AgencyRepsSecondaryAgencies)
.Where(a => a.AgencyRepId == user.Id)
.Select(a => a.Id)
.List<long>(); //if your .Id is of type long - replace with the correct type
var data = _session.QueryOver<Staging.Case>()
.WhereRestrictionOn(x => x.AgencyId).IsIn(agencyIds.ToArray())
.List();
节省了通过连接发送的一些数据
这应该有效,未经测试,但您可以尝试:
var subquery = QueryOver.Of<AgencyRep>()
.JoinQueryOver<AgencyRepsSecondaryAgency>(r => r.AgencyRepsSecondaryAgencies)
.Where(a => a.AgencyRepId == user.Id)
var data = _session.QueryOver<Staging.Case>()
.WithSubquery.WhereProperty(x => x.AgencyId).In(subquery)
.List();