# 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:

```xml
<!-- 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:

```csharp
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:**

```csharp
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:**

```csharp
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 🚀**

```csharp
// 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 🔒**

```csharp
// 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 🗄️**

```csharp
// 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 ☁️**

```csharp
// 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:**

```csharp
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:**

```csharp
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:**

```json
{
  "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:**

```json
{
  "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:**

```json
{
  "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:**

```csharp
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:**

```csharp
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:**

```json
{
  "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**

```csharp
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**

```csharp
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**

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

#### **AppSettingsReadDbConnectionProvider.cs**

```csharp
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**

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

#### **NativeReadDbTenantConnectionProvider.cs**

```csharp
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**

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

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

### **2. Connection String Management**

```json
{
  "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`

```csharp
// 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`

```csharp
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`

```csharp
// 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!** 🚀


---

# Agent Instructions: Querying This Documentation

If you need additional information that is not directly available in this page, you can query the documentation dynamically by asking a question.

Perform an HTTP GET request on the current page URL with the `ask` query parameter:

```
GET https://docs.flexbase.in/solution-structure/getting-started/application/efcore-configuration.md?ask=<question>
```

The question should be specific, self-contained, and written in natural language.
The response will contain a direct answer to the question and relevant excerpts and sources from the documentation.

Use this mechanism when the answer is not explicitly present in the current page, you need clarification or additional context, or you want to retrieve related documentation sections.