EFCore Configuration

Overview

This document explains the Entity Framework Core configuration system in FlexBase applications, using the EBusiness sample as a reference. The EF Core configuration provides a flexible, multi-database, multi-tenant data access layer that supports SQL Server, MySQL, and PostgreSQL with both single-tenant and multi-tenant architectures.

Architecture Overview

The EF Core configuration in FlexBase follows a layered architecture with separate projects for different database providers and tenant configurations. This allows for seamless transitions between different database systems and tenant models.

Configuration Flow

Database Selection → Tenant Model Selection → Connection Provider Setup → DbContext Configuration → Repository Registration

Project Structure

Database Infrastructure Projects

The EBusiness.Application/Infrastructure/Db/ folder contains all EF Core configuration implementations:

EBusiness.Application/Infrastructure/Db/
├── EBusiness.BaseEF/                          # Core EF configuration
│   ├── ApplicationEFDbContext.cs              # Single-tenant DbContext
│   ├── ApplicationTenantEFDbContext.cs        # Multi-tenant DbContext
│   ├── ConnectionProviders/                   # Connection provider implementations
│   │   ├── AppSettingsReadDbConnectionProvider.cs
│   │   ├── AppSettingsWriteDbConnectionProvider.cs
│   │   ├── NativeReadDbTenantConnectionProvider.cs
│   │   └── NativeWriteDbTenantConnectionProvider.cs
│   └── EBusiness.BaseEF.csproj
├── EBusiness.BaseEF.SqlServer/                # SQL Server implementation
│   ├── ApplicationEFSqlServerDbContext.cs
│   ├── FlexEFTenantMasterSqlServerContext.cs
│   ├── FlexReadDbRepositoryEFSQLServer.cs
│   ├── FlexWriteDbRepositoryEFSQLServer.cs
│   ├── FlexTenantMasterRepositoryEFSQLServer.cs
│   └── EBusiness.BaseEF.SqlServer.csproj
├── EBusiness.BaseEF.MySql/                    # MySQL implementation
│   ├── ApplicationEFMySqlDbContext.cs
│   ├── FlexEFTenantMasterMySqlContext.cs
│   ├── FlexReadDbRepositoryEFMySql.cs
│   ├── FlexWriteDbRepositoryEFMySql.cs
│   ├── FlexTenantMasterRepositoryEFMySql.cs
│   └── EBusiness.BaseEF.MySql.csproj
└── EBusiness.BaseEF.PostgreSql/               # PostgreSQL implementation
    ├── ApplicationEFPostgreSqlDbContext.cs
    ├── FlexEFTenantMasterPostgreSqlContext.cs
    ├── FlexReadDbRepositoryEFPostgreSql.cs
    ├── FlexWriteDbRepositoryEFPostgreSql.cs
    ├── FlexTenantMasterRepositoryEFPostgreSql.cs
    └── EBusiness.BaseEF.PostgreSql.csproj
└── EBusiness.BaseEF.Oracle/                   # Oracle implementation (example)
    ├── ApplicationEFOracleDbContext.cs
    ├── FlexEFTenantMasterOracleContext.cs
    ├── FlexReadDbRepositoryEFOracle.cs
    ├── FlexWriteDbRepositoryEFOracle.cs
    ├── FlexTenantMasterRepositoryEFOracle.cs
    └── EBusiness.BaseEF.Oracle.csproj

Dependencies

Each project references specific FlexBase EF Core packages:

<!-- Core EF Package -->
<PackageReference Include="Sumeru.Flex.Data.EntityFramework" Version="9.0.0" />

<!-- Database-Specific Packages -->
<PackageReference Include="Sumeru.Flex.Data.EntityFramework.SqlServer" Version="9.0.0" />
<PackageReference Include="Sumeru.Flex.Data.EntityFramework.MySql" Version="9.0.0" />
<PackageReference Include="Sumeru.Flex.Data.EntityFramework.PostgreSql" Version="9.0.0" />
<PackageReference Include="Sumeru.Flex.Data.EntityFramework.Oracle" Version="9.0.0" />

Configuration Selection Logic

DbEndPointConfig.cs - Database and Tenant Configuration

