Ticomix.EFCore

Purpose Statement

A comprehensive .NET Entity Framework Core extension library that provides enhanced database functionality including automatic audit fields, advanced logging, SQL utilities, and database context management. Built to work with both .NET 6.0 and .NET 8.0 with Entity Framework Core for enterprise applications requiring robust data access patterns and comprehensive logging capabilities.

Core Systems

1. Logging System

Comprehensive database-backed logging with support for error tracking, batch operations, and integration with Microsoft.Extensions.Logging.

2. Audit Fields System

Automatically manages audit fields (AddDate, AddUser, ChangeDate, ChangeUser) for all entities implementing the IAudit or IAuditUtc interfaces.

3. SQL Utilities

Advanced SQL execution utilities for raw queries, stored procedures, and data mapping with async support.

4. Database Schema Management

String length attributes for easy configuration of database string lengths.
Easy overrides of default column types for specific C# data types.

5. Startup and One-Time Scripts

Automated running of sql scripts either every time the application starts or only once.

Core Classes and Interfaces

Database Context

• IDbContext: Core database context interface with user tracking and enhanced functionality
• IErrorLogDbContext: Interface for error logging database context
• DbContextSetup: Automated model configuration for entity properties
• GenericDbSet: Enhanced DbSet with generic operations and key management

Audit System

• AuditHelper: Automatically populates audit fields on entity changes

Logging System

• Logger<TKey>: Default ILogger implementation - Database-backed logger with batch support and error tracking
• ILogger: Interface for logging operations
• LogEntryErrorLevel: Enumeration for log levels (Information, Warning, Error, Debug, etc.)

SQL Utilities

• SqlExtensionMethods: Extension methods for raw SQL execution and data mapping
• SqlSchemaHelper: Database schema inspection and management utilities
• SqlStream: Stream-based SQL data processing
• KeyHelper: Primary key management and generation utilities

Data Context Interfaces - Types referenced by other Core Modules

• IAccountingDbContext: Accounting system database context
• IAttachmentsDbContext: File attachment management context
• ICompanyDbContext: Company data management context
• IEmailNoteDbContext: Email and note management context
• IPrintingDbContext: Printing system context
• IReportingDbContext: Reporting system context
• IWebJobDbContext: Background job management context
• IWorkflowDbContext: Workflow management context
• ICodeMaintenanceDbContext: Code maintenance context

Security and Validation

• Reflection: Reflection utilities for dynamic property access respecting ACL
• ExtensionMethods: Security-related extension methods for setting entity state by ACL

Installation Instructions

Prerequisites

Ensure your application targets:

• .NET 6.0 or .NET 8.0
• Entity Framework Core 6.0+ or 8.0+
• SQL Server 2016+

Configuration

1. Error Logging Setup

Model and DbContext Setup

ErrorLog.cs

public class ErrorLog : IAudit, IErrorLog<int> 
{ 
    // Columns omitted for brevity...
}

ApplicationDbContext.cs

public class ApplicationDbContext : DbContext, IDbContext
{
    // Rest of class omitted for brevity...

    // Error logging support - Required DbSet
    public DbSet<ErrorLog> ErrorLog { get; set; }

    // ...
}

ApplicationDbContextErrorLog.cs

public partial class ApplicationDbContext : IErrorLogDbContext<int>
{
    // Implement IErrorLogDbContext interface for Logger compatibility
    GenericDbSet<IErrorLog<int>> IErrorLogDbContext<int>.ErrorLog => 
        new GenericDbSet<IErrorLog<int>>(this, this.ErrorLog);
}

Startup.cs - Dependency Injection Setup

// ConfigureServices method
services.AddScoped<IErrorLogDbContext<int>>(provider => 
    provider.GetRequiredService<ApplicationDbContext>());

services.AddScoped<ILogger, Ticomix.Common.Helpers.Logger<int>>();

Usage Examples

public class CustomerService
{
    private readonly ILogger logger;
    private readonly ApplicationDbContext db;

    public CustomerService(ILogger logger, ApplicationDbContext db)
    {
        this.logger = logger;
        this.db = db;
    }

