sql-utilities/docs/EFCore_Integration_Guide.md

# Strata.SqlTools.EFCore Integration Guide

## Overview

The `Strata.SqlTools.EFCore` project provides seamless integration between Strata.SqlTools QueryBreakdown functionality and Entity Framework Core, allowing you to persist, query, and manage SQL query breakdowns directly within your EF Core DbContext and database.

## Architecture

### Entity Models

The project defines three main EF Core entity models:

1. **QueryBreakdownEntity**: The main entity that stores all query clause information
2. **QueryParameterEntity**: Stores individual query parameters with relationship to QueryBreakdownEntity
3. **WithClauseEntity**: Stores Common Table Expressions (CTEs) with relationship to QueryBreakdownEntity

### Services

- **IQueryBreakdownMapper**: Converts between `QueryBreakdown` (SQL Tools) and `QueryBreakdownEntity` (EF Core)
- **QueryBreakdownMapper**: Default implementation of IQueryBreakdownMapper
- **IQueryBreakdownRepository**: Repository pattern interface for CRUD operations
- **QueryBreakdownRepository**: Default implementation using EF Core DbContext

### Extension Methods

The `DbContextExtensions` class provides helper methods for easy integration with existing DbContext instances.

## Integration Steps

### Step 1: Add DbSets to Your DbContext

```csharp
using Microsoft.EntityFrameworkCore;
using Strata.SqlTools.EFCore.Models;

public class YourDbContext : DbContext
{
    // Existing DbSets...

    // Add these new DbSets for QueryBreakdown support
    public DbSet<QueryBreakdownEntity> QueryBreakdowns { get; set; }
    public DbSet<QueryParameterEntity> QueryParameters { get; set; }
    public DbSet<WithClauseEntity> WithClauses { get; set; }

    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        base.OnModelCreating(modelBuilder);

        // Configure QueryBreakdown entities using the extension method
        modelBuilder.ConfigureQueryBreakdownEntities();

        // ... rest of your OnModelCreating configuration
    }
}
```

### Step 2: Create and Apply Migrations

```bash
# Create a migration for the new entities
dotnet ef migrations add AddQueryBreakdownEntities

# Apply the migration to your database
dotnet ef database update
```

### Step 3: Register Services (if using Dependency Injection)

```csharp
// In your service configuration (e.g., Program.cs)
services.AddScoped<IQueryBreakdownMapper, QueryBreakdownMapper>();
services.AddScoped<IQueryBreakdownRepository>(
    provider => new QueryBreakdownRepository(
        provider.GetRequiredService<YourDbContext>(),
        provider.GetRequiredService<IQueryBreakdownMapper>()
    )
);
```

### Step 4: Use in Your Application

```csharp
public class QueryManagementService
{
    private readonly IQueryBreakdownRepository _repository;

    public QueryManagementService(IQueryBreakdownRepository repository)
    {
        _repository = repository;
    }

    public async Task<int> SaveQueryAsync(QueryBreakdown queryBreakdown)
    {
        return await _repository.AddAsync(queryBreakdown);
    }

    public async Task<QueryBreakdown?> GetQueryAsync(int id)
    {
        return await _repository.GetByIdAsync(id);
    }

    public async Task<List<QueryBreakdown>> GetAllQueriesAsync()
    {
        return await _repository.GetAllAsync();
    }

    public async Task UpdateQueryAsync(int id, QueryBreakdown queryBreakdown)
    {
        await _repository.UpdateAsync(id, queryBreakdown);
    }

    public async Task<bool> DeleteQueryAsync(int id)
    {
        return await _repository.DeleteAsync(id);
    }
}
```

## Data Persistence

### Serialization Strategy

Complex properties are serialized as JSON for efficient storage:

- **SetupClausesJson**: List of setup clauses
- **FinishClausesJson**: ArrayList of finish clauses
- **ParametersJson**: Dictionary of parameter names and values
- **WithClause**: String representation of the WITH clause

This approach allows for:
- Efficient schema design with minimal tables
- Flexible handling of variable-length data
- Easy deserialization back to the original objects

## Database Schema

### Tables Created