The DbEndPointConfig.cs file contains the logic for selecting the appropriate database configuration:

public static class DbEndPointConfig
{
    public static void AddFlexBaseDbServices(this IServiceCollection services)
    {
        //Configuration for Single Tenant Database
        EnableSingleTenant(services);

        //Configuration for Multi Tenant Database
        //EnableMultitenant(services);
    }

    private static void EnableSingleTenant(IServiceCollection services)
    {
        ConfigureSingleTenantDb(services);
        ConfigureFlexDb.ForRepositoryForSqlServer(services);
    }

    private static void EnableMultitenant(IServiceCollection services)
    {
        ConfigureMultiTenantMasterDb(services);

        //Either 
        //If you want to use the same sql server for all instances for multitenant application
        //You may do this if you want MySql Or PostgreSql respectively
        ConfigureFlexDb.ForMultitenantMasterDbForSqlServer(services);
        ConfigureFlexDb.ForRepositoryForSqlServer(services);
        

        //Or 
        //ConfigureFlexDb.ForRepositoryForMultiDb(services); //If you want to use different database type for each tenant
    }
}

Single Tenant vs Multi-Tenant Architecture

Single Tenant Architecture

Definition: A single tenant architecture means that each customer (tenant) has their own dedicated database instance. All data for a tenant is completely isolated from other tenants.

Characteristics:

  • Data Isolation: Complete physical separation of tenant data

  • Performance: No cross-tenant performance impact

  • Security: Highest level of data security and isolation

  • Scalability: Each tenant can scale independently

  • Cost: Higher cost due to dedicated resources per tenant

Configuration:

private static void EnableSingleTenant(IServiceCollection services)
{
    ConfigureSingleTenantDb(services);
    ConfigureFlexDb.ForRepositoryForSqlServer(services);
}

private static void ConfigureSingleTenantDb(IServiceCollection services)
{
    //Default Settings for connection provider which gives you a simple single tenant application
    services.AddTransient<IWriteDbConnectionProviderBridge, AppSettingsWriteDbConnectionProvider>();
    services.AddTransient<IReadDbConnectionProviderBridge, AppSettingsReadDbConnectionProvider>();
}

Connection Providers:

  • AppSettingsWriteDbConnectionProvider: Reads write database connection from appsettings

  • AppSettingsReadDbConnectionProvider: Reads read database connection from appsettings

Use Cases:

  • Enterprise Applications: Large organizations with strict data isolation requirements

  • Compliance Requirements: Industries requiring complete data separation

  • High-Value Customers: Premium customers requiring dedicated resources

  • Regulatory Compliance: GDPR, HIPAA, SOX compliance scenarios

Multi-Tenant Architecture

Definition: A multi-tenant architecture means that multiple customers (tenants) have their own independent database instances, with complete data isolation and independent scaling capabilities. FlexBase's default implementation provides each tenant with their own dedicated database.

Characteristics:

  • Independent Databases: Each tenant has their own dedicated database instance

  • Complete Data Isolation: Physical separation ensures maximum security and confidentiality

  • Independent Scaling: Each tenant can scale based on their specific usage patterns

  • Cost Optimization: Cloud databases can be scaled independently per tenant usage

  • Security: Highest level of data security with no cross-tenant data access

  • Performance: No cross-tenant performance impact or resource contention

Configuration:

private static void EnableMultitenant(IServiceCollection services)
{
    ConfigureMultiTenantMasterDb(services);

    //Option 1: Same database type for all tenants
    ConfigureFlexDb.ForMultitenantMasterDbForSqlServer(services);
    ConfigureFlexDb.ForRepositoryForSqlServer(services);
    
    //Option 2: Different database types for different tenants
    //ConfigureFlexDb.ForRepositoryForMultiDb(services);
}

private static void ConfigureMultiTenantMasterDb(IServiceCollection services)
{
    //Default Settings for connection provider which gives you a simple multi tenant application
    services.AddTransient<IWriteDbConnectionProviderBridge, NativeWriteDbTenantConnectionProvider>();
    services.AddTransient<IReadDbConnectionProviderBridge, NativeReadDbTenantConnectionProvider>();
}

