// CODE FIRST — write the class, EF creates the table
public class Product
{
    public int    Id    { get; set; }
    public string Name  { get; set; }
    public decimal Price { get; set; }
}

public class AppDbContext : DbContext
{
    public DbSet<Product> Products { get; set; }
}

// DATABASE FIRST — run in Package Manager Console:
// Scaffold-DbContext "..." Microsoft.EntityFrameworkCore.SqlServer
// (or Add → New Item → ADO.NET Entity Data Model → EF Designer from database)

// MODEL FIRST — right-click EDMX designer → Generate Database from Model

// Conventions in action (no configuration needed)
public class Order
{
    public int       OrderId    { get; set; } // PK by convention (TypeNameId)
    public string    Reference  { get; set; } // NVARCHAR(MAX)
    public decimal   Total      { get; set; } // DECIMAL(18,2)
    public DateTime  OrderDate  { get; set; } // DATETIME2
    public bool      IsShipped  { get; set; } // BIT
    public int?      CustomerId { get; set; } // nullable FK by convention
    public Customer  Customer   { get; set; } // navigation property
}

// Override convention with annotation
[Table("SalesOrders")]
public class Order
{
    [Key]
    [Column("order_id")]
    public int OrderId { get; set; }

    [MaxLength(50), Required]
    public string Reference { get; set; }
}

// EF6 — .NET Framework 4.x, install via NuGet:
// Install-Package EntityFramework

// EF6 features NOT in EF Core:
// • EDMX designer (Database First visual model)
// • ObjectContext API
// • Stored procedure mapping in EDMX
// • Some Oracle/Informix providers

// EF Core advantages over EF6:
// • Batch INSERT/UPDATE (fewer round-trips)
// • Shadow properties, owned entities
// • Table splitting, keyless entities
// • Global query filters
// • Cross-platform (Linux/macOS)

// Both share: DbContext, DbSet, LINQ, migrations, Fluent API

// 1. Package Manager Console:
//    Install-Package EntityFramework

// 2. Model class
public class Customer
{
    public int    Id    { get; set; }
    public string Name  { get; set; }
    public string Email { get; set; }
}

// 3. DbContext
public class AppDbContext : DbContext
{
    // Parameterless ctor → looks for "AppDbContext" in App.config
    public AppDbContext() : base("AppDbContext") { }

    public DbSet<Customer> Customers { get; set; }
}

// 4. App.config
// <connectionStrings>
//   <add name="AppDbContext"
//        connectionString="Server=(localdb)\mssqllocaldb;Database=MyApp;Integrated Security=true"
//        providerName="System.Data.SqlClient" />
// </connectionStrings>

// 5. Use it
using (var ctx = new AppDbContext())
{
    ctx.Customers.Add(new Customer { Name = "Alice", Email = "alice@example.com" });
    ctx.SaveChanges();
}

// ✅ Short-lived context — one unit of work per using block
public void PlaceOrder(int customerId, decimal total)
{
    using (var ctx = new AppDbContext())
    {
        var order = new Order
        {
            CustomerId = customerId,
            Total      = total,
            CreatedAt  = DateTime.UtcNow
        };
        ctx.Orders.Add(order);
        ctx.SaveChanges(); // commits transaction
    } // Dispose() called here — connection returned to pool
}

// ❌ Anti-pattern: shared long-lived context
private static AppDbContext _ctx = new AppDbContext(); // grows forever!

// ❌ Anti-pattern: context per class (not per request/unit-of-work)
public class OrderService
{
    private AppDbContext _ctx = new AppDbContext(); // never disposed
}

<!-- App.config -->
<connectionStrings>
  <add name="MyDb"
       connectionString="Server=.\SQLEXPRESS;Database=Shop;Integrated Security=true"
       providerName="System.Data.SqlClient" />
</connectionStrings>

<entityFramework>
  <defaultConnectionFactory
      type="System.Data.Entity.Infrastructure.LocalDbConnectionFactory,
            EntityFramework">
    <parameters>
      <parameter value="mssqllocaldb" />
    </parameters>
  </defaultConnectionFactory>
  <providers>
    <provider invariantName="System.Data.SqlClient"
              type="System.Data.Entity.SqlServer.SqlProviderServices,
                    EntityFramework.SqlServer" />
  </providers>
</entityFramework>

// By name (reads App.config)
public class AppDbContext : DbContext
{
    public AppDbContext() : base("MyDb") { }
}

// By full connection string (programmatic)
public class AppDbContext : DbContext
{
    public AppDbContext(string connStr) : base(connStr) { }
}

var ctx = new AppDbContext(
    "Server=prod-sql;Database=Shop;User Id=app;Password=secret;");

// Modern EF6 — DbContext (recommended)
public class ShopContext : DbContext
{
    public DbSet<Product> Products { get; set; }
}

using (var ctx = new ShopContext())
{
    var products = ctx.Products.Where(p => p.IsActive).ToList();
}

// Legacy ObjectContext (avoid in new code)
using (var ctx = new ShopEntities()) // generated from EDMX
{
    var products = ctx.Products
        .Where(p => p.IsActive)
        .ToList();
}

// Access ObjectContext from DbContext (for advanced features)
using (var ctx = new ShopContext())
{
    var objCtx = ((IObjectContextAdapter)ctx).ObjectContext;
    objCtx.CommandTimeout = 120; // set command timeout
}

public class Product
{
    public int    Id    { get; set; }
    public string Name  { get; set; }
    public decimal Price { get; set; }

    [Timestamp]
    public byte[] RowVersion { get; set; } // auto-managed by SQL Server
}

// Handling concurrency conflict
try
{
    using (var ctx = new AppDbContext())
    {
        var product = ctx.Products.Find(1);
        product.Price = 99.99m;
        ctx.SaveChanges(); // throws if another user changed the row
    }
}
catch (DbUpdateConcurrencyException ex)
{
    var entry   = ex.Entries.Single();
    var dbVals  = entry.GetDatabaseValues(); // current DB values
    var current = entry.CurrentValues;       // your proposed values

    // Strategy: refresh with DB values and discard user changes
    entry.OriginalValues.SetValues(dbVals);
    // Or: prompt user to re-review and resubmit
}

public class ShopContext : DbContext
{
    // Constructor that accepts connection-string name OR full string
    public ShopContext() : base("ShopDb") { }
    public ShopContext(string nameOrConnStr) : base(nameOrConnStr) { }

    public DbSet<Product>  Products  { get; set; }
    public DbSet<Order>    Orders    { get; set; }
    public DbSet<Customer> Customers { get; set; }
    public DbSet<Category> Categories { get; set; }

    protected override void OnModelCreating(DbModelBuilder modelBuilder)
    {
        // Remove pluralizing table name convention
        modelBuilder.Conventions.Remove<PluralizingTableNameConvention>();

        // Apply all entity configurations from the same assembly
        modelBuilder.Configurations.AddFromAssembly(typeof(ShopContext).Assembly);

        base.OnModelCreating(modelBuilder);
    }
}

// Separate configuration file: ProductConfig.cs
public class ProductConfig : EntityTypeConfiguration<Product>
{
    public ProductConfig()
    {
        ToTable("Products");

        HasKey(p => p.Id);

        Property(p => p.Name)
            .IsRequired()
            .HasMaxLength(200)
            .HasColumnName("product_name");

        Property(p => p.Price)
            .HasPrecision(18, 4);

        Property(p => p.RowVersion)
            .IsRowVersion();

        HasMany(p => p.Tags)
            .WithMany()
            .Map(m => m.ToTable("ProductTags"));
    }
}

// Register in DbContext
protected override void OnModelCreating(DbModelBuilder mb)
{
    mb.Configurations.Add(new ProductConfig());
    mb.Configurations.Add(new OrderConfig());
    // OR: auto-discover all in same assembly
    mb.Configurations.AddFromAssembly(GetType().Assembly);
}

using (var ctx = new ShopContext())
{
    // First call — hits DB, entity cached in change tracker
    var p1 = ctx.Products.Find(42);

    // Second call with same key — returns from cache, NO SQL query
    var p2 = ctx.Products.Find(42); // same reference as p1

    // FirstOrDefault ALWAYS queries the DB
    var p3 = ctx.Products.FirstOrDefault(p => p.Id == 42); // SQL each time

    // Find with composite key
    var orderLine = ctx.OrderLines.Find(orderId, productId);

    // Returns null if not found (never throws)
    var missing = ctx.Products.Find(9999); // null
}

// ⚠️ Find only works with primary key — use FirstOrDefault for other columns
var byEmail = ctx.Users.FirstOrDefault(u => u.Email == "a@b.com");

// Disconnected update — entity came from a web form or API
var product = new Product { Id = 5, Name = "Updated", Price = 49.99m };

using (var ctx = new ShopContext())
{
    ctx.Entry(product).State = EntityState.Modified;
    ctx.SaveChanges(); // UPDATE all columns
}

// Update only specific columns (avoid SELECT + dirty tracking)
using (var ctx = new ShopContext())
{
    ctx.Products.Attach(product); // State = Unchanged
    ctx.Entry(product).Property(p => p.Price).IsModified = true;
    ctx.SaveChanges(); // UPDATE only Price column
}

// Inspect current vs. original values
using (var ctx = new ShopContext())
{
    var p = ctx.Products.Find(5);
    p.Price = 100m;
    var entry = ctx.Entry(p);
    Console.WriteLine(entry.Property(x => x.Price).OriginalValue); // old price
    Console.WriteLine(entry.Property(x => x.Price).CurrentValue);  // 100
    Console.WriteLine(entry.State); // Modified
}

using (var ctx = new ShopContext())
{
    // Raw SQL bulk update — no entity loading
    ctx.Database.ExecuteSqlCommand(
        "UPDATE Products SET IsActive = 0 WHERE CategoryId = @p0", 5);

    // Raw SQL bulk delete
    ctx.Database.ExecuteSqlCommand(
        "DELETE FROM Logs WHERE CreatedAt < @p0",
        DateTime.UtcNow.AddMonths(-6));
}

// Using EntityFramework.Extended (Install-Package EntityFramework.Extended)
// ctx.Products
//     .Where(p => p.CategoryId == 5)
//     .Update(p => new Product { IsActive = false });

// Using Z.EntityFramework.Extensions (commercial)
// ctx.Products
//     .Where(p => !p.IsActive)
//     .Delete();

// Batch insert (standard EF6 — one INSERT per row)
var products = Enumerable.Range(1, 1000)
    .Select(i => new Product { Name = $"P{i}", Price = i })
    .ToList();
ctx.Products.AddRange(products);
ctx.SaveChanges(); // 1000 INSERTs (slow for large sets)

public interface ISoftDeletable { bool IsDeleted { get; set; } }

public class Product : ISoftDeletable
{
    public int    Id        { get; set; }
    public string Name      { get; set; }
    public bool   IsDeleted { get; set; }
}

public class ShopContext : DbContext
{
    public DbSet<Product> Products { get; set; }

    public override int SaveChanges()
    {
        // Intercept deletes on soft-deletable entities
        foreach (var entry in ChangeTracker.Entries()
            .Where(e => e.State == EntityState.Deleted
                     && e.Entity is ISoftDeletable))
        {
            entry.State = EntityState.Modified;
            ((ISoftDeletable)entry.Entity).IsDeleted = true;
        }
        return base.SaveChanges();
    }
}

