sql-utilities/docs/EFCore_Project_Summary.md

Strata.SqlTools.EFCore - Project Creation Summary

Overview

Successfully created the Strata.SqlTools.EFCore project, a new Entity Framework Core integration library for the Strata.SqlTools.QueryBreakdown functionality. This project enables seamless persistence, querying, and management of SQL query breakdowns within EF Core DbContexts and existing databases.

Project Structure

Main Project: Strata.SqlTools.EFCore

Located at: src/Strata.SqlTools.EFCore/

Directory Structure

Strata.SqlTools.EFCore/
├── Strata.SqlTools.EFCore.csproj
├── README.md
├── Models/
│   ├── QueryBreakdownEntity.cs       - Main entity for query breakdowns
│   ├── QueryParameterEntity.cs       - Entity for query parameters
│   └── WithClauseEntity.cs           - Entity for CTEs
├── Configurations/
│   ├── QueryBreakdownEntityConfiguration.cs
│   ├── QueryParameterEntityConfiguration.cs
│   └── WithClauseEntityConfiguration.cs
├── Services/
│   ├── QueryBreakdownMapper.cs       - Mapper between QueryBreakdown and entities
│   ├── QueryBreakdownRepository.cs   - Repository pattern implementation
│   └── DbContextExtensions.cs        - Extension methods for DbContext
└── Abstractions/
    └── IQueryBreakdownMapper.cs      - Mapper interface

Test Project: Strata.SqlTools.EFCore.Tests

Located at: tests/Strata.SqlTools.EFCore.Tests/

Test Files

• QueryBreakdownMapperTests.cs - Tests for entity mapping
• QueryBreakdownRepositoryTests.cs - Tests for repository operations
• TestDbContext.cs - In-memory test DbContext

Key Features Implemented

1. Entity Models

QueryBreakdownEntity

• Stores all SQL query clause information (SELECT, FROM, WHERE, GROUP BY, HAVING, ORDER BY)
• Includes comments for each clause
• JSON serialization for complex types (setup clauses, finish clauses, parameters, WITH clauses)
• Timestamp tracking (CreatedAt, UpdatedAt)
• Primary key and relationships defined

QueryParameterEntity

• Represents individual query parameters
• Stores parameter name, value, and type information
• Foreign key relationship to QueryBreakdownEntity
• Unique constraint on (QueryBreakdownEntityId, ParameterName)

WithClauseEntity

• Represents Common Table Expressions (CTEs)
• Stores CTE name, column list, and definition
• Maintains ordering of multiple CTEs
• Foreign key relationship to QueryBreakdownEntity

2. EF Core Configurations

All entities are configured with:

• Proper table names and column types
• Foreign key relationships with cascade delete
• Appropriate indexes for query performance
• Constraints and uniqueness rules
• Default values for timestamps

3. Mapping Services

IQueryBreakdownMapper Interface

• MapToEntity() - Converts QueryBreakdown to QueryBreakdownEntity
• MapToDomainModel() - Converts QueryBreakdownEntity back to QueryBreakdown
• MapToEntityWithRelations() - Includes related entities (parameters, CTEs)
• MapToDomainModelWithRelations() - Restores fully hydrated QueryBreakdown

QueryBreakdownMapper Implementation

• Handles all type conversions and serialization
• Preserves clause comments and metadata
• Properly serializes/deserializes complex types using System.Text.Json
• Full round-trip support for QueryBreakdown objects

4. Repository Pattern

IQueryBreakdownRepository Interface

// CRUD Operations
Task<int> AddAsync(QueryBreakdown queryBreakdown);
Task<QueryBreakdown?> GetByIdAsync(int id);
Task<QueryBreakdownEntity?> GetEntityByIdAsync(int id);
Task<List<QueryBreakdown>> GetAllAsync();
Task<List<QueryBreakdownEntity>> GetAllEntitiesAsync();
Task UpdateAsync(int id, QueryBreakdown queryBreakdown);
Task<bool> DeleteAsync(int id);
Task<int> GetCountAsync();

QueryBreakdownRepository Implementation

• Simplified CRUD operations
• Automatic handling of related entities
• Proper transaction management
• Validation and error handling

5. DbContext Extensions

Extension Methods:

• ConfigureQueryBreakdownEntities() - Apply all entity configurations
• GetQueryBreakdowns() - Queryable set of QueryBreakdownEntity
• GetQueryParameters() - Queryable set of QueryParameterEntity
• GetWithClauses() - Queryable set of WithClauseEntity
• GetQueryBreakdownWithRelatedDataAsync() - Get entity with relations

Documentation

README.md

Comprehensive guide including:

• Feature overview
• Installation instructions
• Quick start examples
• Entity model descriptions
• Mapper and repository interface documentation
• Database schema information
• DbContext extension methods
• Advanced usage examples
• Dependency listing

EFCore_Integration_Guide.md

Detailed integration guide covering:

• Architecture overview
• Step-by-step integration steps
• Data persistence strategies
• Database schema details
• Advanced usage patterns
• Query optimization tips
• Migration scenarios
• Troubleshooting guide
• Best practices

Database Schema

Three tables are created/configured:

1. QueryBreakdowns (Primary table)

   • Stores query clause information
   • Indexes on CreatedAt, UpdatedAt
   • Automatic timestamp defaults