Connection Providers:

  • NativeWriteDbTenantConnectionProvider: Dynamically determines write database based on tenant

  • NativeReadDbTenantConnectionProvider: Dynamically determines read database based on tenant

FlexBase Multi-Tenant Advantages:

1. Independent Database Scaling 🚀

// Each tenant gets their own database instance
Tenant A: Database_A (Small instance - 2 vCores, 8GB RAM)
Tenant B: Database_B (Large instance - 16 vCores, 64GB RAM)
Tenant C: Database_C (Medium instance - 8 vCores, 32GB RAM)

Benefits:

  • Usage-Based Scaling: Each tenant scales based on their actual usage

  • Cost Optimization: Tenants pay only for resources they consume

  • Performance Isolation: Heavy usage by one tenant doesn't affect others

  • Cloud-Native: Leverages cloud database auto-scaling features

2. Enhanced Security & Compliance 🔒

// Complete data isolation per tenant
Tenant A Data: Completely isolated in Database_A
Tenant B Data: Completely isolated in Database_B
Tenant C Data: Completely isolated in Database_C

Benefits:

  • Physical Data Separation: No shared tables or cross-tenant data access

  • Regulatory Compliance: Meets strict compliance requirements (GDPR, HIPAA, SOX)

  • Data Sovereignty: Tenants can be deployed in specific regions

  • Audit Trail: Complete isolation makes auditing easier

3. Independent Database Types 🗄️

// Different tenants can use different database types
Tenant A: SQL Server (Enterprise features)
Tenant B: MySQL (Cost-effective)
Tenant C: PostgreSQL (Advanced features)
Tenant D: Oracle (High-performance analytics)

Benefits:

  • Technology Choice: Each tenant can choose their preferred database

  • Migration Flexibility: Easy to migrate tenants between database types

  • Feature Optimization: Match database features to tenant requirements

  • Cost Optimization: Use cost-effective databases for appropriate workloads

4. Cloud Database Integration ☁️

// Cloud-native database scaling per tenant
Azure SQL Database: Auto-scaling based on tenant usage
AWS RDS: Independent scaling per tenant
Google Cloud SQL: Per-tenant resource allocation

Benefits:

  • Auto-Scaling: Cloud databases automatically scale based on tenant usage

  • Pay-Per-Use: Only pay for actual database usage per tenant

  • Global Distribution: Tenants can be deployed in different regions

  • Disaster Recovery: Independent backup and recovery per tenant

Use Cases:

  • SaaS Applications: Software-as-a-Service applications serving multiple customers with independent scaling

  • Enterprise SaaS: Large enterprise customers requiring dedicated database resources

  • Compliance-Critical Applications: Applications requiring strict data isolation and confidentiality

  • Cloud-Native Applications: Applications leveraging cloud database auto-scaling per tenant

  • Multi-Region Deployments: Tenants can be deployed in different regions for data sovereignty

  • Cost-Optimized Scaling: Each tenant pays only for their actual usage and scales independently

Database Provider Configurations

1. SQL Server Configuration

Project: EBusiness.BaseEF.SqlServer

DbContext Implementation:

public class ApplicationEFSqlServerDbContext : ApplicationEFDbContext
{
    public ApplicationEFSqlServerDbContext()
    {
        //this constructor goes only with the migrations
    }

    public ApplicationEFSqlServerDbContext(DbContextOptions<ApplicationEFSqlServerDbContext> options) : base(options)
    {
        //this constructor is being used by the repos to initialize the context with various options including multitenancy
    }

    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        base.OnModelCreating(modelBuilder);
    }
}

Tenant Master Context:

public class FlexEFTenantMasterSqlServerContext : ApplicationTenantEFDbContext
{
    public FlexEFTenantMasterSqlServerContext() : base()
    {
    }

    public FlexEFTenantMasterSqlServerContext(DbContextOptions options): base(options)
    {
    }
}

Characteristics:

  • Enterprise Grade: Full-featured enterprise database

  • ACID Compliance: Complete transactional support

  • High Performance: Optimized for complex queries

  • Rich Features: Advanced indexing, partitioning, etc.

  • Windows Integration: Native Windows authentication support

Configuration Requirements:

