C#  

Advanced Database Programming with C# 14 and Microsoft SQL Server

Introduction

As of C# 14, Microsoft continues to evolve the language to provide more expressive, safe, and performant coding patterns. When combined with Microsoft SQL Server, C# becomes a powerhouse for building robust and scalable enterprise applications.

In this article, we'll explore advanced C# 14 features in the context of database programming with MS SQL, focusing on,

  • New language features enhancing database code
  • Advanced ADO.NET and Entity Framework Core patterns
  • Performance optimization and security practices
  • Real-world scenarios and architectural strategies

What's New in C# 14 That Matters for Database Developers?

Primary Constructors for Non-Record Types

This C# 14 feature streamlines dependency injection in classes like repositories or services.

public class UserRepository(DbContext context)
{
    public IEnumerable<User> GetAllUsers() => context.Users.ToList();
}

The code above demonstrates the use of primary constructors directly in a class, reducing the need for boilerplate constructor code. Instead of writing a separate constructor and assigning the context parameter to a field, C# 14 allows us to define the dependency right in the class declaration. This pattern is especially powerful for service classes interacting with databases, where dependency injection is a common pattern.

Collection Expressions

C# 14 introduces collection expressions, simplifying complex LINQ and initialization logic often used in data access.

var roles = [ "Admin", "User", "Manager" ];

Use them in filtering queries.

var users = dbContext.Users
    .Where(u => [ "Admin", "User" ].Contains(u.Role))
    .ToList();

In the example, collection expressions simplify how arrays or lists are created and used directly in LINQ queries. This makes the code more concise and easier to read when filtering results against a predefined list of values. It's especially useful in query scenarios where certain values like roles or categories are repeatedly checked.

Interceptors for Diagnostics

Improved support for interceptors can be used to trace SQL queries or inject custom behaviors in EF Core.

Interceptors allow developers to hook into the EF Core query lifecycle, giving them the ability to log, modify, or monitor SQL commands before they’re executed. This is useful for diagnostics, debugging, performance logging, or even enforcing application-level rules across all queries in a central place.

Extension Members: Enhancing LINQ and Repository Patterns

C# 14 introduces extension members, allowing developers to define extension properties and methods within a dedicated extension block.

public static class Extensions
{
    extension(IEnumerable<int> source)
    {
        public bool IsEmpty => !source.Any();
        public IEnumerable<int> FilterPositive() => source.Where(x => x > 0);
    }
}

This feature enables more expressive and reusable code, especially when working with collections and LINQ queries. The ability to extend existing types in a more modular way is particularly valuable in repository or query projection layers of data access code.

Implicit Span Conversions: Optimizing Data Processing

C# 14 introduces implicit conversions between arrays and Span<T> or ReadOnlySpan<T>.

byte[] data = { 1, 2, 3, 4, 5 };
ReadOnlySpan<byte> spanData = data;
ProcessData(spanData);

This enhances performance for high-throughput or binary data operations, such as streaming data from a SQL column with binary types. Span<T> operations avoid heap allocations and improve memory efficiency.

Field-Backed Properties: Simplifying Property Definitions

C# 14 introduces the field keyword to refer to compiler-generated backing fields.

public string Name
{
    get;
    set => field = value ?? throw new ArgumentNullException(nameof(value));
}

This makes it easier to add validation or transformation logic in auto-implemented properties, keeping code concise and clear.

Advanced EF Core Patterns with C# 14

Custom Value Converters

Perfect for mapping custom types or encrypted fields.

builder.Property(u => u.SSN)
    .HasConversion(
        v => Encrypt(v),
        v => Decrypt(v)
    );

This code snippet illustrates the use of custom value converters in EF Core to handle encryption and decryption transparently when reading or writing to the database. Instead of manually encrypting or decrypting fields in your application logic, you centralize this behavior in the model configuration, making your code cleaner and more secure.

Temporal Tables (SQL Server 2016+)

Track historical changes with EF Core.

modelBuilder.Entity<User>().ToTable("Users", t => t.IsTemporal());

EF Core 7+ supports querying history directly.

var oldData = context.Users.TemporalAsOf(someDate).ToList();

Temporal tables store the full history of changes to rows in a SQL Server table, and EF Core now fully supports working with them. The first snippet shows how to enable temporal support in your entity mapping. The second snippet demonstrates how to query the data as it existed at a specific point in time using TemporalAsOf, enabling powerful audit and data recovery scenarios.

Raw SQL + LINQ Composition

var users = dbContext.Users
    .FromSqlInterpolated($"SELECT * FROM Users WHERE Role = {role}")
    .Where(u => u.IsActive)
    .ToList();