// Querying — must always add the filter manually (EF6 has no global filter)
var active = ctx.Products.Where(p => !p.IsDeleted).ToList();

try
{
    using (var ctx = new ShopContext())
    {
        ctx.Products.Add(new Product { Id = 1, Name = "Duplicate" });
        ctx.SaveChanges();
    }
}
catch (DbUpdateException ex)
{
    var inner = ex.InnerException?.InnerException as System.Data.SqlClient.SqlException;
    if (inner != null)
    {
        switch (inner.Number)
        {
            case 2627: // Unique constraint violation
            case 2601:
                Console.WriteLine("Duplicate key: " + inner.Message);
                break;
            case 547:  // FK constraint violation
                Console.WriteLine("FK violation: " + inner.Message);
                break;
            default:
                throw;
        }
    }
}
catch (DbEntityValidationException ex)
{
    // Data annotation validation failures (before hitting DB)
    foreach (var err in ex.EntityValidationErrors
        .SelectMany(e => e.ValidationErrors))
        Console.WriteLine($"{err.PropertyName}: {err.ErrorMessage}");
}

// ASP.NET MVC 5 controller (async action)
public class ProductsController : Controller
{
    public async Task<ActionResult> Index()
    {
        using (var ctx = new ShopContext())
        {
            var products = await ctx.Products
                .Where(p => p.IsActive)
                .OrderBy(p => p.Name)
                .ToListAsync(); // non-blocking DB call

            return View(products);
        }
    }

    [HttpPost]
    public async Task<ActionResult> Create(Product product)
    {
        if (!ModelState.IsValid) return View(product);

        using (var ctx = new ShopContext())
        {
            ctx.Products.Add(product);
            await ctx.SaveChangesAsync(); // non-blocking save
        }
        return RedirectToAction("Index");
    }
}

// Async scalar methods
using (var ctx = new ShopContext())
{
    int    count = await ctx.Products.CountAsync();
    bool   any   = await ctx.Products.AnyAsync(p => p.Price > 1000);
    Product? p   = await ctx.Products.FirstOrDefaultAsync(x => x.Id == 1);
}

<connectionStrings>
  <!-- Windows Authentication (Integrated Security) -->
  <add name="WinAuth"
       connectionString="Server=MYSERVER;Database=ShopDb;Integrated Security=true;MultipleActiveResultSets=true"
       providerName="System.Data.SqlClient" />

  <!-- SQL Authentication -->
  <add name="SqlAuth"
       connectionString="Server=MYSERVER;Database=ShopDb;User Id=appuser;Password=P@ssw0rd;MultipleActiveResultSets=true"
       providerName="System.Data.SqlClient" />

  <!-- Named Instance -->
  <add name="NamedInst"
       connectionString="Server=MYSERVER\SQLEXPRESS;Database=ShopDb;Integrated Security=true"
       providerName="System.Data.SqlClient" />

  <!-- Port override -->
  <add name="CustomPort"
       connectionString="Server=MYSERVER,1434;Database=ShopDb;Integrated Security=true"
       providerName="System.Data.SqlClient" />
</connectionStrings>

<!-- App.config — LocalDB connection strings -->
<connectionStrings>
  <!-- SQL Server 2019+ LocalDB -->
  <add name="ShopDb"
       connectionString="Server=(localdb)\mssqllocaldb;Database=ShopDb;
                         Trusted_Connection=true;MultipleActiveResultSets=true"
       providerName="System.Data.SqlClient" />

  <!-- Attaches an .mdf file from the project (AttachDbFilename) -->
  <add name="ShopFile"
       connectionString="Server=(localdb)\mssqllocaldb;
                         AttachDbFilename=|DataDirectory|\Shop.mdf;
                         Database=ShopDb;Trusted_Connection=true"
       providerName="System.Data.SqlClient" />
</connectionStrings>

# Package Manager Console — create and apply migration
Enable-Migrations
Add-Migration InitialCreate
Update-Database  # creates the LocalDB file and tables

<connectionStrings>
  <add name="AzureSql"
       connectionString="Server=tcp:myserver.database.windows.net,1433;
                         Database=ShopDb;
                         User ID=appuser@myserver;
                         Password=P@ssw0rd!;
                         Encrypt=true;
                         TrustServerCertificate=false;
                         Connection Timeout=30;
                         MultipleActiveResultSets=true"
       providerName="System.Data.SqlClient" />
</connectionStrings>

// Enable Azure SQL transient-fault retry
public class ShopContext : DbContext
{
    public ShopContext() : base("AzureSql")
    {
        // SQL Azure Execution Strategy retries on transient errors
        Configuration.SetExecutionStrategy(
            "System.Data.SqlClient",
            () => new SqlAzureExecutionStrategy(
                maxRetryCount: 5,
                maxDelay: TimeSpan.FromSeconds(30)));
    }
}

using (var ctx = new ShopContext())
{
    // Query returning tracked entities
    var products = ctx.Products
        .SqlQuery("SELECT * FROM Products WHERE Price > @p0", 50m)
        .ToList();

    // Query returning arbitrary DTO (not tracked)
    var summaries = ctx.Database
        .SqlQuery<ProductSummary>(
            "SELECT Id, Name, Price FROM Products WHERE IsActive = 1")
        .ToList();

    // Scalar value
    var count = ctx.Database
        .SqlQuery<int>("SELECT COUNT(*) FROM Orders")
        .Single();

    // Non-query (UPDATE / DELETE / stored proc)
    int rows = ctx.Database.ExecuteSqlCommand(
        "UPDATE Products SET Price = Price * @p0 WHERE CategoryId = @p1",
        1.1m, 3);

    // Named parameters (SqlParameter)
    ctx.Database.ExecuteSqlCommand(
        "EXEC sp_ArchiveOrders @cutoff",
        new System.Data.SqlClient.SqlParameter("@cutoff",
            DateTime.UtcNow.AddYears(-2)));
}

using (var ctx = new ShopContext())
{
    // Output parameter
    var newIdParam = new SqlParameter
    {
        ParameterName = "@NewId",
        SqlDbType     = SqlDbType.Int,
        Direction     = ParameterDirection.Output
    };

    ctx.Database.ExecuteSqlCommand(
        "EXEC sp_CreateOrder @CustomerId, @Total, @NewId OUTPUT",
        new SqlParameter("@CustomerId", 1),
        new SqlParameter("@Total",      199.99m),
        newIdParam);

    int newOrderId = (int)newIdParam.Value;
    Console.WriteLine($"New order ID: {newOrderId}");

    // Return value from stored proc
    var retVal = new SqlParameter
    {
        ParameterName = "@RetVal",
        SqlDbType     = SqlDbType.Int,
        Direction     = ParameterDirection.ReturnValue
    };
    ctx.Database.ExecuteSqlCommand("EXEC sp_CheckInventory", retVal);
    int status = (int)retVal.Value;
}

<connectionStrings>
  <add name="ShopDb"
       connectionString="Server=MYSERVER;Database=ShopDb;
                         Integrated Security=true;
                         Min Pool Size=5;
                         Max Pool Size=100;
                         Connection Lifetime=300;
                         Connect Timeout=30;
                         Pooling=true;
                         MultipleActiveResultSets=true"
       providerName="System.Data.SqlClient" />
</connectionStrings>

// Monitor pool usage — clear the pool (e.g. after server restart)
System.Data.SqlClient.SqlConnection.ClearAllPools();

// Per-connection-string pool clear
var conn = new SqlConnection(connStr);
SqlConnection.ClearPool(conn);

// Disable pooling (testing only)
// connectionString="...;Pooling=false"

// Custom configuration class — discovered by EF6 automatically
public class MyDbConfiguration : DbConfiguration
{
    public MyDbConfiguration()
    {
        // Built-in Azure SQL retry strategy
        SetExecutionStrategy(
            "System.Data.SqlClient",
            () => new SqlAzureExecutionStrategy(
                maxRetryCount: 3,
                maxDelay:      TimeSpan.FromSeconds(15)));
    }
}

// Custom retry strategy for on-prem SQL Server
public class CustomRetryStrategy : DbExecutionStrategy
{
    private static readonly int[] _transientErrors = { 1205, 1204, 49918 };

    public CustomRetryStrategy() : base(maxRetryCount: 3,
        maxDelay: TimeSpan.FromSeconds(10)) { }

    protected override bool ShouldRetryOn(Exception ex)
    {
        var sqlEx = ex?.InnerException as SqlException;
        return sqlEx != null && _transientErrors.Contains(sqlEx.Number);
    }
}

using (var ctx = new ShopContext())
using (var tx  = ctx.Database.BeginTransaction())
{
    try
    {
        // Step 1
        ctx.Orders.Add(new Order { CustomerId = 1, Total = 250m });
        ctx.SaveChanges();

        // Step 2
        ctx.Database.ExecuteSqlCommand(
            "UPDATE Inventory SET Stock = Stock - 1 WHERE ProductId = @p0", 42);

        tx.Commit();
    }
    catch
    {
        tx.Rollback();
        throw;
    }
}

// With isolation level
using (var tx = ctx.Database.BeginTransaction(
    System.Data.IsolationLevel.Serializable))
{
    // ...
}

# Install ODP.NET Managed + EF6 provider
Install-Package Oracle.ManagedDataAccess.EntityFramework

<!-- App.config (auto-added by the NuGet package) -->
<connectionStrings>
  <!-- Easy Connect format: //host:port/service -->
  <add name="OracleDb"
       connectionString="DATA SOURCE=//oraclehost:1521/ORCL;
                         USER ID=shopuser;PASSWORD=secret;"
       providerName="Oracle.ManagedDataAccess.Client" />

  <!-- TNS alias (requires tnsnames.ora or TNS_ADMIN env var) -->
  <add name="OracleTns"
       connectionString="DATA SOURCE=ORCL;USER ID=shopuser;PASSWORD=secret;"
       providerName="Oracle.ManagedDataAccess.Client" />
</connectionStrings>

<oracle.manageddataaccess.client>
  <version number="*">
    <dataSources>
      <dataSource alias="ORCL"
        descriptor="(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=oraclehost)(PORT=1521))
                    (CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=ORCL)))" />
    </dataSources>
  </version>
</oracle.manageddataaccess.client>

public class OracleShopContext : DbContext
{
    public OracleShopContext() : base("OracleDb") { }
    public DbSet<Product> Products { get; set; }
}

-- Oracle 11g: sequence + trigger approach
CREATE SEQUENCE PRODUCT_SEQ START WITH 1 INCREMENT BY 1;

CREATE OR REPLACE TRIGGER PRODUCT_BI
  BEFORE INSERT ON "PRODUCTS"
  FOR EACH ROW
BEGIN
  IF :NEW."Id" IS NULL THEN
    SELECT PRODUCT_SEQ.NEXTVAL INTO :NEW."Id" FROM DUAL;
  END IF;
END;

-- Oracle 12c+: identity column (recommended)
CREATE TABLE "PRODUCTS" (
  "Id"    NUMBER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  "Name"  NVARCHAR2(200) NOT NULL,
  "Price" NUMBER(18,4)
);

