SQL Server with Entity Framework Core

The Ordering service uses SQL Server with Entity Framework Core for managing complex order data with strong relational integrity.

Why SQL Server?

SQL Server is ideal for the Ordering service because:

Complex Relationships: Orders have customers, products, items, addresses, and payments
Strong Consistency: ACID transactions for financial data
Mature Tooling: Enterprise-grade management and monitoring
Familiar Stack: Standard choice for .NET applications

Connection Configuration

Connection String

src/Services/Ordering/Ordering.API/appsettings.json

{
  "ConnectionStrings": {
    "Database": "Server=localhost;Database=OrderDb;User Id=sa;Password=SwN12345678;Encrypt=False;TrustServerCertificate=True"
  },
  "MessageBroker": {
    "Host": "amqp://localhost:5672",
    "UserName": "guest",
    "Password": "guest"
  },
  "FeatureManagement": {
    "OrderFullfilment": false
  }
}

Never commit real passwords to source control. Use Azure Key Vault or environment variables in production.

Entity Framework Core Setup

Service Registration

src/Services/Ordering/Ordering.Infrastructure/DependencyInjection.cs

public static class DependencyInjection
{
    public static IServiceCollection AddInfrastructureServices
        (this IServiceCollection services, IConfiguration configuration)
    {
        var connectionString = configuration.GetConnectionString("Database");

        // Add services to the container.
        services.AddScoped<ISaveChangesInterceptor, AuditableEntityInterceptor>();
        services.AddScoped<ISaveChangesInterceptor, DispatchDomainEventsInterceptor>();

        services.AddDbContext<ApplicationDbContext>((sp, options) =>
        {
            options.AddInterceptors(sp.GetServices<ISaveChangesInterceptor>());
            options.UseSqlServer(connectionString);
        });

        services.AddScoped<IApplicationDbContext, ApplicationDbContext>();

        return services;
    }
}

Key Features:

Interceptors: Audit trail and domain event dispatching
Dependency Injection: DbContext registered as scoped service
Interface Abstraction: IApplicationDbContext for testability

Program.cs Configuration

src/Services/Ordering/Ordering.API/Program.cs

var builder = WebApplication.CreateBuilder(args);

// Add services to the container.
builder.Services
    .AddApplicationServices(builder.Configuration)
    .AddInfrastructureServices(builder.Configuration)
    .AddApiServices(builder.Configuration);

var app = builder.Build();

// Configure the HTTP request pipeline.
app.UseApiServices();

if (app.Environment.IsDevelopment())
{
    await app.InitialiseDatabaseAsync();
}

app.Run();

DbContext Implementation

src/Services/Ordering/Ordering.Infrastructure/Data/ApplicationDbContext.cs

using Microsoft.EntityFrameworkCore;
using Ordering.Application.Data;
using Ordering.Domain.Models;
using System.Reflection;

namespace Ordering.Infrastructure.Data;

public class ApplicationDbContext : DbContext, IApplicationDbContext
{
    public ApplicationDbContext(DbContextOptions<ApplicationDbContext> options) 
        : base(options) { }

    public DbSet<Customer> Customers => Set<Customer>();
    public DbSet<Product> Products => Set<Product>();
    public DbSet<Order> Orders => Set<Order>();
    public DbSet<OrderItem> OrderItems => Set<OrderItem>();

    protected override void OnModelCreating(ModelBuilder builder)
    {
        builder.ApplyConfigurationsFromAssembly(Assembly.GetExecutingAssembly());
        base.OnModelCreating(builder);
    }
}

Entity Configurations

Order Configuration

src/Services/Ordering/Ordering.Infrastructure/Data/Configurations/OrderConfiguration.cs

