DevExpress XPO v23.2.6 Migrate Database - Create Scheme in .Net 6/7/8/9

appsetttings.json

{
  "ConnectionStrings": {
    "PostgreSQL" : "XpoProvider=Postgres;Server=localhost;User ID=postgres;Password=postgres;Database=postgres;Encoding=UNICODE"
  }
}

BaseAuditableDataObject.cs

[Persistent(nameof(BaseAuditableDataObject))]
public abstract class BaseAuditableDataObject : BaseDataObject
{
    public BaseAuditableDataObject()
    {
    }

    public BaseAuditableDataObject(Session session) : base(session)
    {
    }

    public BaseAuditableDataObject(Session session, XPClassInfo classInfo) : base(session, classInfo)
    {
    }

    [Persistent(nameof(Created))] public DateTime Created { get; set; }
    [Persistent(nameof(CreatedBy))] public string? CreatedBy { get; set; }
    [Persistent(nameof(LastModified))] public DateTime LastModified { get; set; }
    [Persistent(nameof(LastModifiedBy))] public string? LastModifiedBy { get; set; }

    public override void AfterConstruction()
    {
        base.AfterConstruction();
        Created = DateTime.Now;
        CreatedBy = "SERVICE USER NAME";
    }

    protected override void OnSaving()
    {
        base.OnSaving();
        if (!Session.IsNewObject(this))
        {
            LastModified = DateTime.Now;
            LastModifiedBy = "SERVICE USER NAME";
        }
    }

}

BaseDataObject.cs

[NonPersistent]
public abstract class BaseDataObject : XPCustomObject
{
    protected BaseDataObject()
    {
    }

    protected BaseDataObject(Session session) : base(session)
    {
    }

    protected BaseDataObject(Session session, XPClassInfo classInfo) : base(session, classInfo)
    {
    }

    [Key(true), Persistent("ID")]
    public virtual int Id { get; set; }

}

DataAccessExtensions.cs

/// <summary>
/// Creates database migration sql
/// </summary>
/// <typeparam name="T">IDataLayer implemented T abstraction</typeparam>
/// <typeparam name="K">ThreadSafeDataLayer derived K subclass</typeparam>
/// <param name="connectionString">Autocreate options</param>
/// <returns></returns>
static DatabaseMigrationRecord<T> CreateDataLayerAndMigrationSql<T, K>(string connectionString) where T : IDataLayer where K : ThreadSafeDataLayer, new()
{
    XpoDefault.Session = new Session() { CaseSensitive = false };

    XPDictionary dictionary = new ReflectionDictionary();

    string path = AppDomain.CurrentDomain.BaseDirectory;
    Assembly dalAssembly = Assembly.LoadFrom($"{path}{Path.DirectorySeparatorChar}ProjectName.DataAccess.dll");

     List<Type> types = dalAssembly
     .GetTypes()
     .Where(at => at.IsSubclassOf(typeof(BaseDataObject)))
     .DistinctBy(x => x.Name)
     .ToList();

    DBTable[] tables = dictionary.GetDataStoreSchema(types);

    IDataStore provider = CreateProvider(connectionString, AutoCreateOption.SchemaOnly);
    IDataStoreSchemaMigrationProvider migrationProvider = (IDataStoreSchemaMigrationProvider)provider;
    IUpdateSchemaSqlFormatter migrationScriptFormatter = (IUpdateSchemaSqlFormatter)provider;

    SchemaMigrationOptions migrationOptions = new();
    UpdateSchemaStatement[] updateSchemaStatements = migrationProvider.CompareSchema(tables, migrationOptions);
    string sql = migrationScriptFormatter.FormatUpdateSchemaScript(updateSchemaStatements);

    return new((T)Activator.CreateInstance(typeof(K), dictionary, provider), sql);
}

/// <summary>
/// Creates a T type datalayer.
/// </summary>
/// <typeparam name="T">IDataLayer implemented T abstraction</typeparam>
/// <typeparam name="K">ThreadSafeDataLayer derived K subclass</typeparam>
/// <param name="connectionString">Connection string</param>
/// <param name="autoCreateOption">Auto create options</param>
/// <returns></returns>
static T CreateDataLayer<T, K>(string connectionString, AutoCreateOption autoCreateOption) where T : IDataLayer where K : ThreadSafeDataLayer, new()
{
    XpoDefault.Session = new Session() { CaseSensitive = false };

    XPDictionary dictionary = new ReflectionDictionary();

    string path = AppDomain.CurrentDomain.BaseDirectory;
    Assembly dalAssembly = Assembly.LoadFrom($"{path}{Path.DirectorySeparatorChar}ProjectName.DataAccess.dll");

    List<Type> types = dalAssembly
        .GetTypes()
        .Where(at => at.IsSubclassOf(typeof(BaseDataObject)))
        .DistinctBy(x => x.Name)
        .ToList();

    DBTable[] tables = dictionary.GetDataStoreSchema(types);

    return (T)Activator.CreateInstance(typeof(K), dictionary, CreateProvider(connectionString, autoCreateOption));
}

/// <summary>
/// Migrate database schema with persistent objects.
/// </summary>
/// <param name="connectionString">Connection string</param>
public static void MigrateSchema(string connectionString)
{
    DatabaseMigrationRecord<IDataLayer> response = CreateDataLayerAndMigrationSql<IDataLayer, ThreadSafeDataLayer>(connectionString);

    Console.WriteLine("SQL Script to database schema migration:");
    Console.WriteLine("-----begin script------");
    Console.WriteLine(response.MigrationSql);
    Console.WriteLine("------end script-------");

    UnitOfWork uow = new(response.Layer);
    uow.ExecuteNonQuery(response.MigrationSql);
    uow.CommitChanges();
}

/// <summary>
/// Gets PostgreSQL Datalayer
/// </summary>
/// <param name="autoCreateOption">Auto creation option</param>
/// <param name="connectionString">Connection string</param>
/// <returns></returns>
public static IDataLayer GetDataLayer(AutoCreateOption autoCreateOption, string connectionString)
{
    return CreateDataLayer<IDataLayer, ThreadSafeDataLayer>(connectionString, autoCreateOption);
}

DatabaseMigrationRecord.cs

 public record DatabaseMigrationRecord<T>(T Layer, string MigrationSql);

DependencyInjection.cs

public static class DependencyInjection
{
    const string PostgreSQLConfigurationKey = "PostgreSQL";

    public static IServiceCollection AddDbModule(this IServiceCollection services, IConfiguration configuration)
    {
        // If the database is to be rebuilt, remove this line from the comment and run the API.
        //DataAccessExtensions.MigrateSchema(configuration.GetConnectionString(PostgreSQLConfigurationKey));

        services.AddScoped(dl => DataAccessExtensions.GetDataLayer(AutoCreateOption.SchemaAlreadyExists, configuration.GetConnectionString(PostgreSQLConfigurationKey)));

        return services;
    }
}

Employee.cs

[Persistent(nameof(Employee))]
public class Employee(Session session) : BaseAuditableDataObject
{
    [Persistent(nameof(Name)), Size(200)] public string? Name { get; set; }
    [Persistent(nameof(Surname)), Size(200)] public string? Surname { get; set; }
    [Persistent(nameof(Age))] public int Age { get; set; }
    [Persistent(nameof(IsActive))] public bool IsActive { get; set; }
}