SQL Query to NHibernate with HAVING COUNT

Question

I am trying to recreate the following query in NHibernate:

SELECT DISTINCT
        orderid ,
        tasktype
FROM    "Task"
WHERE   orderid IN ( SELECT orderid
                     FROM   "Task"
                     GROUP BY orderid
                     HAVING COUNT(orderid) > 1 )
ORDER BY orderid

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.

var taskList = new List<Task>();
PendingTasks = session.QueryOver<Model.Task>()
              .WhereRestrictionOn(c => c.OrderId).IsIn(taskList)
              .SelectList
              (list => list
                 .SelectGroup(b => b.OrderId)
                 .Select(b => b.TaskType)
              )
              .Where(Restrictions.Eq(Projections.Count<Model.Task>(x => x.OrderId), taskList.Count() > 1))
              .TransformUsing((Transformers.AliasToBean<TaskType>()))
              .List<TaskType>()

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.

public List<TaskType> PendingTasks { get; set; }

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.

UPDATE: Entire Method:

private static readonly string[] TaskTypeKeys = Enum.GetNames(typeof(TaskType));

var tasksByType = new List<TaskGroup>();
                Task taskObject = null;
                QueryOver subQuery = QueryOver.Of<Task>()
                            .Select(
                            Projections.GroupProperty(
                                Projections.Property<Task>(t => t.OrderId)
                            )
                            )
                            .Where(Restrictions.Gt(Projections.Count<Task>(t => t.OrderId), 1));

                foreach (var type in TaskTypeKeys)
                {
                    TaskType typeEnum;
                    Enum.TryParse(type, out typeEnum);

                    var tasks = session.QueryOver<Model.Task>()
                        .Where(
                            task =>
                            task.TaskType == typeEnum &&
                            task.Completed == false &&
                            task.DueDate <= DateTime.Today
                        )
                        .OrderBy(t => t.DueDate).Asc
                        .List<Model.Task>()
                        .Select(t => 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<Task>(() => taskObject)
                                                                        // the WHERE clause: OrderId IN (subquery)
                                    .WithSubquery
                                    .WhereProperty(() => taskObject.OrderId)
                     ERROR------->  .In(subQuery)

                                    // the rest of your SELECT/projections and transformation
                                    .SelectList(list => list
                                        .SelectGroup(b => b.OrderId)
                                        .Select(b => b.TaskType)
                                    )
                                    .TransformUsing((Transformers.AliasToBean<TaskType>()))
                                    .List<TaskType>()
                            }
                        ).ToList();

                    tasksByType.Add(new TaskGroup()
                        {
                            Title = TaskTypeTitles[type.ToString()],
                            Content = tasks,
                            RemainingCount = tasks.Count(),
                            OverdueCount =
                                tasks.Count(
                                    task =>
                                    task.DueDate < DateTime.Today),
                        });
                };

                return tasksByType;

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.

Answer 1

The subquery syntax will help us in this case. First of all, let's declare the inner select this way:

QueryOver<Task> subQuery = QueryOver.Of<Task>()
    .Select(
       Projections.GroupProperty(
           Projections.Property<Task>(t => t.OrderId)
       )
     )
     .Where(Restrictions.Gt(Projections.Count<Task>(t => t.OrderId), 1))
     ;

This will produce the:

(SELECT this_0_.OrderId as y0_ 
  FROM [Task] this_0_ 
  GROUP BY this_0_.OrderId 
  HAVING count(this_0_.OrderId) > 1)

Now we can use it as a subquery in the outer SELECT:

Task task = null;
var PendingTasks = 
    session.QueryOver<Task>(() => task)
    // the WHERE clause: OrderId IN (subquery)
    .WithSubquery
    .WhereProperty(() => task.OrderId)
    .In(subQuery)

    // the rest of your SELECT/projections and transformation
    .SelectList(list => list
        .SelectGroup(b => b.OrderId)
        .Select(b => b.TaskType)
    )         
    .TransformUsing((Transformers.AliasToBean<TaskType>()))
    .List<TaskType>()
    ;

And this will create the rest, with included subquery