Data Model and ORM Mapping

Data modeling with Entity Framework Core, Repository pattern, and multi-database architecture.

Overview

The APS data architecture relies on Entity Framework Core as the primary ORM (Object-Relational Mapping), enabling performant and type-safe object-relational mapping between C# code and databases.

Technologies Used

• Entity Framework Core: Microsoft's modern ORM
• SQL Server: Primary relational database
• Oracle: Optional support for Oracle Database
• Code First Migrations: Database schema management
• LINQ: Strongly typed queries

Database Contexts

The application uses two main EF Core contexts, separating directory data from business data.

DirectoryContext

Namespace: Avanteam.Directory.Repository.EFCore
File: /Directory/Repository/EFCore/Src/DirectoryContext.cs

The DirectoryContext manages directory data: users, resources, roles, delegations.

public class DirectoryContext : DbContext, IRawDataSourceProvider
{
    public DirectoryContext(
        DbContextOptions<DirectoryContext> options)
        : base(options)
    {
        this.RawDbDataSource = options
            .FindExtension<RawDbDataSourceOptionsExtension>()
            ?.CreateRawDbDataSource(this);
    }

    public IRawDbDataSource? RawDbDataSource { get; }

    // DbSets
    public DbSet<DirectoryResource> DirectoryResources => Set<DirectoryResource>();
    public DbSet<DirectoryRelation> DirectoryRelations => Set<DirectoryRelation>();
    public DbSet<Delegation> Delegations => Set<Delegation>();
    public DbSet<IdentityProvider> IdentityProviders => Set<IdentityProvider>();
    // ... other entities
}

Main Entities:

• DirectoryResource: Directory resources (users, services, etc.)
• DirectoryResourceType: Resource types
• DirectoryRelation: Relations between resources
• Delegation: Delegations between users
• IdentityProvider: Identity providers (SAML, OAuth)
• AuthnToken: Authentication tokens

Database: Common APSDirectory database shared between applications.

ApplicationContext

Namespace: Avanteam.Application.Repository.EFCore
File: /Application/Repository/EFCore/Src/ApplicationContext.cs

The ApplicationContext manages business data: documents, forms, workflows, views.

public class ApplicationContext : DbContext, IRawDataSourceProvider
{
    public ApplicationContext(
        DbContextOptions<ApplicationContext> options)
        : base(options)
    {
        this.RawDbDataSource = options
            .FindExtension<RawDbDataSourceOptionsExtension>()
            ?.CreateRawDbDataSource(this);
    }

    public IRawDbDataSource? RawDbDataSource { get; }

    // DbSets - Documents
    public DbSet<Document> Documents => Set<Document>();
    public DbSet<DocumentObject> DocumentObjects => Set<DocumentObject>();
    public DbSet<DocumentBlob> DocumentBlobs => Set<DocumentBlob>();
    public DbSet<DocumentAccessRight> DocumentAccessRights => Set<DocumentAccessRight>();
    
    // DbSets - Forms
    public DbSet<Form> Forms => Set<Form>();
    public DbSet<FormField> FormFields => Set<FormField>();
    public DbSet<FieldDefinition> FieldDefinitions => Set<FieldDefinition>();
    
    // DbSets - Views
    public DbSet<View> Views => Set<View>();
    public DbSet<PersonalizedView> PersonalizedViews => Set<PersonalizedView>();
    
    // DbSets - Agents
    public DbSet<AgentSchedule> AgentSchedules => Set<AgentSchedule>();
    
    // ... other entities (100+ DbSets)
}

Main Entities:

• Document: Business documents
• Form: Form definitions
• View: Views and queries
• AgentSchedule: Agent schedules
• ProcessInstance: Workflow instances
• ApplicationLog: Application logs
• ArchiveProfile: Archive profiles

Database: One database per client application (multi-tenant).

DbContext Configuration

Registration in DI Container

Contexts are registered in the dependency injection container with a Scoped lifetime (one instance per HTTP request).

DirectoryContext:

services.AddDbContext<DirectoryContext>(ConfigureDirectoryContext);