#### QueryBreakdowns
```sql
CREATE TABLE [QueryBreakdowns] (
    [Id] int NOT NULL IDENTITY,
    [SelectClause] nvarchar(max),
    [SelectClauseComment] nvarchar(max),
    [FromClause] nvarchar(max),
    [FromClauseComment] nvarchar(max),
    [WhereClause] nvarchar(max),
    [WhereClauseComment] nvarchar(max),
    [GroupByClause] nvarchar(max),
    [GroupByClauseComment] nvarchar(max),
    [HavingClause] nvarchar(max),
    [HavingClauseComment] nvarchar(max),
    [OrderByClause] nvarchar(max),
    [OrderByClauseComment] nvarchar(max),
    [WithClause] nvarchar(max),
    [RawSql] nvarchar(max),
    [SetupClausesJson] nvarchar(max),
    [FinishClausesJson] nvarchar(max),
    [ParametersJson] nvarchar(max),
    [CreatedAt] datetime2 DEFAULT GETUTCDATE(),
    [UpdatedAt] datetime2 DEFAULT GETUTCDATE(),
    CONSTRAINT [PK_QueryBreakdowns] PRIMARY KEY ([Id])
);
```

#### QueryParameters
```sql
CREATE TABLE [QueryParameters] (
    [Id] int NOT NULL IDENTITY,
    [QueryBreakdownEntityId] int NOT NULL,
    [ParameterName] nvarchar(256) NOT NULL,
    [ParameterValue] nvarchar(max),
    [ParameterTypeName] nvarchar(256),
    CONSTRAINT [PK_QueryParameters] PRIMARY KEY ([Id]),
    CONSTRAINT [FK_QueryParameters_QueryBreakdowns] FOREIGN KEY ([QueryBreakdownEntityId]) REFERENCES [QueryBreakdowns] ([Id]) ON DELETE CASCADE,
    CONSTRAINT [IX_QueryParameters_Unique] UNIQUE NONCLUSTERED ([QueryBreakdownEntityId], [ParameterName])
);
```

#### WithClauses
```sql
CREATE TABLE [WithClauses] (
    [Id] int NOT NULL IDENTITY,
    [QueryBreakdownEntityId] int NOT NULL,
    [CteName] nvarchar(256) NOT NULL,
    [ColumnList] nvarchar(max),
    [CteDefinition] nvarchar(max) NOT NULL,
    [OrderIndex] int NOT NULL,
    CONSTRAINT [PK_WithClauses] PRIMARY KEY ([Id]),
    CONSTRAINT [FK_WithClauses_QueryBreakdowns] FOREIGN KEY ([QueryBreakdownEntityId]) REFERENCES [QueryBreakdowns] ([Id]) ON DELETE CASCADE
);
```

## Advanced Usage

### Custom Entity Configuration

To customize the entity mappings, you can create your own configuration classes:

```csharp
using Microsoft.EntityFrameworkCore;
using Microsoft.EntityFrameworkCore.Metadata.Builders;
using Strata.SqlTools.EFCore.Models;

public class CustomQueryBreakdownConfiguration : IEntityTypeConfiguration<QueryBreakdownEntity>
{
    public void Configure(EntityTypeBuilder<QueryBreakdownEntity> builder)
    {
        // Map to a specific schema
        builder.ToTable("QueryBreakdowns", "queries");

        // Add additional indexes
        builder.HasIndex(e => e.CreatedAt).IsDescending();

        // Change column types
        builder.Property(e => e.RawSql)
            .HasColumnType("varchar(max)");
    }
}

// Then apply in your DbContext
protected override void OnModelCreating(ModelBuilder modelBuilder)
{
    modelBuilder.ApplyConfiguration(new CustomQueryBreakdownConfiguration());
}
```

### Querying QueryBreakdowns

You can use LINQ queries to filter and search QueryBreakdowns:

