Alias don't include quoted table name

ServiceStack.OrmLite
1

Hi

I get error “Npgsql.PostgresException: ‘42P01: missing FROM-clause entry for table “account”’” when I want to exec select with join on this DTO:

public class Account : IHasId<long>
{
	[PrimaryKey, AutoIncrement] public long Id { get; set; }

	[StringLength(250), Required]
	public string Desc { get; set; }

	[StringLength(50)]
	public string FIX_Id { get; set; }

	[StringLength(250)]
	public string FIX_Desc { get; set; }

	[Required]
	public int Active { get; set; }

	[Required]
	public long Currency { get; set; }

	[Default(OrmLiteVariables.SystemUtc), Required]
	public DateTime DInsert { get; set; }

	[RowVersion, Alias("LastChanged")]
	public ulong RowVersion { get; set; }
}

Here is the code how I have configured provider:

var dialect = PostgreSqlDialect.Provider;
dialect.GetDateTimeConverter().DateStyle = DateTimeKind.Utc;
dialect.GetStringConverter().UseUnicode = true;
dialect.NamingStrategy = new OrmLiteNamingStrategyBase();  
var dbcf = new OrmLiteConnectionFactory(appConfig.SqlDbServerPrimaryTotalConnectionString, dialect);

Here is generated SQL:

SELECT "Account"."Id", "Account"."Desc", "Account"."FIX_Id", "Account"."FIX_Desc", "Account"."Active", "Account"."Currency", "Account"."DInsert", Account."xmin" AS "LastChanged" 
FROM "Account" INNER JOIN "ClientAccount" ON (("Account"."Id" = "ClientAccount"."Account") AND ("ClientAccount"."Client" = 12345))

As you can see, in SQL statement there is no quoted table name for alias LastChanged, and that is reason for the error that I get, can you please help me what I need to do so that AliasAttribute will take quoted TableName?

Thank you for your answer.
best regards
Ervin

2

RowVersion is a special db column/feature, which in PostgreSQL uses xmin for its implementation, it can’t be changed with an alias.

3

Thank you, I understand, but what I need to do, so that OrmLite will generate quoted table name on RowVersion?

4

Can you provide an example of a query with the issue?

5 
var q = con.From<Account>();
q.Join<ClientAccount>((a, ac) => a.Id == ac.Account && ac.Client == 12345);
var result = con.Select(q);
6

Can you output the generated SQL with:

OrmLiteUtils.PrintSql();

Before running the query (without any aliases on RowVersion).

7 
SQL: SELECT "Account"."Id", "Account"."Desc", "Account"."FIX_Id", "Account"."FIX_Desc", "Account"."Active", "Account"."Currency", "Account"."DInsert", Account."xmin" AS "RowVersion"
FROM "Account" INNER JOIN "ClientAccount" ON (("Account"."Id" = "ClientAccount"."Account") AND ("ClientAccount"."Client" = 12345))

and on DTO I have made change:

[RowVersion] //, Alias("LastChanged")]
public ulong RowVersion { get; set; }
8

There’s no RowVersion column in PostgreSQL as it uses xmin system column, the issue here is that it’s not using a quoted "Account" reference since it’s not using the default snake_case PostgreSQL Naming convention.

This should be fixed in the latest v6.1.1 that’s now available on MyGet.

9

Exactly, reference was problem, v6.1.1 has fixed my problem. Thank you very much!

3 Likes