private static void ConfigureDirectoryContext(
    IServiceProvider serviceProvider,
    DbContextOptionsBuilder options)
{
    var dbConnection = serviceProvider
        .GetRequiredService<IWebSiteDescriptor>()
        .DirectoryDbConnectionDescriptor;

    options.WithContext<DirectoryContext>()
        .UseConnectionStringAndStandardFeatures(dbConnection);
}

ApplicationContext:

services.AddDbContext<ApplicationContext>(ConfigureApplicationContext);

private static void ConfigureApplicationContext(
    IServiceProvider serviceProvider,
    DbContextOptionsBuilder options)
{
    var profile = serviceProvider.GetRequiredService<ApplicationProfile>();
    
    options.WithContext<ApplicationContext>()
        .UseConnectionStringAndStandardFeatures(
            profile.GetDbConnectionDescriptor("APSApp"));
}

Database Providers

The architecture supports two database providers via extension methods:

SQL Server:

options.UseSqlServerDirectoryContext(connectionString);
options.UseSqlServerApplicationContext(connectionString);

Oracle:

options.UseOracleDirectoryContext(connectionString);
options.UseOracleApplicationContext(connectionString);

Extension methods are defined in the projects:

• Directory.Repository.EFCore.SqlServer
• Directory.Repository.EFCore.Oracle
• Application.Repository.EFCore.SqlServer
• Application.Repository.EFCore.Oracle

Entities and Configuration

Fluent API Configuration

Entity configuration uses the IEntityTypeConfiguration pattern with automatic configuration:

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

    // Auto-discovery and registration of all configurations
    var assembly = typeof(DirectoryContext).Assembly;
    modelBuilder.ApplyConfigurationsFromAssembly(assembly);

    // Provider-specific configurations (SqlServer vs Oracle)
    var migrationAssembly = this.GetInfrastructure()
        .GetService<IMigrationsAssembly>();
    if (migrationAssembly is not null)
    {
        modelBuilder.ApplyConfigurationsFromAssembly(
            migrationAssembly.Assembly);
    }
}

Entity Configuration Example

public class DirectoryResourceConfiguration 
    : IEntityTypeConfiguration<DirectoryResource>
{
    public void Configure(EntityTypeBuilder<DirectoryResource> builder)
    {
        builder.ToTable("DirectoryResources");
        
        builder.HasKey(e => e.Id);
        
        builder.Property(e => e.Id)
            .HasColumnName("id")
            .HasColumnType("char(36)")
            .IsFixedLength()
            .HasMaxLength(36);
        
        builder.Property(e => e.DisplayName)
            .HasColumnName("cn_name")
            .HasMaxLength(255)
            .IsRequired();
        
        builder.Property(e => e.Reference)
            .HasColumnName("dn_name")
            .HasMaxLength(255)
            .IsRequired();
    }
}

Naming Conventions

• Tables: PascalCase in English (e.g., DirectoryResources, Documents)
• Columns: snake_case in English (e.g., cn_name, creation_date)
• Primary Keys: Column id of type char(36) (GUID)
• Dates: Type datetime2 on SQL Server
• Strings: nvarchar(max) or nvarchar(n) as needed

Migrations

Migration Management

EF Core migrations are used to manage database schema evolution.

Migration Structure:

Directory/Repository/EFCore/
├── SqlServer/Src/Migrations/    # SQL Server migrations
│   ├── Initial.cs
│   ├── AddIdentityProviders.cs
│   └── DirectoryContextModelSnapshot.cs
└── Oracle/Src/Migrations/       # Oracle migrations
    ├── Initial.cs
    └── OracleDirectoryContextModelSnapshot.cs

Creating Migrations

# Migration for SQL Server
dotnet ef migrations add MigrationName `
  --project Application/Repository/EFCore/SqlServer/Src `
  --context ApplicationContext

# Migration for Oracle
dotnet ef migrations add MigrationName `
  --project Application/Repository/EFCore/Oracle/Src `
  --context ApplicationContext

Applying Migrations

Migrations are applied automatically at startup via DatabaseInitializer:

public class DirectoryDatabaseInitializer : 
    DatabaseInitializerBase<DirectoryContext>
{
    public override async Task InitializeAsync(
        CancellationToken cancellationToken = default)
    {
        await base.InitializeAsync(cancellationToken);
        
        // Apply pending migrations
        await Context.Database.MigrateAsync(cancellationToken);
        
        // Seed initial data
        await SeedDataAsync(cancellationToken);
    }
}