{
  "FlexBase": {
    "AppDbConnection": "Data Source=YourServer;Initial Catalog=YourDatabase;User ID=YourUser;Password=YourPassword;TrustServerCertificate=True",
    "AppReadDbConnection": "Data Source=YourServer;Initial Catalog=YourReadDatabase;User ID=YourUser;Password=YourPassword;TrustServerCertificate=True"
  }
}

2. MySQL Configuration

Project: EBusiness.BaseEF.MySql

Characteristics:

  • Open Source: Free and open-source database

  • Cross-Platform: Runs on multiple operating systems

  • High Performance: Optimized for read-heavy workloads

  • Easy Setup: Simple installation and configuration

  • Cost Effective: No licensing costs

Configuration Requirements:

{
  "FlexBase": {
    "AppDbConnection": "Server=YourServer;Database=YourDatabase;Uid=YourUser;Pwd=YourPassword;",
    "AppReadDbConnection": "Server=YourServer;Database=YourReadDatabase;Uid=YourUser;Pwd=YourPassword;"
  }
}

3. PostgreSQL Configuration

Project: EBusiness.BaseEF.PostgreSql

Characteristics:

  • Advanced Features: Rich set of data types and functions

  • ACID Compliance: Full transactional support

  • Extensible: Custom data types and functions

  • JSON Support: Native JSON and JSONB support

  • Open Source: Free and open-source

Configuration Requirements:

{
  "FlexBase": {
    "AppDbConnection": "Host=YourServer;Database=YourDatabase;Username=YourUser;Password=YourPassword;",
    "AppReadDbConnection": "Host=YourServer;Database=YourReadDatabase;Username=YourUser;Password=YourPassword;"
  }
}

4. Oracle Configuration

Project: EBusiness.BaseEF.Oracle

DbContext Implementation:

public class ApplicationEFOracleDbContext : ApplicationEFDbContext
{
    public ApplicationEFOracleDbContext()
    {
        //this constructor goes only with the migrations
    }

    public ApplicationEFOracleDbContext(DbContextOptions<ApplicationEFOracleDbContext> options) : base(options)
    {
        //this constructor is being used by the repos to initialize the context with various options including multitenancy
    }

    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        base.OnModelCreating(modelBuilder);
    }
}

Tenant Master Context:

public class FlexEFTenantMasterOracleContext : ApplicationTenantEFDbContext
{
    public FlexEFTenantMasterOracleContext() : base()
    {
    }

    public FlexEFTenantMasterOracleContext(DbContextOptions options): base(options)
    {
    }
}

Characteristics:

  • Enterprise Grade: Full-featured enterprise database

  • ACID Compliance: Complete transactional support

  • High Performance: Optimized for complex enterprise workloads

  • Advanced Features: Partitioning, advanced analytics, machine learning

  • Scalability: Handles large-scale enterprise applications

  • Security: Advanced security features and compliance

Configuration Requirements:

{
  "FlexBase": {
    "AppDbConnection": "Data Source=YourServer:1521/YourServiceName;User Id=YourUser;Password=YourPassword;",
    "AppReadDbConnection": "Data Source=YourServer:1521/YourReadServiceName;User Id=YourUser;Password=YourPassword;"
  }
}

Oracle-Specific Features:

  • Partitioning: Advanced table partitioning support

  • JSON Support: Native JSON data type support

  • Spatial Data: Geographic and spatial data support

  • Machine Learning: Built-in machine learning algorithms

  • Advanced Analytics: OLAP and data warehousing features

  • Real Application Clusters (RAC): High availability and scalability

Core EF Configuration

ApplicationEFDbContext.cs - Single Tenant Context

public class ApplicationEFDbContext : FlexEFDbContext
{
    public ApplicationEFDbContext()
    {
        //this constructor goes only with the migrations
    }

    public ApplicationEFDbContext(DbContextOptions options) : base(options)
    {
        //this constructor is being used by the repos to initialize the context with various options including multitenancy

        //Uncomment below code to enable audit trail for your CUD transactions
        //this.EnableAuditTrail();
    }

    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        base.OnModelCreating(modelBuilder);