public class OrderConfiguration : IEntityTypeConfiguration<Order>
{
    public void Configure(EntityTypeBuilder<Order> builder)
    {
        builder.HasKey(o => o.Id);

        builder.Property(o => o.Id).HasConversion(
            orderId => orderId.Value,
            dbId => OrderId.Of(dbId));

        builder.HasOne<Customer>()
            .WithMany()
            .HasForeignKey(o => o.CustomerId)
            .IsRequired();

        builder.HasMany(o => o.OrderItems)
            .WithOne()
            .HasForeignKey(oi => oi.OrderId);

        builder.ComplexProperty(
            o => o.OrderName, nameBuilder =>
            {
                nameBuilder.Property(n => n.Value)
                    .HasColumnName(nameof(Order.OrderName))
                    .HasMaxLength(100)
                    .IsRequired();
            });

        builder.ComplexProperty(
            o => o.ShippingAddress, addressBuilder =>
            {
                addressBuilder.Property(a => a.FirstName)
                    .HasMaxLength(50)
                    .IsRequired();
                addressBuilder.Property(a => a.LastName)
                    .HasMaxLength(50)
                    .IsRequired();
                addressBuilder.Property(a => a.EmailAddress)
                    .HasMaxLength(50);
                addressBuilder.Property(a => a.AddressLine)
                    .HasMaxLength(180)
                    .IsRequired();
                addressBuilder.Property(a => a.Country)
                    .HasMaxLength(50);
                addressBuilder.Property(a => a.State)
                    .HasMaxLength(50);
                addressBuilder.Property(a => a.ZipCode)
                    .HasMaxLength(5)
                    .IsRequired();
            });

        builder.ComplexProperty(
            o => o.Payment, paymentBuilder =>
            {
                paymentBuilder.Property(p => p.CardName)
                    .HasMaxLength(50);
                paymentBuilder.Property(p => p.CardNumber)
                    .HasMaxLength(24)
                    .IsRequired();
                paymentBuilder.Property(p => p.Expiration)
                    .HasMaxLength(10);
                paymentBuilder.Property(p => p.CVV)
                    .HasMaxLength(3);
                paymentBuilder.Property(p => p.PaymentMethod);
            });

        builder.Property(o => o.Status)
            .HasDefaultValue(OrderStatus.Draft)
            .HasConversion(
                s => s.ToString(),
                dbStatus => (OrderStatus)Enum.Parse(typeof(OrderStatus), dbStatus));

        builder.Property(o => o.TotalPrice);
    }
}

Configuration Highlights:

Value Objects: Complex properties for addresses and payment
Strong Typing: Custom ID types with conversions
Relationships: One-to-many between orders and items
Enum Conversion: Store enums as strings

Customer Configuration

src/Services/Ordering/Ordering.Infrastructure/Data/Configurations/CustomerConfiguration.cs

public class CustomerConfiguration : IEntityTypeConfiguration<Customer>
{
    public void Configure(EntityTypeBuilder<Customer> builder)
    {
        builder.HasKey(c => c.Id);
        
        builder.Property(c => c.Id).HasConversion(
            customerId => customerId.Value,
            dbId => CustomerId.Of(dbId));

        builder.Property(c => c.Name)
            .HasMaxLength(100)
            .IsRequired();

        builder.Property(c => c.Email)
            .HasMaxLength(255);

        builder.HasIndex(c => c.Email)
            .IsUnique();
    }
}

Database Initialization

Initial Data Seeding

src/Services/Ordering/Ordering.Infrastructure/Data/Extensions/InitialData.cs

internal class InitialData
{
    public static IEnumerable<Customer> Customers =>
        new List<Customer>
        {
            Customer.Create(
                CustomerId.Of(new Guid("58c49479-ec65-4de2-86e7-033c546291aa")),
                "mehmet",
                "[email protected]"),
            Customer.Create(
                CustomerId.Of(new Guid("189dc8dc-990f-48e0-a37b-e6f2b60b9d7d")),
                "john",
                "[email protected]")
        };

    public static IEnumerable<Product> Products =>
        new List<Product>
        {
            Product.Create(
                ProductId.Of(new Guid("5334c996-8457-4cf0-815c-ed2b77c4ff61")),
                "IPhone X",
                500),
            Product.Create(
                ProductId.Of(new Guid("c67d6323-e8b1-4bdf-9a75-b0d0d2e7e914")),
                "Samsung 10",
                400)
        };

    public static IEnumerable<Order> OrdersWithItems
    {
        get
        {
            var address1 = Address.Of(
                "mehmet", "ozkaya", "[email protected]",
                "Bahcelievler No:4", "Turkey", "Istanbul", "38050");
            
            var payment1 = Payment.Of(
                "mehmet", "5555555555554444", "12/28", "355", 1);

            var order1 = Order.Create(
                OrderId.Of(Guid.NewGuid()),
                CustomerId.Of(new Guid("58c49479-ec65-4de2-86e7-033c546291aa")),
                OrderName.Of("ORD_1"),
                shippingAddress: address1,
                billingAddress: address1,
                payment1);
            
            order1.Add(ProductId.Of(new Guid("5334c996-8457-4cf0-815c-ed2b77c4ff61")), 2, 500);
            order1.Add(ProductId.Of(new Guid("c67d6323-e8b1-4bdf-9a75-b0d0d2e7e914")), 1, 400);

            return new List<Order> { order1 };
        }
    }
}

Migrations

Create Migration

cd src/Services/Ordering/Ordering.Infrastructure

