CODEWITHSUNDEEP
servicestackormlite-servicestack
jaybird
jaybird

Reputation: 117

ServiceStack OrmLite SUBSTRING() Incorrect for SqlServerDialect?

In my AppHost.cs, I'm creating an OrmLiteConnectionFactory using the SqlServerDialect.Provider:

OrmLiteConnectionFactory connectionFactory = new OrmLiteConnectionFactory(
    ConfigurationManager.ConnectionStrings["Key"].ConnectionString, SqlServerDialect.Provider
);

I'm creating a SqlExpression to get a DISTINCT list of first letters of employee last names (don't ask...).

SqlExpression<Employee> sql = db.From<Employee>().SelectDistinct(e => e.LastName.Substring(0,1));

When I run this with

db.Select(sql)

I get a SqlException "Incorrect syntax near the keyword 'from'." A trace shows that the query is

SELECT DISTINCT substring("LastName" from 1 for 1) FROM "Employee"

which is not the correct syntax for the SQL Server SUBSTRING function (looks like MySQL and others).

Am I missing something?

Thanks!

Jay

Upvotes: 1

Views: 179

Answers (1)

mythz
mythz

Reputation: 143284

OrmLite uses the SQL-92 standard format for substring which is supported by most RDBMS's.

Support for SQL Servers Substring() syntax was just added in this commit.

This change is available from v4.0.43+ that's now available on MyGet.

Upvotes: 1

Related Questions