CODEWITHSUNDEEP
c#sqlservicestackormlite-servicestack
l3m00r
l3m00r

Reputation: 73

ServiceStack.OrmLite: using aliases in SqlExpression for anonymous types

I am posting this question here, as I'm still waiting for approval on the ServiceStack customer forum. It's a rather specific question, so I don't expect many can help... @mythz :)

I'm migrating ServiceStack from v5.4 to v5.7 and there is an issue with aliases in SqlExpression.

I wrote a custom sql concat to get a "kind-of-csv-format" in one column, to merge data into one column, when using unions. From the SQL side, simplified version would be:


SELECT CONCAT(Col1, ',', Col2) as Data FROM Table1
UNION ALL
SELECT CONCAT(Col3, ',', Col4, ',', Col5) as Data FROM Table2

In C#, using OrmLite api, I do:

                var q1 = db.From<Table1>();
                q1.Select(x => new
                {
                    Data = Sql.Custom(q1.ConcatWithSeparator("@delimiter", y => new { y.Col1, y.Col2 }))
                });

ConcatWithSeparator is my custom method, that calls the underlying IOrmLiteDialectProvider.SqlConcat() under the hood, inserting before the @delimiter between the members of anonymous types.

That gave me: SELECT CONCAT("Table1"."Col1", @delimiter, "Table1"."Col2") AS DATA FROM "Table1"

This worked well for the v5.4, but as I noticed, in v5.7, there was a change introduced in methodSqlExpression.SetAnonTypePropertyNamesForSelectExpression() (https://github.com/ServiceStack/ServiceStack.OrmLite/blob/v5.7/src/ServiceStack.OrmLite/Expressions/SqlExpression.cs)

            if (arg is ConditionalExpression ce ||                           // new { Alias = x.Value > 1 ? 1 : x.Value }
                arg is BinaryExpression      be ||                           // new { Alias = x.First + " " + x.Last }
                arg is MemberExpression      me ||                           // new { Alias = DateTime.UtcNow }
                arg is ConstantExpression ct)                                // new { Alias = 1 }
            {
                IOrmLiteConverter converter;
                var strExpr = !(expr is PartialSqlString) && (converter = DialectProvider.GetConverterBestMatch(expr.GetType())) != null
                    ? converter.ToQuotedString(expr.GetType(), expr)
                    : expr.ToString();

                return new PartialSqlString(strExpr + " AS " + member.Name);
            }

In particular, it's about this check:

            if (
                ...
                arg is MemberExpression      me ||                           // new { Alias = DateTime.UtcNow }
                ...
                )

This evaluates to true also for new { x.Col1 }, as it's a short for new { Col1 = x.Col1 } which further makes the sql syntax Col1 as Col1

In my concat, it results in a wrong SQL SELECT CONCAT("Table1"."Col1" AS Col1, @delimiter, "Table1"."Col2" AS Col2) AS DATA FROM "Table1"

So the real question is, shouldn't there be an extra check, that if it's a MemberExpression me, also me.Member.Name != member.Name? Or maybe some extra configuration option to bypass the alias generation?

Or... is there any other, easier approach to achieve, what am I trying to do? (I have to support MySQL, MSSQL, Sqlite and PostgreSQL).

Update, regarding @mythz answer below

Unfortunately, it's still not working as expected. But I think, we are getting closer. The converter here:

IOrmLiteConverter converter;
var strExpr = !(expr is PartialSqlString) && (converter = DialectProvider.GetConverterBestMatch(expr.GetType())) != null
    ? converter.ToQuotedString(expr.GetType(), expr)
    : expr.ToString();

return new PartialSqlString(strExpr != member.Name
    ? strExpr + " AS " + member.Name
    : strExpr);

evaluates the expression to quoted string, so in the end there is a check "\"Col1\"" != "Col1".

Additionally, I also wrote a version of ConcatWithSeparator<Table1, Table2> that can take a lambda as (t1, t2) => new { t1.Col1, t2.Col1 }. In that case, it will evaluate member expression into something like "Table1"."Col1" (depending on DBMS) - I hope, multi-table version is not a hit-in-the-wall and it still can be implemented without raw sql...

Upvotes: 3

Views: 418

Answers (1)

mythz
mythz

Reputation: 143359

I've changed it to not use the alias when the alias is the same name in this commit.

This change is available from the latest v5.7.1 that's now available on MyGet.

Upvotes: 1

Related Questions