Databases, data movement & protection

// EF Core 10: vector column + similarity search on Azure SQL / SQL Server 2025
public class Doc
{
    public int Id { get; set; }
    public string Text { get; set; } = "";
    [Column(TypeName = "vector(1536)")] public SqlVector<float> Embedding { get; set; }
}

var top3 = await db.Docs
    .OrderBy(d => EF.Functions.VectorDistance("cosine", d.Embedding, queryVector))
    .Take(3)
    .ToListAsync();

public class Account
{
    public int Id { get; set; }
    public decimal Balance { get; set; }
    [Timestamp] public byte[] RowVersion { get; set; } = [];   // optimistic concurrency
}

// Transfer: both updates commit together, or neither does
await using var tx = await db.Database.BeginTransactionAsync(IsolationLevel.Snapshot, ct);
var from = await db.Accounts.SingleAsync(a => a.Id == fromId, ct);
var to   = await db.Accounts.SingleAsync(a => a.Id == toId, ct);
from.Balance -= amount;
to.Balance   += amount;
await db.SaveChangesAsync(ct);   // throws DbUpdateConcurrencyException if someone else changed a row
await tx.CommitAsync(ct);

Snapshot needs ALLOW_SNAPSHOT_ISOLATION ON in SQL Server (Azure SQL has it on). With EnableRetryOnFailure, wrap manual transactions in db.Database.CreateExecutionStrategy().ExecuteAsync(…).

modelBuilder.Entity<Payment>(b =>
{
    // Composite: helps WHERE CustomerId = @c ORDER BY CreatedAt (leftmost-prefix rule)
    b.HasIndex(p => new { p.CustomerId, p.CreatedAt })
     .IncludeProperties(p => new { p.Amount, p.Status });       // covering: no key lookups

    b.HasIndex(p => p.Reference).IsUnique();
    b.HasIndex(p => p.Status).HasFilter("[Status] = 'Pending'"); // small, targeted index
});
// then: dotnet ef migrations add AddPaymentIndexes

Cosmos DB indexes every property by default. Tune it with IndexingPolicy on the container (Microsoft.Azure.Cosmos) to exclude paths you never query and cut write RU cost.

Guid paymentId = Guid.CreateVersion7();          // .NET 9+: UUID v7, time-ordered

var generator = new IdGenerator(7);              // IdGen: worker ID 7 (unique per node)
long orderId = generator.CreateId();             // 64-bit Snowflake-style

Ulid traceId = Ulid.NewUlid();                   // Cysharp Ulid: 26-char sortable text

// EF Core: Hi/Lo on SQL Server - one round trip reserves a block of IDs
modelBuilder.HasSequence<long>("OrderIds").IncrementsBy(10);
modelBuilder.Entity<Order>().Property(o => o.Id).UseHiLo("OrderIds");

SQL Server sorts uniqueidentifier by its last bytes first, so Guid.CreateVersion7() values are not in index order there. On SQL Server, let EF Core generate GUID keys (its default generator is SQL Server-friendly) or use NEWSEQUENTIALID(), or use BIGINT. On PostgreSQL, UUID v7 works well, and PostgreSQL 18 has a built-in uuidv7().

var search = new SearchClient(new Uri(endpoint), "products", new DefaultAzureCredential());

var options = new SearchOptions
{
    QueryType = SearchQueryType.Full,             // Lucene syntax: enables fuzzy ~1
    Filter = "segment eq 'personal'",
    Size = 10
};
options.Facets.Add("category");                   // e.g. Cards (12), Loans (4)

SearchResults<Product> results = await search.SearchAsync<Product>("platnum~1 card", options);
await foreach (var hit in results.GetResultsAsync())
    Console.WriteLine($"{hit.Score:F2} {hit.Document.Name}");

// EF Core + SQL Server geography. Remember: X = longitude, Y = latitude
builder.Services.AddDbContext<AppDbContext>(o =>
    o.UseSqlServer(cs, sql => sql.UseNetTopologySuite()));

public class Atm { public int Id { get; set; } public Point Location { get; set; } = null!; }

var here = new Point(151.2061, -33.8732) { SRID = 4326 };
var nearest = await db.Atms
    .Where(a => a.Location.IsWithinDistance(here, 1000))       // metres on geography
    .OrderBy(a => a.Location.Distance(here))
    .Take(5)
    .ToListAsync(ct);

// Redis: the same idea, in memory
await redis.GeoAddAsync("atms", 151.2061, -33.8732, "atm:42");
var near = await redis.GeoSearchAsync("atms", 151.2093, -33.8688,
    new GeoSearchCircle(1, GeoUnit.Kilometers), count: 5);

var cs = builder.Configuration.GetConnectionString("sql");

