LeftOuterJoin生成重复的行
本文关键字:LeftOuterJoin | 更新日期: 2023-09-27 17:49:40
假设我有以下类:
public class Fund : EntityBase
{
public virtual string Name { get; set; }
public virtual IList<FundDetail> FundDetails { get; set; }
public virtual IList<FundAlias> FundAliases { get; set; }
public Fund()
{
FundDetails=new List<FundDetail>();
FundAliases=new List<FundAlias>();
}
}
public class FundDetail : EntityBase
{
public virtual string Symbol { get; set; }
public virtual Fund Fund { get; set; }
}
public class FundAlias : EntityBase
{
public virtual string Symbol { get; set; }
public virtual string Name { get; set; }
public virtual Fund Fund { get; set; }
}
我查询:filterValue = "sometext"
var criteria = session.CreateCriteria<Fund>();
criteria.CreateAlias("FundDetails", "fd", JoinType.LeftOuterJoin);
criteria.CreateAlias("FundAliases", "fa", JoinType.LeftOuterJoin);
criteria.Add(
Restrictions.InsensitiveLike("fd.Symbol", filterValue, MatchMode.Anywhere) ||
Restrictions.InsensitiveLike("Name", filterValue, MatchMode.Anywhere) ||
Restrictions.InsensitiveLike("fa.Symbol", filterValue, MatchMode.Anywhere));
criteria.SetFirstResult(0).SetMaxResults(100);
criteria.SetResultTransformer(new DistinctRootEntityResultTransformer());
var list = criteria.List<Fund>();
我试图获得所有的基金,其中基金或符号的名称从fundDetail或符号从FundAlias包含filterValue是简单的字符串,并获得100个结果。
因为LeftOuterJoin生成了重复的基金行,并且因为" criteria.SetResultTransformer(new DistinctRootEntityResultTransformer());"
不存在于生成的查询(SQL语法)中,尽管有超过100个结果,我没有得到100行,我根据有多少行被复制得到不同的数字。我打算用"投影":
criteria.SetProjection(
Projections.Distinct(Projections.ProjectionList()
.Add(Projections.Alias(Projections.Property("Name"), "Name"))
.Add(Projections.Alias(Projections.Property("Id"), "Id"))
)
);
但是我没有找到如何使用CriteriaNote:
为集合设置投影我做错了什么?这是为什么使用标准来做这个?
注意:请不要说我不想这样做使用QueryOver,我需要这个与标准。
var subquery = DetachedCriteria.For<Fund>()
.CreateAlias("FundDetails", "fd", JoinType.LeftOuterJoin)
.CreateAlias("FundAliases", "fa", JoinType.LeftOuterJoin)
.Add(
Restrictions.InsensitiveLike("fd.Symbol", filterValue, MatchMode.Anywhere) ||
Restrictions.InsensitiveLike("Name", filterValue, MatchMode.Anywhere) ||
Restrictions.InsensitiveLike("fa.Symbol", filterValue, MatchMode.Anywhere))
.SetProjection(Projections.Distinct(Projections.Id()));
var funds = session.CreateCriteria<Fund>()
.Add(Subqueries.PropertyIn(Projections.Id()).In(subquery))
.SetFetchMode("FundDetails", FetchMode.Eager) // for example
.OrderBy(Projections.Id())
.SetFirstResult(0).SetMaxResults(100)
.SetResultTransformer(Transformers.DistinctRootEntity())
.List<Fund>();