c# - Fluent NHibernate - ProjectionList - ICriteria is returning null values

Question

I'm quite new in NHibernate, but I have googled around and didn't found anything to help with this issue. I hope you guys can ! ;) I'm changing names of properties, and methods, because this code is company's property but basically this is what I need some help.

I have the following scenario:

My Domain Entity:

public class Structure
{
    public virtual int Id { get; set; }
    public virtual string Name { get; set; }
    public virtual Person Manager { get; set; } //I need to fill here.
    //and others
}

My map class:

public class MapStructure : ClassMap<Structure>
{
    public MapStructure()
    {
        Table("TB_Structure");
        Id(x => x.Id).Column("Id").GeneratedBy.Identity();
        Map(x => x.Name).Column("Name");
        References<Person>(x => x.Manager).Column("PersonId").Fetch.Join().NotFound.Ignore();
        //...
    }
}

Repository:

    public IEnumerable<T> SelectByColumns()
    {
        ICriteria searchCriteria = _sessao.CreateCriteria<T>("this");

        searchCriteria.CreateAlias("this.Manager", "Manager");

        //Only for example purpose. Those columns come as an array string parameter, but the treatment is the same one.
        var columns = Projections.ProjectionList();
        columns.Add(Projections.Property("Manager.Id"));
        columns.Add(Projections.Property("Manager.Name"));
        columns.Add(Projections.Property("Manager.Document"));

        searchCriteria.SetProjection(columns);
        searchCriteria.SetResultTransformer(Transformers.AliasToBean<T>());

        return searchCriteria.List<T>();
    }

And finally the call:

public IEnumerable<Person> GetManager()
{
    using (IDbSession dbSession = _sessionFactory.Create())
    {
        try
        {
            IRepository<Structure> _repository = dbSession.CreateRepository<Structure>();
            IEnumerable<Structure> structureList = _repository.SelectByColumns();

            var managerList = (from structure in structureList
                                where structure.Manager != null
                                select new Person()
                                {
                                    Id = structure.Manager.Id,
                                    Name = structure.Manager.Name,
                                    Document = structure.Manager.Document
                                });

            return managerList.OrderBy(x => x.Name);
        }
        catch (Exception)
        {
            throw;
        }
    }
}

This generates me a sql query like below:

SELECT manager1_.PersonId as y0_, manager1_.Name as y1_, manager1_.Document as y2_
FROM TB_Structure this_
inner join TB_Person manager1_ on this_.ManagerId=manager1_.PersonId

And this is exactly what I need. If I run this query in management studio, I got all the results I was expecting.

But when I reach the var managerList, the structureList have all records returned from sql, but all with null values as shown:

I have already tryed with CreateAlias, CreateCriteria, return IList<>, return IEnumerable. I've already changed Transformers.AliasToBean() to Transformers.AliasToEntityMap. A lot of different things I found googling, but I always got the same result.

I appreciate any help, and thank you for your time!

Answer 1

You are almost there. What we need, is to properly convert Projections into entity/object tree. That would require two steps:

I. use the alias for each column

Column Alias, is useful more for ex post processing, than for SQL statement generation. But it is a must for next step. So instead of this:

columns.Add(Projections.Property("Manager.Id"));
columns.Add(Projections.Property("Manager.Name"));
columns.Add(Projections.Property("Manager.Document"));

we need this:

columns.Add(Projections.Property("Manager.Id").As("Manager.Id");
columns.Add(Projections.Property("Manager.Name").As("Manager.Name"));
columns.Add(Projections.Property("Manager.Document").As("Manager.Document"));

In fact, this would be enough, if we are using the first level (no JOIN) entity. For JOINed reference tree (many-to-one) it won't work. but

II. use custom result transformer

As always, NHibernate provides many open points for custom extensions. One of them would be the Custom IResultTransformer. The one, ready to handle reference tree we need is here:

• DeepTransformer

Having that in our solution we should instead of this:

searchCriteria.SetResultTransformer(Transformers.AliasToBean<T>());

use this:

searchCriteria.SetResultTransformer(new DeepTransformer<T>());

This implemenation is strongly dependent on proper alias setting, describing the real entity properties (to use reflection to find what to set). So the first point - column/property alias is really essential