I am trying to set up an additional EF Core provider for Postgres and I am having an issue overriding the naming strategy used by OrmLite. I have the following in my ConfigureDb which will correctly INSERT records into the quay.ApiKey table, but will not SELECT correctly:
typeof(ApiKeysFeature.ApiKey)
.AddAttributes(new SchemaAttribute("quay"));
typeof(CrudEvent)
.AddAttributes(new SchemaAttribute("quay"));
typeof(Migration)
.AddAttributes(new SchemaAttribute("quay"));
typeof(Migration1000.Booking)
.AddAttributes(new SchemaAttribute("quay"));
typeof(Migration1000.Coupon)
.AddAttributes(new SchemaAttribute("quay"));
// Reset the naming strategy to get the correct TableName casing
PostgreSqlDialect.Provider.NamingStrategy = new PostgresNamingStrategy();
services.AddSingleton<IDbConnectionFactory>(new OrmLiteConnectionFactory(
connectionString, PostgreSqlDialect.Provider));
services.AddDbContext<ApplicationDbContextPostgres>(options =>
options.UseNpgsql(connectionString, b =>
{
b.MigrationsAssembly("Quay.Hive.Core.Data.Postgres");
b.MigrationsHistoryTable("MigrationCore", "quay");
}));
The naming strategy is as below (a bit redundant, but I wanted to check the values):
public class PostgresNamingStrategy : OrmLiteNamingStrategyBase
{
public override string GetTableName(string name)
{
Console.WriteLine("table name: " + name);
return name;
}
public override string GetColumnName(string name)
{
Console.WriteLine("column name: " + name);
return name;
}
public override string GetSchemaName(string name)
{
Console.WriteLine("schema name: " + name);
return name;
}
}
The error I am getting is below:
fail: ServiceStack.ServiceStackHost[0]
42703: column "id" does not exist
POSITION: 269
Npgsql.PostgresException (0x80004005): 42703: column "id" does not exist
POSITION: 269
at ServiceStack.OrmLite.OrmLiteExecFilter.Exec[T](IDbConnection dbConn, Func`2 filter) in /home/runner/work/ServiceStack/ServiceStack/ServiceStack.OrmLite/src/ServiceStack.OrmLite/OrmLiteExecFilter.cs:line 140
at ServiceStack.UserApiKeysService.Get(QueryUserApiKeys request) in /home/runner/work/ServiceStack/ServiceStack/ServiceStack/src/ServiceStack.Server/ApiKeysFeature.cs:line 509
at ServiceStack.Host.ServiceRunner`1.ExecuteAsync(IRequest req, Object instance, TRequest requestDto) in /home/runner/work/ServiceStack/ServiceStack/ServiceStack/src/ServiceStack/Host/ServiceRunner.cs:line 149
Exception data:
Severity: ERROR
SqlState: 42703
MessageText: column "id" does not exist
Hint: Perhaps you meant to reference the column "ApiKey.Id".
Position: 269
File: parse_relation.c
Line: 3729
Routine: errorMissingColumn
Is there somewhere else I should be assigning the PostgresNamingStrategy to ensure that it is used for the var q = db.From<ApiKeysFeature.ApiKey>()?
Hi @bradbrowne ,
I had a go at reproducing your setup and can get the same error you are seeing, however I noticed that if I didn’t use the custom PostgresNamingStrategy applied to PostgreSqlDialect.Provider.NamingStrategy everything works as expected. Are you able to remove this to confirm you are seeing the same thing, so I that I know the reproduction I have is behaving the same way.
I updated the custom PostgresNamingStrategy the following, inheriting from the default one and calling the base, logging both names, showing the difference which I think might be causing the problem.
public class PostgresNamingStrategy : PostgreSqlNamingStrategy
{
public override string GetTableName(string name)
{
Console.WriteLine("table name: " + name);
var res = base.GetTableName(name);
Console.WriteLine($"resolved table name: {res}");
return res;
}
public override string GetColumnName(string name)
{
Console.WriteLine("column name: " + name);
var res = base.GetColumnName(name);
Console.WriteLine($"resolved column name: {res}");
return res;
}
public override string GetSchemaName(string name)
{
Console.WriteLine("schema name: " + name);
var res = base.GetSchemaName(name);
Console.WriteLine($"resolved schema name: {res}");
return res;
}
}
Some of the output below when loading the Admin UI API keys page:
table name: ApiKey
resolved table name: api_key
column name: Name
resolved column name: name
schema name: quay
resolved schema name: quay
table name: ApiKey
resolved table name: api_key
column name: UserId
resolved column name: user_id
How are you initializing the ApiKeysFeature schema?
Try changing that and let me know if you are still seeing an error.
Ahh apologies, I missed this the first time. I can indeed reproduce, and think have found the root cause but still investigating.
1 Like
I am initializing the ApiKeysFeature schema using the npm run migrate task that ships by default and I am configuring it as follows:
public class ConfigureApiKeys : IHostingStartup
{
public void Configure(IWebHostBuilder builder) => builder
.ConfigureServices(services => {
services.AddPlugin(new ApiKeysFeature());
})
.ConfigureAppHost(appHost => {
using var db = appHost.Resolve<IDbConnectionFactory>().Open();
var feature = appHost.GetPlugin<ApiKeysFeature>();
feature.InitSchema(db);
});
}
Just to clarify – the table name is created successfully as quay.ApiKey (once I override the PostgreSqlDialect.Provider.NamingStrategy) and I would like to keep that.
It is just that OrmLite does not seem to reflect the PostgreSqlDialect.Provider.NamingStrategy when querying the columns of the quay.ApiKey table, i.e., it is using id when it should be Id.
Yeah, it looks like it was a problem with the Admin UI combined with the query service. The OrderBy option from the Admin UI is always in a specific case since the Admin UI needs consistent casing. This has been updated to handle this in this commit and should be available in our pre-release feeds as the latest v8.3.1. If you were already using these feeds, you will need to locally clear your NuGet cache to pull the latest.
1 Like
Thank you – I have referenced the 8.3.1 Nuget packages and can confirm that the issue is resolved!
I really appreciate your help 
1 Like