public class Product
{
    // Tell EF the DB generates the ID (sequence/trigger or IDENTITY)
    [DatabaseGenerated(DatabaseGeneratedOption.Identity)]
    public int Id { get; set; }

    public string Name  { get; set; }
    public decimal Price { get; set; }
}

// Fluent API equivalent
modelBuilder.Entity<Product>()
    .Property(p => p.Id)
    .HasDatabaseGeneratedOption(DatabaseGeneratedOption.Identity);

// Data annotation — Oracle schema
[Table("PRODUCTS", Schema = "SHOPUSER")]
public class Product
{
    [Column("PRODUCT_ID")]
    public int Id { get; set; }

    [Column("PRODUCT_NAME")]
    [MaxLength(200)]
    public string Name { get; set; }

    [Column("PRICE")]
    public decimal Price { get; set; }
}

// Fluent API equivalent
protected override void OnModelCreating(DbModelBuilder mb)
{
    mb.Entity<Product>()
      .ToTable("PRODUCTS", schemaName: "SHOPUSER");

    mb.Entity<Product>()
      .Property(p => p.Id).HasColumnName("PRODUCT_ID");

    mb.Entity<Product>()
      .Property(p => p.Name).HasColumnName("PRODUCT_NAME");
}

public class Order
{
    public int      Id        { get; set; }

    // Oracle DATE → DateTime (date + time, no sub-second)
    [Column("ORDER_DATE", TypeName = "DATE")]
    public DateTime OrderDate  { get; set; }

    // Oracle TIMESTAMP(6) → DateTime with microseconds
    [Column("CREATED_AT", TypeName = "TIMESTAMP")]
    public DateTime CreatedAt  { get; set; }

    // Oracle TIMESTAMP WITH TIME ZONE → DateTimeOffset
    [Column("MODIFIED_AT")]
    public DateTimeOffset? ModifiedAt { get; set; }
}

// Fluent API — override column type for Oracle
modelBuilder.Entity<Order>()
    .Property(o => o.OrderDate)
    .HasColumnType("DATE");

// Oracle NUMBER → decimal mapping (precision matters)
modelBuilder.Entity<Order>()
    .Property(o => o.Total)
    .HasColumnType("NUMBER")
    .HasPrecision(18, 4);

using Oracle.ManagedDataAccess.Client;

using (var ctx = new OracleShopContext())
{
    // Call a stored procedure with IN/OUT parameters
    var p_customer_id = new OracleParameter("p_customer_id",
        OracleDbType.Int32, 1, ParameterDirection.Input);

    var p_total = new OracleParameter("p_total",
        OracleDbType.Decimal) { Direction = ParameterDirection.Output };

    ctx.Database.ExecuteSqlCommand(
        "BEGIN SHOPUSER.GET_CUSTOMER_TOTAL(:p_customer_id, :p_total); END;",
        p_customer_id, p_total);

    decimal total = Convert.ToDecimal(p_total.Value);

    // Call a function returning a SYS_REFCURSOR
    var results = ctx.Database.SqlQuery<ProductDto>(
        "SELECT * FROM TABLE(SHOPUSER.GET_ACTIVE_PRODUCTS(:p_cat))",
        new OracleParameter("p_cat", "Electronics"))
        .ToList();
}

<connectionStrings>
  <add name="OracleDb"
       connectionString="DATA SOURCE=//oraclehost:1521/ORCL;
                         USER ID=shopuser;PASSWORD=secret;
                         Min Pool Size=2;
                         Max Pool Size=20;
                         Connection Lifetime=300;
                         Connection Timeout=30;
                         Pooling=true;
                         Validate Connection=true"
       providerName="Oracle.ManagedDataAccess.Client" />
</connectionStrings>

// Clear pool programmatically
OracleConnection.ClearAllPools();

// Validate connections on checkout (detect stale connections)
// Add "Validate Connection=true" to the connection string

// ✅ Safe: uppercase names match Oracle's default storage
[Table("PRODUCTS")]
public class Product
{
    [Column("ID")]
    public int Id { get; set; }

    [Column("PRODUCT_NAME")]
    public string Name { get; set; }
}

// Fluent API — uppercase everywhere
modelBuilder.Entity<Product>()
    .ToTable("PRODUCTS")
    .HasKey(p => p.Id);

modelBuilder.Entity<Product>()
    .Property(p => p.Name)
    .HasColumnName("PRODUCT_NAME");

// Remove the PluralizingTableNameConvention (PRODUCTS not Products)
modelBuilder.Conventions
    .Remove<PluralizingTableNameConvention>();

public class Document
{
    public int Id { get; set; }

    // Oracle CLOB — large text
    [Column("CONTENT", TypeName = "CLOB")]
    public string Content { get; set; }

    // Oracle BLOB — binary data
    [Column("FILE_DATA", TypeName = "BLOB")]
    public byte[] FileData { get; set; }
}

// Fluent API
modelBuilder.Entity<Document>()
    .Property(d => d.Content)
    .HasColumnType("CLOB");

modelBuilder.Entity<Document>()
    .Property(d => d.FileData)
    .HasColumnType("BLOB");

// ⚠️ For very large LOBs, use OracleBlob directly:
// var cmd = conn.CreateCommand();
// cmd.CommandText = "SELECT FILE_DATA FROM DOCUMENTS WHERE ID=:p0";
// using var reader = cmd.ExecuteReader();
// reader.Read();
// var blob = reader.GetOracleBlob(0);
// blob.CopyTo(fileStream);

Install-Package System.Data.SQLite.EF6

<!-- App.config -->
<connectionStrings>
  <!-- Absolute path -->
  <add name="SqliteDb"
       connectionString="Data Source=C:\Data\shop.db;Version=3;"
       providerName="System.Data.SQLite.EF6" />

  <!-- Relative to app folder -->
  <add name="SqliteRel"
       connectionString="Data Source=|DataDirectory|\shop.db;Version=3;"
       providerName="System.Data.SQLite.EF6" />
</connectionStrings>

<system.data>
  <DbProviderFactories>
    <remove invariant="System.Data.SQLite.EF6" />
    <add name="SQLite Data Provider (Entity Framework 6)"
         invariant="System.Data.SQLite.EF6"
         description="ADO.NET provider for SQLite (Entity Framework 6)"
         type="System.Data.SQLite.EF6.SQLiteProviderFactory, System.Data.SQLite.EF6" />
  </DbProviderFactories>
</system.data>

public class SqliteContext : DbContext
{
    public SqliteContext() : base("SqliteDb") { }
    public DbSet<Product> Products { get; set; }
}

// Limitations summary:
// ❌ AlterColumn()     — NOT supported
// ❌ DropColumn()      — NOT supported (workaround: recreate table)
// ❌ AddColumn() NOT NULL without default — NOT supported
// ✅ CreateTable()     — supported
// ✅ DropTable()       — supported
// ✅ CreateIndex()     — supported
// ✅ AddColumn() nullable or with default — supported

// Workaround for rename/change column in SQLite migration:
public override void Up()
{
    // 1. Create new table with desired schema
    CreateTable("dbo.Products_New", c => new
    {
        Id    = c.Int(nullable: false, identity: true),
        Title = c.String(maxLength: 200),   // renamed from "Name"
        Price = c.Decimal(precision: 18, scale: 2)
    })
    .PrimaryKey(t => t.Id);

    // 2. Copy data
    Sql("INSERT INTO Products_New (Id, Title, Price) SELECT Id, Name, Price FROM Products");

    // 3. Drop old table
    DropTable("dbo.Products");

    // 4. Rename new table
    RenameTable("dbo.Products_New", "Products");
}

// Test helper — in-memory SQLite for EF6
public class SqliteInMemoryContext : DbContext
{
    private static SQLiteConnection _sharedConn;

    public static SqliteInMemoryContext Create()
    {
        // Keep one connection open so the in-memory DB persists
        if (_sharedConn == null)
        {
            _sharedConn = new SQLiteConnection("FullUri=file::memory:?cache=shared;Version=3;");
            _sharedConn.Open();
        }
        return new SqliteInMemoryContext(_sharedConn);
    }

    private SqliteInMemoryContext(DbConnection conn)
        : base(conn, contextOwnsConnection: false) { }

    public DbSet<Product> Products { get; set; }
}

// Unit test (MSTest / xUnit)
[TestMethod]
public void CanAddAndQueryProduct()
{
    using (var ctx = SqliteInMemoryContext.Create())
    {
        ctx.Database.CreateIfNotExists();
        ctx.Products.Add(new Product { Name = "Test", Price = 9.99m });
        ctx.SaveChanges();

        var p = ctx.Products.FirstOrDefault(x => x.Name == "Test");
        Assert.IsNotNull(p);
        Assert.AreEqual(9.99m, p.Price);
    }
}

// App.xaml.cs (WPF startup)
protected override void OnStartup(StartupEventArgs e)
{
    base.OnStartup(e);

    // Set DataDirectory to executable folder
    AppDomain.CurrentDomain.SetData(
        "DataDirectory",
        AppDomain.CurrentDomain.BaseDirectory);

    // Create DB on first run (no migrations for simple desktop apps)
    Database.SetInitializer(
        new CreateDatabaseIfNotExists<AppDbContext>());

    // Warm up the context
    using (var ctx = new AppDbContext())
        ctx.Database.Initialize(force: false);
}

// App.config
// <add name="AppDb"
//      connectionString="Data Source=|DataDirectory|\app.db;Version=3;"
//      providerName="System.Data.SQLite.EF6" />

// Enable WAL mode for better concurrent reads
using (var ctx = new AppDbContext())
{
    ctx.Database.ExecuteSqlCommand("PRAGMA journal_mode=WAL;");
    ctx.Database.ExecuteSqlCommand("PRAGMA foreign_keys=ON;");
}

// SQLite Affinity → C# type mapping
// TEXT      → string
// INTEGER   → int, long, bool (0/1), DateTime (ticks)
// REAL      → double, float
// NUMERIC   → decimal
// BLOB      → byte[]

public class Product
{
    public int     Id        { get; set; } // INTEGER → int
    public string  Name      { get; set; } // TEXT    → string
    public decimal Price     { get; set; } // NUMERIC → decimal
    public bool    IsActive  { get; set; } // INTEGER (0/1) → bool
    public DateTime CreatedAt { get; set; } // TEXT (ISO8601) → DateTime

    // byte[] — stored as BLOB
    public byte[]  Thumbnail { get; set; }
}

// Fluent API — override column type
modelBuilder.Entity<Product>()
    .Property(p => p.Price)
    .HasColumnType("numeric"); // explicit affinity

// ⚠️ SQLite stores DateTime as TEXT by default (ISO8601)
// Set DateTimeKind to UTC to avoid timezone issues
// SQLiteConnectionStringBuilder: DateTimeKind=Utc