        //Use this for adding indexes or other customizations to tables pertaining to EF implementation
    }

    protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
    {
        ILogger _logger = FlexContainer.ServiceProvider.GetRequiredService<ILogger<ApplicationEFDbContext>>();

        base.OnConfiguring(optionsBuilder);

        optionsBuilder
            .EnableSensitiveDataLogging() 
            .LogTo(message => _logger.LogDebug(message)) //This is to log the EF queries to the logger
            .AddInterceptors(new DefaultCreationTimeInterceptor()); //This is to set the creation time for the entities
    }
}

Key Features:

  • Migration Support: Constructor for EF migrations

  • Repository Integration: Constructor for repository initialization

  • Audit Trail: Optional audit trail for CUD operations

  • Query Logging: Automatic query logging to logger

  • Creation Time: Automatic creation time setting for entities

ApplicationTenantEFDbContext.cs - Multi-Tenant Context

public class ApplicationTenantEFDbContext : FlexEFTenantContext<FlexTenantBridge>
{
    public ApplicationTenantEFDbContext() : base()
    {
    }

    public ApplicationTenantEFDbContext(DbContextOptions options) :base(options)
    {
    }
}

Key Features:

  • Tenant Awareness: Built-in tenant context support

  • Tenant Isolation: Automatic tenant-based data filtering

  • Flexible Configuration: Supports various tenant models

Connection Providers

Single Tenant Connection Providers

AppSettingsWriteDbConnectionProvider.cs

public class AppSettingsWriteDbConnectionProvider : FlexAppSettingsWriteDbConnectionProvider<FlexAppContextBridge>, IWriteDbConnectionProviderBridge
{
    public AppSettingsWriteDbConnectionProvider(IConfiguration configuration) : base(configuration)
    {
    }
}

AppSettingsReadDbConnectionProvider.cs

public class AppSettingsReadDbConnectionProvider : FlexAppSettingsReadDbConnectionProvider<FlexAppContextBridge>, IReadDbConnectionProviderBridge
{
    public AppSettingsReadDbConnectionProvider(IConfiguration configuration) : base(configuration)
    {
    }
}

Characteristics:

  • Configuration-Based: Reads connection strings from appsettings

  • Static: Same connection for all operations

  • Simple: Easy to configure and maintain

Multi-Tenant Connection Providers

NativeWriteDbTenantConnectionProvider.cs

public class NativeWriteDbTenantConnectionProvider : FlexNativeWriteDbTenantConnectionProvider<FlexTenantBridge, FlexAppContextBridge>, IWriteDbConnectionProviderBridge
{
    public NativeWriteDbTenantConnectionProvider(IFlexNativeHostTenantProviderBridge flexTenantProvider) : base(flexTenantProvider)
    {
    }
}

NativeReadDbTenantConnectionProvider.cs

public class NativeReadDbTenantConnectionProvider : FlexNativeReadDbTenantConnectionProvider<FlexTenantBridge, FlexAppContextBridge>, IReadDbConnectionProviderBridge
{
    public NativeReadDbTenantConnectionProvider(IFlexNativeHostTenantProviderBridge flexTenantProvider) : base(flexTenantProvider)
    {
    }
}

Characteristics:

  • Dynamic: Connection determined at runtime based on tenant

  • Tenant-Aware: Automatically switches between tenant databases

  • Flexible: Supports different database types per tenant

Repository Implementations

SQL Server Repositories

FlexWriteDbRepositoryEFSQLServer.cs

  • Write Operations: Handles all database write operations

  • SQL Server Specific: Optimized for SQL Server features

  • Transaction Support: Full transactional support

FlexReadDbRepositoryEFSQLServer.cs

  • Read Operations: Handles all database read operations

  • Query Optimization: Optimized for read performance

  • Connection Pooling: Efficient connection management

FlexTenantMasterRepositoryEFSQLServer.cs

  • Tenant Management: Handles tenant-specific operations

  • Master Data: Manages tenant master data

  • Tenant Resolution: Resolves tenant information

MySQL Repositories

FlexWriteDbRepositoryEFMySql.cs

  • MySQL Optimized: Optimized for MySQL features

  • Performance Tuned: Tuned for MySQL performance characteristics

  • MySQL Specific: Leverages MySQL-specific features

