Inconsistencies about alias on fields

ServiceStack.OrmLite
1

I have the following OrmLite query:

[from and joins...]
q.Select<LRAListaDiLavoro, LRARichiesta, LRAContenitore, LRDAnalisi, LRDDevice>((alis, arich, acont, dana, devc) =>
    new
    {
        Id = Sql.JoinAlias(alis.Id, typeof(LRAListaDiLavoro).GetModelMetadata().Alias),

        DataOraAccettazione = arich.DataOraAccettazione,
        Barcode = acont.Barcode,
        DataOraPrimoCheckIn = acont.DataOraPrimoCheckin,
        DevicePrimoCheckInId = acont.DeviceIdPrimoCheckIn,
        DevicePrimoCheckInCodice = Sql.JoinAlias(devc.Codice, "devcheckin"),
        Codice = dana.Codice,
        Descrizione = dana.Descrizione,
        DataOraInserimento = alis.DataOraInserimento,
        Stato = alis.Stato,
        VersioneRecord = alis.VersioneRecord
    }
);

That statement produces the following query:

SELECT LRALISTEDILAVORO."IDALISTADILAVORO" AS Id,
       "LRARICHIESTE"."DATAORAACCETTAZIONE",
       "LRACONTENITORI"."BARCODE",
       "LRACONTENITORI"."DATAORAPRIMOCHECKIN" AS "DataOraPrimoCheckIn",
       "LRACONTENITORI"."DDEVICEIDPRIMOCHECKIN" AS "DevicePrimoCheckInId",
       devcheckin."CODICE" AS DevicePrimoCheckInCodice,
       "LRDANALISI"."CODICE",
       "LRDANALISI"."DESCRIZIONE",
       "LRALISTEDILAVORO"."DATAORAINSERIMENTO",
       "LRALISTEDILAVORO"."STATO",
       "LRALISTEDILAVORO"."VERSIONERECORD"
FROM "LRALISTEDILAVORO"
INNER JOIN "LRAANALISI" ON ("LRALISTEDILAVORO"."AANALISIID" = "LRAANALISI"."IDAANALISI")
INNER JOIN "LRACONTENITORI" ON ("LRAANALISI"."ACONTENITOREID" = "LRACONTENITORI"."IDACONTENITORE")
INNER JOIN "LRARICHIESTE" ON ("LRACONTENITORI"."ARICHIESTAID" = "LRARICHIESTE"."IDARICHIESTA")
INNER JOIN "LRDANALISI" ON ("LRAANALISI"."DANALISIID" = "LRDANALISI"."IDDANALISI")
LEFT JOIN "LRDDEVICES" devcheckin ON ("LRACONTENITORI"."DDEVICEIDPRIMOCHECKIN" = "devcheckin"."IDDDEVICE")
INNER JOIN "LRDLISTEDILAVORO" ON ("LRALISTEDILAVORO"."DLISTADILAVOROID" = "LRDLISTEDILAVORO"."IDDLISTADILAVORO")
INNER JOIN "LRDDEVICES" ON ("LRDLISTEDILAVORO"."IDDLISTADILAVORO" = "LRDDEVICES"."DLISTADILAVOROID")
WHERE ("LRDDEVICES"."IDDDEVICE" = @0)
OFFSET 0 ROWS FETCH NEXT 15 ROWS ONLY

The query works per-se but if I want to order by programmatically, I can’t do it because some fields has Aliases while others don’t. Could you apply aliases to all fields? In my specific case, I want to order on the fields defined in the anonimous object, so i expect every field to have the alias equal to the name of the property defined on the anonymous object (like it already does for DataOraPrimoCheckin and DevicePrimoCheckInId)

2

Aliases are only applied when it doesn’t match the field name. I don’t understand the OrderBy issue, you can order by any string, with a lambda or positionally with the selected field index e.g. .OrderBy(2).

3

The Order By field is passed by the frontend, using the name of the DTO property (that matches my anonymous object property).

The fact is that i’m trying to order by string, but my strings always matches anonymous object properties, while aliases don’t.

My DB is also case sensitive, so that’s why Barcode and BARCODE are different.

Could you please set the check as case sensitive, so that aliases with different cases from the field name gets set as normal?

It shouldn’t be a breaking changes for case insensitive databases, or you could check the OrmLiteConfig.IsCaseInsensitive property for this check.

Thanks.

4

I’ve changed it so it uses aliases when selecting a field from a joined table (i.e. different from the source table) in this commit.

This change is now available from the latest v5.4.1 on MyGet.

5

Thanks, it works a bit better but it doesn’t work with field of the main table.

If I OrderBy using string referencing a column of the main table like the others, I get this query:

