Auto generate table models with names as defined in the database

Hi,

we use database first and want to switch from t4 to autoquery/ autocrud for generation of model classes.

Our database has around 500 different tables using inconsistent naming conventions regarding tablenames and column names. We already use the t4 generated models a lot in our code base.

How can we change the generation of the table data model classes so that they are not converted to c# naming conventions? The table model classes should just use the names that we use in our ms sql server database for table names and columns.

I tried the following:

services.AddPlugin(new AutoQueryFeature
{
  MaxLimit = 1000,
  StripUpperInLike = true,
  GenerateCrudServices = new GenerateCrudServices
  {
    GenerateOperationsFilter = ctx =>
    {
      ctx.DataModelName = ctx.TableName;
    }
  }
});

I suppose that this should use the table name for the datamodel. But then calling

x csharp http://localhost:5008 -path /crud/all/csharp

returns 500 Invalid Data Exception

I also tried setting a custom Naming Strategy:

public class MyNamingStrategy : OrmLiteNamingStrategyBase
{
    public override string GetTableName(string name)
    {
        return name;
    }

    public override string GetColumnName(string name)
    {
        return name;
    }
}

// set strategy
GenerateOperationsFilter = ctx =>
{
    ctx.Dialect.NamingStrategy = new MyNamingStrategy();                            
}

But the custom Naming strategy is never called.

How can we setup the autogen so that it uses the table and column names as defined in the database?

Best regards,
Michael

Have a look at AutoGen Customizations.

Your preferred TableName and C# class name should be configured there. The ctx.TableName controls which RDBMS Name is used (using OrmLite’s [Alias]) whilst the ctx.DataModelName controls the C# class name to use.

Hi,

I found the problem with the data member names. Our database has table names with hyphens that are illegal in c#.

For reference, I came up with this solution:

services.AddPlugin(new AutoQueryFeature
{
    MaxLimit = 1000,
    StripUpperInLike = true,
    GenerateCrudServices = new GenerateCrudServices
    {
        AccessRole = "Admin",
        DbFactory = dbFactory,
        TypeFilter = (mt, req) =>
        {
            if (mt.RequestType == null && mt.Inherits == null && mt.Properties != null)
            {
                foreach (var prop in mt.Properties)
                {
                    if (prop.Items.TryGetValue("ColumnSchema", out object? value))
                    {
                        var dict = value.ToObjectDictionary();
                        var propName = (string)dict["BaseColumnName"];
                        if (char.IsDigit(propName[0])) propName = "_" + propName;
                        if (((string)dict["ColumnDefinition"]).Contains("primary key", StringComparison.CurrentCultureIgnoreCase)) propName = "Id";
                        if (propName == (string)dict["BaseTableName"] || propName == "long") propName = prop.Name;
                        prop.Name = propName;   
                        prop.Attributes?.RemoveAll(x => x.Name == "Alias" && (x.Attribute as ServiceStack.DataAnnotations.AliasAttribute)?.Name == prop.Name);
                    }
                }
            }
        },
        IncludeCrudOperations = [],        
        GenerateOperationsFilter = ctx =>
        {
            if (ctx.TableName.Contains("-")) return;
            ctx.DataModelName = ctx.PluralDataModelName = ctx.TableName;
        }
    }
});

This now almost generates the code in the same way as the T4 template. It misses the [Reference] attribute, though. Do you know if I can somehow get this? I know I can add them to the Attribute collection. But I didn’t find the foreign key information in any of the objects that are available in the TypeFilter. (Perhaps I just missed them.)

Best regards,
Michael

1 Like

You’d need to explicitly add the [Reference] attributes and properties you want. I recommend Exporting the Code-First DTOs and remove the runtime code generation so you can take over maintenance of the Data Models to apply your preferred customizations.