SiLA2.Database.SQL 10.2.2

SiLA2.Database.SQL

Entity Framework Core SQL Database Persistence for SiLA2 Servers

NuGet Package	SiLA2.Database.SQL on NuGet.org
Repository	https://gitlab.com/SiLA2/sila_csharp
SiLA Standard	https://sila-standard.com
License	MIT

Overview

SiLA2.Database.SQL is an optional module for the sila_csharp implementation that provides Entity Framework Core-based SQL database persistence for SiLA2 servers. It implements the Repository pattern with automatic transaction management, enabling feature implementations to store and retrieve data using a clean, testable abstraction layer.

Key Value Proposition

This module bridges the gap between SiLA2's gRPC-based laboratory automation protocol and enterprise-grade relational database systems. It provides:

• Repository Pattern: Clean separation between business logic and data access
• GUID-based Entities: Standardized primary key strategy across all tables
• Transaction Management: Automatic transaction wrapping for all write operations
• Provider-Agnostic: Works with SQL Server, PostgreSQL, SQLite, and any EF Core provider
• Testability: Interface-based design enables easy unit testing and mocking
• Type Safety: Strongly-typed LINQ queries instead of raw SQL

When to Use This Module

Use Case	Use SiLA2.Database.SQL	Use SiLA2.Database.NoSQL
Complex relational data with foreign keys	✅ Yes	❌ No
Multi-table joins and aggregations	✅ Yes	⚠️ Limited
ACID transaction requirements	✅ Yes	⚠️ Varies by provider
Simple document storage	⚠️ Possible but overkill	✅ Yes
Schema migrations and versioning	✅ Yes (EF Migrations)	❌ No
Enterprise backup/recovery	✅ Yes	⚠️ Provider-dependent
Edge devices with minimal dependencies	⚠️ Requires SQL engine	✅ Yes (LiteDB)

Choose SQL when:

• Your feature data has complex relationships (e.g., experimental protocols with multiple steps)
• You need ACID guarantees for critical laboratory data
• You want to leverage existing SQL Server/PostgreSQL infrastructure
• Reporting and analytics are important

Choose NoSQL when:

• You're storing simple documents (e.g., AnIML experimental data)
• You need embedded database on edge devices
• Schema flexibility is more important than joins

Installation

Install via NuGet Package Manager:

dotnet add package SiLA2.Database.SQL

Or via Package Manager Console:

Install-Package SiLA2.Database.SQL

Requirements

• .NET 10.0+
• Microsoft.EntityFrameworkCore 10.0.2+ (automatically installed)
• Database Provider Package (choose one):
  • SQL Server: Microsoft.EntityFrameworkCore.SqlServer
  • PostgreSQL: Npgsql.EntityFrameworkCore.PostgreSQL
  • SQLite: Microsoft.EntityFrameworkCore.Sqlite

Quick Start

Get up and running with SQL persistence in 5 minutes.

1. Define Your Entity Model

using SiLA2.Database.SQL.Domain;

namespace MyFeature.Database
{
    public class ExperimentResult : BaseEntity
    {
        public DateTime Timestamp { get; set; }
        public double Temperature { get; set; }
        public string SampleId { get; set; }
        public bool IsValid { get; set; }
    }
}

2. Create a DbContext

using Microsoft.EntityFrameworkCore;
using SiLA2.Database.SQL;
using SiLA2.Database.SQL.Domain;

namespace MyFeature.Database
{
    // Define interface for dependency injection
    public interface IMyFeatureDbContext : IDbContext { }

    public class MyFeatureDbContext : DbContext, IMyFeatureDbContext
    {
        public MyFeatureDbContext(DbContextOptions<MyFeatureDbContext> options)
            : base(options)
        {
            Database.EnsureCreated(); // Creates database if it doesn't exist
        }

        // Implement IDbContext
        public IEnumerable<Type> DbMappingTypes => new[] { typeof(ExperimentResult) };

        public new DbSet<TEntity> Set<TEntity>() where TEntity : BaseEntity
        {
            return base.Set<TEntity>();
        }

        protected override void OnModelCreating(ModelBuilder modelBuilder)
        {
            // Configure entity mappings
            modelBuilder.Entity<ExperimentResult>(entity =>
            {
                entity.HasKey(e => e.Id);
                entity.Property(e => e.SampleId).HasMaxLength(100).IsRequired();
            });

            base.OnModelCreating(modelBuilder);
        }
    }
}

3. Register in Dependency Injection

// Program.cs
using Microsoft.EntityFrameworkCore;

var builder = WebApplication.CreateBuilder(args);

// Register DbContext with SQLite (for development)
builder.Services.AddDbContext<IMyFeatureDbContext, MyFeatureDbContext>(options =>
    options.UseSqlite("Data Source=myfeature.db"));

// Register repository
builder.Services.AddScoped<IRepository<ExperimentResult>, Repository<ExperimentResult>>();

var app = builder.Build();

4. Use in Your Feature Service

using SiLA2.Database.SQL;

public class MyFeatureService : MyFeature.MyFeatureBase
{
    private readonly IRepository<ExperimentResult> _repository;

    public MyFeatureService(IRepository<ExperimentResult> repository)
    {
        _repository = repository;
    }