FlexReadDbRepositoryEFMySql.cs

  • Read Optimization: Optimized for MySQL read operations

  • Query Tuning: Tuned for MySQL query patterns

  • Connection Management: Efficient MySQL connection handling

PostgreSQL Repositories

FlexWriteDbRepositoryEFPostgreSql.cs

  • PostgreSQL Features: Leverages PostgreSQL advanced features

  • JSON Support: Native JSON and JSONB support

  • Advanced Types: Support for custom data types

FlexReadDbRepositoryEFPostgreSql.cs

  • Query Optimization: Optimized for PostgreSQL query patterns

  • Index Support: Advanced indexing support

  • Performance Tuning: Tuned for PostgreSQL performance

Oracle Repositories

FlexWriteDbRepositoryEFOracle.cs

  • Oracle Optimized: Optimized for Oracle features

  • Performance Tuned: Tuned for Oracle performance characteristics

  • Oracle Specific: Leverages Oracle-specific features

  • Partitioning Support: Advanced partitioning capabilities

FlexReadDbRepositoryEFOracle.cs

  • Read Optimization: Optimized for Oracle read operations

  • Query Tuning: Tuned for Oracle query patterns

  • Connection Management: Efficient Oracle connection handling

  • Analytics Support: Advanced analytics query support

FlexTenantMasterRepositoryEFOracle.cs

  • Tenant Management: Handles tenant-specific operations

  • Master Data: Manages tenant master data

  • Tenant Resolution: Resolves tenant information

  • Oracle RAC Support: High availability and scalability

Configuration Best Practices

1. Environment-Specific Configuration

// Development
if (env.IsDevelopment())
{
    EnableSingleTenant(services);
}

// Production
else if (env.IsProduction())
{
    EnableMultitenant(services);
}

2. Connection String Management

{
  "FlexBase": {
    "AppDbConnection": "Data Source=YourServer;Initial Catalog=YourDatabase;User ID=YourUser;Password=YourPassword;TrustServerCertificate=True",
    "AppReadDbConnection": "Data Source=YourServer;Initial Catalog=YourReadDatabase;User ID=YourUser;Password=YourPassword;TrustServerCertificate=True",
    "TenantMasterDbConnection": "Data Source=YourServer;Initial Catalog=YourTenantMasterDatabase;User ID=YourUser;Password=YourPassword;TrustServerCertificate=True"
  }
}

3. Database Selection Guidelines

Scenario
Recommended Database
Reason

Enterprise

SQL Server/Oracle

Full features, enterprise support

Cost-Sensitive

MySQL

Open source, no licensing costs

Advanced Features

PostgreSQL

Rich feature set, extensibility

Large Scale Enterprise

Oracle

Advanced analytics, partitioning, RAC

Windows Environment

SQL Server

Native integration

Linux Environment

MySQL/PostgreSQL/Oracle

Cross-platform support

High Availability

Oracle

RAC, advanced clustering

Analytics & ML

Oracle

Built-in ML algorithms, advanced analytics

4. Tenant Model Selection

Scenario
Recommended Model
Reason

High Security

Single Tenant

Complete data isolation

Cost Optimization

Multi-Tenant (FlexBase)

Independent scaling per tenant usage

Rapid Scaling

Multi-Tenant (FlexBase)

Easy tenant onboarding with independent databases

Compliance

Multi-Tenant (FlexBase)

Physical data separation meets regulatory requirements

SaaS Application

Multi-Tenant (FlexBase)

Independent scaling and cost optimization

Enterprise SaaS

Multi-Tenant (FlexBase)

Dedicated resources per tenant with cloud scaling

Multi-Region

Multi-Tenant (FlexBase)

Independent deployment per region

Cloud-Native

Multi-Tenant (FlexBase)

Leverages cloud database auto-scaling

Performance Considerations

1. Connection Pooling

File: YourApplication.Application/Infrastructure/Db/YourApplication.BaseEF/ApplicationEFDbContext.cs

// Configure connection pooling
services.AddDbContext<ApplicationEFDbContext>(options =>
{
    options.UseSqlServer(connectionString, sqlOptions =>
    {
        sqlOptions.EnableRetryOnFailure(maxRetryCount: 3);
        sqlOptions.CommandTimeout(30);
    });
});

