OrmLite Alias issue in Postgres

1

I have a table called “master_._clients” in a Postgres database. Note the . as part of the table. This is causing issues when trying to run a query like Db.Select().ToList() using SS OrmLite.

C# Dto class as per below

[Alias("master.Clients")]
public class DbClient
{
    public int Id { get; set; }
}

I am unable to query this table using ServiceStack OrmLite using the latest version of SS as the db command created is resulting in a raw SQL query that looks like this. Note, it worked on previous versions.

OrmLite’s created dbcommand looks as follows:

"Select id from “master_"."_clients” "

2

How can you have a table with a .? Do you mean you have a Table in a master schema? In which case you would use:

[Schema("master")]
[Alias("Clients")]
public class DbClient
{
    public int Id { get; set; }
}
3

No I actually have a table with a . inside the name.
It is inside the public schema.
table name: “master_._clients”

Note, this used to work on older versions, but no longer works.

So e.g. this works:

 var clients =  db.Select<DbClient>("select id from public.\"master_._clients\"").ToList();
4

Why would it use db naming significant characters like .? That’s inevitably going to be running into issues.

What was the latest version it was working at? What’s the source code and Exception StackTrace for the code that’s failing?

5

Failing at this point:

image
image911×546 53.2 KB

The issue is the resulting CommandText from latest version produces:
“SELECT “id” FROM “master_"."_clients””

image
image1070×388 50 KB

Exception:
“42P01: relation “master_._clients” does not exist”

Stack trace:
" at Npgsql.NpgsqlConnector.g__ReadMessageLong|194_0(NpgsqlConnector connector, Boolean async, DataRowLoadingMode dataRowLoadingMode, Boolean readingNotifications, Boolean isReadingPrependedMessage)\r\n at Npgsql.NpgsqlDataReader.NextResult(Boolean async, Boolean isConsuming, CancellationToken cancellationToken)\r\n at Npgsql.NpgsqlDataReader.NextResult()\r\n at Npgsql.NpgsqlCommand.ExecuteReader(CommandBehavior behavior, Boolean async, CancellationToken cancellationToken)\r\n at Npgsql.NpgsqlCommand.ExecuteReader(CommandBehavior behavior, Boolean async, CancellationToken cancellationToken)\r\n at Npgsql.NpgsqlCommand.ExecuteReader(CommandBehavior behavior)\r\n at Npgsql.NpgsqlCommand.ExecuteDbDataReader(CommandBehavior behavior)\r\n at System.Data.Common.DbCommand.System.Data.IDbCommand.ExecuteReader()\r\n at ServiceStack.OrmLite.OrmLiteCommand.ExecuteReader() in /home/runner/work/ServiceStack/ServiceStack/ServiceStack.OrmLite/src/ServiceStack.OrmLite/OrmLiteCommand.cs:line 51\r\n at ServiceStack.OrmLite.OrmLiteReadCommandExtensions.ExecReader(IDbCommand dbCmd, String sql) in /home/runner/work/ServiceStack/ServiceStack/ServiceStack.OrmLite/src/ServiceStack.OrmLite/OrmLiteReadCommandExtensions.cs:line 42\r\n at ServiceStack.OrmLite.OrmLiteResultsFilterExtensions.ConvertToList[T](IDbCommand dbCmd, String sql) in /home/runner/work/ServiceStack/ServiceStack/ServiceStack.OrmLite/src/ServiceStack.OrmLite/OrmLiteResultsFilterExtensions.cs:line 98\r\n at ServiceStack.OrmLite.OrmLiteReadCommandExtensions.Select[T](IDbCommand dbCmd, String sql, Object anonType) in /home/runner/work/ServiceStack/ServiceStack/ServiceStack.OrmLite/src/ServiceStack.OrmLite/OrmLiteReadCommandExtensions.cs:line 485\r\n at ServiceStack.OrmLite.OrmLiteReadCommandExtensions.Select[T](IDbCommand dbCmd) in /home/runner/work/ServiceStack/ServiceStack/ServiceStack.OrmLite/src/ServiceStack.OrmLite/OrmLiteReadCommandExtensions.cs:line 77\r\n at ServiceStack.OrmLite.OrmLiteReadApi.<>c__01.<Select>b__0_0(IDbCommand dbCmd) in /home/runner/work/ServiceStack/ServiceStack/ServiceStack.OrmLite/src/ServiceStack.OrmLite/OrmLiteReadApi.cs:line 17\r\n at ServiceStack.OrmLite.OrmLiteExecFilter.Exec[T](IDbConnection dbConn, Func2 filter) in /home/runner/work/ServiceStack/ServiceStack/ServiceStack.OrmLite/src/ServiceStack.OrmLite/OrmLiteExecFilter.cs:line 66"

6

That’s the generic db exec command, what’s an example source code that it fails on?

What version did this work in?

7

Example source code:

image
image827×498 30 KB

It works for v5.2.0

8

This implementation has long since been changed to support the more common use-case of quoted names containing schema & table names.

You should be able to restore old behavior with a custom Dialect Provider, e.g:

public class MyPostgreSqlDialectProvider 
    : ServiceStack.OrmLite.PostgreSQL.PostgreSqlDialectProvider
{
    public static MyPostgreSqlDialectProvider Instance = new();
    public override string GetQuotedName(string name) => string.IsNullOrEmpty(name) 
        ? null : name[0] == '"' ? name : '"' + name + '"';
}

// Use instead of PostgreSqlDialect.Provider
var dbFactory = new OrmLiteConnectionFactory(
    connStr, MyPostgreSqlDialectProvider.Instance);
1 Like
9

Thank you! Greatly appreciated!!

1 Like