NHibernate: Projecting child entities into parent properties throws an exception

Question

I have the following parent entity Department which contains a collection of child entities Sections

public class Department  
{
    private Iesi.Collections.Generic.ISet<Section> _sections;
    public Department()
    {
        _sections = new HashedSet<Section>();
    }
    public virtual Guid Id { get; protected set; }
    public virtual string Name { get; set; }
    public virtual ICollection<Section> Sections
    {
        get { return _sections; }
    }
    public virtual int Version { get; set; }
}

public partial class Section  
{
    public Section()
    {
        _employees = new HashedSet<Employee>();
    }
    public virtual Guid Id { get; protected set; }
    public virtual string Name { get; set; }
    public virtual Department Department { get;  protected set; }
    public virtual int Version { get; set; }
}

I would like to transform (flatten) it to the following DTO

public class SectionViewModel
{
    public string DepartmentName { get; set; }
    public string  SectionName { get; set; }
}

Using the following code.

SectionModel sectionModel = null;
Section sections = null;
var result = _session.QueryOver<Department>().Where(d => d.Company.Id == companyId)
            .Left.JoinQueryOver(x => x.Sections, () => sections)
            .Select(
                    Projections.ProjectionList()
                        .Add(Projections.Property<Department>(d => sections.Department.Name).WithAlias(() => sectionModel.DepartmentName))
                        .Add(Projections.Property<Department>(s => sections.Name).WithAlias(() => sectionModel.SectionName))
                   )
            .TransformUsing(Transformers.AliasToBean<SectionModel>())
            .List<SectionModel>();

I am however getting the following exception: could not resolve property: Department.Name of: Domain.Section

I have even tried the following LINQ expression

        var result = (from d in _session.Query<Department>()
                      join s in _session.Query<Section>()
                          on d.Id equals s.Department.Id into ds
                      from sm in ds.DefaultIfEmpty()
                      select new SectionModel
                          {
                              DepartmentName = d.Name,
                              SectionName = sm.Name ?? null
                          }).ToList();

Mappings

public class DepartmentMap : ClassMapping<Department>
{
    public DepartmentMap()
    {           
        Id(x => x.Id, m => m.Generator(Generators.GuidComb));
        Property(x => x.Name,
            m =>
            {
                m.Length(100);
                m.NotNullable(true);
            });

        Set(x => x.Sections,
                    m =>
                    {
                        m.Access(Accessor.Field);
                        m.Inverse(true);
                        m.BatchSize(20);
                        m.Key(k => { k.Column("DeptId"); k.NotNullable(true); });
                        m.Table("Section");
                        m.Cascade( Cascade.All | Cascade.DeleteOrphans);
                    },
                    ce => ce.OneToMany());
    }
}


public class SectionMap : ClassMapping<Section>
{
    public SectionMap()
    {
        Id(x => x.Id, m => m.Generator(Generators.GuidComb));
        Property(x => x.Name,
            m =>
            {
                m.Length(100);
                m.NotNullable(true);
            });
        ManyToOne(x => x.Department,
                m =>
                {
                    m.Column("DeptId");
                    m.NotNullable(true);
                });
    }
}

But this throws a method or operation is not implemented.

Seeking guidance on what I am doing wrong or missing.

You're going to need to explicitly join from sections to Departments... Or possibly just select `Departments.Name`. Do you know what the SQL would look like? — Andrew Whitaker, Jun 20 '13 at 18:03
Following is the expected SQL `select d.Name as deptName, s.Name as SectionName from Dept d left join section s on d.id = s.deptid order by d.Name asc, s.Name asc` — kagundajm, Jun 21 '13 at 10:28

score 2 · Accepted Answer · answered Jun 21 '13 at 12:12

NHibernate doesn't know how to access a child property's child through the parent entity. A useful thing to remember about QueryOver is that it gets translated directly into SQL. You couldn't write the following SQL:

select [Section].[Department].[Name]

right? Therefore you can't do the same thing in QueryOver. I would create an alias for the Department entity you start on and use that in your projection list:

Department department;
Section sections;    

var result = _session.QueryOver<Department>(() => department)
    .Where(d => d.Company.Id == companyId)
    .Left.JoinQueryOver(x => x.Sections, () => sections)
    .Select(
            Projections.ProjectionList()
                .Add(Projections.Property(() => department.Name).WithAlias(() => sectionModel.DepartmentName))
                .Add(Projections.Property(() => sections.Name).WithAlias(() => sectionModel.SectionName))
           )
    .TransformUsing(Transformers.AliasToBean<SectionModel>())
    .List<SectionModel>();

I noticed in your comment you'd like an order by clause. Let me know if you need help with that and I can probably come up with it.

Hope that helps!

This works. I see where I was going wrong. Yes I would like an `order by` but this I can handle. Thanks for the patience and trouble. — kagundajm, Jun 22 '13 at 05:23
@CallMeKags: No problem! I know QueryOver can be tough to figure out sometimes. — Andrew Whitaker, Jun 22 '13 at 13:17

score 0 · Answer 2 · answered Jun 18 '13 at 12:56

0

This may be now fixed in 3.3.3. Look for

New Feature
[NH-2986] - Add ability to include collections into projections

Not sure but if this is your problem specifically but if you are not using 3.3.3 then upgrade and check it out.

Aslo check out the JIRA

answered Jun 18 '13 at 12:56

Rippo

22,117
14
78
117

Upgraded to 3.3.3 but the problem persists. Maybe the problem is with the structure of the querying. – kagundajm Jun 20 '13 at 04:14

score 0 · Answer 3 · answered Jun 19 '13 at 21:13

0

Have you tried a linq query like

from d in Departments
from s in d.Sections
select new SectionModel
{
DepartmentName = d.Name,
SectionName = s == null ? String.Empty : s.Name
}

answered Jun 19 '13 at 21:13

Fran

6,440
1
23
35

This throws **Specified method is not supported** exception. – kagundajm Jun 20 '13 at 04:59
what is the specific linq query you are running? what do your mappings look like? I've run queries like this using NH 3.0 and above without a problem. – Fran Jun 20 '13 at 15:42
Sorry! My mistake. I was calling wrong method for `from d in Departments`. Works but returns an inner join while I am interested in a left join as there are Departments without sections. – kagundajm Jun 20 '13 at 17:27

NHibernate: Projecting child entities into parent properties throws an exception

3 Answers3