The above pattern combines raw SQL querying with the safety and expressiveness of LINQ. FromSqlInterpolated ensures parameters are safely injected (protecting against SQL injection), while LINQ composition allows for additional filters like .Where(u => u.IsActive) to be applied to the results. It’s a powerful way to write optimized SQL without giving up the benefits of the EF Core context.

LeftJoin Support (EF Core Preview)

EF Core introduces native support for LeftJoin, simplifying complex joins.

var query = from user in context.Users
            join order in context.Orders
            on user.Id equals order.UserId into userOrders
            from order in userOrders.DefaultIfEmpty()
            select new { user.Name, OrderId = order?.Id };

This LINQ syntax creates a left outer join between users and their orders, allowing nulls when no matching orders exist. With LeftJoin becoming a native API in EF Core, such logic will be more concise and intuitive.

Performance and Optimization Techniques

Batching and Streaming

Use EF Core's IAsyncEnumerable<T> and ToListAsync() for streaming data, especially large datasets.

await foreach (var user in dbContext.Users.AsAsyncEnumerable())
{
    // Stream process
}

Using AsAsyncEnumerable allows the application to process records one at a time rather than loading the entire result set into memory at once. This is crucial for large-scale data processing, where memory usage and latency must be tightly controlled. It's especially helpful in APIs that stream data to clients in real-time.

Stored Procedures with Output

CREATE PROCEDURE GetUserCount
    @Count INT OUTPUT
AS
BEGIN
    SELECT @Count = COUNT(*) FROM Users
END

Call from C#

var param = new SqlParameter
{
    ParameterName = "@Count",
    SqlDbType = SqlDbType.Int,
    Direction = ParameterDirection.Output
};

await context.Database.ExecuteSqlRawAsync("EXEC GetUserCount @Count OUTPUT", param);
var count = (int)param.Value;

This example shows how to call a stored procedure with an output parameter using ADO.NET with EF Core. The stored procedure returns a count of users, and the SqlParameter is configured to capture this output. It’s an effective way to use server-side logic without needing a full entity result set, especially for summary values or quick lookups.

Security and Best Practices

  • Parameterized Queries: Always use FromSqlInterpolated or parameterized ADO.NET commands.
  • Connection Pooling: Ensure pooling is configured in appsettings.json.
  • Data Protection: Use .NET Data Protection API or Azure Key Vault for credentials/encryption.
  • Role-Based Access: Implement at both the SQL and application level.

These security best practices are critical for safeguarding database applications. Using parameterized queries protects against SQL injection, while connection pooling ensures efficient use of database connections. Centralized encryption strategies with services like Azure Key Vault reduce the risk of sensitive data exposure. Finally, enforcing authorization both in SQL and C# ensures a robust multi-layered security posture.

Real-World Use Case: Audit Logging Service

Using C# 14 primary constructors and EF Core.

public class AuditService(AppDbContext context)
{
    public async Task LogActionAsync(string userId, string action)
    {
        context.AuditLogs.Add(new AuditLog
        {
            UserId = userId,
            Action = action,
            Timestamp = DateTime.UtcNow
        });
        await context.SaveChangesAsync();
    }
}

In this example, the AuditService uses a primary constructor to inject the AppDbContext, making it concise and modern. The LogActionAsync method records audit logs with timestamps for user actions, an essential practice in applications that require compliance, monitoring, and traceability. Combined with temporal tables, this pattern enables powerful historical tracking without complex logic.

SQL Server CLR Integration and SSIS Automation

SQL CLR Functions in .NET

[SqlFunction]
public static int AddNumbers(int a, int b)
{
    return a + b;
}

This C# method can be registered as a SQL Server function, enabling advanced business logic directly within SQL queries. SQL CLR integration is powerful when procedural logic is best handled in .NET, such as complex calculations or text processing.

Automating ETL with SSIS from C#

Application app = new Application();
Package package = app.LoadPackage(@"C:\Packages\MyPackage.dtsx", null);
DTSExecResult result = package.Execute();

This code demonstrates how to control SQL Server Integration Services (SSIS) packages programmatically from C#. This allows automation of ETL pipelines, monitoring, and conditional executions directly from business logic.

Conclusion

C# 14 empowers developers with elegant syntax improvements and tighter integration with modern .NET and SQL Server features. By combining advanced EF Core techniques, proper security, and thoughtful architecture, developers can build high-performance, maintainable database-driven applications.

For production systems, always profile SQL queries, monitor latency, and consider caching strategies like Redis for read-heavy workloads.

Membership not found