// Entity Framework: Specifying clustered index in SQL Server
protected override void OnModelCreating(ModelBuilder modelBuilder)
{
    modelBuilder.Entity<Order>(entity =>
    {
        // This creates a clustered index by default
        entity.HasKey(e => e.OrderId);

        // Or explicitly configure clustering
        entity.HasIndex(e => e.OrderDate)
            .IsClustered();
    });
}

// Npgsql with EF Core: Index configuration
protected override void OnModelCreating(ModelBuilder modelBuilder)
{
    modelBuilder.Entity<Order>(entity =>
    {
        entity.HasIndex(e => e.OrderDate)
            .HasMethod("btree"); // Default, but explicit
    });
}

// Common EF Core query pattern that benefits from covering index
var customerOrders = await context.Orders
    .Where(o => o.CustomerId == customerId)
    .Select(o => new OrderSummaryDto
    {
        OrderDate = o.OrderDate,
        TotalAmount = o.TotalAmount,
        Status = o.Status
    })
    .ToListAsync();

// EF Core 7+ index configuration
modelBuilder.Entity<Order>()
    .HasIndex(e => e.CustomerId)
    .IncludeProperties(e => new { e.OrderDate, e.TotalAmount, e.Status });

// Query that benefits from partial index
var activeOrders = await context.Orders
    .Where(o => o.Status == OrderStatus.Active)
    .Where(o => o.CustomerId == customerId)
    .OrderByDescending(o => o.OrderDate)
    .ToListAsync();

// EF Core query that uses the expression index
var users = await context.Users
    .Where(u => EF.Functions.Lower(u.Email) == email.ToLower())
    .ToListAsync();

public class IndexHealthService
{
    private readonly IDbConnection _connection;

    public async Task<IEnumerable<IndexStats>> GetFragmentedIndexesAsync()
    {
        // SQL Server
        const string sql = @"
            SELECT
                OBJECT_NAME(ips.object_id) AS TableName,
                i.name AS IndexName,
                ips.avg_fragmentation_in_percent AS FragmentationPercent
            FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, 'LIMITED') ips
            JOIN sys.indexes i ON ips.object_id = i.object_id AND ips.index_id = i.index_id
            WHERE ips.avg_fragmentation_in_percent > 30
            AND i.name IS NOT NULL";

        return await _connection.QueryAsync<IndexStats>(sql);
    }
}

protected override void OnModelCreating(ModelBuilder modelBuilder)
{
    modelBuilder.Entity<Order>(entity =>
    {
        // Composite index with specific order
        entity.HasIndex(e => new { e.CustomerId, e.Status, e.OrderDate })
            .HasDatabaseName("IX_Orders_Customer_Status_Date");

        // Covering index (EF Core 7+)
        entity.HasIndex(e => e.CustomerId)
            .IncludeProperties(e => new { e.OrderDate, e.TotalAmount })
            .HasDatabaseName("IX_Orders_Customer_Covering");

        // Filtered index
        entity.HasIndex(e => e.CustomerId)
            .HasFilter("[Status] = 'Active'")
            .HasDatabaseName("IX_Orders_Active");

        // Unique index
        entity.HasIndex(e => e.OrderNumber)
            .IsUnique()
            .HasDatabaseName("UX_Orders_OrderNumber");
    });
}

public partial class AddAdvancedIndexes : Migration
{
    protected override void Up(MigrationBuilder migrationBuilder)
    {
        // PostgreSQL: Expression index
        migrationBuilder.Sql(@"
            CREATE INDEX ix_users_email_lower
            ON users(LOWER(email))");

        // PostgreSQL: Partial index with covering
        migrationBuilder.Sql(@"
            CREATE INDEX ix_orders_active_covering
            ON orders(customer_id)
            INCLUDE (order_date, total_amount)
            WHERE status = 'Active'");

        // SQL Server: Filtered covering index
        migrationBuilder.Sql(@"
            CREATE NONCLUSTERED INDEX IX_Orders_Active_Covering
            ON Orders(CustomerId)
            INCLUDE (OrderDate, TotalAmount)
            WHERE Status = 'Active'");
    }
}

// SQL Server: Enable actual execution plan in EF Core
services.AddDbContext<AppDbContext>(options =>
{
    options.UseSqlServer(connectionString)
        .LogTo(Console.WriteLine, LogLevel.Information)
        .EnableSensitiveDataLogging();
});

// Analyze specific query
var query = context.Orders
    .Where(o => o.CustomerId == customerId)
    .TagWith("Analyzing index usage");

var sql = query.ToQueryString();

[MemoryDiagnoser]
public class IndexBenchmarks
{
    private AppDbContext _context;

    [GlobalSetup]
    public void Setup()
    {
        _context = new AppDbContext(/* connection */);
    }

    [Benchmark(Baseline = true)]
    public async Task<List<Order>> WithoutCoveringIndex()
    {
        return await _context.Orders
            .Where(o => o.CustomerId == 1)
            .Select(o => new Order { OrderDate = o.OrderDate, TotalAmount = o.TotalAmount })
            .ToListAsync();
    }

    [Benchmark]
    public async Task<List<Order>> WithCoveringIndex()
    {
        // Same query, but after adding covering index
        return await _context.Orders
            .Where(o => o.CustomerId == 1)
            .Select(o => new Order { OrderDate = o.OrderDate, TotalAmount = o.TotalAmount })
            .ToListAsync();
    }
}