    public override async Task<SaveResult_Responses> SaveExperimentResult(
        SaveResult_Parameters request, ServerCallContext context)
    {
        var result = new ExperimentResult
        {
            Id = Guid.NewGuid(),
            Timestamp = DateTime.UtcNow,
            Temperature = request.Temperature.Value,
            SampleId = request.SampleId.Value,
            IsValid = true
        };

        var transactionResult = await _repository.Insert(result);

        if (transactionResult.TransactionResult == TransactionResult.Success)
        {
            return new SaveResult_Responses
            {
                ResultId = new String { Value = result.Id.ToString() }
            };
        }
        else
        {
            ErrorHandling.RaiseSiLAError(
                ErrorHandling.CreateDefinedExecutionError(
                    "DatabaseError", transactionResult.Message));
            return null;
        }
    }

    public override async Task<GetResults_Responses> GetRecentResults(
        GetResults_Parameters request, ServerCallContext context)
    {
        // Use LINQ queries via Table property
        var recentResults = _repository.Table
            .Where(r => r.Timestamp >= DateTime.UtcNow.AddHours(-24))
            .OrderByDescending(r => r.Timestamp)
            .Take(100)
            .ToList();

        var response = new GetResults_Responses();
        foreach (var result in recentResults)
        {
            response.Results.Add(new ResultData
            {
                Id = new String { Value = result.Id.ToString() },
                Temperature = new Real { Value = result.Temperature },
                Timestamp = new Timestamp { Value = Google.Protobuf.WellKnownTypes.Timestamp.FromDateTime(result.Timestamp) }
            });
        }

        return response;
    }
}

Core Concepts

1. Repository Pattern

The module implements the Repository pattern to provide a consistent, testable abstraction over Entity Framework Core. Instead of injecting DbContext directly into services, you inject IRepository<T>, which provides:

Benefits:

• Abstraction: Business logic doesn't depend on EF Core details
• Testability: Easy to mock repositories in unit tests
• Consistency: All features use the same data access patterns
• Transaction Safety: Write operations are automatically wrapped in transactions

Why This Matters for SiLA2: Laboratory automation requires reliable data persistence. The repository pattern ensures that experimental results, calibration data, and device configurations are saved consistently across all features.

2. BaseEntity and GUID Primary Keys

All entities inherit from BaseEntity, which provides a GUID-based primary key:

public abstract class BaseEntity
{
    [Key]
    public Guid Id { get; set; }
}

Why GUIDs Instead of Auto-Increment Integers?

Aspect	GUID	Auto-Increment Integer
Distributed Systems	✅ Can generate on client	❌ Must query database
Merge/Replication	✅ No conflicts	❌ Conflicts likely
Security	✅ Non-guessable	⚠️ Sequential/predictable
Database Portability	✅ Universal	⚠️ Provider-specific
Index Performance	⚠️ Slightly slower	✅ Optimal

For SiLA2 servers that may run distributed experiments or replicate data across devices, GUIDs provide significant advantages.

3. IDbContext Abstraction

IDbContext is a minimal interface that wraps Entity Framework Core's DbContext:

public interface IDbContext
{
    IEnumerable<Type> DbMappingTypes { get; }
    DatabaseFacade Database { get; }
    DbSet<TEntity> Set<TEntity>() where TEntity : BaseEntity;
    int SaveChanges();
    Task<int> SaveChangesAsync(CancellationToken cancellationToken = default);
}

Why an Interface?

• Testability: You can create in-memory implementations for unit tests
• Dependency Injection: Enable proper scoping and lifetime management
• Multiple Contexts: Support multiple databases in the same application

4. Automatic Transaction Management

All write operations (Insert, Update, Delete) are wrapped in database transactions:

public async Task<TransactionResultMessage> Insert(T entity)
{
    try
    {
        using (var transaction = _context.Database.BeginTransaction())
        {
            Entities.Add(entity);
            _context.SaveChanges();
            await transaction.CommitAsync();
            return new TransactionResultMessage(TransactionResult.Success);
        }
    }
    catch (Exception ex)
    {
        return new TransactionResultMessage(TransactionResult.Error, ex.ToString());
    }
}

Transaction Guarantees:

• Atomicity: Either all changes succeed or none are applied
• Rollback on Failure: Exceptions trigger automatic rollback
• Error Reporting: Exceptions are caught and returned (no silent failures)

Why This Matters: In laboratory automation, partial updates can corrupt experimental data. Automatic transactions ensure data integrity without requiring developers to manage transactions manually.

5. LINQ Query Support

The Table property provides full LINQ query capabilities:

// Complex queries without raw SQL
var highTempResults = _repository.Table
    .Where(r => r.Temperature > 100)
    .Where(r => r.IsValid)
    .OrderByDescending(r => r.Timestamp)
    .Include(r => r.RelatedData)  // Eager loading
    .ToListAsync();

LINQ Benefits:

• Type Safety: Compile-time checking of queries
• Refactoring Support: Rename entities/properties safely
• IntelliSense: Full IDE support for query composition
• Deferred Execution: Queries run only when enumerated

Architecture & Components

Component Overview

┌─────────────────────────────────────────────────────────────┐
│                  SiLA2 Feature Service                      │
│  (Business Logic - Commands, Properties, Workflows)         │
└───────────────────────────┬─────────────────────────────────┘
                            │
                            ▼