Repository Pattern

Repository Abstraction

Data access goes through Stores (repositories) that encapsulate access logic:

public interface IResourceStore
{
    Task<DirectoryResource?> FindByIdAsync(
        string id, 
        CancellationToken cancellationToken);
    
    Task<DirectoryResource?> FindByReferenceAsync(
        string reference, 
        CancellationToken cancellationToken);
    
    IQueryable<DirectoryResource> GetAll();
    
    Task CreateAsync(
        DirectoryResource resource, 
        CancellationToken cancellationToken);
    
    Task UpdateAsync(
        DirectoryResource resource, 
        CancellationToken cancellationToken);
    
    Task DeleteAsync(
        DirectoryResource resource, 
        CancellationToken cancellationToken);
}

Store Implementation

public class ResourceStore : IResourceStore
{
    private readonly DirectoryContext _context;
    
    public ResourceStore(DirectoryContext context)
    {
        _context = context;
    }
    
    public Task<DirectoryResource?> FindByIdAsync(
        string id, 
        CancellationToken cancellationToken)
    {
        return _context.DirectoryResources
            .FirstOrDefaultAsync(r => r.Id == id, cancellationToken);
    }
    
    public IQueryable<DirectoryResource> GetAll()
    {
        return _context.DirectoryResources.AsQueryable();
    }
    
    public async Task CreateAsync(
        DirectoryResource resource, 
        CancellationToken cancellationToken)
    {
        _context.DirectoryResources.Add(resource);
        await _context.SaveChangesAsync(cancellationToken);
    }
}

Managers (Business Layer)

Managers use Stores and add business logic:

public class ResourceManager : IResourceManager
{
    private readonly IResourceStore _store;
    private readonly ILogger<ResourceManager> _logger;
    
    public ResourceManager(
        IResourceStore store, 
        ILogger<ResourceManager> logger)
    {
        _store = store;
        _logger = logger;
    }
    
    public async Task<DirectoryResource> CreateUserAsync(
        string login, 
        string displayName)
    {
        // Business validation
        if (string.IsNullOrWhiteSpace(login))
            throw new ArgumentException("Login required");
        
        // Uniqueness check
        var existing = await _store.FindByReferenceAsync(login);
        if (existing != null)
            throw new InvalidOperationException("User already exists");
        
        // Creation
        var user = new DirectoryResource
        {
            Id = Guid.NewGuid().ToString(),
            Reference = login,
            DisplayName = displayName,
            Type = "User"
        };
        
        await _store.CreateAsync(user);
        
        _logger.LogInformation("User {Login} created", login);
        
        return user;
    }
}

Multi-Tenant Model

Database Isolation

Each client application has its own Application database, providing:

• Complete business data isolation
• Optimal performance (no tenant filtering)
• Enhanced security
• Independent backup and restore

ApplicationProfile

The ApplicationProfile contains connection metadata for each tenant:

public class ApplicationProfile
{
    public string ApplicationName { get; set; }
    public string ConnectionString { get; set; }
    public ConnectionType ConnectionType { get; set; }
    
    public DbConnectionDescriptor GetDbConnectionDescriptor(string name)
    {
        return new DbConnectionDescriptor
        {
            ConnectionString = ConnectionString,
            ConnectionType = ConnectionType
        };
    }
}

Context Resolution

For each request, the correct context is resolved based on the target application:

// The ApplicationProfile is injected and automatically resolved
// based on the URL or request context
services.AddScoped<ApplicationProfile>(sp =>
{
    var httpContext = sp.GetService<IHttpContextAccessor>()?.HttpContext;
    var appName = httpContext?.Request.Host.Host;
    
    return profileRepository.GetProfileByName(appName);
});

Queries and Performance

LINQ to Entities

Queries use LINQ for a type-safe approach:

public async Task<List<Document>> GetRecentDocumentsAsync(
    string userId, 
    int count)
{
    return await _context.Documents
        .Where(d => d.CreatedBy == userId)
        .OrderByDescending(d => d.CreatedAt)
        .Take(count)
        .Include(d => d.Form)
        .ToListAsync();
}