    public async Task<Customer> CreateCustomerAsync(Customer customer)
    {
        try
        {
            db.Set<Customer>().Add(customer);
            await db.SaveChangesAsync();
            
            logger.LogMessage(LogEntryErrorLevel.Information, 
                $"Customer created: {customer.Name}");
            
            return customer;
        }
        catch (Exception ex)
        {
            logger.LogError(ex);
            throw;
        }
    }

    // Batch logging for related operations
    public async Task ProcessCustomerBatch(List<Customer> customers)
    {
        logger.Batch = Guid.NewGuid();
        
        foreach (var customer in customers)
        {
            try
            {
                await CreateCustomerAsync(customer);
                logger.LogMessageBatch(LogEntryErrorLevel.Information, 
                    $"Processed customer: {customer.Name}");
            }
            catch (Exception ex)
            {
                logger.LogErrorBatch(ex);
            }
        }
    }
}

2. Audit Fields Implementation

Entity with Audit Fields

public class Customer : IAudit
{
    // Columns omitted for brevity...

    // Audit fields
    public DateTime? AddDate { get; set; }
    public string? AddUser { get; set; }
    public DateTime? ChangeDate { get; set; }
    public string? ChangeUser { get; set; }
}

Audit Configuration

When used in the SaveChanges or SaveChangesAsync methods, the AuditHelper automatically:

• Sets AddDate and AddUser on entity creation
• Updates ChangeDate and ChangeUser on entity modification
• Prevents modification of AddDate and AddUser after creation
• Supports both DateTime and DateTimeOffset types

ApplicationDbContext.cs - Audit Helper usage

public class ApplicationDbContext : DbContext, IDbContext
{
    // Rest of class omitted for brevity...
    public bool SaveChangesAuditEnabled { get; set; } = true;

    public override int SaveChanges()
    {
        try
        {
            if (SaveChangesAuditEnabled)
            {
                new AuditHelper().SaveChangesAudit(this, UserName);
            }

            return base.SaveChanges();
        }
        catch (DbUpdateException ex)
        {
            if (ex.InnerException != null)
            {
                string message = ex.Message + "\r\n" + ex.InnerException.Message;
                if (ex.InnerException.InnerException != null)
                {
                    message += "\r\n" + ex.InnerException.InnerException.Message;
                }

                foreach (var entry in this.ChangeTracker.Entries().Where(a => a.State == EntityState.Added || a.State == EntityState.Modified))
                {
                    string values = "\r\n";
                    foreach (var prop in entry.CurrentValues.Properties.Select(a => a.Name))
                    {
                        values += "\r\n" + prop + ": " + entry.CurrentValues[prop];
                    }
                    message += values;
                }
                throw new DbUpdateException(message, ex);
            }
            else
            {
                throw;
            }
        }
    }

    public override Task<int> SaveChangesAsync(CancellationToken cancellationToken = default(CancellationToken))
    {
        try
        {
            if (SaveChangesAuditEnabled)
            {
                new AuditHelper().SaveChangesAudit(this, UserName);
            }

            return base.SaveChangesAsync(cancellationToken);
        }
        catch (DbUpdateException ex)
        {
            if (ex.InnerException != null)
            {
                string message = ex.Message + "\r\n" + ex.InnerException.Message;
                if (ex.InnerException.InnerException != null)
                {
                    message += "\r\n" + ex.InnerException.InnerException.Message;
                }

                foreach (var entry in this.ChangeTracker.Entries().Where(a => a.State == EntityState.Added || a.State == EntityState.Modified))
                {
                    string values = "\r\n";
                    foreach (var prop in entry.CurrentValues.Properties.Select(a => a.Name))
                    {
                        values += "\r\n" + prop + ": " + entry.CurrentValues[prop];
                    }
                    message += values;
                }
                throw new DbUpdateException(message, ex);
            }
            else
            {
                throw;
            }
        }
    }
}

3. SQL Utilities Usage

Raw SQL Queries

public class ReportService
{
    private readonly IDbContext db;

    public ReportService(IDbContext db)
    {
        this.db = db;
    }

    // Execute scalar query
    public async Task<int> GetCustomerCountAsync()
    {
        return await db.ExecuteScalarAsync<int>(
            "SELECT COUNT(*) FROM Customers WHERE Active = 1");
    }