┌─────────────────────────────────────────────────────────────┐
│                IRepository<TEntity>                         │
│  - GetById(id)                                              │
│  - Insert(entity)                                           │
│  - Update(entity)                                           │
│  - Delete(entity)                                           │
│  - Table (IQueryable<T> for LINQ)                           │
└───────────────────────────┬─────────────────────────────────┘
                            │
            ┌───────────────┴───────────────┐
            ▼                               ▼
┌─────────────────────┐         ┌─────────────────────┐
│   Repository<T>     │         │   IDbContext        │
│                     │         │                     │
│ - Transaction       │◄────────┤ - Set<TEntity>()    │
│   management        │         │ - SaveChanges()     │
│ - CRUD operations   │         │ - Database          │
└─────────────────────┘         └──────────┬──────────┘
                                           │
                                           ▼
                            ┌─────────────────────────┐
                            │   DbContext (EF Core)   │
                            │   - Change Tracking     │
                            │   - Query Translation   │
                            │   - Migrations          │
                            └──────────┬──────────────┘
                                       │
                                       ▼
                        ┌─────────────────────────────────┐
                        │   Database Provider             │
                        │   (SQL Server, PostgreSQL,      │
                        │    SQLite, etc.)                │
                        └─────────────────────────────────┘

IDbContext

Purpose: Abstracts Entity Framework Core's DbContext to enable testability and dependency injection.

Key Properties:

• DbMappingTypes: Returns all entity types registered in the context

  • Used for dynamic configuration and reflection-based operations
  • Useful when generating database schemas or documentation

• Database: Provides access to database-level operations

  • Transaction management (BeginTransaction, CommitTransaction)
  • Migrations (Migrate, EnsureCreated, EnsureDeleted)
  • Connection management
  • Raw SQL execution

• Set<TEntity>(): Returns a DbSet<TEntity> for entity operations

  • CRUD operations
  • LINQ queries
  • Change tracking

IRepository<T>

Purpose: Provides a consistent API for entity operations with automatic transaction management.

Methods:

public interface IRepository<T> where T : BaseEntity
{
    Task<T> GetById(object id);
    Task<TransactionResultMessage> Insert(T entity);
    Task<TransactionResultMessage> Update(T entity);
    Task<TransactionResultMessage> Delete(T entity);
    IQueryable<T> Table { get; }
}

Method Behaviors:

• GetById(id): Uses EF Core's FindAsync, which checks the change tracker first
• Insert(entity): Adds entity, saves changes, commits transaction
• Update(entity): Updates entity, saves changes, commits transaction
• Delete(entity): Removes entity, saves changes, commits transaction
• Table: Returns IQueryable<T> for LINQ queries (read-only access)

Repository<T>

Purpose: Default implementation of IRepository<T> with virtual methods for customization.

Key Features:

• Protected Entities property for derived classes
• Virtual methods enable custom repository implementations
• Lazy initialization of DbSet<T>
• Exception handling with TransactionResultMessage

Customization Example:

public class ExperimentRepository : Repository<Experiment>
{
    public ExperimentRepository(IDbContext context) : base(context) { }

    // Override to include related data
    public override async Task<Experiment> GetById(object id)
    {
        return await Entities
            .Include(e => e.Steps)
            .Include(e => e.Results)
            .SingleOrDefaultAsync(e => e.Id == (Guid)id);
    }

    // Override to include related data in all queries
    public override IQueryable<Experiment> Table =>
        Entities.Include(e => e.Steps).Include(e => e.Results);

    // Add custom query methods
    public async Task<List<Experiment>> GetExperimentsBySample(string sampleId)
    {
        return await Table
            .Where(e => e.SampleId == sampleId)
            .OrderByDescending(e => e.CreatedDate)
            .ToListAsync();
    }
}

BaseEntity

Purpose: Provides a standard GUID-based primary key for all entities.

public abstract class BaseEntity
{
    [Key]
    public Guid Id { get; set; }
}

Usage Pattern:

public class MyEntity : BaseEntity
{
    // Id property inherited from BaseEntity
    public string Name { get; set; }
    public DateTime CreatedDate { get; set; }
}

// Creating new entities
var entity = new MyEntity
{
    Id = Guid.NewGuid(),  // Generate GUID before insertion
    Name = "Sample 1",
    CreatedDate = DateTime.UtcNow
};

TransactionResult & TransactionResultMessage

Purpose: Communicate transaction outcomes without throwing exceptions.

public enum TransactionResult
{
    Success,
    Error
}

public class TransactionResultMessage
{
    public TransactionResult TransactionResult { get; }
    public string Message { get; }
}

Pattern:

var result = await _repository.Insert(entity);

if (result.TransactionResult == TransactionResult.Success)
{
    // Success path
}
else
{
    // Error handling - result.Message contains exception details
    _logger.LogError($"Database error: {result.Message}");
}

Usage Examples

Creating a Custom DbContext

using Microsoft.EntityFrameworkCore;
using Microsoft.Extensions.Configuration;
using SiLA2.Database.SQL;
using SiLA2.Database.SQL.Domain;

namespace MyFeature.Database
{
    public interface IMyFeatureDbContext : IDbContext { }