SELECT LRALISTEDILAVORO."IDALISTADILAVORO"      AS Id,
       "LRARICHIESTE"."DATAORAACCETTAZIONE"     AS "DataOraAccettazione",
       "LRACONTENITORI"."BARCODE"               AS "Barcode",
       "LRACONTENITORI"."DATAORAPRIMOCHECKIN"   AS "DataOraPrimoCheckIn",
       "LRACONTENITORI"."DDEVICEIDPRIMOCHECKIN" AS "DevicePrimoCheckInId",
       devcheckin."CODICE"                      AS DevicePrimoCheckInCodice,
       "LRDANALISI"."CODICE"                    AS "Codice",
       "LRDANALISI"."DESCRIZIONE"               AS "Descrizione",
       "LRALISTEDILAVORO"."DATAORAINSERIMENTO",
       "LRALISTEDILAVORO"."STATO",
       "LRALISTEDILAVORO"."VERSIONERECORD"
FROM "LRALISTEDILAVORO"
       INNER JOIN "LRAANALISI" ON ("LRALISTEDILAVORO"."AANALISIID" = "LRAANALISI"."IDAANALISI")
       INNER JOIN "LRACONTENITORI" ON ("LRAANALISI"."ACONTENITOREID" = "LRACONTENITORI"."IDACONTENITORE")
       INNER JOIN "LRARICHIESTE" ON ("LRACONTENITORI"."ARICHIESTAID" = "LRARICHIESTE"."IDARICHIESTA")
       INNER JOIN "LRDANALISI" ON ("LRAANALISI"."DANALISIID" = "LRDANALISI"."IDDANALISI")
       LEFT JOIN "LRDDEVICES" devcheckin ON ("LRACONTENITORI"."DDEVICEIDPRIMOCHECKIN" = "devcheckin"."IDDDEVICE")
ORDER BY Stato

The query crashes because Stato is an invalid column. I think you should apply aliases to all columns…

6

It should use the full name if you use the typed API, i.e.:

.OrderBy(x => x.Stato)

If you want to order by field name you can use:

.OrderByFields("Stato")

Or if you’re us a string use the fully qualified name.

7

Well ok, I understand that. My problem is like I said a couple of post behind:
I have the sort field passed from frontend, and that field is equal to the name of the anonymous object property. If, for an anonymous select query, you could keep all aliases equals to the anonymous object properties, that would be perfect for us (it could solve other problems too, because we make heavy use of custom queries like you saw)

8

That’s changing how custom queries are created for everyone just to support your niche use-case. There are a number of different ways you can order by fields. If you don’t want to pass the field name you can specify the column number positionally since you know the order of the new expression properties, you can even use Sql.Custom() to specify the exact SQL you want.

The purpose of the select is to change how the resultset is returned so it can be hydrated into a matching schema, it’s internal impl is not going to be constrained to support a conflicting purpose of forcing the internal impl to match anon properties. Honestly I want to revert the last change added which adds constraints to support a niche use-case that’s built on the assumption of internal impl details (corner cases which I dislike inflicting code-bases with).

I don’t think you should be writing code that assumes internal behavior like this, I’d either be using the exact field name or maintain a mapping of your App field name to data field name.

9

Well, I understand what you are saying, it’s just strange to me that aliases on every column wasn’t the standard.

Anyway, I’ve solved using some of the tools you showed me in the InsertIntoSelect thread:

private static void ApplySortingForAnonymousObject<TResult, TQuery>(SqlExpression<TQuery> q, string[] sortFields, int orderDirection)
{
    // Get the tokenized select list
    var commands = q
        .SelectExpression
        .Substring("SELECT ".Length)
        .ParseCommands();

    // Get field names from the tokenized list  
    var selectFields = commands
        .Select(k => Regex.Match(k.Name, @"((?>""\w+"".)?""\w+"")$").Groups[1].Value);

    bool isFirstIteration = true;

    // match Sort Fields and Select List Fields
    var sortItems =
        from sel in selectFields
        from sort in sortFields
        where sort != null
              && CultureInfo.InvariantCulture
                  .CompareInfo
                  .IndexOf(sel, sort, CompareOptions.OrdinalIgnoreCase) >= 0
        select sel;

    // create order by clauses
    foreach (string sorts in sortItems)
    {
        if (isFirstIteration)
        {
            if (orderDirection > 0)
                q.OrderBy(sorts);
            else
                q.OrderByDescending(sorts);
            isFirstIteration = false;
        }
        else
        {
            if (orderDirection > 0)
                q.ThenBy(sorts);
            else
                q.ThenByDescending(sorts);
        }
    }
}