NHibernate parent list with child count

Question

i am using NHibernate 4 with mysql. i have got 2 tables. My tables are cat and answer.

public class cat
{
    [Key]
    public virtual int id { get; set; }
    public virtual string catName { get; set; }

    public virtual IList<answer> answers { get; set; }
}

public class answer
{
    [Key]
    public virtual int id { get; set; }
    public virtual int catId { get; set; }
    public virtual string detail { get; set; }
    public virtual bool stat { get; set; }

    [ForeignKey("catId")]
    public virtual cat cats { get; set; }
}

i want to select all cat record(with answer list) and with their cild answers count.

my sql query like that;

select count(t2.id) as count, cats.*from cat cats left join answer t2 ON(cats.id=t2.catId and t2.stat=0) GROUP BY(cats.id);

Result like this;

id - catName - count

1 - Book - 5

2 - Pc - 0

3 - English - 22

4 - Arts - 56

i have try also this NH query;

public class myClass {
  public virtual int count { get; set; }
  public virtual cat cats { get; set; }
}

var u = db.CreateCriteria(typeof(cat), "cats")
       .CreateAlias("answer", "t2", NHibernate.SqlCommand.JoinType.LeftOuterJoin, Restrictions.Eq("t2.stat", false))
      .SetProjection(Projections.ProjectionList()
      .Add(Projections.Count("t2.id"), "count")
      .Add(Projections.Group<cat>(g => g.id)));


var list = u.SetFetchMode("answer", FetchMode.Eager)
        .SetResultTransformer(Transformers.AliasToBean<myClass>())
        .List<myClass>();

This NHibernate query return also answers count. but cats always return null. How can i do my query for this result ?

Edit 1 i can do it like that

public class myClass {
  public virtual int count { get; set; }
  public virtual catId count { get; set; }
  public virtual cat cats { get; set; }
}


cat cats = null;
answer answers = null;

var u = db.QueryOver<cat>(() => cats)
    .JoinQueryOver(x => x.answers, () => answers, NHibernate.SqlCommand.JoinType.LeftOuterJoin, Restrictions.Eq("answers.stat", false))
    .SelectList(cv => cv
        .SelectCount(() => answers.id)
        .SelectGroup(c => c.id))
    .List<object[]>()
    .Select(ax => new myClass
    {
      count = (int)ax[0],
      catId = (int)ax[1],
      cats = (cat)db.QueryOver<cat>().Where(w=>w.id==(int)ax[1]).Fetch(fe => fe.answers).Eager.SingleOrDefault()
    })
    .ToList();

Small Offtopic hint: if you have the choice, get rid of the criteria api and use the [QueryOver or Query Api](http://stackoverflow.com/q/15020817/6666799) from NHibernate — Rabban, Jan 13 '17 at 13:14

Florian Lim · Answer 1 · 2017-01-16T15:54:30.630

In your result cats is always null, because the ResultTransformer tries to map the properties by their names.

Please check your NHibernate logfile. You will probably see that your query returns the columns count and id, but myClass has the properties count and cats.

Edit:

New suggestion:

The previous suggestion did not work, because the property id is of type Int32 and cannot be assigned to myClass.cat (which is of type cat).

If you don't need a reference to a cat-object in myClass then you can change it to this:

public class myClass {
  public virtual int count { get; set; }
  public virtual int catId { get; set; }
}

and have the projection like this:

.Add(Projections.Group<cat>(g => g.id), "catId"));

If you do need a property of type cat in your result class I don't think this can be done with a simple projection but I might be wrong.

Edit 2:

Since you require an object of type cat in your result I suggest you put it together manually after the query, e.g.:

New result class:

public class myClassResult {
  public virtual int count { get; set; }
  public virtual cat cats { get; set; }
}

Add this after your query logic:

IList<myClassResult> result = new List<myClassResult>();
foreach (var idWithCount in list) 
{
  result.Add(new myClassResult() 
  {
    cats = catsOnlyList.FirstOrDefault(x => x.id == idWithCount.catId),
    count = idWithCount.count
  });
}

catsOnlyList refers to a simple list of cats that you need to get beforehand. I know this isn't pretty but I don't think you can group by cat itself in the query.

Old suggestion (does not work because of incompatible types):

Instead of

.Add(Projections.Group<cat>(g => g.id)));

use

.Add(Projections.Group<cat>(g => g.id), "cats"));

Florian Lim it's returning same columns. give this error : The type System.Int32 can not be assigned to a property of type cat — ASPMaker, Jan 13 '17 at 23:28
@ASPMaker Sorry, I didn't test the solution. I should have known this doesn't work, since grouping by ID will not get you a result property of type `cat`. I modified my answer. — Florian Lim, Jan 14 '17 at 20:59
i'am sorry but this change only give me id. i want for this query, it must return count of answer and cat table with children of answer. i explain all of them in my question. — ASPMaker, Jan 16 '17 at 12:09
@ASPMaker In that case you will have to put the result together manually. See my second edit. — Florian Lim, Jan 16 '17 at 15:42

ASPMaker · Accepted Answer · 2017-01-17T13:20:05.217

i can do that query like that;

public class myClass {
  public virtual int count { get; set; }
  public virtual İnt catId { get; set; }
  public virtual cat cats { get; set; }
}

cat cats = null;
answer answers = null;

var u = db.QueryOver<cat>(() => cats)
    .JoinQueryOver(x => x.answers, () => answers, NHibernate.SqlCommand.JoinType.LeftOuterJoin, Restrictions.Eq("answers.stat", false))
    .SelectList(cv => cv
        .SelectCount(() => answers.id)
        .SelectGroup(c => c.id))
    .List<object[]>()
    .Select(ax => new myClass
    {
      count = (int)ax[0],
      catId = (int)ax[1],
      cats = (cat)db.QueryOver<cat>().Where(w=>w.id==(int)ax[1]).Fetch(fe=>fe.answers).Eager.SingleOrDefault()
    })
    .ToList();

NHibernate parent list with child count

2 Answers2