sql-utilities/src/Strata.SqlTools.EFCore

Strata.SqlTools.EFCore

Entity Framework Core integration and support for Strata.SqlTools QueryBreakdown functionality

This project provides seamless integration between the Strata.SqlTools query analysis framework and Entity Framework Core, allowing you to persist, query, and manage QueryBreakdown objects within your existing EF Core DbContext.

Key Features

• EF Core Integration: Map QueryBreakdown objects directly to your DbContext
• Entity Models: Fully normalized entity models for QueryBreakdownEntity, QueryParameterEntity, and WithClauseEntity
• Automatic Mapping: IQueryBreakdownMapper for converting between SQL Tools and EF Core models
• Repository Pattern: IQueryBreakdownRepository for simplified CRUD operations
• DbContext Extensions: Easy-to-use extension methods for DbContext integration
• JSON Serialization: Intelligent serialization of complex types (parameters, clauses) to JSON for efficient storage

Installation

Add the NuGet package reference:

<PackageReference Include="Strata.SqlTools.EFCore" Version="1.0.0" />

Or via the .NET CLI:

dotnet add package Strata.SqlTools.EFCore

Quick Start

1. Configure Your DbContext

Add the QueryBreakdown entities to your DbContext:

using Microsoft.EntityFrameworkCore;
using Strata.SqlTools.EFCore.Models;
using Strata.SqlTools.EFCore.Configurations;

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);

        // Configure QueryBreakdown entities
        modelBuilder.ConfigureQueryBreakdownEntities();
    }
}

2. Register Services

Register the mapper and repository in your dependency injection container:

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

3. Use the Repository

Inject and use the repository in your application:

public class QueryService
{
    private readonly IQueryBreakdownRepository _repository;

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

    public async Task SaveQueryAsync(QueryBreakdown queryBreakdown)
    {
        int id = await _repository.AddAsync(queryBreakdown);
        Console.WriteLine($"Query saved with ID: {id}");
    }

    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 DeleteQueryAsync(int id)
    {
        bool deleted = await _repository.DeleteAsync(id);
        Console.WriteLine(deleted ? "Query deleted." : "Query not found.");
    }
}

Entity Models

QueryBreakdownEntity

The main entity that represents a SQL query breakdown:

• Id: Primary key
• SelectClause: The SELECT clause
• FromClause: The FROM clause
• WhereClause: The WHERE clause
• GroupByClause: The GROUP BY clause
• HavingClause: The HAVING clause
• OrderByClause: The ORDER BY clause
• WithClause: Common Table Expressions (CTEs)
• RawSql: Original SQL statement
• SetupClausesJson: JSON serialized setup clauses
• FinishClausesJson: JSON serialized finish clauses
• ParametersJson: JSON serialized parameters
• CreatedAt: Creation timestamp
• UpdatedAt: Last update timestamp

Related Entities

• QueryParameterEntity: Represents parameters used in the query
• WithClauseEntity: Represents individual Common Table Expressions (CTEs)

Mapper Interface

The IQueryBreakdownMapper provides the following operations:

public interface IQueryBreakdownMapper
{
    QueryBreakdownEntity MapToEntity(QueryBreakdown queryBreakdown);
    QueryBreakdown MapToDomainModel(QueryBreakdownEntity entity);
    (QueryBreakdownEntity Entity, List<QueryParameterEntity> Parameters, List<WithClauseEntity> WithClauses) MapToEntityWithRelations(QueryBreakdown queryBreakdown);
    QueryBreakdown MapToDomainModelWithRelations(QueryBreakdownEntity entity);
}

Repository Interface

The IQueryBreakdownRepository provides the following operations:

public interface IQueryBreakdownRepository
{
    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();
}

Database Schema

The project includes three main tables:

QueryBreakdowns Table

Stores the main query breakdown information

QueryParameters Table

Stores individual query parameters with foreign key to QueryBreakdowns

WithClauses Table

Stores Common Table Expressions with foreign key to QueryBreakdowns

DbContext Extension Methods

// Configure query breakdown entities during model creation
modelBuilder.ConfigureQueryBreakdownEntities();

// Get queryable sets from context
var queryBreakdowns = dbContext.GetQueryBreakdowns();
var parameters = dbContext.GetQueryParameters();
var withClauses = dbContext.GetWithClauses();

// Get a query breakdown with related data
var entity = await dbContext.GetQueryBreakdownWithRelatedDataAsync(id);

Advanced Usage

Custom Entity Configuration

If you need to customize the entity configuration, you can create your own configuration classes that implement IEntityTypeConfiguration<T>:

public class CustomQueryBreakdownConfiguration : IEntityTypeConfiguration<QueryBreakdownEntity>
{
    public void Configure(EntityTypeBuilder<QueryBreakdownEntity> builder)
    {
        // Apply custom configuration
        builder.ToTable("CustomQueryBreakdowns", "dbo");
        // ... other configurations
    }
}

Working with Existing DbContext

If you already have an existing DbContext, simply:

1. Add the DbSets for QueryBreakdown entities
2. Call modelBuilder.ConfigureQueryBreakdownEntities() in OnModelCreating
3. Create a migration: dotnet ef migrations add AddQueryBreakdownEntities
4. Update the database: dotnet ef database update

Dependencies

• Microsoft.EntityFrameworkCore (8.0.0+)
• Microsoft.EntityFrameworkCore.Relational (8.0.0+)
• Strata.SqlTools (1.0.0+)
• Strata.SqlTools.SqlServer (1.0.0+)

License

MIT

Support

For issues, feature requests, or questions, please visit the GitHub repository.