dotnet ef migrations add InitialCreate \
  --startup-project ../Ordering.API/Ordering.API.csproj \
  --context ApplicationDbContext

Apply Migration

dotnet ef database update \
  --startup-project ../Ordering.API/Ordering.API.csproj \
  --context ApplicationDbContext

Auto-Migration in Development

if (app.Environment.IsDevelopment())
{
    await app.InitialiseDatabaseAsync();
}

Docker Compose Setup

sqlserver:
  image: mcr.microsoft.com/mssql/server:2022-latest
  environment:
    - ACCEPT_EULA=Y
    - SA_PASSWORD=SwN12345678
  ports:
    - "1433:1433"
  volumes:
    - sqlserver_data:/var/opt/mssql

Query Examples

Basic Queries

// Get order by ID
var order = await _context.Orders
    .Include(o => o.OrderItems)
    .FirstOrDefaultAsync(o => o.Id == orderId);

// Get customer orders
var orders = await _context.Orders
    .Where(o => o.CustomerId == customerId)
    .OrderByDescending(o => o.CreatedAt)
    .ToListAsync();

Complex Queries

// Orders with specific status
var pendingOrders = await _context.Orders
    .Include(o => o.OrderItems)
    .Where(o => o.Status == OrderStatus.Pending)
    .ToListAsync();

// Total revenue by customer
var revenue = await _context.Orders
    .Where(o => o.CustomerId == customerId)
    .SumAsync(o => o.TotalPrice);

Performance Tips

Use Projections

// Don't load entire entities
var orderSummaries = await _context.Orders
    .Select(o => new OrderSummary
    {
        Id = o.Id,
        OrderName = o.OrderName,
        Total = o.TotalPrice
    })
    .ToListAsync();

AsNoTracking for Read-Only

var orders = await _context.Orders
    .AsNoTracking()
    .ToListAsync();

Batch Operations

// Add multiple entities
_context.Orders.AddRange(orders);
await _context.SaveChangesAsync();

Troubleshooting

Connection Issues

# Test SQL Server connection
sqlcmd -S localhost -U sa -P SwN12345678

# List databases
SELECT name FROM sys.databases;

View Applied Migrations

dotnet ef migrations list \
  --startup-project ../Ordering.API/Ordering.API.csproj

Reset Database

dotnet ef database drop \
  --startup-project ../Ordering.API/Ordering.API.csproj

dotnet ef database update \
  --startup-project ../Ordering.API/Ordering.API.csproj

Database Overview

Learn about polyglot persistence strategy

EF Core Documentation

Official Entity Framework Core docs

Building Blocks

API Gateway

Messaging

Databases

Why SQL Server?

Connection Configuration

Connection String

Entity Framework Core Setup

Service Registration

Program.cs Configuration

DbContext Implementation

Entity Configurations

Order Configuration

Customer Configuration

Database Initialization

Initial Data Seeding

Migrations

Create Migration

Apply Migration

Auto-Migration in Development

Docker Compose Setup

Query Examples

Basic Queries

Complex Queries

Performance Tips

Use Projections

AsNoTracking for Read-Only

Batch Operations

Troubleshooting

Connection Issues

View Applied Migrations

Reset Database

Database Overview

EF Core Documentation

Build docs developers (and LLMs) love

Building Blocks

API Gateway

Messaging

Databases

​Why SQL Server?

​Connection Configuration

​Connection String

​Entity Framework Core Setup

​Service Registration

​Program.cs Configuration

​DbContext Implementation

​Entity Configurations

​Order Configuration

​Customer Configuration

​Database Initialization

​Initial Data Seeding

​Migrations

​Create Migration

​Apply Migration

​Auto-Migration in Development

​Docker Compose Setup

​Query Examples

​Basic Queries

​Complex Queries

​Performance Tips

​Use Projections

​AsNoTracking for Read-Only

​Batch Operations

​Troubleshooting

​Connection Issues

​View Applied Migrations

​Reset Database

​Related Resources

Database Overview

EF Core Documentation

Build docs developers (and LLMs) love

Why SQL Server?

Connection Configuration

Connection String

Entity Framework Core Setup

Service Registration

Program.cs Configuration

DbContext Implementation

Entity Configurations

Order Configuration

Customer Configuration

Database Initialization

Initial Data Seeding

Migrations

Create Migration

Apply Migration

Auto-Migration in Development

Docker Compose Setup

Query Examples

Basic Queries

Complex Queries

Performance Tips

Use Projections

AsNoTracking for Read-Only

Batch Operations

Troubleshooting

Connection Issues

View Applied Migrations

Reset Database

Related Resources