Raw SQL Queries

For complex queries, using raw SQL:

public async Task<List<DocumentStats>> GetDocumentStatsAsync()
{
    return await _context.Database
        .SqlQueryRaw<DocumentStats>(
            @"SELECT FormId, COUNT(*) as Count, 
                     MAX(CreatedAt) as LastCreated
              FROM Documents
              GROUP BY FormId")
        .ToListAsync();
}

IRawDataSourceProvider

DbContexts implement IRawDataSourceProvider for direct access:

public interface IRawDataSourceProvider
{
    IRawDbDataSource? RawDbDataSource { get; }
}

Enables direct ADO.NET queries for maximum performance:

var dataSource = _context.RawDbDataSource;
using var connection = await dataSource.OpenConnectionAsync();
using var command = connection.CreateCommand();
command.CommandText = "SELECT * FROM Documents WHERE ...";
// ...

Mapping and AutoMapper

AutoMapper Configuration

AutoMapper is used to map entities ↔ DTOs:

public class DirectoryMapperProfile : Profile
{
    public DirectoryMapperProfile()
    {
        CreateMap<DirectoryResource, ResourceDto>()
            .ForMember(d => d.Name, 
                opt => opt.MapFrom(s => s.DisplayName));
        
        CreateMap<ResourceDto, DirectoryResource>()
            .ForMember(d => d.DisplayName, 
                opt => opt.MapFrom(s => s.Name));
    }
}

Registration

services.AddAutoMapper(typeof(DirectoryMapperProfile));

Transaction Management

Implicit Transactions

SaveChangesAsync() automatically manages transactions:

public async Task TransferDocumentAsync(string docId, string newOwnerId)
{
    var doc = await _context.Documents.FindAsync(docId);
    doc.OwnerId = newOwnerId;
    
    var log = new ApplicationLog { Action = "Transfer", DocumentId = docId };
    _context.ApplicationLogs.Add(log);
    
    // Implicit transaction around SaveChanges
    await _context.SaveChangesAsync();
}

Explicit Transactions

For complex multi-context transactions:

using var transaction = await _context.Database.BeginTransactionAsync();
try
{
    // Operations...
    await _context.SaveChangesAsync();
    
    // Other operations...
    await _context.SaveChangesAsync();
    
    await transaction.CommitAsync();
}
catch
{
    await transaction.RollbackAsync();
    throw;
}

Unit Testing

In-Memory Contexts

For testing, using SQLite in-memory:

public class FakeDirectoryDbContext : DirectoryContext
{
    public FakeDirectoryDbContext()
        : base(BuildOptions())
    {
        Database.EnsureCreated();
    }
    
    private static DbContextOptions<DirectoryContext> BuildOptions()
    {
        var connection = new SqliteConnection("Filename=:memory:");
        connection.Open();
        
        return new DbContextOptionsBuilder<DirectoryContext>()
            .UseSqlite(connection)
            .Options;
    }
}

Usage in Tests

[Test]
public async Task Can_Create_User()
{
    // Arrange
    using var context = new FakeDirectoryDbContext();
    var store = new ResourceStore(context);
    var manager = new ResourceManager(store, logger);
    
    // Act
    var user = await manager.CreateUserAsync("jdoe", "John Doe");
    
    // Assert
    Assert.That(user.Reference, Is.EqualTo("jdoe"));
}

Best Practices

Asynchronous Queries

Always use asynchronous methods:

// ✅ Good
await _context.Documents.ToListAsync();

// ❌ Bad
_context.Documents.ToList();

Explicit Includes

Explicitly load necessary relations:

var doc = await _context.Documents
    .Include(d => d.Form)
    .Include(d => d.DocumentObjects)
    .FirstAsync(d => d.Id == id);

Projections

Use Select to load only necessary data:

var summaries = await _context.Documents
    .Where(d => d.FormId == formId)
    .Select(d => new { d.Id, d.Reference, d.CreatedAt })
    .ToListAsync();

Disable Tracking

For read-only queries:

var docs = await _context.Documents
    .AsNoTracking()
    .ToListAsync();

References

• .NET Framework 4.8 Architecture
• APS Modular Structure