    public class MyFeatureDbContext : DbContext, IMyFeatureDbContext
    {
        private readonly IConfiguration _configuration;

        public IEnumerable<Type> DbMappingTypes => new[]
        {
            typeof(Experiment),
            typeof(ExperimentStep),
            typeof(CalibrationData)
        };

        public MyFeatureDbContext(
            DbContextOptions<MyFeatureDbContext> options,
            IConfiguration configuration) : base(options)
        {
            _configuration = configuration;
            Database.EnsureCreated(); // Simple approach for dev/testing
            // Use Database.Migrate() for production with migrations
        }

        public new DbSet<TEntity> Set<TEntity>() where TEntity : BaseEntity
        {
            return base.Set<TEntity>();
        }

        protected override void OnModelCreating(ModelBuilder modelBuilder)
        {
            // Configure Experiment entity
            modelBuilder.Entity<Experiment>(entity =>
            {
                entity.HasKey(e => e.Id);
                entity.Property(e => e.Name).HasMaxLength(200).IsRequired();
                entity.Property(e => e.Description).HasMaxLength(1000);
                entity.HasMany(e => e.Steps)
                      .WithOne()
                      .HasForeignKey("ExperimentId");
            });

            // Configure ExperimentStep entity
            modelBuilder.Entity<ExperimentStep>(entity =>
            {
                entity.HasKey(e => e.Id);
                entity.Property(e => e.StepNumber).IsRequired();
                entity.Property(e => e.Temperature).HasPrecision(18, 2);
            });

            base.OnModelCreating(modelBuilder);
        }
    }
}

Defining Entity Models

using SiLA2.Database.SQL.Domain;

namespace MyFeature.Database
{
    public class Experiment : BaseEntity
    {
        public string Name { get; set; }
        public string Description { get; set; }
        public DateTime StartTime { get; set; }
        public DateTime? EndTime { get; set; }
        public ExperimentStatus Status { get; set; }

        // Navigation property for related entities
        public List<ExperimentStep> Steps { get; set; } = new();
    }

    public class ExperimentStep : BaseEntity
    {
        public int StepNumber { get; set; }
        public double Temperature { get; set; }
        public int DurationSeconds { get; set; }
        public string Notes { get; set; }
    }

    public enum ExperimentStatus
    {
        Pending,
        Running,
        Completed,
        Failed
    }
}

Dependency Injection Setup

// Program.cs
using Microsoft.EntityFrameworkCore;
using MyFeature.Database;

var builder = WebApplication.CreateBuilder(args);

// SQL Server (production)
builder.Services.AddDbContext<IMyFeatureDbContext, MyFeatureDbContext>(options =>
    options.UseSqlServer(
        builder.Configuration.GetConnectionString("MyFeatureDatabase"),
        sqlOptions => sqlOptions.EnableRetryOnFailure()));

// Or PostgreSQL
builder.Services.AddDbContext<IMyFeatureDbContext, MyFeatureDbContext>(options =>
    options.UseNpgsql(
        builder.Configuration.GetConnectionString("MyFeatureDatabase")));

// Or SQLite (development/testing)
builder.Services.AddDbContext<IMyFeatureDbContext, MyFeatureDbContext>(options =>
    options.UseSqlite("Data Source=myfeature.db"));

// Register repositories
builder.Services.AddScoped<IRepository<Experiment>, Repository<Experiment>>();
builder.Services.AddScoped<IRepository<ExperimentStep>, Repository<ExperimentStep>>();

// Or register custom repositories
builder.Services.AddScoped<ExperimentRepository>();

var app = builder.Build();

// Apply migrations on startup (production)
using (var scope = app.Services.CreateScope())
{
    var context = scope.ServiceProvider.GetRequiredService<IMyFeatureDbContext>();
    if (context is DbContext dbContext)
    {
        dbContext.Database.Migrate();
    }
}

app.Run();

CRUD Operations via Repository

public class ExperimentService
{
    private readonly IRepository<Experiment> _experimentRepository;
    private readonly ILogger<ExperimentService> _logger;

    public ExperimentService(
        IRepository<Experiment> experimentRepository,
        ILogger<ExperimentService> logger)
    {
        _experimentRepository = experimentRepository;
        _logger = logger;
    }

    // CREATE
    public async Task<Guid?> CreateExperiment(string name, string description)
    {
        var experiment = new Experiment
        {
            Id = Guid.NewGuid(),
            Name = name,
            Description = description,
            StartTime = DateTime.UtcNow,
            Status = ExperimentStatus.Pending
        };

        var result = await _experimentRepository.Insert(experiment);

        if (result.TransactionResult == TransactionResult.Success)
        {
            _logger.LogInformation($"Created experiment: {experiment.Id}");
            return experiment.Id;
        }
        else
        {
            _logger.LogError($"Failed to create experiment: {result.Message}");
            return null;
        }
    }

    // READ
    public async Task<Experiment> GetExperiment(Guid id)
    {
        return await _experimentRepository.GetById(id);
    }

    // UPDATE
    public async Task<bool> UpdateExperimentStatus(Guid id, ExperimentStatus status)
    {
        var experiment = await _experimentRepository.GetById(id);
        if (experiment == null)
        {
            return false;
        }

        experiment.Status = status;
        if (status == ExperimentStatus.Completed)
        {
            experiment.EndTime = DateTime.UtcNow;
        }

        var result = await _experimentRepository.Update(experiment);
        return result.TransactionResult == TransactionResult.Success;
    }

