Different behavior between SQL Server 2016 and SQL Server 2008 R2

[labilbe]

Hi,

Symptoms

My code runs fine on SQL Server 2016 (dev) but throws SqlException with message 'CONCAT' n'est pas une option nom de fonction intégrée reconnue. Syntaxe incorrecte vers 'Clients'. Syntaxe incorrecte vers 'Clients'. in SQL Server 2008 R2.

Explanation

I am using ServiceStack.OrmLite on a database hosted on SQL Server 2016 (dev env) and same code is applied to SQL Server 2008 R2 (prod env).
I know CONCAT function is not supported on SQL Server 2008 R2, and I would like to know if it is possible to force something like SqlServer2008Dialect.Provider, so the generated function will not be using CONCAT.
In consequence I am using something like that var dbFactory = new OrmLiteConnectionFactory(ConfigurationManager.ConnectionStrings["mydatabase"].ConnectionString, SqlServerDialect.Provider).
My version of ServiceStack is 4.5.14.

Thank you.

[mythz]

Which generated function are you referring to? Please provide a stand-alone code example.

[labilbe]

Here is a code fragment of C# code

      var ev = db.From()
                .Join((p, q) => p.Id == q.CustomerId && q.DocumentSendingModeId != DocumentSendingMode.Fax)
                .LeftJoin((p, q) => p.Id == q.ThirdPartyId && q.Prefix == CustomerContact.DefaultPrefix && q.Civility == "Adresse" && q.Name == "adresse facture")
                .Select((p, q, r) => new
                {
                    Id = Sql.As(p.IdNumber, nameof(CustomerEskerExport.Id)),
                    Email = Sql.As(q.DocumentSendingModeId == DocumentSendingMode.Email ? p.AccountingEmail : "", nameof(CustomerEskerExport.Email)),
                    Address = Sql.As(q.DocumentSendingModeId == DocumentSendingMode.SnailMail ? (r.ThirdPartyId ?? "") == "" ? p.Address + "\r\n" + p.PostalCode + " " + p.City + "\r\n" + p.CountryId : r.Address + "\r\n" + r.PostalCode + " " + r.City + "\r\n" + r.CountryId : "", nameof(CustomerEskerExport.Address)),
                    SendingMode = Sql.As(q.DocumentSendingModeId, nameof(CustomerEskerExport.SendingMode))
                });
            var eskerExports = db.Select(ev);

[labilbe]

The line Address = Sql.As(q.DocumentSendingModeId == DocumentSendingMode.SnailMail ? (r.ThirdPartyId ?? "") == "" ? p.Address + "\r\n" + p.PostalCode + " " + p.City + "\r\n" + p.CountryId : r.Address + "\r\n" + r.PostalCode + " " + r.City + "\r\n" + r.CountryId : "", nameof(CustomerEskerExport.Address)), is generating SQL code with CONCAT function, even on SQL Server 2008 R2.

[labilbe]

@mythz , is is ok for you or you need something shorter?

[mythz]

I've added support for the new SqlServer2008Dialect.Provider with a custom SqlConcat() in this commit and an x-xplat Sql.Cast(field, Sql.VARCHAR) in this commit.

Note: SQL Server 2008's + operator is more fragile than the CONCAT operator and will require casting non-char columns you want to concatenate.

This change is available from v5.0.3 that's now available on MyGet.

[labilbe]

Wow! Thank you very much!