public class AppDbContext : DbContext
{
    public AppDbContext() : base("SqliteDb")
    {
        // Fired when the context is first used
        Database.Connection.StateChange += (s, e) =>
        {
            if (e.CurrentState == System.Data.ConnectionState.Open)
            {
                var conn = (SQLiteConnection)Database.Connection;
                using var cmd = conn.CreateCommand();

                // Enable Write-Ahead Logging (multiple readers + one writer)
                cmd.CommandText = "PRAGMA journal_mode=WAL;";
                cmd.ExecuteNonQuery();

                // Enforce FK constraints (off by default in SQLite)
                cmd.CommandText = "PRAGMA foreign_keys=ON;";
                cmd.ExecuteNonQuery();

                // Improve performance on write-heavy workloads
                cmd.CommandText = "PRAGMA synchronous=NORMAL;";
                cmd.ExecuteNonQuery();
            }
        };
    }
}

// Factory approach — reads from appSettings
public static class DbContextFactory
{
    public static AppDbContext Create()
    {
        string provider = ConfigurationManager.AppSettings["DbProvider"] ?? "sqlite";

        return provider == "sqlserver"
            ? new AppDbContext("SqlServerDb")
            : new AppDbContext("SqliteDb");
    }
}

// App.config (development)
// <appSettings>
//   <add key="DbProvider" value="sqlite" />
// </appSettings>

// App.Release.config transform (production SQL Server)
// <add key="DbProvider" value="sqlserver"
//      xdt:Transform="SetAttributes" xdt:Locator="Match(key)" />

// Separate migration configurations
// Migrations\SqliteMigrations\Configuration.cs
// Migrations\SqlServerMigrations\Configuration.cs

<!-- BinaryGUID=false: store as text "xxxxxxxx-xxxx-..." -->
<add name="SqliteDb"
     connectionString="Data Source=shop.db;Version=3;BinaryGUID=false;"
     providerName="System.Data.SQLite.EF6" />

public class Session
{
    [Key]
    [DatabaseGenerated(DatabaseGeneratedOption.None)]
    public Guid Id { get; set; } // stored as TEXT in SQLite

    public string UserId { get; set; }
}

// Generate GUID in code before inserting
var session = new Session
{
    Id     = Guid.NewGuid(), // must set manually (SQLite can't generate)
    UserId = "alice"
};
ctx.Sessions.Add(session);
ctx.SaveChanges();

Install-Package EntityFramework.SqlServerCompact

<!-- App.config -->
<connectionStrings>
  <add name="SqlCeDb"
       connectionString="Data Source=|DataDirectory|\Shop.sdf;Max Database Size=4091"
       providerName="System.Data.SqlServerCe.4.0" />
</connectionStrings>

<entityFramework>
  <defaultConnectionFactory
      type="System.Data.Entity.Infrastructure.SqlCeConnectionFactory,
            EntityFramework">
    <parameters>
      <parameter value="System.Data.SqlServerCe.4.0" />
    </parameters>
  </defaultConnectionFactory>
  <providers>
    <provider invariantName="System.Data.SqlServerCe.4.0"
              type="System.Data.Entity.SqlServerCompact.SqlCeProviderServices,
                    EntityFramework.SqlServerCompact" />
  </providers>
</entityFramework>

// SQL CE limitations — design around these:

// ❌ Stored procedures — not supported
// ctx.Database.ExecuteSqlCommand("EXEC sp_MyProc"); // will fail

// ❌ Views — not supported

// ❌ Skip() without Take() — must use Take() with Skip()
var page = ctx.Products
    .OrderBy(p => p.Id)
    .Skip(20)
    .Take(10) // ✅ Must always pair Skip with Take in SQL CE
    .ToList();

// ❌ GROUP BY with HAVING — use Where after grouping in memory
// ❌ Subqueries in some positions

// ✅ Workaround for complex queries: load + process in memory
var summary = ctx.Orders
    .AsNoTracking()
    .ToList() // load into memory
    .GroupBy(o => o.CustomerId)
    .Where(g => g.Sum(o => o.Total) > 1000) // HAVING equivalent
    .Select(g => new { CustomerId = g.Key, Total = g.Sum(o => o.Total) })
    .ToList();

// Migrations\Configuration.cs
internal sealed class Configuration : DbMigrationsConfiguration<AppDbContext>
{
    public Configuration()
    {
        AutomaticMigrationsEnabled = false;
        // Specify SQL CE provider
        SetSqlGenerator("System.Data.SqlServerCe.4.0",
            new SqlCeMigrationSqlGenerator());
    }

    protected override void Seed(AppDbContext context)
    {
        context.Categories.AddOrUpdate(
            c => c.Name,
            new Category { Name = "Electronics" },
            new Category { Name = "Books" }
        );
    }
}

// Package Manager Console
// Enable-Migrations
// Add-Migration InitialCreate
// Update-Database -Verbose

public interface IDbContextFactory
{
    AppDbContext Create();
}

public class SmartDbContextFactory : IDbContextFactory
{
    private readonly string _sqlServerConn;
    private readonly string _sqlCeConn;

    public SmartDbContextFactory(string sqlServer, string sqlCe)
    {
        _sqlServerConn = sqlServer;
        _sqlCeConn     = sqlCe;
    }

    public AppDbContext Create()
    {
        if (IsServerReachable())
        {
            Console.WriteLine("Online: using SQL Server");
            return new AppDbContext(_sqlServerConn);
        }
        Console.WriteLine("Offline: using SQL CE");
        return new AppDbContext(_sqlCeConn);
    }

    private bool IsServerReachable()
    {
        try
        {
            using var conn = new SqlConnection(_sqlServerConn);
            conn.Open();
            return true;
        }
        catch { return false; }
    }
}

<!-- Encrypted SQL CE connection string -->
<add name="SqlCeSecure"
     connectionString="Data Source=|DataDirectory|\Shop.sdf;
                       Password=MyStr0ngP@ssword;
                       Encrypt Database=TRUE"
     providerName="System.Data.SqlServerCe.4.0" />

// Create encrypted SDF programmatically
var engine = new SqlCeEngine(
    "Data Source=Shop.sdf;Password=MyP@ss;Encrypt Database=TRUE");
engine.CreateDatabase();

// Change password by compacting
var newConnStr = "Data Source=Shop.sdf;Password=NewP@ss;Encrypt Database=TRUE";
engine.Compact(newConnStr);

// ⚠️ Store the password securely — not in plain text App.config
// Use DPAPI, environment variables, or a secrets manager in production

using System.Data.SqlServerCe;

// Compact (shrink) — reclaims space from deleted rows
public static void CompactDatabase(string sdfPath, string password = null)
{
    string connStr = $"Data Source={sdfPath};" +
                     (password != null ? $"Password={password};" : "");
    using var engine = new SqlCeEngine(connStr);
    engine.Compact(null); // null = same file, same password
    Console.WriteLine("Database compacted successfully.");
}

// Repair — recovers from corruption (may lose some data)
public static void RepairDatabase(string sdfPath)
{
    string connStr = $"Data Source={sdfPath};";
    using var engine = new SqlCeEngine(connStr);
    engine.Repair(null, RepairOption.RecoverCorruptedRows);
    Console.WriteLine("Database repaired.");
}

// Schedule compact on app shutdown (runs in background)
AppDomain.CurrentDomain.ProcessExit += (s, e) =>
    CompactDatabase(AppDbPath);

// Set DataDirectory to AppData (writable by user, persists across updates)
protected override void OnStartup(StartupEventArgs e)
{
    base.OnStartup(e);

    string appData = Environment.GetFolderPath(
        Environment.SpecialFolder.ApplicationData);
    string appFolder = Path.Combine(appData, "MyApp");
    Directory.CreateDirectory(appFolder);

    // Point DataDirectory to user's AppData
    AppDomain.CurrentDomain.SetData("DataDirectory", appFolder);

    Database.SetInitializer(
        new MigrateDatabaseToLatestVersion<AppDbContext, Configuration>());

    using var ctx = new AppDbContext();
    ctx.Database.Initialize(force: false);
}

// Check current database file size
public static long GetDatabaseSizeBytes(string sdfPath)
    => new FileInfo(sdfPath).Length;

// Warn when approaching the 4 GB limit
public static void CheckSizeAndWarn(string sdfPath)
{
    const long MaxSizeBytes = 4L * 1024 * 1024 * 1024; // 4 GB
    long size = GetDatabaseSizeBytes(sdfPath);
    double pct = (double)size / MaxSizeBytes * 100;

    if (pct > 80)
        Console.WriteLine($"⚠ Database at {pct:F1}% of maximum size ({size / 1024 / 1024} MB)");
}

// Archive old data to reduce size
public static void ArchiveOldOrders(AppDbContext ctx, int months = 24)
{
    var cutoff = DateTime.UtcNow.AddMonths(-months);
    ctx.Database.ExecuteSqlCommand(
        "DELETE FROM Orders WHERE OrderDate < @p0", cutoff);
    // Then compact
    CompactDatabase(AppDbPath);
}

Install-Package MongoDB.Driver

// Connect to MongoDB — analogous to DbContext
var client   = new MongoClient("mongodb://localhost:27017");
var database = client.GetDatabase("ShopDb");
var products = database.GetCollection<Product>("products");

// Thread-safe: MongoClient is a long-lived singleton
// Register as singleton in IoC container, not per-request

using MongoDB.Bson;
using MongoDB.Bson.Serialization.Attributes;

public class Product
{
    [BsonId]
    [BsonRepresentation(BsonType.ObjectId)]
    public string Id { get; set; }             // auto-generated "_id"

    [BsonElement("name")]
    public string Name { get; set; }

    [BsonElement("price")]
    public decimal Price { get; set; }

    [BsonElement("tags")]
    public List<string> Tags { get; set; } = new();

    // Nested document (no JOIN needed)
    [BsonElement("category")]
    public CategoryEmbedded Category { get; set; }

    [BsonIgnore]
    public string ComputedField => $"{Name} - ${Price}"; // not stored
}

public class CategoryEmbedded
{
    [BsonElement("id")]   public string Id   { get; set; }
    [BsonElement("name")] public string Name { get; set; }
}

var col = database.GetCollection<Product>("products");

// CREATE
await col.InsertOneAsync(new Product { Name = "Widget", Price = 9.99m });

// READ — strongly typed LINQ-style filter
var product = await col
    .Find(p => p.Name == "Widget")
    .FirstOrDefaultAsync();

// READ — all matching, sorted
var cheap = await col
    .Find(p => p.Price < 50)
    .SortBy(p => p.Price)
    .ToListAsync();

// UPDATE — atomic field set
var filter = Builders<Product>.Filter.Eq(p => p.Id, product.Id);
var update = Builders<Product>.Update.Set(p => p.Price, 12.99m);
await col.UpdateOneAsync(filter, update);

// REPLACE — entire document
product.Price = 14.99m;
await col.ReplaceOneAsync(p => p.Id == product.Id, product);

// DELETE
await col.DeleteOneAsync(p => p.Id == product.Id);

var col = database.GetCollection<Product>("products");

// LINQ on MongoDB (uses AsQueryable)
var results = col.AsQueryable()
    .Where(p => p.Price > 10 && p.Tags.Contains("sale"))
    .OrderByDescending(p => p.Price)
    .Select(p => new { p.Id, p.Name, p.Price })
    .Skip(0)
    .Take(20)
    .ToList();