    // Execute mapped query
    public async Task<List<CustomerSummary>> GetCustomerSummariesAsync()
    {
        var results = new List<CustomerSummary>();
        await foreach (var item in db.SQLexecAsync<CustomerSummary>(
            "SELECT Id, Name, Email, LastOrderDate FROM vw_CustomerSummary"))
        {
            results.Add(item);
        }
        return results;
    }

    // Parameterized queries
    public List<Customer> GetCustomersByState(string state)
    {
        return db.SQLexec<Customer>(
            "SELECT * FROM Customers WHERE State = @State",
            new Dictionary<string, object?> { { "@State", state } });
    }

    // Custom mapping
    public async Task<List<CustomResult>> GetCustomDataAsync()
    {
        var results = new List<CustomResult>();
        await foreach (var item in db.RawSqlQueryAsync(
            "SELECT Col1, Col2, Col3 FROM CustomView",
            reader => new CustomResult
            {
                Value1 = reader.GetString("Col1"),
                Value2 = reader.GetInt32("Col2"),
                Value3 = reader.GetStringNullable(2) // Nullable string helper
            }))
        {
            results.Add(item);
        }
        return results;
    }
}

4. Database Schema Configuration

String Length Attributes

public class Product
{
    public int Id { get; set; }
    
    [StringLengthSmall] // Uses StringLengths.Small
    public string Code { get; set; }
    
    [StringLengthMedium] // Uses StringLengths.Medium  
    public string Name { get; set; }
    
    [StringLengthMax] // Uses maximum length (nvarchar(max))
    public string Description { get; set; }
    
    // Default length applied automatically
    public string Category { get; set; }
}

5. One-Time Scripts and Startup Scripts

The package supports exexution of one-time scripts and startup scripts:

ApplicationDbContext.cs - One-Time Script History

public class ApplicationDbContext : DbContext, IOneTimeScriptHistoryDbContext
{
    public DbSet<OneTimeScriptHistory> OneTimeScriptHistory { get; set; }
    
    // Implementation automatically tracks script execution
}

Startup.cs - add the following lines after migrations are applied

if (!env.IsDevelopment())
{
    SqlSchemaHelper.RunStartupScripts(context);
}
SqlSchemaHelper.RunOneTimeScripts(context);


var logger = serviceScope.ServiceProvider.GetService<ILogger>();
logger.Source = "Startup";
logger.LogMessage(LogEntryErrorLevel.Information, "Database updated");

Advanced Features

Custom Schema Setup

public class CustomDbContextSetup : DbContextSetup
{
    public CustomDbContextSetup()
    {
        // Set default string length (default is StringLengths.Large)
        DefaultStringLength = 500;
    }

    // Override property filters for custom behavior
    public override bool StringPropertyFilter(ModelBuilderProperty p)
    {
        // Custom logic for string property configuration
        return base.StringPropertyFilter(p) && !p.MutableProperty.Name.EndsWith("Code");
    }

    // Custom property building
    public override void BuildStringProperty(ModelBuilderProperty property)
    {
        if (property.MutableProperty.Name.EndsWith("Code"))
        {
            property.PropertyBuilder.HasMaxLength(20).IsUnicode(false);
        }
        else
        {
            base.BuildStringProperty(property);
        }
    }
}

ApplicationDbContext.cs - OnModelCreating

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

    var dbContextSetup = new DbContextSetup();
    dbContextSetup.OnModelCreating(modelBuilder);
}

Schema Inspection

public class SchemaService
{
    private readonly IDbContext db;

    public SchemaService(IDbContext db)
    {
        this.db = db;
    }

    public async Task<List<string>> GetTableColumnsAsync(string tableName)
    {
        var columns = await db.SQLexecAsync<SysColumn>(
            "SELECT name FROM sys.columns WHERE object_id = OBJECT_ID(@TableName)",
            new Dictionary<string, object?> { { "@TableName", tableName } });

        return await columns.Select(c => c.name).ToListAsync();
    }
}

Exception Handling

try
{
    await db.SaveChangesAsync();
}
catch (Exception ex)
{
    var parser = new EfCoreExceptionParser();
    var friendlyMessage = parser.GetUserFriendlyMessage(ex);
    
    logger.LogError(ex);
    return BadRequest(friendlyMessage);
}