Fill property of DTO with SubQuery in NHibernate Query

Question

I have a DTO object like this:

public class TreeViewDTO
{
   public string Value { get; set; }
   public string Text { get; set; }
   public bool HasChildren { get; set; }
}

and my entity mapped with Nhibernate is:

public class Entity
{
   public virtual int Id { get; set; }
   public virtual string Name { get; set; }
   public virtual Entity Parent { get; set; }
   /* other properties */
}

I would like to know, how can I get a List of my DTOs and fill the HasChildren property using a count method or a subquery to know if there are childrens?

I have tried this, but does not work:

return Session.QueryOver<Entity>
                        .Select(entity => new TreeViewViewModel() {
                                                        Value = entity.Id.ToString(),
                                                        Text = entity.Name,
                                                        HasChildren = (Session.QueryOver<Entity>().Where(x => x.ParentId == entity.Id).RowCount() > 0)})
                        .ToList();

I got an exception with this: NotSupportedException and the messages says: x => (x.Parent.Id == [100001].Id) and it is not supported.

How could I create a query to fill this property?

PS: I would like to have a query to select only the Id, Name and Count... because my entity can have 30 fields or more...

Thank you.

Haemolysin

So there is no Entity.Children property? – Nelidanelie

No, in my Entity class I have the Parent property that refereces another entity, (not necessary the same type). I just would like to know if there is Children that references the Entity in question. – Haemolysin

I'm not too familiar with NHibernate but shouldn't you use Session.Query with linq? – Nelidanelie

Yes, NHibernate support Linq using Session.Query<Entity> but I'm not sure if it supported everything like EF. – Haemolysin

Can you not have the HasChildren property mapped using Formula with the SQL defined which checks if there exists a record with ParentId as Id ? – Brownnose

Answer 1

Using the NHibernate Linq provider then you can do this:-

public class dto
{
    public long Value { get; set; }
    public int Count { get; set; }
    public bool HasCount { get { return Count > 0; } }
}

Note: my DTO has a read-only property that looks at the actual count, the query is then:-

var a = Db.Session.Query<Support>().Select(
         s => new dto {
                        Value = s.Id,
                        Count = s.CommentList.Count
                      }
            ).ToList();

This generates the following sQL

select support0_.Id                                   as col_0_0_,
       (select cast(count(*) as SIGNED)
        from   supportcomment commentlis1_
        where  support0_.Id = commentlis1_.SupportId) as col_1_0_
from   support support0_

I have never seen a working example of this using QueryOver. I have had had a stab at it but couldn't get it working..

Calciferol

Thank you Rippo, but it help me but I solve it using the let operator on linq query, for sample: var query = from d in Query<T> let c = d.Parents.Any() select new ViewModel (...). thank you – Haemolysin

Perfect, didn't know ANY was implemented yet. Great to see they making LINQ provider better. – Calciferol

Mind you sometimes having the actual count is useful, but not in all cases. – Calciferol

Answer 2

Didn't you consider the option of using something else rather than NHibernate for this job?
In my opinion, lightweight library like Dapper can be a brilliant solution for this use case. You'll end up with a resonably simple sql query instead of jiggling with Nhibernate.

Edit:
dapper code will be as simple as this:

public IDbConnection ConnectionCreate()
{
    IDbConnection dbConnection = new SQLiteConnection("Data Source=:memory:;pooling = true;");
    dbConnection.Open();
    return dbConnection;
}

public void Select()
{
    using (IDbConnection dbConnection = ConnectionCreate())
    {
        var query = @"SELECT e1.id as Value, e1.name as Text, CASE WHEN EXISTS
                        (SELECT TOP 1 1 FROM Entity e2 WHERE e2.parent = e1.id) 
                        THEN 1 ELSE 0 END as HasChildren
                    FROM Entity e1";
        var productDto = dbConnection.Query<TreeViewDTO>(query);
    }
}

Haggle

Well, I cannot change it anymore. Do you know how can I do this? – Haemolysin