// Filter builder (more expressive for complex filters)
var filter = Builders<Product>.Filter.And(
    Builders<Product>.Filter.Gt(p => p.Price, 10),
    Builders<Product>.Filter.AnyEq(p => p.Tags, "sale")
);

// Projection — return only specific fields
var projection = Builders<Product>.Projection
    .Include(p => p.Name)
    .Include(p => p.Price)
    .Exclude(p => p.Id);

var projected = await col.Find(filter)
    .Project(projection)
    .SortByDescending(p => p.Price)
    .Skip(0).Limit(20)
    .ToListAsync();

var orders   = database.GetCollection<Order>("orders");
var inventory = database.GetCollection<InventoryItem>("inventory");

using var session = await client.StartSessionAsync();
session.StartTransaction();
try
{
    // All operations share the same transaction
    await orders.InsertOneAsync(session, new Order
    {
        ProductId = "abc123",
        Qty       = 2,
        Total     = 49.98m
    });

    var stockFilter = Builders<InventoryItem>.Filter.Eq(i => i.ProductId, "abc123");
    var stockUpdate = Builders<InventoryItem>.Update.Inc(i => i.Stock, -2);
    await inventory.UpdateOneAsync(session, stockFilter, stockUpdate);

    await session.CommitTransactionAsync();
}
catch
{
    await session.AbortTransactionAsync();
    throw;
}

var col = database.GetCollection<Product>("products");

// Single field ascending index
await col.Indexes.CreateOneAsync(
    new CreateIndexModel<Product>(
        Builders<Product>.IndexKeys.Ascending(p => p.Name)));

// Unique index
await col.Indexes.CreateOneAsync(
    new CreateIndexModel<Product>(
        Builders<Product>.IndexKeys.Ascending(p => p.Sku),
        new CreateIndexOptions { Unique = true }));

// Compound index (Price DESC, Name ASC)
await col.Indexes.CreateOneAsync(
    new CreateIndexModel<Product>(
        Builders<Product>.IndexKeys
            .Descending(p => p.Price)
            .Ascending(p => p.Name)));

// Text index for full-text search
await col.Indexes.CreateOneAsync(
    new CreateIndexModel<Product>(
        Builders<Product>.IndexKeys.Text(p => p.Name)));

// TTL index — auto-delete expired sessions after 1 hour
var sessions = database.GetCollection<Session>("sessions");
await sessions.Indexes.CreateOneAsync(
    new CreateIndexModel<Session>(
        Builders<Session>.IndexKeys.Ascending(s => s.CreatedAt),
        new CreateIndexOptions { ExpireAfter = TimeSpan.FromHours(1) }));

var col = database.GetCollection<Order>("orders");

// Watch for insert events only
var pipeline = new EmptyPipelineDefinition<ChangeStreamDocument<Order>>()
    .Match(change => change.OperationType == ChangeStreamOperationType.Insert);

using var cursor = await col.WatchAsync(pipeline);

Console.WriteLine("Watching for new orders...");
await cursor.ForEachAsync(change =>
{
    var newOrder = change.FullDocument;
    Console.WriteLine($"New order: {newOrder.Id} — ${newOrder.Total}");
    // Trigger email, push notification, etc.
});

// Watch all operations (insert/update/delete/replace)
using var allCursor = await col.WatchAsync();
await allCursor.ForEachAsync(change =>
    Console.WriteLine($"{change.OperationType}: {change.DocumentKey}"));

// Unity / Autofac registration example
container.RegisterInstance<IMongoClient>(
    new MongoClient("mongodb://localhost:27017")); // singleton

container.RegisterType<ShopContext>(
    new PerRequestLifetimeManager()); // per HTTP request

// Service using both
public class OrderService
{
    private readonly ShopContext         _sql;   // EF6 — orders, customers
    private readonly IMongoDatabase      _mongo; // MongoDB — event logs, carts

    public OrderService(ShopContext sql, IMongoClient mongo)
    {
        _sql   = sql;
        _mongo = mongo.GetDatabase("ShopDb");
    }

    public async Task PlaceOrderAsync(OrderDto dto)
    {
        // Save order to SQL Server via EF6
        _sql.Orders.Add(new Order { Total = dto.Total, CustomerId = dto.CustomerId });
        await _sql.SaveChangesAsync();

        // Log event to MongoDB
        await _mongo.GetCollection<OrderEvent>("order_events")
            .InsertOneAsync(new OrderEvent
            {
                OrderId   = dto.OrderId,
                Event     = "placed",
                Timestamp = DateTime.UtcNow
            });
    }
}

# Step 1: enable migrations (creates Migrations\Configuration.cs)
Enable-Migrations -ContextTypeName AppDbContext

# Step 2: snapshot current model
Add-Migration InitialCreate

# Step 3: apply to database
Update-Database -Verbose  # shows generated SQL

# Later: add a new migration after model changes
Add-Migration AddCategoryTable
Update-Database

// Generated migration file: 202406_InitialCreate.cs
public partial class InitialCreate : DbMigration
{
    public override void Up()
    {
        CreateTable("dbo.Products", c => new
        {
            Id    = c.Int(nullable: false, identity: true),
            Name  = c.String(maxLength: 200),
            Price = c.Decimal(nullable: false, precision: 18, scale: 4),
        })
        .PrimaryKey(t => t.Id);
    }

    public override void Down()
    {
        DropTable("dbo.Products");
    }
}

// Migrations\Configuration.cs
protected override void Seed(AppDbContext context)
{
    // AddOrUpdate: insert if not exists, update if found by the key
    context.Categories.AddOrUpdate(
        c => c.Name,  // unique key for idempotency
        new Category { Name = "Electronics", SortOrder = 1 },
        new Category { Name = "Books",       SortOrder = 2 },
        new Category { Name = "Clothing",    SortOrder = 3 }
    );

    context.SaveChanges(); // save categories first (FK dependency)

    var electronics = context.Categories.First(c => c.Name == "Electronics");

    context.Products.AddOrUpdate(
        p => p.Sku,
        new Product { Sku = "E001", Name = "Laptop",  Price = 999m, CategoryId = electronics.Id },
        new Product { Sku = "E002", Name = "Monitor", Price = 399m, CategoryId = electronics.Id }
    );
}

// Option A: automatic via initializer (simplest)
Database.SetInitializer(
    new MigrateDatabaseToLatestVersion<AppDbContext, Configuration>());

using (var ctx = new AppDbContext())
    ctx.Database.Initialize(force: false); // runs pending migrations

// Option B: explicit DbMigrator (more control, logs output)
var config   = new Configuration();
var migrator = new DbMigrator(config);

var pending = migrator.GetPendingMigrations().ToList();
if (pending.Any())
{
    Console.WriteLine($"Applying {pending.Count} migration(s):");
    pending.ForEach(m => Console.WriteLine("  " + m));
    migrator.Update(); // applies all pending
}
else
{
    Console.WriteLine("Database is up to date.");
}

# Rollback to a specific migration name
Update-Database -TargetMigration "AddCategoryTable"

# Rollback ALL migrations (empty database — keeps schema history table)
Update-Database -TargetMigration $InitialDatabase

# View applied migrations
Get-Migrations

// Programmatic rollback
var migrator = new DbMigrator(new Configuration());
migrator.Update("AddCategoryTable"); // reverts migrations after this one

// Generate a rollback SQL script (for DBA review)
// Update-Database -TargetMigration "AddCategoryTable" -Script
// Outputs: rollback.sql with all Down() SQL statements

# Generate SQL script for all pending migrations
Update-Database -Script -SourceMigration $InitialDatabase -DestinationMigration Head

# Script from a specific migration
Update-Database -Script -SourceMigration "InitialCreate" -DestinationMigration "AddCategoryTable"

# Output: migration.sql — commit to repo, run in CI pipeline via sqlcmd
# sqlcmd -S $(Server) -d $(Database) -i migration.sql

// Programmatic script generation
var config  = new Configuration();
var scriptr = new MigratorScriptingDecorator(new DbMigrator(config));
string sql  = scriptr.ScriptUpdate(
    sourceMigration: null,       // from beginning
    targetMigration: null);      // to latest

File.WriteAllText("migrate.sql", sql);
Console.WriteLine("Script written to migrate.sql");

public partial class RenameProductsTable : DbMigration
{
    public override void Up()
    {
        // Rename table
        RenameTable("dbo.Products", "Catalogue");

        // Rename column (table, old name, new name)
        RenameColumn("dbo.Catalogue", "Name", "Title");

        // Move column to different table (rarely needed)
        // AddColumn → copy data → DropColumn
    }

    public override void Down()
    {
        RenameColumn("dbo.Catalogue", "Title", "Name");
        RenameTable("dbo.Catalogue", "Products");
    }
}

// Update C# entity and mappings to match
[Table("Catalogue")]
public class Product
{
    [Column("Title")]
    public string Name { get; set; } // C# property name unchanged
}

# Both Dev A and Dev B added migrations — merge:
# 1. Pull latest, check for conflicts in Migrations folder
# 2. Run merge migration to reconcile model snapshots
Add-Migration MergeConflict -IgnoreChanges

# The merge migration has empty Up/Down (already applied manually)
# EF6 uses it to sync the model snapshot with the DB state
Update-Database

// Best practice: use a migration naming convention with timestamp prefix
// EF6 uses UTC timestamp automatically: 202406141030_AddCategoryTable
// Avoid sequential numbering — collisions with parallel dev branches

// Team workflow:
// 1. Always pull latest before Add-Migration
// 2. Use feature branches; merge migrations last
// 3. Squash multiple WIP migrations before merging to main

# Enable migrations for each context in separate folders
Enable-Migrations -ContextTypeName ShopContext    -MigrationsDirectory "Migrations\Shop"
Enable-Migrations -ContextTypeName AuthContext    -MigrationsDirectory "Migrations\Auth"
Enable-Migrations -ContextTypeName ReportContext  -MigrationsDirectory "Migrations\Reports"

# Add and apply per context
Add-Migration Init -ConfigurationTypeName Shop.Migrations.Configuration
Add-Migration Init -ConfigurationTypeName Auth.Migrations.Configuration
Update-Database   -ConfigurationTypeName Shop.Migrations.Configuration

// Separate configuration per context
namespace Shop.Migrations
{
    internal sealed class Configuration : DbMigrationsConfiguration<ShopContext>
    {
        public Configuration()
        {
            AutomaticMigrationsEnabled = false;
            MigrationsDirectory        = @"Migrations\Shop";
        }
    }
}

namespace Auth.Migrations
{
    internal sealed class Configuration : DbMigrationsConfiguration<AuthContext>
    {
        public Configuration()
        {
            AutomaticMigrationsEnabled = false;
            MigrationsDirectory        = @"Migrations\Auth";
        }
    }
}

using System.Data.Entity; // required for EF6 Include with lambda

using (var ctx = new ShopContext())
{
    // Single level eager load
    var orders = ctx.Orders
        .Include(o => o.Customer)
        .ToList();

    // Multiple includes
    var orders2 = ctx.Orders
        .Include(o => o.Customer)
        .Include(o => o.Items)
        .ToList();

    // Multi-level: Orders → Items → Product
    var orders3 = ctx.Orders
        .Include(o => o.Items.Select(i => i.Product)) // EF6 syntax
        .ToList();

    // String-based (works without lambda, but refactoring-unsafe)
    var orders4 = ctx.Orders
        .Include("Customer")
        .Include("Items.Product")
        .ToList();
}