    // DELETE
    public async Task<bool> DeleteExperiment(Guid id)
    {
        var experiment = await _experimentRepository.GetById(id);
        if (experiment == null)
        {
            return false;
        }

        var result = await _experimentRepository.Delete(experiment);
        return result.TransactionResult == TransactionResult.Success;
    }
}

Custom Repository Implementation

using Microsoft.EntityFrameworkCore;
using SiLA2.Database.SQL;
using System.Linq;

namespace MyFeature.Database
{
    public class ExperimentRepository : Repository<Experiment>
    {
        public ExperimentRepository(IMyFeatureDbContext context) : base(context) { }

        // Override GetById to include related entities
        public override async Task<Experiment> GetById(object id)
        {
            return await Entities
                .Include(e => e.Steps)
                .SingleOrDefaultAsync(e => e.Id == (Guid)id);
        }

        // Override Table to always include Steps
        public override IQueryable<Experiment> Table =>
            Entities.Include(e => e.Steps);

        // Add custom query methods
        public async Task<List<Experiment>> GetRunningExperiments()
        {
            return await Table
                .Where(e => e.Status == ExperimentStatus.Running)
                .OrderBy(e => e.StartTime)
                .ToListAsync();
        }

        public async Task<List<Experiment>> GetExperimentsByDateRange(
            DateTime startDate, DateTime endDate)
        {
            return await Table
                .Where(e => e.StartTime >= startDate && e.StartTime <= endDate)
                .OrderByDescending(e => e.StartTime)
                .ToListAsync();
        }

        public async Task<int> CountExperimentsByStatus(ExperimentStatus status)
        {
            return await Table
                .Where(e => e.Status == status)
                .CountAsync();
        }
    }

    // Register custom repository
    // services.AddScoped<ExperimentRepository>();
}

Using LINQ Queries via Table Property

public class ExperimentQueryService
{
    private readonly IRepository<Experiment> _repository;

    public ExperimentQueryService(IRepository<Experiment> repository)
    {
        _repository = repository;
    }

    // Complex filtering
    public async Task<List<Experiment>> GetRecentCompletedExperiments(int count)
    {
        return await _repository.Table
            .Where(e => e.Status == ExperimentStatus.Completed)
            .Where(e => e.EndTime.HasValue)
            .OrderByDescending(e => e.EndTime.Value)
            .Take(count)
            .ToListAsync();
    }

    // Projections (select specific fields)
    public async Task<List<ExperimentSummary>> GetExperimentSummaries()
    {
        return await _repository.Table
            .Select(e => new ExperimentSummary
            {
                Id = e.Id,
                Name = e.Name,
                Status = e.Status,
                Duration = e.EndTime.HasValue
                    ? (e.EndTime.Value - e.StartTime).TotalMinutes
                    : 0
            })
            .ToListAsync();
    }

    // Aggregations
    public async Task<double> GetAverageDuration()
    {
        return await _repository.Table
            .Where(e => e.Status == ExperimentStatus.Completed && e.EndTime.HasValue)
            .Select(e => (e.EndTime.Value - e.StartTime).TotalMinutes)
            .AverageAsync();
    }

    // Grouping
    public async Task<Dictionary<ExperimentStatus, int>> GetStatusCounts()
    {
        return await _repository.Table
            .GroupBy(e => e.Status)
            .Select(g => new { Status = g.Key, Count = g.Count() })
            .ToDictionaryAsync(x => x.Status, x => x.Count);
    }

    // Exists checks
    public async Task<bool> HasActiveExperiments()
    {
        return await _repository.Table
            .AnyAsync(e => e.Status == ExperimentStatus.Running ||
                          e.Status == ExperimentStatus.Pending);
    }

    // Pagination
    public async Task<List<Experiment>> GetExperimentsPage(int pageNumber, int pageSize)
    {
        return await _repository.Table
            .OrderByDescending(e => e.StartTime)
            .Skip((pageNumber - 1) * pageSize)
            .Take(pageSize)
            .ToListAsync();
    }
}

Database Provider Support

The module works with any Entity Framework Core database provider.

SQL Server

Install Package:

dotnet add package Microsoft.EntityFrameworkCore.SqlServer

Configure:

builder.Services.AddDbContext<IMyFeatureDbContext, MyFeatureDbContext>(options =>
    options.UseSqlServer(
        "Server=localhost;Database=SiLA2Feature;Trusted_Connection=True;TrustServerCertificate=True;",
        sqlOptions =>
        {
            sqlOptions.EnableRetryOnFailure(
                maxRetryCount: 5,
                maxRetryDelay: TimeSpan.FromSeconds(30),
                errorNumbersToAdd: null);
        }));

appsettings.json:

{
  "ConnectionStrings": {
    "MyFeatureDatabase": "Server=localhost;Database=SiLA2Feature;Trusted_Connection=True;TrustServerCertificate=True;"
  }
}

PostgreSQL

Install Package:

dotnet add package Npgsql.EntityFrameworkCore.PostgreSQL

Configure:

