CODEWITHSUNDEEP
.netsqlnhibernatecastle-activerecord
Cosmo
Cosmo

Reputation: 369

ActiveRecord (NHibernate) Eager Loading Bug?

I have two classes: File, Applicant and I'm using ActiveRecord 3.0 RC (NHibernate 3.1.0.4000).

File

[ActiveRecord("`File`", Lazy = true)]
public class File : TestProDb<File> {

    [PrimaryKey("`Id`")]
    public virtual long Id { get; private set; }

    [Property("`Name`")]
    public virtual string Name { get; set; }

    [HasMany(Cascade = ManyRelationCascadeEnum.AllDeleteOrphan, Inverse = true, Lazy = true)]
    public virtual IList<Applicant> Applicants { get; set; }

    public File() {
        this.Applicants = new List<Applicant>();
    }
}

Applicant

[ActiveRecord("`Applicant`", Lazy = true)]
public class Applicant : TestProDb<Applicant> {

    [PrimaryKey("`Id`")]
    public virtual long Id { get; private set; }

    [Property("`Surname`")]
    public virtual string Surname { get; set; }

    [BelongsTo(Column = "IdFile", Lazy = FetchWhen.OnInvoke)]
    public virtual File File { get; set; }
}

Now I want to select Files based on some Applicant criterias. The result Files should contain eagerly loaded Applicants:

using (new SessionScope()) {
    DetachedCriteria fileQuery = DetachedCriteria.For<File>();
    fileQuery.SetResultTransformer(new DistinctRootEntityResultTransformer());
    fileQuery.SetFetchMode("Applicants", NHibernate.FetchMode.Eager);
    fileQuery.CreateCriteria("Applicants").Add(Expression.Like("Surname", "a", MatchMode.Anywhere));

    IList<File> files = File.FindAll(fileQuery);
    foreach (File file in files) {
        foreach (Applicant applicant in file.Applicants) {
            Console.WriteLine(applicant.Surname);
        }
    }
}

From NHProof - first query when I perform FindAll:

SELECT this_.[Id]            as Id1_0_1_,
   this_.[Name]          as Name2_0_1_,
   applicant1_.[Id]      as Id1_1_0_,
   applicant1_.[Surname] as Surname2_1_0_,
   applicant1_.IdFile    as IdFile1_0_
FROM   [File] this_
   inner join [Applicant] applicant1_
     on this_.[Id] = applicant1_.IdFile
WHERE  applicant1_.[Surname] like '%a%' /* @p0 */

From NHProof - second query in loop Console.WriteLine(applicant.Surname):

SELECT applicants0_.IdFile    as IdFile1_,
   applicants0_.[Id]      as Id1_1_,
   applicants0_.[Id]      as Id1_1_0_,
   applicants0_.[Surname] as Surname2_1_0_,
   applicants0_.IdFile    as IdFile1_0_
FROM   [Applicant] applicants0_
WHERE  applicants0_.IdFile = 1 /* @p0 */

Why do I get an additional roundtrip to the database for each Applicant loop (second query example)? There should only be one DB query in total because of FetchMode.Eager. I'm totally confused about this. I even tried to remove the virtual keyword and set all lazy values to false. Still the same. Is this a bug?

Upvotes: 2

Views: 461

Answers (1)

Sly
Sly

Reputation: 15217

You are getting additional request because for NHibernate to eager load associations requires outer join, but you have inner join in query. To fix this you need to specify:

JoinType.LeftOuterJoin

I've had similar issue.

EDIT

The reason why NHibernate requires left join is the following, consider the following code:

DetachedCriteria fileQuery = DetachedCriteria.For<File>();
fileQuery.SetFetchMode("Applicants", NHibernate.FetchMode.Eager);

If NHibernate would made inner join here then Files that doesn't have Applicants won't be present in query result. Thats probably not what you would expect.

But when you create a restriction on Applicants name then you definitely don't want files that have applicants empty. That allows NHibernate to optimize query a bit, but disables lazy loading.

Upvotes: 1

Related Questions