Generating aliases for columns with AutoGen CRUD For Db with case sensitive collation and where the column first letter is in lower cap

1

I’ve “servicified” an existing database (SQL Server 2008 DB) following the tutorial:

Servicify an existing RDBMS with C# AutoQuery .NET APIs and Deploy with GitHub Actions

(Btw thanks for the new videos it really helps better understand how ServiceStack works).

Unfortunately, the DB I’m servicifying doesn’t follow any kind of naming convention, is a total mess, and uses a case-sensitive collation (Latin1_General_BIN) and of course, some columns start with a lower cap letter.

So for example a column named context (lowercase) will have a generated property of:

    [Required]
    public string Context { get; set; }

With no alias.

Is there a way to generate those aliases automatically for databases using case-sensitive collations?

Currently trying to get a column through the client will throw System.ArgumentException: Invalid column name ‘Context’.

Btw this seems to affect only the first letter… Everywhere else the property generated seems to match the case for example a column name that is all-cap will have a property name that is all-cap as well and column names with weird capitalization like ColumnNAME or ColUMNName are preserved when generating property names of the type.

Thanks!

2

Just to clarify, you’ve setup the use of AutoQueryFeature with GenerateCrudServices and generated the code you need for the AutoQuery services to move to a code-first setup? Or are to continuing to use purely runtime GenerateCrudServices?

If you have used the code generation as the initial setup, I would just use [Alias] on those problem column names and go from there.

If you are getting regular updates for the schema where you have to regenerate the service model using /crud/all/csharp? You might be better going with a hybrid of generated and code first using the /crud/new/csharp endpoint. Eg define the problem table manually with the same class name and it should be skipped.

I’ll have a look into the root cause of the issue but I think it will be related to naming conventions to the NativeTypes generator for C#. Having inconsistent naming does make things trickier, but will see if this is something that can be handled programmatically.

Saying that, being able to handle problem cases explicitly might be a nicer way forward. Depending on how much control you have on the database updates, you’ll want some process in place to be sure that changes to your database aren’t going to break your service API integrations. Using runtime types only with GenerateCrudServices is ideal for older static datasets that you need to expose via expressive web APIs but code-first is definitely more flexible and maintainable for living systems. Separation of your Data Model DTOs and those exposed through your ServiceModel allows you to manage those database changes without breaking service API integrations.

3

FYI there’s a number of customization and extensibility options for AutoCrud Gen Services illustrated in the docs.

The AutoGen Customizations also contains an example of changing the default DataModelName (aka Table Name), e.g:

Plugins.Add(new AutoQueryFeature {
    MaxLimit = 1000,
    GenerateCrudServices = new GenerateCrudServices {
        AutoRegister = true,
        GenerateOperationsFilter = ctx => {
            if (ctx.TableName == "applications")
            {
                ctx.DataModelName = "Application";
                ctx.PluralDataModelName = "Apps";
                ctx.RoutePathBase = "/apps";
                ctx.OperationNames = new Dictionary<string, string> {
                    [AutoCrudOperation.Create] = "CreateApp",
                    [AutoCrudOperation.Patch] = "ModifyApp",
                };
            }
        }
    }
});
4

Hi Layoric,

Yes, I moved to a code-first setup.

I used x mix autocrudgen.

Instead of using sqllite I’m using SqlServer.

In the ConfigureAutoQuery class I’ve uncommented AddDataContractAttributes re-run the service and generated the dtos using:

x csharp https://localhost:5001 - path /crud/all/csharp

To clarify…

That database will never change or receive any kind of update. The vendor went out of business and we only have access to the DB, not the application code running on top of it. So there’s no risk of the database breaking the service and we don’t want to risk modifying the data model since there’s no way to know how it will affect the application.

I was planning on using ormlight directly with the generated dtos and/or the service API to migrate the old application and access the old information until we are ready to phase the old system out completely.

I already added some alias on a couple of columns (works perfectly fine) but the database has over 200 tables and a ridiculous amount of columns where the first letter is a small-cap… So that’s going to be a fun little project.

So I thought to ask if there was a flag or way to force the tool to match the exact case of the DB.

Let me know if you find a way to do this.

I’ve looked at the AutoGen Customizations doc that mythz posted.

Are we saying this be achieved through this?

For example from the context generate the proper alias based on the value of

if(char.IsLower(ctx.TableSchema.Columns[0].ColumnName[0]))
{
    //generate alias ?
}

Or something along those lines…?

Thanks for your help.

5

The DataModelName controls what class name is used where if it doesn’t match up with the original Table Name (+ OrmLite Naming Strategy used) it will emit an [Alias] attribute that matches the original table name.

As the DB wont change I recommend you use AutoCrud Gen to generate the Data Models then maintain modified classes from there.

6

I’ve also just added support for custom custom table and column aliases where previously it would only emit [Alias] for non case-insensitive matches, it can be changed to emit [Alias] for non exact (i.e. case-sensitive) matches with:

Plugins.Add(new AutoQueryFeature {
    MaxLimit = 1000,
    GenerateCrudServices = new GenerateCrudServices {
        AutoRegister = true,
        GenerateOperationsFilter = ctx => {
            ctx.GetTableAlias = () => !string.Equals(
                ctx.Dialect.NamingStrategy.GetTableName(ctx.DataModelName), ctx.TableSchema.Name) 
                ? ctx.TableSchema.Name 
                : null;
            ctx.GetColumnAlias = (prop, column) => !string.Equals(
                ctx.Dialect.NamingStrategy.GetColumnName(prop.Name), column.ColumnName)
                ? column.ColumnName
                : null;
            //...
        }
    }
});

This change is available from v5.12.1 that’s now available on MyGet.

7

Works like a charm… Thank you for this.

1 Like