SelectMulti GroupBy Aggregate

#1

I’d like to GroupBy and get a Count, but in a SelectMulti setting.

Example, I’m joining three tables, and grouping by a column on T3. I’d like to count that column.

var q = db.From<T1>().Join<T2>(...).Join<T3>()
.GroupBy<T1,T2,T3>((t1,t2,t3)=>new {t1,t2,t3.Status})

Then finally

var results = db.SelectMulti<T1,T2,T3>(q.SelectDistinct())

This gives me what you’d expect from a SQL

SELECT t1.*, t2.*, t3.Status
group by ALL T1'S COLUMNS, ALL T2'S COLUMNS, T3.STATUS

But I’d like to have a Count aggregate, like I’d have from this SQL:

SELECT t1.*, t2.*, t3.Status, Count(*)
group by ALL T1'S COLUMNS, ALL T2'S COLUMNS, T3.STATUS

How can this be achieved?

#2

Hi @specimen151, the current SelectMulti API assumes that types specified will be a table rather than scalar value from an aggregate on the end, so you won’t be able to get the structure you want back from that API as it stands with just your table models. Your best bet I think would be to create a single class to map the structure of result to are looking for and construct this as a custom query in straight SQL. The column names will have to be unique to map to a single class, so you will likely need to expand on the t1.*, t2.* to ensure you have unique names to match.

Alternatively you could make this a view in your database, again mapping to custom DTO structure to match.

#3

Hmm, thanks for your suggestions.

If I could write raw SQL I would just do that, but for this specific query the ORM is actually rather helpful, because based on different parameters I’m using .Or, .And and .Ensure to construct a dynamic query (could also be done by string concatenation I guess).

The view route can perhaps be a path to optimization at a later stage. But then “code first” is broken, and deployment has an extra step. Perhaps create the view through code.

I think the solution for now will be to make a separate query for the aggregates, so I won’t need SelectMulti and just return a Dictionary or custom class.