FluentNhibernate Criteria LeftOuterJoin Generates duplicate rows

Question

Suppose I have following classes :

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; }
}

My query:

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>();

I try to get all fund where name of fund or symbol from fundDetail or symbol from FundAlias contains filterValue that is simple string , and get 100 results.

Because LeftOuterJoin generated duplicate rows of fund , and because " criteria.SetResultTransformer(new DistinctRootEntityResultTransformer());" is not present in generated query (SQL syntax) despite the fact that there are more than 100 results I am not getting 100 rows, I am getting different number according to how many rows was duplicated. I was taring to do it with "Projections":

criteria.SetProjection(
    Projections.Distinct(Projections.ProjectionList()
        .Add(Projections.Alias(Projections.Property("Name"), "Name"))
        .Add(Projections.Alias(Projections.Property("Id"), "Id"))
    )
);

but I didn't find how to SetProjections For a collection using CriteriaNote:

What I am doing wrong ?Is it a why to do this using Criteria?

Note:Please not that I do not want to do this using QueryOver ,I need this with Criteria.

Firo · Answer 1 · 2015-05-22T12:55:15.380

1

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>();

edited May 22 '15 at 12:55

answered May 22 '15 at 10:24

Firo

30,626
4
55
94

Thanks a lot, this method was in my mind, what about performance of this subquery , is it ok? also can you please explain why I am getting duplicate results? – Nic May 22 '15 at 10:30
the databse is responsible to make it performant. There are no intermediate results transmitted. Look at the query plan of your database to confirm. Duplicate will occure when you eager load dependent collections which result in a cartesian product. I will edit the answer to accomondate that – Firo May 22 '15 at 12:54

FluentNhibernate Criteria LeftOuterJoin Generates duplicate rows

1 Answers1