I'm trying to dynamically compose a groupby query with queryspec, and I am able to do just about everything I need to except I'm getting hung up on the Select projection. If I do this it works fine:

var qf = new QueryFactory();

List<object> groupFields = new List<object>();

var workerQuery = qf.Worker
  .InnerJoin(qf.Task).On(WorkerFields.Id == TaskFields.WorkerId)
  .InnerJoin(qf.TaskDefinition).On(TaskDefinitionFields.Id == TaskFields.DefinitionId);

groupFields.Add(WorkerFields.Group1Id);
workerQuery = workerQuery.InnerJoin(qf.WorkerGroup).On(WorkerFields.Group1Id == WorkerGroupFields.Id);

groupFields.Add(TaskFields.DefinitionId);
groupFields.Add( TaskDefinitionFields.Title );
groupFields.Add( WorkerGroupFields.Description );

var query = qf.Create().Select(() => 
  new { 
      GroupDescr = WorkerGroupFields.Description.ToValue<string>(),
      TaskDefName = TaskDefinitionFields.Title.ToValue<string>() ,
      TotalCount = WorkerFields.Id.Count().As("Counted").ToValue<int>()
  })
.From(workerQuery)
.Where( TaskFields.CompletedUTC != DBNull.Value)
  .Where( WorkerFields.Group1Id != DBNull.Value)
.GroupBy( groupFields.ToArray());

this.AdapterToUse.FetchQuery(query).Dump();

I took out all of the conditional code to simplify the example, but the idea is that I am able to build up the groupFields collection and inner joins dynamically, but so far I haven't been able to do the same for the .Select. I want to replace the anonymous type with something like an ExpandoObject so I can conditionally choose which fields to include(this would coincide with conditional decisions of what groupbys to include).

dynamic projection = new System.Dynamic.ExpandoObject();

IDictionary<string, object> dictionary = (IDictionary<string, object>)projection;
dictionary.Add("GroupDescr", WorkerGroupFields.Description.ToValue<string>());
dictionary.Add("TaskDefName", TaskDefinitionFields.Title.ToValue<string>());
dictionary.Add("TotalCount", WorkerFields.Id.Count().As("Counted").ToValue<int>());

var query = qf.Create().Select(() => projection)
.From(workerQuery)
//...

Error: "No projection elements specified."

So I took a step back and just tried declaring the anonymous type outside of the expression(even though this doesn't give me the dynamic capability I need, it is just troubleshooting step):

var projection = new {  
      GroupDescr = WorkerGroupFields.Description.ToValue<string>(),
      TaskDefName = TaskDefinitionFields.Title.ToValue<string>() ,
      TotalCount = WorkerFields.Id.Count().As("Counted").ToValue<int>()
  };

var query = qf.Create().Select(() => projection )
.From(workerQuery)
//...

Error: "No projection elements specified."

I'm not really sure why this last one would not work, as it seems identical to declaring the anonymous type inline. The .Select() would infer the anonymous type from the projection variable. I suspect the problem is some nuance of expressions that I don't understand.

I've tried the other overload of .Select that takes a params [] object, similar to how I used groupFields list, but I don't know how to include aggregate functions in that.

Anyone have any suggestions?

LLBLGen DLL File version is 3.5.12.317

Using the array approach should work. You can use aggregate functions just the way you would do it inside the query. Example:

var selectList = new List<object>();
selectList.Add(OrderDetailFields.OrderId);
selectList.Add((OrderDetailFields.Quantity * OrderDetailFields.UnitPrice).Sum().As("Total"));

var groupByList = new List<object>();
groupByList.Add(OrderDetailFields.OrderId);

var qf = new QueryFactory();
var q = qf.Create("g")
        .Select(selectList.ToArray())
        .GroupBy(groupByList.ToArray());

Thanks David, works perfectly.

Just adding in case someone else comes across this thread. With this approach each item returned is an object[] instead of an anoymous type with column names. In my case the output columns will always be the same, it's just the source columns that are dynamic/different. This is where WithProjector comes in handy:

selectedFields.Add(WorklerGroupFields.Description.As("GroupAxisLabel"));
selectedFields.Add(TaskDefinitionFields.Title.As("TaskAxisLabel"));
selectedFields.Add(WorkerFields.Id.Count().As("Total"));

var finalQuery = qf.Create().Select(selectedFields.ToArray())
.From(workerQuery)
.GroupBy( groupFields.ToArray())
.WithProjector(r => 
new {
  XAxisLabel = (string)r["GroupAxisLabel"],
  YAxisLabel = (string)r["TaskAxisLabel"],
  Total = (int)r["Total"]
});

var list = this.AdapterToUse.FetchQuery(finalQuery );

This ensures each item in the list has the anonymous object structure defined in WithProjector instead of being an array.

Thanks for the feedback Aaron

Is it possible to define an aggregate field with a condition like:

selectList.Add( OrderDetailFields.CategoryId );
selectList.Add( OrderDetailFields.CategoryId.Count().As("TotalCount") );
selectList.Add( (OrderDetailFields.OrderUTC > OrderPolicyFields.DeadlineUTC ? 1 : 0  ).Sum().As("OverdueCount") );

This would be like the following query if you had orders, and grouped by some order category to get a count of the number of orders in that category and a count of the number of overdue orders in that category:

Select 
  od.CategoryId,
  Count(*) as TotalCount,
  Sum(CASE When od.OrderUTC > op.DeadlineUTC Then 1 Else 0 END) as OverdueCount
From OrderDetail od 
Inner Join OrderPolicy op on od.PolicyId = op.Id
Group By od.CategoryId

Nevermind, figured it out using DbFunctionCall based on some other forum psots. Might suggest adding mention of this under the documentation here:

https://www.llblgen.com/documentation/2.6/Using%20the%20generated%20code/gencode_expressionsaggregates.htm

var expCase = new DbFunctionCall(
  "CASE WHEN {0} < {1} and {2} is null THEN 1 ELSE 0 END", 
  new object[] { OrderFields.DueUTC, DateTime.Now, OrderFields.CompletedUTC });
selectedFields.Add(new EntityField2("OverdueTotal", expCase, AggregateFunction.Sum));