// Lazy loading — virtual navigation + proxy required
public class Order
{
    public int               Id       { get; set; }
    public virtual Customer  Customer { get; set; } // virtual = lazy
    public virtual ICollection<OrderItem> Items { get; set; }
}

// Lazy load fires when you access the property
using (var ctx = new ShopContext())
{
    var order = ctx.Orders.Find(1);         // loads only Order
    var name  = order.Customer.Name;        // ← fires 2nd SQL query HERE
}

// Disable globally in DbContext constructor
public ShopContext()
{
    Configuration.LazyLoadingEnabled  = false; // no proxy SQL on access
    Configuration.ProxyCreationEnabled = false; // also disables proxies
}

// Disable for single query scope
ctx.Configuration.LazyLoadingEnabled = false;

using (var ctx = new ShopContext())
{
    // ✅ Read-only report — no tracking
    var report = ctx.Orders
        .AsNoTracking()
        .Where(o => o.CreatedAt >= DateTime.Today.AddMonths(-1))
        .Include(o => o.Customer)
        .Select(o => new { o.Id, o.Total, CustomerName = o.Customer.Name })
        .ToList();

    // ⚠️ Don't call SaveChanges after AsNoTracking — changes won't be detected
    var product = ctx.Products.AsNoTracking().Find(1);
    // product.Price = 99; ctx.SaveChanges(); // Will NOT update (not tracked)
}

// Disable tracking globally for the whole context lifetime
ctx.Configuration.AutoDetectChangesEnabled = false;

// Re-enable when needed for an update
ctx.Configuration.AutoDetectChangesEnabled = true;
ctx.Entry(entity).State = EntityState.Modified;
ctx.SaveChanges();

using System.Data.Entity; // DbFunctions

using (var ctx = new ShopContext())
{
    var today     = DateTime.Today;
    var thisMonth = today.Month;
    var thisYear  = today.Year;

    // Date truncation to day (ignore time component)
    var todaysOrders = ctx.Orders
        .Where(o => DbFunctions.TruncateTime(o.CreatedAt) == today)
        .ToList();

    // Date difference in days
    var recentOrders = ctx.Orders
        .Where(o => DbFunctions.DiffDays(o.CreatedAt, DateTime.Now) < 30)
        .ToList();

    // Group by month + year
    var monthly = ctx.Orders
        .GroupBy(o => new
        {
            Year  = DbFunctions.GetYear(o.CreatedAt),
            Month = DbFunctions.GetMonth(o.CreatedAt)
        })
        .Select(g => new
        {
            g.Key.Year,
            g.Key.Month,
            Total = g.Sum(o => o.Amount)
        })
        .OrderBy(x => x.Year).ThenBy(x => x.Month)
        .ToList();
}

using (var ctx = new ShopContext())
{
    ctx.Configuration.LazyLoadingEnabled = false; // explicit only

    var order = ctx.Orders.Find(1); // loads Order only

    // Load reference navigation (single related entity)
    ctx.Entry(order).Reference(o => o.Customer).Load();
    Console.WriteLine(order.Customer.Name);

    // Load collection navigation
    ctx.Entry(order).Collection(o => o.Items).Load();
    Console.WriteLine($"Items: {order.Items.Count}");

    // Load with a filter (conditional explicit load)
    ctx.Entry(order)
        .Collection(o => o.Items)
        .Query()                             // IQueryable — filter here
        .Where(i => i.Qty > 1)
        .Load();

    // Check if already loaded
    bool isLoaded = ctx.Entry(order).Reference(o => o.Customer).IsLoaded;
    if (!isLoaded)
        ctx.Entry(order).Reference(o => o.Customer).Load();
}

using (var ctx = new ShopContext())
{
    // ❌ Loads all columns including large Description blob
    var all = ctx.Products.Where(p => p.IsActive).ToList();
    var names = all.Select(p => p.Name); // wasteful

    // ✅ Only fetches Id, Name, Price in SQL
    var dtos = ctx.Products
        .Where(p => p.IsActive)
        .Select(p => new ProductDto
        {
            Id    = p.Id,
            Name  = p.Name,
            Price = p.Price
        })
        .ToList();

    // ✅ Computed field in DB via projection
    var orderSummaries = ctx.Orders
        .Select(o => new
        {
            o.Id,
            o.CreatedAt,
            ItemCount    = o.Items.Count(),       // COUNT() in SQL
            Total        = o.Items.Sum(i => i.Price * i.Qty), // SUM in SQL
            CustomerName = o.Customer.Name        // JOIN in SQL
        })
        .ToList();
}

using System.Data.Entity;
using System.Data.Objects; // CompiledQuery

// Compile once (static field — shared across all instances)
private static readonly Func<ShopContext, int, Product> GetProductById =
    CompiledQuery.Compile<ShopContext, int, Product>(
        (ctx, id) => ctx.Products.FirstOrDefault(p => p.Id == id));

// Multiple parameters
private static readonly Func<ShopContext, string, decimal, IQueryable<Product>>
    GetByCategoryAndPrice = CompiledQuery.Compile<ShopContext, string, decimal, IQueryable<Product>>(
        (ctx, cat, maxPrice) =>
            ctx.Products.Where(p => p.Category == cat && p.Price <= maxPrice));

// Use — no re-compilation overhead
using (var ctx = new ShopContext())
{
    var product  = GetProductById(ctx, 42);
    var products = GetByCategoryAndPrice(ctx, "Books", 25m).ToList();
}

public class PagedResult<T>
{
    public List<T> Items    { get; set; }
    public int     Total    { get; set; }
    public int     Page     { get; set; }
    public int     PageSize { get; set; }
    public int     Pages    => (int)Math.Ceiling((double)Total / PageSize);
}

public async Task<PagedResult<ProductDto>> GetProductsAsync(
    int page, int pageSize, string sortBy = "Name")
{
    using (var ctx = new ShopContext())
    {
        var query = ctx.Products.Where(p => p.IsActive);

        int total = await query.CountAsync();

        var items = await query
            .OrderBy(p => p.Name)              // ← required before Skip
            .Skip((page - 1) * pageSize)
            .Take(pageSize)
            .Select(p => new ProductDto { Id = p.Id, Name = p.Name, Price = p.Price })
            .ToListAsync();

        return new PagedResult<ProductDto>
        {
            Items    = items,
            Total    = total,
            Page     = page,
            PageSize = pageSize
        };
    }
}

public class Customer
{
    public int    Id     { get; set; }
    public string Name   { get; set; }
    public virtual ICollection<Order> Orders { get; set; }
}

public class Order
{
    public int      Id         { get; set; }
    public int      CustomerId { get; set; }
    public virtual  Customer Customer { get; set; }
}

// Fluent API configuration
protected override void OnModelCreating(DbModelBuilder mb)
{
    mb.Entity<Customer>()
        .HasMany(c => c.Orders)          // Customer has many Orders
        .WithRequired(o => o.Customer)   // Order requires a Customer (NOT NULL FK)
        .HasForeignKey(o => o.CustomerId)
        .WillCascadeOnDelete(true);      // CASCADE DELETE

    // Optional relationship (nullable FK)
    mb.Entity<Product>()
        .HasOptional(p => p.Supplier)
        .WithMany(s => s.Products)
        .HasForeignKey(p => p.SupplierId);
}

// Simple many-to-many (no extra columns on join table)
public class Product { public virtual ICollection<Tag> Tags { get; set; } }
public class Tag     { public virtual ICollection<Product> Products { get; set; } }

protected override void OnModelCreating(DbModelBuilder mb)
{
    mb.Entity<Product>()
        .HasMany(p => p.Tags)
        .WithMany(t => t.Products)
        .Map(m =>
        {
            m.ToTable("ProductTags");
            m.MapLeftKey("ProductId");
            m.MapRightKey("TagId");
        });
}

// Many-to-many WITH extra columns → explicit join entity
public class ProductTag
{
    public int      ProductId  { get; set; }
    public int      TagId      { get; set; }
    public DateTime TaggedAt   { get; set; }
    public virtual  Product Product { get; set; }
    public virtual  Tag     Tag     { get; set; }
}

mb.Entity<ProductTag>()
    .HasKey(pt => new { pt.ProductId, pt.TagId }); // composite PK

public abstract class Payment
{
    public int    Id     { get; set; }
    public decimal Amount { get; set; }
}

public class CreditCardPayment : Payment
{
    public string CardNumber { get; set; }
    public string CardBrand  { get; set; }
}

public class BankTransferPayment : Payment
{
    public string IBAN          { get; set; }
    public string BankName      { get; set; }
}

// Fluent API — TPH with custom discriminator column
protected override void OnModelCreating(DbModelBuilder mb)
{
    mb.Entity<Payment>()
        .Map<CreditCardPayment>(m => m.Requires("PaymentType").HasValue("CC"))
        .Map<BankTransferPayment>(m => m.Requires("PaymentType").HasValue("BT"));
}

// Generated table: Payments (Id, Amount, PaymentType, CardNumber, IBAN, ...)

[Table("Payments")]
public abstract class Payment
{
    public int    Id     { get; set; }
    public decimal Amount { get; set; }
}

[Table("CreditCardPayments")] // each derived type → own table
public class CreditCardPayment : Payment
{
    public string CardNumber { get; set; }
}

[Table("BankTransferPayments")]
public class BankTransferPayment : Payment
{
    public string IBAN { get; set; }
}

// Or via Fluent API
protected override void OnModelCreating(DbModelBuilder mb)
{
    mb.Entity<Payment>().ToTable("Payments");
    mb.Entity<CreditCardPayment>().ToTable("CreditCardPayments");
    mb.Entity<BankTransferPayment>().ToTable("BankTransferPayments");
}

// Generated: Payments + CreditCardPayments(FK→Payments.Id) + BankTransferPayments(FK→Payments.Id)

// Data annotation approach
public class OrderItem
{
    [Key, Column(Order = 0)]
    public int OrderId   { get; set; }

    [Key, Column(Order = 1)]
    public int ProductId { get; set; }

    public int    Qty   { get; set; }
    public decimal Price { get; set; }

    public virtual Order   Order   { get; set; }
    public virtual Product Product { get; set; }
}

// Fluent API approach (preferred)
protected override void OnModelCreating(DbModelBuilder mb)
{
    mb.Entity<OrderItem>()
        .HasKey(oi => new { oi.OrderId, oi.ProductId });

    mb.Entity<OrderItem>()
        .HasRequired(oi => oi.Order)
        .WithMany(o => o.Items)
        .HasForeignKey(oi => oi.OrderId);
}

// Find by composite key
var item = ctx.OrderItems.Find(orderId, productId);

-- Create the view in SQL Server
CREATE VIEW vw_OrderSummary AS
SELECT
    o.Id,
    c.Name    AS CustomerName,
    o.Total,
    o.Status,
    COUNT(oi.Id) AS ItemCount