2. QueryParameters (Related table)

   • Stores individual parameters
   • Foreign key to QueryBreakdowns (cascade delete)
   • Unique index on (QueryBreakdownEntityId, ParameterName)

3. WithClauses (Related table)

   • Stores CTEs/WITH clauses
   • Foreign key to QueryBreakdowns (cascade delete)
   • Index on (QueryBreakdownEntityId, OrderIndex)

Dependencies

Project Dependencies

• Strata.SqlTools (Core library)
• Strata.SqlTools.SqlServer (SQL Server implementation)

NuGet Dependencies

• Microsoft.EntityFrameworkCore (8.0.0+)
• Microsoft.EntityFrameworkCore.Relational (8.0.0+)

Test Dependencies

• Microsoft.EntityFrameworkCore.InMemory (for in-memory testing)
• NUnit (4.1.0+)
• NUnit3TestAdapter (4.5.0+)
• Microsoft.NET.Test.Sdk (17.8.2+)

Build Status

✅ Successful Build

• Main project: Strata.SqlTools.EFCore - Builds successfully
• Test project: Strata.SqlTools.EFCore.Tests - Builds successfully
• Solution: Strata.SqlTools.QueryBreakdown.sln - Builds successfully
• No compilation errors
• Zero warnings in main projects

Project Files

Newly Created Files

Source Project Files:

• src/Strata.SqlTools.EFCore/Strata.SqlTools.EFCore.csproj
• src/Strata.SqlTools.EFCore/README.md
• src/Strata.SqlTools.EFCore/Models/QueryBreakdownEntity.cs
• src/Strata.SqlTools.EFCore/Models/QueryParameterEntity.cs
• src/Strata.SqlTools.EFCore/Models/WithClauseEntity.cs
• src/Strata.SqlTools.EFCore/Configurations/QueryBreakdownEntityConfiguration.cs
• src/Strata.SqlTools.EFCore/Configurations/QueryParameterEntityConfiguration.cs
• src/Strata.SqlTools.EFCore/Configurations/WithClauseEntityConfiguration.cs
• src/Strata.SqlTools.EFCore/Abstractions/IQueryBreakdownMapper.cs
• src/Strata.SqlTools.EFCore/Services/QueryBreakdownMapper.cs
• src/Strata.SqlTools.EFCore/Services/QueryBreakdownRepository.cs
• src/Strata.SqlTools.EFCore/Services/DbContextExtensions.cs

Test Project Files:

• tests/Strata.SqlTools.EFCore.Tests/Strata.SqlTools.EFCore.Tests.csproj
• tests/Strata.SqlTools.EFCore.Tests/QueryBreakdownMapperTests.cs
• tests/Strata.SqlTools.EFCore.Tests/QueryBreakdownRepositoryTests.cs
• tests/Strata.SqlTools.EFCore.Tests/TestDbContext.cs

Documentation Files:

• docs/EFCore_Integration_Guide.md

Modified Files

• Strata.SqlTools.QueryBreakdown.sln - Added new projects with proper GUIDs and configuration

Usage Example

// 1. Configure DbContext
public class YourDbContext : DbContext
{
    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);
        modelBuilder.ConfigureQueryBreakdownEntities();
    }
}

// 2. Use the repository
var mapper = new QueryBreakdownMapper();
var repository = new QueryBreakdownRepository(dbContext, mapper);

// 3. Save a query breakdown
var query = new QueryBreakdown("ID, Name", "Users", "Active = 1");
query.AddParameter("Status", "Active");
int id = await repository.AddAsync(query);

// 4. Retrieve and work with it
var retrievedQuery = await repository.GetByIdAsync(id);
var sql = retrievedQuery?.GetSql(); // Get the final SQL

Next Steps

1. Database Migration: Create and apply EF Core migrations for your database

   dotnet ef migrations add AddQueryBreakdownEntities
   dotnet ef database update
   

2. Dependency Injection: Register the mapper and repository in your DI container

   services.AddScoped<IQueryBreakdownMapper, QueryBreakdownMapper>();
   services.AddScoped<IQueryBreakdownRepository>(provider =>
       new QueryBreakdownRepository(
           provider.GetRequiredService<YourDbContext>(),
           provider.GetRequiredService<IQueryBreakdownMapper>()
       )
   );
   

3. Integration Testing: Run the test suite to verify everything works correctly

   dotnet test tests/Strata.SqlTools.EFCore.Tests/
   

4. Custom Configuration: Extend entity configurations for your specific database needs

Notes

• The project follows the same naming and structure conventions as other Strata.SqlTools projects
• All code includes comprehensive XML documentation comments
• The implementation supports both SQL Server and other EF Core-supported databases
• JSON serialization is used for efficient storage of complex types
• The mapper handles type conversions and null values gracefully
• Full transaction support for multi-entity operations
• Cascade delete is configured for referential integrity

Package Information

When ready for NuGet publishing:

• Package ID: Strata.SqlTools.EFCore
• Version: 1.0.0
• Framework: .NET 8.0
• License: MIT
• Product: Strata SQL Utilities - EF Core
• Description: Entity Framework Core integration for Strata.SqlTools QueryBreakdown functionality

---

Created: February 23, 2026 Status: Complete and Ready for Use