OrmLite does not sets Column Aliases consistently and fails to project results

ServiceStack.OrmLite
1

OrmLite does not sets column aliases correctly on calculated fields and, in some cases, even on simple fields, that leads to no value being projected on the output result.

The following is a query written with OrmLite:

var q = _db.From<LRDDevice>();
            q.LeftJoin<LRDDevice,LRDConnessione>((a,b)=>a.Id==b.DeviceId);
            q.LeftJoin<LRDDevice, LRAAlert>((a, b) => a.Id == b.DeviceID && b.Visto==(int)SiNo.No);
            q.Where<LRDDevice>(x => x.Id == ADeviceId);
            q.GroupBy<LRDDevice,LRDConnessione, LRAAlert>((dev,con, ale) => new {dev.Id, con.DeviceId, ale.DeviceID,ale.Tipo });
            q.Select<LRDDevice, LRDConnessione, LRAAlert>((dev, con, ale) => new //DeviceDashboardDTO
            {
                DeviceId = dev.Id,
                //Device = dev,
                StatoLinea = Sql.Min(con.StatoConnessione),
                StatoQC = (Sql.Count(ale.Tipo == (int)TipoAlert.AlertQC ? ale.Id : 0) > 0 ? (int)SiNo.Si : (int)SiNo.No),
                StatoWarning = (Sql.Count(ale.Tipo == (int)TipoAlert.WarningDevice ? ale.Id : 0) > 0 ? (int)SiNo.Si : (int)SiNo.No)
            });
            var aret = _db.Select<DeviceDashboardDTO>(q);

            return aret;

This is the generated Sql Statement:

SELECT "LRDDEVICES"."IDDDEVICE" AS "DeviceId", Min("LRDCONNESSIONI"."STATOCONNESSIONE") AS StatoLinea, (CASE WHEN (Count((CASE WHEN ("LRAALERT"."TIPO"=1) THEN "LRAALERT"."IDAALERT" 
  ELSE 0 END))>0) THEN 1 ELSE 0 END), (CASE WHEN (Count((CASE WHEN ("LRAALERT"."TIPO"=2) THEN "LRAALERT"."IDAALERT" ELSE 0 END))>0) THEN 1 ELSE 0 END)
  FROM "LRDDEVICES" LEFT JOIN "LRDCONNESSIONI" ON ("LRDDEVICES"."IDDDEVICE" = "LRDCONNESSIONI"."DDEVICEID") LEFT JOIN "LRAALERT" ON (("LRDDEVICES"."IDDDEVICE" = "LRAALERT"."DDEVICEID") 
  AND ("LRAALERT"."VISTO" = 0))
WHERE ("LRDDEVICES"."IDDDEVICE" = 10)
  GROUP BY "LRDDEVICES"."IDDDEVICE", "LRDCONNESSIONI"."DDEVICEID", "LRAALERT"."DDEVICEID", "LRAALERT"."TIPO"

Notice that the third and fourth column does not have a column alias. That specific query executed manually on SSMS return 1 as result of both third and fourth column, while aret contains 0 for both.

As stated in previous issues, failing to project results to DTOs is a critical issue for an ORM. Please, investigate this issue, or allow to project our results to concrete types instead of anonymous ones if this helps in projecting the result.

2

ok this should be resolved with this commit which is available from v5.1.1 that’s now available on MyGet.

Note the Sql.* helpers were only meant for simple expressions, e.g. calling an built-in RDBMS functions on a column not for supporting complex C# logic expressions. The SQL Expression Visitor generation will only go so far, but it’s not going to support full C# logic code expressions.

When the SQL projection gets too complicated you should use Custom SQL so you can query the exact SELECT you’re after. The Column and Table will let you reference typed fields and include any aliases or naming conventions, etc.