builder.Services.AddDbContext<IMyFeatureDbContext, MyFeatureDbContext>(options =>
    options.UseNpgsql(
        "Host=localhost;Database=sila2feature;Username=postgres;Password=yourpassword",
        pgOptions =>
        {
            pgOptions.EnableRetryOnFailure(
                maxRetryCount: 5,
                maxRetryDelay: TimeSpan.FromSeconds(30));
        }));

appsettings.json:

{
  "ConnectionStrings": {
    "MyFeatureDatabase": "Host=localhost;Database=sila2feature;Username=postgres;Password=yourpassword"
  }
}

SQLite

Install Package:

dotnet add package Microsoft.EntityFrameworkCore.Sqlite

Configure:

builder.Services.AddDbContext<IMyFeatureDbContext, MyFeatureDbContext>(options =>
    options.UseSqlite("Data Source=myfeature.db"));

appsettings.json:

{
  "ConnectionStrings": {
    "MyFeatureDatabase": "Data Source=myfeature.db"
  }
}

Use Case: Perfect for development, testing, and edge devices.

Provider Comparison

Feature	SQL Server	PostgreSQL	SQLite
Production Ready	✅ Enterprise	✅ Enterprise	⚠️ Small-scale only
Platform	Windows/Linux	Cross-platform	Cross-platform
License	Commercial	Open Source	Public Domain
Max Database Size	524 PB	Unlimited	281 TB (file limit)
Concurrent Writes	✅ Excellent	✅ Excellent	⚠️ Limited (file locking)
Setup Complexity	Medium	Medium	✅ None (file-based)
Cost	$$$ (licensing)	Free	Free
Best For	Windows enterprise	Linux/cloud	Dev/testing/embedded

Working Example: Temperature Controller

The Temperature Controller feature demonstrates complete SQL integration.

Location: src/Examples/TemperatureController/

Example Structure

TemperatureController.Features/
├── Database/
│   ├── TemperatureDbContext.cs          # DbContext implementation
│   ├── TemperatureProfile.cs            # Entity model
│   ├── TemperatureProfileStep.cs        # Related entity
│   ├── TemperatureProfileRepository.cs  # Custom repository
│   └── Maps/                            # EF Core configurations
└── Services/
    ├── TemperatureProfileService.cs     # Uses repository
    └── TemperatureControllerService.cs  # Feature implementation

DbContext Implementation

public interface IDbTemperatureProfileContext : IDbContext { }

public class TemperatureDbContext : DbContext, IDbTemperatureProfileContext
{
    public IEnumerable<Type> DbMappingTypes => new[]
    {
        typeof(TemperatureProfileStepMap),
        typeof(TemperatureProfileMap)
    };

    public TemperatureDbContext(
        DbContextOptions<TemperatureDbContext> options,
        IConfiguration configuration,
        ILogger<TemperatureDbContext> logger) : base(options)
    {
        if (Database.EnsureCreated())
        {
            // Run DbUp migrations if needed
        }
    }

    public new DbSet<TEntity> Set<TEntity>() where TEntity : BaseEntity
    {
        return base.Set<TEntity>();
    }

    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        foreach (var type in DbMappingTypes)
        {
            dynamic configurationInstance = Activator.CreateInstance(type);
            modelBuilder.ApplyConfiguration(configurationInstance);
        }
        base.OnModelCreating(modelBuilder);
    }
}

Entity Model

public class TemperatureProfile : BaseEntity
{
    public int ClockInMilliseconds { get; private set; }
    public int Loops { get; }
    public List<TemperatureProfileStep> Temperatures { get; private set; }

    public TemperatureProfile(
        int clockInMilliseconds,
        int loops,
        List<TemperatureProfileStep> temperatures)
    {
        ClockInMilliseconds = clockInMilliseconds;
        Loops = loops;
        Temperatures = temperatures;
    }

    public TemperatureProfile() { }
}

Custom Repository

public class TemperatureProfileRepository : Repository<TemperatureProfile>
{
    public TemperatureProfileRepository(IDbTemperatureProfileContext context)
        : base(context) { }

    // Override to include related entities
    public override async Task<TemperatureProfile> GetById(object id)
    {
        return await Entities
            .Include(x => x.Temperatures)
            .SingleOrDefaultAsync(x => x.Id == (Guid)id);
    }

    // Override to always include related data
    public override IQueryable<TemperatureProfile> Table =>
        Entities.Include(x => x.Temperatures);
}

Service Usage

public class TemperatureProfileService : ITemperatureProfileService
{
    private readonly IRepository<TemperatureProfile> _repository;

    public TemperatureProfileService(
        IRepository<TemperatureProfile> repository)
    {
        _repository = repository;
    }

    public async Task<TemperatureProfile> GetTemperatureProfile(int id)
    {
        return await _repository.GetById(id);
    }

    public IQueryable<TemperatureProfile> GetTemperatureProfiles()
    {
        return _repository.Table;
    }

    public async Task InsertTemperatureProfile(TemperatureProfile profile)
    {
        await _repository.Insert(profile);
    }

    public async Task UpdateTemperatureProfile(TemperatureProfile profile)
    {
        await _repository.Update(profile);
    }

    public async Task DeleteTemperatureProfile(TemperatureProfile profile)
    {
        await _repository.Delete(profile);
    }
}

Registration (Program.cs)

