How to do a GroupBy statement with ServiceStack OrmLite

Question

I am doing some queries for Data Visualization and rely on GroupBy, Avg, Sum, and similar functions to get a good dataset from the DB.

I would like to use something similar to GroupBy with ServiceStack OrmLite. On the ShippersExample page I see the following query. Is there an easier or better way to do this?

For example, I have a 'location' column and would like to find the top 5 locations of an entry, and list these locations with the amount of times it occurs. I only have 1 table, so no need for joins.

    var rows = db.SqlList<ShipperTypeCount>(
       "SELECT {0}, COUNT(*) AS Total 
       FROM Shippers 
       GROUP BY {0} 
       ORDER BY Total".Fmt("ShipperTypeId".SqlColumn()));

Answer 1

You can also use a SqlExpression, e.g:

var rows = db.SqlList<ShipperTypeCount>(
    db.From<Shipper>()
    .GroupBy(x => x.ShipperTypeId)
    .OrderBy("Total")
    .Select(x => new { x.ShipperTypeId, Total = Sql.As(Sql.Count("*"), "Total") }));

Alternatively instead of using a concrete POCO you can use a generic dictionary to populate a dictionary of ShipperTypeId => Total, e.g:

var q = db.From<Shipper>()
    .GroupBy(x => x.ShipperTypeId)
    .OrderBy("2")
    .Select(x => new { x.ShipperTypeId, Total = Sql.Count("*") });

var results = db.Dictionary<int, int>(q);