FROM Orders o
JOIN Customers c  ON c.Id = o.CustomerId
JOIN OrderItems oi ON oi.OrderId = o.Id
GROUP BY o.Id, c.Name, o.Total, o.Status;

[Table("vw_OrderSummary")]
public class OrderSummary
{
    [Key]                           // view must have a logical PK
    public int    Id           { get; set; }
    public string CustomerName { get; set; }
    public decimal Total       { get; set; }
    public string Status       { get; set; }
    public int    ItemCount    { get; set; }
}

public class ShopContext : DbContext
{
    public DbSet<OrderSummary> OrderSummaries { get; set; }
}

// Query the view — read only
var summaries = ctx.OrderSummaries
    .Where(s => s.Status == "Shipped")
    .OrderByDescending(s => s.Total)
    .ToList();

public interface IAuditable
{
    DateTime  CreatedAt  { get; set; }
    DateTime  UpdatedAt  { get; set; }
    string    CreatedBy  { get; set; }
}

public class ShopContext : DbContext
{
    private readonly string _currentUser;

    public ShopContext(string currentUser = "system") : base("ShopDb")
        => _currentUser = currentUser;

    public override int SaveChanges()
    {
        var now = DateTime.UtcNow;
        foreach (var entry in ChangeTracker.Entries<IAuditable>())
        {
            if (entry.State == EntityState.Added)
            {
                entry.Entity.CreatedAt = now;
                entry.Entity.CreatedBy = _currentUser;
                entry.Entity.UpdatedAt = now;
            }
            else if (entry.State == EntityState.Modified)
            {
                entry.Entity.UpdatedAt = now;
                // Prevent overwriting original CreatedAt/By
                entry.Property(x => x.CreatedAt).IsModified = false;
                entry.Property(x => x.CreatedBy).IsModified = false;
            }
        }
        return base.SaveChanges();
    }
}

// After importing via EDMX, EF generates a method on the context:
using (var ctx = new ShopEntities())
{
    // Calls sp_GetTopProducts stored procedure
    var products = ctx.sp_GetTopProducts(10).ToList();
    // Returns ObjectResult<sp_GetTopProducts_Result>

    foreach (var p in products)
        Console.WriteLine($"{p.Name}: ${p.Price}");
}

// Code First equivalent (no EDMX):
using (var ctx = new ShopContext())
{
    var products = ctx.Database
        .SqlQuery<TopProductResult>(
            "EXEC sp_GetTopProducts @top",
            new SqlParameter("@top", 10))
        .ToList();
}

// Enable SQL logging to spot N+1
ctx.Database.Log = sql => System.Diagnostics.Debug.WriteLine(sql);

// ❌ N+1: 1 query for orders + N queries for each customer access
var orders = ctx.Orders.ToList(); // 1 SQL
foreach (var o in orders)
    Console.WriteLine(o.Customer.Name); // N SQL (lazy load per item)

// ✅ Fix: eager load in original query
var orders = ctx.Orders.Include(o => o.Customer).ToList(); // 1 SQL

// ✅ Fix: project to avoid navigation access entirely
var names = ctx.Orders
    .Select(o => new { o.Id, CustomerName = o.Customer.Name })
    .ToList(); // 1 SQL with JOIN

// Quick debug logging
ctx.Database.Log = s => Console.WriteLine(s);

// Production interceptor
public class SqlTimingInterceptor : DbCommandInterceptor
{
    private readonly Stopwatch _sw = new();

    public override void ReaderExecuting(DbCommand cmd,
        DbCommandInterceptionContext<DbDataReader> ctx)
    {
        _sw.Restart();
        base.ReaderExecuting(cmd, ctx);
    }

    public override void ReaderExecuted(DbCommand cmd,
        DbCommandInterceptionContext<DbDataReader> ctx)
    {
        _sw.Stop();
        if (_sw.ElapsedMilliseconds > 500)
            Logger.Warn($"SLOW QUERY ({_sw.ElapsedMilliseconds}ms):\n{cmd.CommandText}");
    }
}

// Register globally (once at app startup)
DbInterception.Add(new SqlTimingInterceptor());

// SqlBulkCopy — fastest approach for SQL Server
public static void BulkInsertProducts(IEnumerable<Product> products)
{
    var dt = new DataTable();
    dt.Columns.Add("Name",  typeof(string));
    dt.Columns.Add("Price", typeof(decimal));

    foreach (var p in products)
        dt.Rows.Add(p.Name, p.Price);

    using var conn = new SqlConnection(ConnStr);
    conn.Open();
    using var bulk = new SqlBulkCopy(conn)
    {
        DestinationTableName = "Products",
        BatchSize            = 1000
    };
    bulk.ColumnMappings.Add("Name",  "Name");
    bulk.ColumnMappings.Add("Price", "Price");
    bulk.WriteToServer(dt);
}

// Disable AutoDetectChanges for large Add loops
ctx.Configuration.AutoDetectChangesEnabled = false;
for (int i = 0; i < 10000; i++)
    ctx.Products.Add(new Product { Name = $"P{i}", Price = i });
ctx.SaveChanges();
ctx.Configuration.AutoDetectChangesEnabled = true;

// ❌ Memory leak — static context accumulates entities
public class OrderService
{
    private static readonly ShopContext _ctx = new ShopContext(); // never disposed!
    public List<Order> GetOrders() => _ctx.Orders.ToList(); // tracked forever
}

// ✅ Per-operation context — disposed immediately
public class OrderService
{
    public List<OrderDto> GetOrders()
    {
        using (var ctx = new ShopContext())
        {
            return ctx.Orders
                .AsNoTracking()  // don't track — read-only
                .Select(o => new OrderDto { Id = o.Id, Total = o.Total })
                .ToList();
        } // context disposed, all objects eligible for GC
    }
}

// ❌ Processing huge result set — all in memory at once
var all = ctx.Orders.ToList(); // loads 1M rows

// ✅ Stream in batches
int page = 0, size = 1000;
List<Order> batch;
do {
    batch = ctx.Orders.OrderBy(o => o.Id)
        .Skip(page++ * size).Take(size).ToList();
    ProcessBatch(batch);
} while (batch.Count == size);

// ❌ DEADLOCK in ASP.NET — .Result blocks the sync context
public ActionResult Index()
{
    var products = ctx.Products.ToListAsync().Result; // DEADLOCK!
    return View(products);
}

// ✅ Async all the way
public async Task<ActionResult> Index()
{
    using var ctx = new ShopContext();
    var products = await ctx.Products.ToListAsync(); // no deadlock
    return View(products);
}

// ❌ Don't share context across async calls
public class OrderService
{
    private ShopContext _ctx = new ShopContext(); // shared — not thread-safe!

    public async Task<Order> GetAsync(int id)
        => await _ctx.Orders.FindAsync(id); // race condition if concurrent
}

// ✅ Create context per operation
public async Task<Order> GetAsync(int id)
{
    using var ctx = new ShopContext();
    return await ctx.Orders.FindAsync(id);
}

// Manual MemoryCache wrapping
private static readonly MemoryCache _cache = MemoryCache.Default;

public List<Category> GetCategories()
{
    const string key = "AllCategories";
    if (_cache[key] is List<Category> cached) return cached;

    using var ctx = new ShopContext();
    var categories = ctx.Categories.AsNoTracking().ToList();

    _cache.Set(key, categories, DateTimeOffset.UtcNow.AddMinutes(15));
    return categories;
}

public void InvalidateCategoryCache()
    => _cache.Remove("AllCategories");

// Install-Package EFCache for automatic query-level caching
// Wraps the DbProvider and caches query results transparently

// MiniProfiler for ASP.NET MVC 5 (NuGet: MiniProfiler.EF6)
// Global.asax:
MiniProfilerEF6.Initialize();

// View query results in dev toolbar at /mini-profiler-resources/results-list

// Structured logging interceptor
public class NLogInterceptor : DbCommandInterceptor
{
    private static readonly NLog.Logger Log = NLog.LogManager.GetCurrentClassLogger();

    public override void NonQueryExecuted(DbCommand cmd,
        DbCommandInterceptionContext<int> ctx)
    {
        Log.Debug("SQL [{0}ms]: {1}", ctx.TaskStatus, cmd.CommandText);
    }
    // override ReaderExecuted, ScalarExecuted similarly
}

// View SQL of a LINQ query without running it
var query = ctx.Orders.Where(o => o.Total > 100);
string sql = ((System.Data.Entity.Core.Objects.ObjectQuery)query).ToTraceString();
Console.WriteLine(sql);

// ❌ Shared context across threads — race conditions
private ShopContext _sharedCtx = new ShopContext();

await Task.WhenAll(
    Task.Run(() => _sharedCtx.Orders.ToList()),  // thread 1
    Task.Run(() => _sharedCtx.Products.ToList()) // thread 2 — CRASH
);

// ✅ New context per thread/Task
Func<ShopContext> contextFactory = () => new ShopContext();

await Task.WhenAll(
    Task.Run(() =>
    {
        using var ctx = contextFactory();
        return ctx.Orders.AsNoTracking().ToList();
    }),
    Task.Run(() =>
    {
        using var ctx = contextFactory();
        return ctx.Products.AsNoTracking().ToList();
    })
);

// ✅ Windows Service / background worker — context per job execution
public class OrderProcessingJob
{
    public void Execute()
    {
        using var ctx = new ShopContext(); // fresh context per run
        var pending = ctx.Orders.Where(o => o.Status == "Pending").ToList();
        foreach (var o in pending) { /* process */ }
        ctx.SaveChanges();
    }
}

public interface IRepository<T> where T : class
{
    T           GetById(int id);
    IQueryable<T> Query();
    void        Add(T entity);
    void        Remove(T entity);
}

public class EfRepository<T> : IRepository<T> where T : class
{
    private readonly ShopContext _ctx;
    public EfRepository(ShopContext ctx) => _ctx = ctx;

    public T            GetById(int id)  => _ctx.Set<T>().Find(id);
    public IQueryable<T> Query()         => _ctx.Set<T>();
    public void         Add(T entity)    => _ctx.Set<T>().Add(entity);
    public void         Remove(T entity) => _ctx.Set<T>().Remove(entity);
}

public interface IUnitOfWork : IDisposable
{
    IRepository<Order>    Orders    { get; }
    IRepository<Product>  Products  { get; }
    int Commit();
}

public class EfUnitOfWork : IUnitOfWork
{
    private readonly ShopContext _ctx = new ShopContext();

    public IRepository<Order>   Orders   => new EfRepository<Order>(_ctx);
    public IRepository<Product> Products => new EfRepository<Product>(_ctx);

    public int  Commit()  => _ctx.SaveChanges();
    public void Dispose() => _ctx.Dispose();
}

// Autofac registration (Global.asax / Startup)
var builder = new ContainerBuilder();

// Per HTTP request (ASP.NET MVC 5 / Web API 2)
builder.RegisterType<ShopContext>()
       .AsSelf()
       .As<IUnitOfWork>()
       .InstancePerRequest();

builder.RegisterGeneric(typeof(EfRepository<>))
       .As(typeof(IRepository<>))
       .InstancePerRequest();

IContainer container = builder.Build();
DependencyResolver.SetResolver(new AutofacDependencyResolver(container));

