Databases
StackOverflow2013
Postsdbo
POSQL Query to NHibernate with HAVING COUNT
Body
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POSQL Query to NHibernate with HAVING COUNT
    text
    copied!<p>I am trying to recreate the following query in NHibernate: </p> <pre><code>SELECT DISTINCT orderid , tasktype FROM "Task" WHERE orderid IN ( SELECT orderid FROM "Task" GROUP BY orderid HAVING COUNT(orderid) &gt; 1 ) ORDER BY orderid </code></pre> <p>In NH, I need a QueryOver that returns a list of task types based on the order id. Basically, I am iterating over each task and for each task that occurs more than once, (because of a different task type), i need to add all those tasks into a list that gets returned to the client. This is what I have tried so far with NH.</p> <pre><code>var taskList = new List&lt;Task&gt;(); PendingTasks = session.QueryOver&lt;Model.Task&gt;() .WhereRestrictionOn(c =&gt; c.OrderId).IsIn(taskList) .SelectList (list =&gt; list .SelectGroup(b =&gt; b.OrderId) .Select(b =&gt; b.TaskType) ) .Where(Restrictions.Eq(Projections.Count&lt;Model.Task&gt;(x =&gt; x.OrderId), taskList.Count() &gt; 1)) .TransformUsing((Transformers.AliasToBean&lt;TaskType&gt;())) .List&lt;TaskType&gt;() </code></pre> <p>I have just started NH, and found some examples on here regarding the use of grouping and having. The property from the model I am returning to the client is here with TaskType being a simple enum.</p> <pre><code>public List&lt;TaskType&gt; PendingTasks { get; set; } </code></pre> <p>It seems to me so far, that the QueryOver is trying to return an IList, against my target type of List, however there is no .ToList(), so I do not know what this will return. Any help matching the sql query above is helpful.</p> <p>UPDATE: Entire Method:</p> <pre><code>private static readonly string[] TaskTypeKeys = Enum.GetNames(typeof(TaskType)); var tasksByType = new List&lt;TaskGroup&gt;(); Task taskObject = null; QueryOver subQuery = QueryOver.Of&lt;Task&gt;() .Select( Projections.GroupProperty( Projections.Property&lt;Task&gt;(t =&gt; t.OrderId) ) ) .Where(Restrictions.Gt(Projections.Count&lt;Task&gt;(t =&gt; t.OrderId), 1)); foreach (var type in TaskTypeKeys) { TaskType typeEnum; Enum.TryParse(type, out typeEnum); var tasks = session.QueryOver&lt;Model.Task&gt;() .Where( task =&gt; task.TaskType == typeEnum &amp;&amp; task.Completed == false &amp;&amp; task.DueDate &lt;= DateTime.Today ) .OrderBy(t =&gt; t.DueDate).Asc .List&lt;Model.Task&gt;() .Select(t =&gt; new Task() { Id = t.Id, OrderId = t.OrderId, CustomerId = t.CustomerId, CustomerName = t.CustomerName, GroupName = t.GroupName, TripDate = t.TripDate, TaskType = TaskTypeTitles[t.TaskType.ToString()], DueDate = t.DueDate, Completed = t.Completed, IsActiveTask = t.IsActiveTask, PendingTasks = session.QueryOver&lt;Task&gt;(() =&gt; taskObject) // the WHERE clause: OrderId IN (subquery) .WithSubquery .WhereProperty(() =&gt; taskObject.OrderId) ERROR-------&gt; .In(subQuery) // the rest of your SELECT/projections and transformation .SelectList(list =&gt; list .SelectGroup(b =&gt; b.OrderId) .Select(b =&gt; b.TaskType) ) .TransformUsing((Transformers.AliasToBean&lt;TaskType&gt;())) .List&lt;TaskType&gt;() } ).ToList(); tasksByType.Add(new TaskGroup() { Title = TaskTypeTitles[type.ToString()], Content = tasks, RemainingCount = tasks.Count(), OverdueCount = tasks.Count( task =&gt; task.DueDate &lt; DateTime.Today), }); }; return tasksByType; </code></pre> <p>The type arguments for method 'NHibernate.Criterion.Lambda.QueryOverSubqueryPropertyBuilderBase,Api.Task,Api.Task>.In(NHibernate.Criterion.QueryOve‌​r)' cannot be inferred from the usage. Try specifying the type arguments explicitly.</p>
 

Querying!

 
Guidance
An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload