Nhibernate Self-reference Mapping

Question

Hello, I need to return a list of child objects of the same kind as the father. however, using nhibernate, it only returns one child object, while in the bank I have 3 children. Below are excerpts of the code:

Data Stage

    public Etapa ObterPorId(int id, bool completo = false)
    {
        if (!completo) return base.ObterPorId(id);
        using (var session = HibernateUtil.GetSessionFactory().OpenSession())
        {
            return session.Query<Etapa>()
                //.Fetch(e => e.EtapaFilhas) // já tentei com este tb..
                .FetchMany(e => e.EtapaFilhas).FirstOrDefault(e => e.Id == id);
        }
    }

Etapamap:

public class EtapaMap:ClassMap<Etapa>
{
    public EtapaMap()
    {   
        Table("TEMA_ETAPA");
        Not.LazyLoad();

        Id(i => i.Id, "id").GeneratedBy.Identity();

        HasMany(e => e.EtapaFilhas)
        .Cascade.DeleteOrphan().KeyColumn("id_TEMA_ETAPA_pai");

        References(e => e.EtapaPai).Column("id_TEMA_ETAPA_pai");
        References(e => e.Tema).Column("id_TEMA_FORMULARIO");
    }
}

Step (entity)

public class Etapa
{
    public virtual int Id { get; set; }
    public virtual Etapa EtapaPai { get; set; }
    public virtual Tema Tema { get; set; }
    public virtual ISet<Etapa> EtapaFilhas { get; set; }

}

Test method:

        [Test]
        public void DeveRetornarQuantidadeDeFilhas()
        {
            var etapaPaiId = 13;

            var sut = new EtapaDados();

            var result = sut.ObterPorId(etapaPaiId, true).EtapaFilhas;

            Assert.That(result.Count, Is.EqualTo(3));
        }

Using Linqpad or EF, it returns 3 children. I think I may be missing in the mapping or data call method, but I don’t know how to fix.

Alternatively, I have created a method that returns only children. However I wanted to avoid this approach, since I should access your children through the property ISet<Etapa> EtapaFilhas:

    public IEnumerable<Etapa> ObterFilhas(int idEtapaPai)
    {
        using (var session = 
        HibernateUtil.GetSessionFactory().OpenSession())
            return session.Query<Etapa>()
                .Where(e => e.EtapaPai.Id == idEtapaPai)
                .ToList();
    }

Answer 1

To solve this problem, I found two valid approaches:

Where().FetchMany().Single()

or

Where().FetchMany().AsEnumerable().FirstOrDefault()

The problem is that the First() and its variations propagates a select top 1 limiting the query output.

Solution in practice:

    public Etapa ObterPorId(int id, bool completo = false)
    {
        if (!completo) return base.ObterPorId(id);
        using (var session = HibernateUtil.GetSessionFactory().OpenSession())
        {
            var approachOne = session.Query<Etapa>()
                .Where(e => e.Id == id)
                .Fetch(e => e.EtapaFilhas)
                .Single();

            var approachTwo = session.Query<Etapa>()
                .Fetch(e => e.EtapaFilhas).AsEnumerable()
                .FirstOrDefault(e => e.Id == id);

            return approachOne;
        }
    }

Asenumerable()

As Dr Gabriel . Schenker and Aaron Cure wrote, in their book Nhibernate 3 Beginner’s Guide, the instruction "AsEnumerable" is used to toggle the context from [LINQ to "Nhibernate" to LINQ to objects] without firing the query execution.

Now, whenever you iterate on the result of the query, the linq provider knows which part of the expression tree to take and convert into an SQL statement, which can be executed from the database.

I also suggest as a reference stack Nhibernate: Why does Linq First() force only one item in all Child and grandchild Collections with Fetchmany().