// From SiLA2.Temperature.Server.App.Webfrontend/Program.cs
services.AddDbContext<IDbTemperatureProfileContext, TemperatureDbContext>(
    options => options.UseSqlite(
        configuration.GetConnectionString("TemperatureServiceConnection")));

services.AddScoped<IRepository<TemperatureProfile>, TemperatureProfileRepository>();
services.AddScoped<ITemperatureProfileService, TemperatureProfileService>();

Run the Example:

cd src/Examples/TemperatureController
dotnet run --project SiLA2.Temperature.Server.App.Webfrontend

Navigate to: https://localhost:5011

Advanced Topics

Entity Framework Migrations

Migrations provide version control for your database schema.

Create Initial Migration:

cd src/MyFeature.Features
dotnet ef migrations add InitialCreate --context MyFeatureDbContext

Apply Migration:

dotnet ef database update --context MyFeatureDbContext

Or apply on startup:

// Program.cs
using (var scope = app.Services.CreateScope())
{
    var context = scope.ServiceProvider.GetRequiredService<IMyFeatureDbContext>();
    if (context is DbContext dbContext)
    {
        dbContext.Database.Migrate();
    }
}

Generate SQL Script:

dotnet ef migrations script --context MyFeatureDbContext -o migration.sql

Transaction Handling

Using Database Facade:

public async Task<bool> TransferData(Guid sourceId, Guid targetId)
{
    using (var transaction = _context.Database.BeginTransaction())
    {
        try
        {
            var source = await _sourceRepository.GetById(sourceId);
            var target = await _targetRepository.GetById(targetId);

            // Multiple operations in one transaction
            source.Status = Status.Transferred;
            target.Data = source.Data;

            await _sourceRepository.Update(source);
            await _targetRepository.Update(target);

            await transaction.CommitAsync();
            return true;
        }
        catch
        {
            await transaction.RollbackAsync();
            return false;
        }
    }
}

Note: Individual repository methods already use transactions, but you can use Database.BeginTransaction() for multi-repository operations.

Concurrency Management

Optimistic Concurrency:

public class Experiment : BaseEntity
{
    [Timestamp]
    public byte[] RowVersion { get; set; }
}

// In DbContext OnModelCreating
modelBuilder.Entity<Experiment>()
    .Property(e => e.RowVersion)
    .IsRowVersion();

Handle Concurrency Conflicts:

public async Task<bool> UpdateExperimentSafely(Experiment experiment)
{
    try
    {
        var result = await _repository.Update(experiment);
        return result.TransactionResult == TransactionResult.Success;
    }
    catch (DbUpdateConcurrencyException ex)
    {
        _logger.LogWarning("Concurrency conflict updating experiment {Id}", experiment.Id);
        // Reload entity and retry, or notify user
        return false;
    }
}

Testing Strategies

In-Memory Database for Unit Tests:

using Microsoft.EntityFrameworkCore;
using Xunit;

public class ExperimentRepositoryTests
{
    private IDbContext CreateInMemoryContext()
    {
        var options = new DbContextOptionsBuilder<MyFeatureDbContext>()
            .UseInMemoryDatabase(databaseName: Guid.NewGuid().ToString())
            .Options;

        return new MyFeatureDbContext(options, configuration, logger);
    }

    [Fact]
    public async Task Insert_ShouldAddEntity()
    {
        // Arrange
        var context = CreateInMemoryContext();
        var repository = new Repository<Experiment>(context);
        var experiment = new Experiment
        {
            Id = Guid.NewGuid(),
            Name = "Test Experiment"
        };

        // Act
        var result = await repository.Insert(experiment);

        // Assert
        Assert.Equal(TransactionResult.Success, result.TransactionResult);
        var retrieved = await repository.GetById(experiment.Id);
        Assert.NotNull(retrieved);
        Assert.Equal("Test Experiment", retrieved.Name);
    }
}

Mock Repository:

using Moq;
using Xunit;

public class ExperimentServiceTests
{
    [Fact]
    public async Task CreateExperiment_ShouldReturnId_WhenSuccessful()
    {
        // Arrange
        var mockRepository = new Mock<IRepository<Experiment>>();
        mockRepository
            .Setup(r => r.Insert(It.IsAny<Experiment>()))
            .ReturnsAsync(new TransactionResultMessage(TransactionResult.Success));

        var service = new ExperimentService(mockRepository.Object, Mock.Of<ILogger<ExperimentService>>());

        // Act
        var result = await service.CreateExperiment("Test", "Description");

        // Assert
        Assert.NotNull(result);
        mockRepository.Verify(r => r.Insert(It.IsAny<Experiment>()), Times.Once);
    }
}

Custom Query Methods

Extension Methods for Common Queries:

public static class ExperimentQueryExtensions
{
    public static IQueryable<Experiment> Active(this IQueryable<Experiment> query)
    {
        return query.Where(e =>
            e.Status == ExperimentStatus.Running ||
            e.Status == ExperimentStatus.Pending);
    }

    public static IQueryable<Experiment> CompletedAfter(
        this IQueryable<Experiment> query, DateTime date)
    {
        return query.Where(e =>
            e.Status == ExperimentStatus.Completed &&
            e.EndTime.HasValue &&
            e.EndTime.Value >= date);
    }
}