Configuration Location: YourApplication.Application/EndPoints/YourApplication.EndPoint.WebAPI/Program.cs

2. Query Optimization

File: YourApplication.Application/Infrastructure/Db/YourApplication.BaseEF/ApplicationEFDbContext.cs

protected override void OnModelCreating(ModelBuilder modelBuilder)
{
    base.OnModelCreating(modelBuilder);

    // Add indexes for performance
    modelBuilder.Entity<Order>()
        .HasIndex(o => o.CustomerId)
        .HasDatabaseName("IX_Orders_CustomerId");

    modelBuilder.Entity<Order>()
        .HasIndex(o => new { o.CustomerId, o.OrderDate })
        .HasDatabaseName("IX_Orders_CustomerId_OrderDate");
}

Alternative Database-Specific Files:

  • SQL Server: YourApplication.Application/Infrastructure/Db/YourApplication.BaseEF.SqlServer/ApplicationEFSqlServerDbContext.cs

  • MySQL: YourApplication.Application/Infrastructure/Db/YourApplication.BaseEF.MySql/ApplicationEFMySqlDbContext.cs

  • PostgreSQL: YourApplication.Application/Infrastructure/Db/YourApplication.BaseEF.PostgreSql/ApplicationEFPostgreSqlDbContext.cs

  • Oracle: YourApplication.Application/Infrastructure/Db/YourApplication.BaseEF.Oracle/ApplicationEFOracleDbContext.cs

3. Read/Write Separation

File: YourApplication.Application/Infrastructure/Db/YourApplication.BaseEF/DbEndPointConfig.cs

// Write database for CUD operations
services.AddTransient<IWriteDbConnectionProviderBridge, AppSettingsWriteDbConnectionProvider>();

// Read database for query operations
services.AddTransient<IReadDbConnectionProviderBridge, AppSettingsReadDbConnectionProvider>();

Connection Provider Files:

  • Write Provider: YourApplication.Application/Infrastructure/Db/YourApplication.BaseEF/ConnectionProviders/AppSettingsWriteDbConnectionProvider.cs

  • Read Provider: YourApplication.Application/Infrastructure/Db/YourApplication.BaseEF/ConnectionProviders/AppSettingsReadDbConnectionProvider.cs

Troubleshooting

Common Issues

  1. Connection String Errors

    • Verify connection strings in configuration

    • Check database server accessibility

    • Validate credentials and permissions

  2. Migration Issues

    • Ensure migration context is properly configured

    • Check database schema compatibility

    • Verify migration history

  3. Tenant Resolution Issues

    • Verify tenant provider configuration

    • Check tenant context setup

    • Validate tenant identification logic

  4. Performance Problems

    • Monitor query execution times

    • Check connection pool utilization

    • Review database indexes

Debugging Tips

  1. Enable Query Logging: Use EnableSensitiveDataLogging() for detailed query information

  2. Monitor Connections: Track connection pool usage and performance

  3. Query Analysis: Use database query analysis tools

  4. Health Checks: Implement database health checks

Key Benefits

  • Multi-Database Support: SQL Server, MySQL, PostgreSQL, Oracle

  • Advanced Multi-Tenant Architecture: Independent databases per tenant with complete data isolation

  • Cloud-Native Scaling: Independent database scaling per tenant usage and cost optimization

  • Enhanced Security: Physical data separation ensures maximum security and confidentiality

  • Read/Write Separation: Optimized for different operation types

  • Connection Management: Efficient connection pooling and management

  • Migration Support: Full EF Core migration support

  • Audit Trail: Optional audit trail for data changes

  • Query Logging: Built-in query logging and monitoring

  • Performance Optimized: Tuned for different database systems

  • Flexible Configuration: Easy to switch between database providers

  • Enterprise Features: Oracle RAC, partitioning, advanced analytics

  • Compliance Ready: Meets strict regulatory requirements (GDPR, HIPAA, SOX)

  • Production Ready: Battle-tested for enterprise applications

This Entity Framework Core configuration system provides a robust, scalable, and maintainable data access layer that adapts to different database systems and tenant models! 🚀

PreviousDefault Bus ConfigurationNextFeatures

Last updated