4968

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.

Answer1:

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..

Answer2:

Didn't you consider the option of using something else rather than NHibernate for this job?<br /> 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.

<strong>Edit:</strong><br /> 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); } }

Recommend

  • Implementing Cascade Delete in a self referencing table in EF Core 2
  • How to create a tree structure from JSON data
  • Creating a nested UL from flat array in PHP
  • How to write a query to check if a child has any children in a table
  • How to import relationships between nodes of the same label from a csv file in neo4j?
  • Run a function without a parameter but needed a variable outside of the function - PHP
  • Datagridview to Clipboard with formatting
  • Increment inside angular expression
  • Why Is My Azure SQL Database Table Permanently Locked?
  • QStandardItemModel::removeRows() does not work in my use case
  • Linq to SQL left outer join using Lambda syntax and joining on 2 columns (composite join key)
  • ObjectMaterialize in EF not firing on first level query
  • Spring Integration debounce/deduplicate
  • How to bind comma separated list of values to List
  • Stitching 2 images (OpenCV)
  • If I am creating a simple client server application in IntelliJ, how should this work?
  • Cuda Clang and OS X Mavericks
  • NHibernate manually control fetching
  • Exception gevent.hub.LoopExit: LoopExit('This operation would block forever',)
  • How to 'create temp table as select' in Slick?
  • How to get data from **Realm database** using **date object**?
  • Do query loads all the data in memory
  • Merging rows to columns
  • Zurb Foundation _global.scss meta styles for js?
  • Is there a way to do normal logging with EureakLog?
  • ActiveRecord query for a count of new users by day
  • Can you perform a UNION without a subquery in SQLAlchemy?
  • PostgreSQL Query without WHERE only ORDER BY and LIMIT doesn't use index
  • Launch Runnable Jar from Web Start
  • Volley JsonObjectRequest send headers in GET Request
  • How to convert from System.Drawing.Color to Excel.ColorFormat in C#? Change comment color
  • javascript inside java/jsp code
  • Javascript convert timezone issue
  • Display Images one by one with next and previous functionality
  • Android Studio and gradle
  • SQL merge duplicate rows and join values that are different
  • FormattedException instead of throw new Exception(string.Format(…)) in .NET
  • IndexOutOfRangeException on multidimensional array despite using GetLength check
  • apache spark aggregate function using min value
  • How can i traverse a binary tree from right to left in java?