```csharp
// Get all queries created in the last 7 days
var recentQueries = await _context.GetQueryBreakdowns()
    .Where(q => q.CreatedAt >= DateTime.UtcNow.AddDays(-7))
    .OrderByDescending(q => q.CreatedAt)
    .ToListAsync();

// Find queries that select from a specific table
var userQueries = await _context.GetQueryBreakdowns()
    .Where(q => q.FromClause != null && q.FromClause.Contains("Users"))
    .ToListAsync();

// Get a query with its related parameters
var queryWithParams = await _context.GetQueryBreakdowns()
    .Include(q => q.QueryBreakdownEntity)  // Include navigation properties if configured
    .FirstOrDefaultAsync(q => q.Id == queryId);
```

### Bulk Operations

For efficient bulk operations:

```csharp
var mapper = new QueryBreakdownMapper();

// Bulk insert
var queryBreakdowns = LoadQueriesFromSource();
var entities = queryBreakdowns.Select(q => mapper.MapToEntity(q)).ToList();

_context.Set<QueryBreakdownEntity>().AddRange(entities);
await _context.SaveChangesAsync();

// Bulk update
var existingQueries = await _context.GetQueryBreakdowns().ToListAsync();
foreach (var entity in existingQueries)
{
    entity.UpdatedAt = DateTime.UtcNow;
}
await _context.SaveChangesAsync();
```

## Performance Considerations

### Indexing

The configuration includes indexes on:
- Primary keys (Id)
- Foreign keys (QueryBreakdownEntityId)
- Timestamp columns (CreatedAt, UpdatedAt)
- Unique combinations (QueryBreakdownEntityId + ParameterName)
- OrderIndex for WITH clauses

### Query Optimization

For best performance:

1. **Use LINQ projections** instead of loading full entities when possible
2. **Use .AsNoTracking()** for read-only queries
3. **Include related data** with `.Include()` only when needed
4. **Use pagination** for large result sets
5. **Create indexes** on frequently filtered columns

Examples:

```csharp
// Good: Projection for read-only access
var queryTexts = await _context.GetQueryBreakdowns()
    .AsNoTracking()
    .Select(q => new { q.Id, q.SelectClause, q.FromClause })
    .ToListAsync();

// Good: Pagination
var page = await _context.GetQueryBreakdowns()
    .AsNoTracking()
    .OrderByDescending(q => q.CreatedAt)
    .Skip((pageNumber - 1) * pageSize)
    .Take(pageSize)
    .ToListAsync();
```

## Migration Scenarios

### Existing DbContext with Query Tables

If you already have query tables in your database:

1. Create a custom configuration that maps to your existing tables
2. Adjust property names and column types as needed
3. Create a migration with appropriate mapping

```csharp
protected override void OnModelCreating(ModelBuilder modelBuilder)
{
    modelBuilder.Entity<QueryBreakdownEntity>()
        .ToTable("YourExistingQueryTable");

    modelBuilder.Entity<QueryBreakdownEntity>()
        .Property(e => e.SelectClause)
        .HasColumnName("YourSelectColumn");
}
```

## Troubleshooting

### Issue: SqlException when creating entities

**Solution**: Ensure the migration has been applied: `dotnet ef database update`

### Issue: JSON deserialization errors

**Solution**: Verify that the JSON serialization format matches. The mapper uses `System.Text.Json.JsonSerializer`.

### Issue: Navigation properties are null

**Solution**: Use `.Include()` when querying to load related entities:
```csharp
var entity = await _context.GetQueryBreakdowns()
    .Include(q => q.QueryBreakdownEntity)
    .FirstOrDefaultAsync(q => q.Id == id);
```

### Issue: Foreign key constraint violations

**Solution**: Ensure that parent entities (QueryBreakdownEntity) are saved before child entities (QueryParameterEntity, WithClauseEntity). The repository handles this automatically.

## Best Practices

1. **Always use transactions** for operations that modify multiple entities
2. **Validate input** before saving to the database
3. **Use eager loading** (`.Include()`) sparingly to avoid performance issues
4. **Monitor database growth** as JSON columns can become large
5. **Implement archival policies** for old query breakdowns
6. **Use async/await** for all database operations
7. **Handle concurrency** using datetime stamps or EF Core's concurrency tokens

## Support and Documentation

- For detailed API documentation, see the README.md in the main EFCore project folder
- For examples of QueryBreakdown usage, see the Strata.SqlTools documentation
- For EF Core documentation, visit https://docs.microsoft.com/en-us/ef/core/