// Unity registration
var container = new UnityContainer();
container.RegisterType<ShopContext>(new PerRequestLifetimeManager());
container.RegisterType(typeof(IRepository<>), typeof(EfRepository<>),
    new PerRequestLifetimeManager());

// Controller receives ShopContext via constructor injection
public class OrdersController : Controller
{
    private readonly IUnitOfWork _uow;
    public OrdersController(IUnitOfWork uow) => _uow = uow;

    public async Task<ActionResult> Index()
    {
        var orders = await _uow.Orders.Query().AsNoTracking().ToListAsync();
        return View(orders);
    }
}

using System.Data.Entity.Spatial;

public class Store
{
    public int         Id       { get; set; }
    public string      Name     { get; set; }
    public DbGeography Location { get; set; } // maps to geography column
}

// Create a point from lat/lon
// WGS84 SRID = 4326
var store = new Store
{
    Name     = "Downtown",
    Location = DbGeography.FromText("POINT(-73.985 40.748)", 4326)
};
ctx.Stores.Add(store);
ctx.SaveChanges();

// Find stores within 5 km of a point
var myLocation = DbGeography.FromText("POINT(-73.990 40.750)", 4326);
var nearby = ctx.Stores
    .Where(s => s.Location.Distance(myLocation) < 5000) // metres
    .OrderBy(s => s.Location.Distance(myLocation))
    .ToList();

public class Product
{
    public int    Id      { get; set; }
    public string Name    { get; set; }
    public decimal Price  { get; set; }

    [ConcurrencyCheck]
    public int Version    { get; set; } // manually incremented
}

// Override SaveChanges to auto-increment version
public override int SaveChanges()
{
    foreach (var entry in ChangeTracker.Entries<Product>()
        .Where(e => e.State == EntityState.Modified))
    {
        entry.Entity.Version++;
    }
    return base.SaveChanges();
}

// On conflict: DbUpdateConcurrencyException is thrown
// Generated SQL:
// UPDATE Products SET Name=@p0, Price=@p1, Version=@p2
// WHERE Id=@p3 AND Version=@p4  ← version must match

// Mockable interface
public interface IShopContext : IDisposable
{
    IDbSet<Product> Products { get; }
    int SaveChanges();
}

public class ShopContext : DbContext, IShopContext
{
    public IDbSet<Product> Products { get; set; }
}

// Unit test with Moq
[TestMethod]
public void GetActiveProducts_ReturnsOnlyActive()
{
    var data = new List<Product>
    {
        new Product { Id = 1, Name = "A", IsActive = true  },
        new Product { Id = 2, Name = "B", IsActive = false },
    }.AsQueryable();

    var mockSet = new Mock<IDbSet<Product>>();
    mockSet.As<IQueryable<Product>>().Setup(m => m.Provider).Returns(data.Provider);
    mockSet.As<IQueryable<Product>>().Setup(m => m.Expression).Returns(data.Expression);
    mockSet.As<IQueryable<Product>>().Setup(m => m.ElementType).Returns(data.ElementType);
    mockSet.As<IQueryable<Product>>().Setup(m => m.GetEnumerator()).Returns(data.GetEnumerator());

    var mockCtx = new Mock<IShopContext>();
    mockCtx.Setup(c => c.Products).Returns(mockSet.Object);

    var service = new ProductService(mockCtx.Object);
    var result  = service.GetActive();

    Assert.AreEqual(1, result.Count);
    Assert.AreEqual("A", result[0].Name);
}

// Built-in initializers:
Database.SetInitializer(new CreateDatabaseIfNotExists<ShopContext>()); // default
Database.SetInitializer(new DropCreateDatabaseAlways<ShopContext>());  // dev only
Database.SetInitializer(new DropCreateDatabaseIfModelChanges<ShopContext>());
Database.SetInitializer(new MigrateDatabaseToLatestVersion<ShopContext, Configuration>());
Database.SetInitializer<ShopContext>(null); // disable — DB already exists

// Custom initializer with seed data
public class ShopDbInitializer : DropCreateDatabaseAlways<ShopContext>
{
    protected override void Seed(ShopContext ctx)
    {
        ctx.Categories.AddRange(new[]
        {
            new Category { Name = "Electronics" },
            new Category { Name = "Books"       },
        });
        ctx.SaveChanges();
        base.Seed(ctx);
    }
}

Database.SetInitializer(new ShopDbInitializer());

-- SQL Server TVF
CREATE FUNCTION dbo.GetProductsByCategory(@CategoryId INT)
RETURNS TABLE AS RETURN
(
    SELECT Id, Name, Price FROM Products WHERE CategoryId = @CategoryId
);

// Map the TVF
public class ShopContext : DbContext
{
    [DbFunction("ShopContext", "GetProductsByCategory")]
    public IQueryable<Product> GetProductsByCategory(int categoryId)
    {
        var param = new ObjectParameter("CategoryId", categoryId);
        return ((IObjectContextAdapter)this)
            .ObjectContext
            .CreateQuery<Product>(
                "[ShopContext].[GetProductsByCategory](@CategoryId)", param);
    }
}

// Use inside a LINQ query — translated to SQL function call
using (var ctx = new ShopContext())
{
    var electronics = ctx.GetProductsByCategory(1)
        .Where(p => p.Price < 500)
        .OrderBy(p => p.Name)
        .ToList();
}

// Write context — primary DB
public class WriteDbContext : ShopContext
{
    public WriteDbContext() : base("PrimaryDb") { }
}

// Read context — replica DB (read-only)
public class ReadDbContext : ShopContext
{
    public ReadDbContext() : base("ReplicaDb")
    {
        // Always use AsNoTracking on read context
        Configuration.AutoDetectChangesEnabled = false;
        Configuration.LazyLoadingEnabled       = false;
    }
}

// CQRS command (write)
public class PlaceOrderHandler
{
    public void Handle(PlaceOrderCommand cmd)
    {
        using var ctx = new WriteDbContext();
        ctx.Orders.Add(new Order { Total = cmd.Total });
        ctx.SaveChanges();
    }
}

// CQRS query (read — replica)
public class GetOrdersHandler
{
    public List<OrderDto> Handle(GetOrdersQuery q)
    {
        using var ctx = new ReadDbContext();
        return ctx.Orders.AsNoTracking()
            .Select(o => new OrderDto { Id = o.Id, Total = o.Total })
            .ToList();
    }
}

// Enable in Configuration.cs
internal sealed class Configuration : DbMigrationsConfiguration<ShopContext>
{
    public Configuration()
    {
        AutomaticMigrationsEnabled            = true;  // ← auto-apply
        AutomaticMigrationDataLossAllowed     = false; // ← refuse destructive changes
    }
}

// Set initializer to auto-migrate on startup
Database.SetInitializer(
    new MigrateDatabaseToLatestVersion<ShopContext, Configuration>());

// ⚠️ Production risks:
// • Dropping a column (losing data) will throw if DataLossAllowed=false
// • Schema changes are irreversible without a backup
// • No audit trail of schema history

// Best practice: use explicit migrations in production,
// automatic migrations only in development/prototyping

public class TenantInterceptor : DbCommandInterceptor
{
    private readonly int _tenantId;
    public TenantInterceptor(int tenantId) => _tenantId = tenantId;

    public override void ReaderExecuting(DbCommand cmd,
        DbCommandInterceptionContext<DbDataReader> ctx)
    {
        // Inject WHERE TenantId = @t into SELECT statements
        if (cmd.CommandText.Contains("SELECT") &&
            cmd.CommandText.Contains("[TenantId]"))
        {
            var param = cmd.CreateParameter();
            param.ParameterName = "@__tenantId";
            param.Value         = _tenantId;
            cmd.Parameters.Add(param);

            // Replace the last WHERE or add one
            // (real implementation parses SQL properly)
            cmd.CommandText += $" AND [TenantId] = @__tenantId";
        }
        base.ReaderExecuting(cmd, ctx);
    }
}

// Register per-request (with tenant from auth context)
DbInterception.Add(new TenantInterceptor(currentTenantId));

# 1. Upgrade target framework: .NET Framework → .NET 6/8
# 2. Replace NuGet packages
Uninstall-Package EntityFramework
Install-Package Microsoft.EntityFrameworkCore.SqlServer
Install-Package Microsoft.EntityFrameworkCore.Tools

// EF6 → EF Core breaking changes to fix:

// 1. Namespace
// EF6:  using System.Data.Entity;
// Core: using Microsoft.EntityFrameworkCore;

// 2. DbContext configuration (moved to OnConfiguring or DI)
// EF6:  public AppDbContext() : base("ConnStr") {}
// Core: optionsBuilder.UseSqlServer(connStr);

// 3. Include syntax for multi-level
// EF6:  .Include(o => o.Items.Select(i => i.Product))
// Core: .Include(o => o.Items).ThenInclude(i => i.Product)

// 4. EntityTypeConfiguration base class
// EF6:  EntityTypeConfiguration<T>
// Core: IEntityTypeConfiguration<T> with Configure(EntityTypeBuilder<T>)

// 5. No ObjectContext — no CompiledQuery.Compile
// Core: Use EF.CompileAsyncQuery instead

// 6. Removed: Database.Log (use ILoggerFactory)
// 7. Removed: DbEntityValidationException (use Data Annotations + manual validation)
// 8. Removed: EntityFunctions/DbFunctions → EF.Functions

public class AppDbContext : DbContext
{
    private readonly string _currentUser;

    public AppDbContext(string connStr = "AppDb", string currentUser = "system")
        : base(connStr)
    {
        _currentUser = currentUser;

        // Performance: disable lazy loading globally
        Configuration.LazyLoadingEnabled       = false;
        Configuration.ProxyCreationEnabled     = false;
        Configuration.AutoDetectChangesEnabled = true;

        // Retry on transient SQL Azure errors
        Configuration.SetExecutionStrategy(
            "System.Data.SqlClient",
            () => new SqlAzureExecutionStrategy(3, TimeSpan.FromSeconds(10)));

        // Log slow queries (registered once per app — use DbInterception.Add at startup)
    }

    public DbSet<Product>  Products  { get; set; }
    public DbSet<Order>    Orders    { get; set; }
    public DbSet<Customer> Customers { get; set; }

    protected override void OnModelCreating(DbModelBuilder mb)
    {
        mb.Conventions.Remove<PluralizingTableNameConvention>();
        mb.Configurations.AddFromAssembly(typeof(AppDbContext).Assembly);
        base.OnModelCreating(mb);
    }

    public override int SaveChanges()
    {
        var now = DateTime.UtcNow;

        // Soft deletes
        foreach (var e in ChangeTracker.Entries<ISoftDeletable>()
            .Where(x => x.State == EntityState.Deleted))
        { e.State = EntityState.Modified; e.Entity.IsDeleted = true; }

        // Audit columns
        foreach (var e in ChangeTracker.Entries<IAuditable>())
        {
            if (e.State == EntityState.Added)
            { e.Entity.CreatedAt = now; e.Entity.CreatedBy = _currentUser; }
            if (e.State == EntityState.Modified || e.State == EntityState.Added)
                e.Entity.UpdatedAt = now;
        }

        return base.SaveChanges();
    }
}