// Writes go to the primary
builder.Services.AddDbContextPool<AppDbContext>(o => o.UseSqlServer(cs));

// Reads go to a readable secondary, with no change tracking
builder.Services.AddDbContextPool<ReadDbContext>(o => o
    .UseSqlServer(cs + ";ApplicationIntent=ReadOnly")
    .UseQueryTrackingBehavior(QueryTrackingBehavior.NoTracking));

Replicas lag. After a write, read your own data from the primary (or cache it) to avoid "my update disappeared" bugs.

var service = new BlobServiceClient(new Uri(accountUrl), new DefaultAzureCredential());
var blob = service.GetBlobContainerClient("statements").GetBlobClient($"{accountId}/2026-10.pdf");

await blob.UploadAsync(pdfStream, new BlobUploadOptions { AccessTier = AccessTier.Hot });

// Short-lived, read-only link signed with Entra ID (user delegation SAS)
var expires = DateTimeOffset.UtcNow.AddMinutes(15);
UserDelegationKey key = await service.GetUserDelegationKeyAsync(DateTimeOffset.UtcNow, expires);
var sas = new BlobSasBuilder(BlobSasPermissions.Read, expires)
{
    BlobContainerName = blob.BlobContainerName,
    BlobName = blob.Name,
    Resource = "b"                                   // a single blob
};
Uri link = new BlobUriBuilder(blob.Uri) { Sas = sas.ToSasQueryParameters(key, service.AccountName) }.ToUri();

The identity requesting the user delegation key needs the "Storage Blob Delegator" role (or a data role that includes it), and the SAS can never grant more than that identity's own access.

The "0 errors" part needs a scheduled restore test: restore to a scratch database in a pipeline, then run row counts and checksums against it.

// Bulk-load history from the legacy DB into the new one, streaming row by row
await using var cmd = new SqlCommand("SELECT Id, Name, Balance FROM dbo.Accounts", legacyConn);
await using var source = await cmd.ExecuteReaderAsync(ct);

using var bulk = new SqlBulkCopy(targetConn)
{
    DestinationTableName = "dbo.Accounts",
    BatchSize = 10_000,
    EnableStreaming = true
};
await bulk.WriteToServerAsync(source, ct);
// then reconcile: compare COUNT(*) and SUM(Balance) on both sides

Avoid Database.Migrate() at app start-up in production. Run the bundle once from the pipeline, so many instances don't race and the app doesn't need DDL rights.

// ETL in one process: extract CSV, mask PII before it lands, load in batches
var channel = Channel.CreateBounded<Txn>(10_000);          // back-pressure

var extract = Task.Run(async () =>
{
    using var csv = new CsvReader(File.OpenText(path), CultureInfo.InvariantCulture);
    await foreach (var row in csv.GetRecordsAsync<Txn>(ct))
        await channel.Writer.WriteAsync(row with { CardNumber = Mask(row.CardNumber) }, ct);
    channel.Writer.Complete();
});

var batch = new List<Txn>(5_000);
await foreach (var txn in channel.Reader.ReadAllAsync(ct))
{
    batch.Add(txn);
    if (batch.Count == 5_000) { await LoadAsync(batch, ct); batch.Clear(); }  // SqlBulkCopy
}
if (batch.Count > 0) await LoadAsync(batch, ct);
await extract;

Don't start new work on .NET for Apache Spark; it is no longer maintained. For Spark, use the platform (Fabric, Databricks, Synapse) and trigger it from .NET.

// Cosmos DB change feed: react to every insert/update, e.g. invalidate a cache
var processor = ordersContainer
    .GetChangeFeedProcessorBuilder<Order>("order-projector", HandleChangesAsync)
    .WithInstanceName(Environment.MachineName)
    .WithLeaseContainer(leasesContainer)
    .Build();
await processor.StartAsync();

async Task HandleChangesAsync(ChangeFeedProcessorContext context,
    IReadOnlyCollection<Order> changes, CancellationToken ct)
{
    foreach (var order in changes)
        await cache.RemoveAsync($"order:{order.Id}", ct);
}

The default change feed mode doesn't show deletes. Use a soft-delete flag plus TTL, or the newer "all versions and deletes" mode where your account supports it.

// Quick check over the silver layer without a cluster
using var conn = new DuckDBConnection("DataSource=:memory:");
conn.Open();
using var cmd = conn.CreateCommand();
cmd.CommandText = """
    SELECT merchant, SUM(amount) AS total
    FROM read_parquet('silver/transactions/*.parquet')
    GROUP BY merchant ORDER BY total DESC LIMIT 10
    """;
using var reader = cmd.ExecuteReader();

There is no official Microsoft Delta Lake or Iceberg library for .NET. Land Parquet from .NET, and let Fabric, Databricks or Synapse Spark manage the ACID tables.