// Usage
var activeExperiments = await _repository.Table
    .Active()
    .ToListAsync();

var recentExperiments = await _repository.Table
    .CompletedAfter(DateTime.UtcNow.AddDays(-7))
    .OrderByDescending(e => e.EndTime)
    .ToListAsync();

Comparison: SQL vs NoSQL

When to Use SiLA2.Database.SQL

✅ Use SQL when:

• Complex relational data (experiments with steps, protocols with parameters)
• Need for ACID transactions (critical laboratory data)
• Multi-table joins and aggregations
• Schema migrations and versioning
• Enterprise backup/recovery requirements
• Reporting and analytics
• Team familiar with SQL

When to Use SiLA2.Database.NoSQL

✅ Use NoSQL when:

• Simple document storage (AnIML experimental data)
• Embedded database requirements (edge devices, no SQL server)
• Schema flexibility over joins
• Minimal dependencies
• Rapid prototyping

Hybrid Approach

Many SiLA2 servers use both:

• SQL: Structured data (device configurations, user management, workflow state)
• NoSQL: Unstructured data (AnIML documents, binary blobs, logs)

// Register both persistence layers
builder.Services.AddDbContext<IMyFeatureDbContext, MyFeatureDbContext>(
    options => options.UseSqlServer(connectionString));

builder.Services.AddSingleton<ILiteDatabase>(sp =>
    new LiteDatabase("animl.db"));

// Use SQL for structured data
services.AddScoped<IRepository<Experiment>, Repository<Experiment>>();

// Use NoSQL for documents
services.AddSingleton<IAnIMLRepository, AnIMLRepository>();

API Reference Summary

IDbContext

public interface IDbContext
{
    IEnumerable<Type> DbMappingTypes { get; }
    DatabaseFacade Database { get; }
    DbSet<TEntity> Set<TEntity>() where TEntity : BaseEntity;
    int SaveChanges();
    Task<int> SaveChangesAsync(CancellationToken cancellationToken = default);
}

IRepository<T>

public interface IRepository<T> where T : BaseEntity
{
    Task<T> GetById(object id);
    Task<TransactionResultMessage> Insert(T entity);
    Task<TransactionResultMessage> Update(T entity);
    Task<TransactionResultMessage> Delete(T entity);
    IQueryable<T> Table { get; }
}

Repository<T>

public class Repository<T> : IRepository<T> where T : BaseEntity
{
    protected readonly IDbContext _context;
    protected DbSet<T> Entities { get; }

    public Repository(IDbContext context);

    public virtual Task<T> GetById(object id);
    public virtual Task<TransactionResultMessage> Insert(T entity);
    public virtual Task<TransactionResultMessage> Update(T entity);
    public virtual Task<TransactionResultMessage> Delete(T entity);
    public virtual IQueryable<T> Table { get; }
}

BaseEntity

public abstract class BaseEntity
{
    [Key]
    public Guid Id { get; set; }
}

TransactionResult

public enum TransactionResult
{
    Success,
    Error
}

TransactionResultMessage

public class TransactionResultMessage
{
    public TransactionResult TransactionResult { get; }
    public string Message { get; }

    public TransactionResultMessage(
        TransactionResult transactionResult,
        string message = null);
}

Related Packages

• SiLA2.Core - Core SiLA2 server implementation (required dependency)
• SiLA2.Database.NoSQL - NoSQL persistence using LiteDB
• SiLA2.AnIML - AnIML scientific data format support
• SiLA2.Audit - Automatic audit logging for compliance
• SiLA2.AspNetCore - ASP.NET Core integration and DI extensions

Contributing & Development

This package is part of the sila_csharp project.

Building from Source

git clone --recurse-submodules https://gitlab.com/SiLA2/sila_csharp.git
cd sila_csharp/src
dotnet build SiLA2.Database.SQL/SiLA2.Database.SQL.csproj

Running Tests

dotnet test Tests/SiLA2.Database.SQL.Tests/SiLA2.Database.SQL.Tests.csproj

Project Structure

SiLA2.Database.SQL/
├── Domain/
│   └── BaseEntity.cs                 # Base class for all entities
├── IDbContext.cs                     # DbContext abstraction
├── IRepository.cs                    # Repository interface
├── Repository.cs                     # Repository implementation
├── TransactionResult.cs              # Transaction status enum
├── TransactionResultMessage.cs       # Transaction result wrapper
└── SiLA2.Database.SQL.csproj        # Project file

Links & Resources

• SiLA2 Standard: https://sila-standard.com
• Repository: https://gitlab.com/SiLA2/sila_csharp
• NuGet Package: https://www.nuget.org/packages/SiLA2.Database.SQL
• Documentation Wiki: https://gitlab.com/SiLA2/sila_csharp/-/wikis/home
• Issue Tracker: https://gitlab.com/SiLA2/sila_csharp/-/issues
• Entity Framework Core Docs: https://learn.microsoft.com/ef/core/
• SiLA Slack: Join the community

License

This project is licensed under the MIT License.

Maintainer

Christoph Pohl (@Chamundi)

Security

For security vulnerabilities, please refer to the SiLA2 Vulnerability Policy.

---

Questions or Issues?

• Open an issue on GitLab
• Join the SiLA community on Slack
• Check the Wiki for additional documentation