sql-utilities/docs/SqlUtilities.LinqToSql.md

# Strata.SqlTools.LinqToSql

**LINQ to SQL Query Analysis and Visualization**

---

## Overview

The `Strata.SqlTools.LinqToSql` package provides comprehensive support for analyzing LINQ to SQL queries by examining their expression trees. It extracts query components (SELECT, WHERE, ORDER BY, etc.) and provides visualization tools for understanding query structure and execution flow.

This package is particularly useful for:
- **Query Analysis**: Understanding how LINQ queries translate to SQL
- **Performance Optimization**: Identifying inefficient query patterns
- **Documentation**: Generating visual diagrams of query structure
- **Debugging**: Tracing LINQ method chains and their SQL equivalents

### Key Features

- ✅ **Expression Tree Analysis** - Parse IQueryable expression trees to extract SQL components
- ✅ **LINQ Method Chain Tracking** - Track Where, Select, OrderBy, GroupBy method calls
- ✅ **Statement Type Analysis** - Analyze INSERT, UPDATE, DELETE, PROCEDURE, and TRACE operations
- ✅ **Mermaid Diagram Generation** - Visualize queries with flowcharts and sequence diagrams
- ✅ **SQL Component Extraction** - Extract SELECT, WHERE, ORDER BY, GROUP BY clauses
- ✅ **Integration with SqlServer** - Built on top of SqlServer.QueryBreakdown
- ✅ **Type-Safe Analysis** - Strongly-typed entity detection

---

## Installation

```bash
dotnet add package Strata.SqlTools.LinqToSql
```

**Dependencies:**
- `Strata.SqlTools` (core functionality)
- `Strata.SqlTools.SqlServer` (base query breakdown)
- .NET 8.0+

---

## Quick Start

### Basic Query Analysis

```csharp
using Strata.SqlTools.Breakdowns.LinqToSql;

// Define your data context with IQueryable properties
public class DataContext
{
    public IQueryable<User> Users => new List<User>().AsQueryable();
}

public class User
{
    public int Id { get; set; }
    public string Name { get; set; }
    public int Age { get; set; }
    public bool IsActive { get; set; }
}

// Analyze a LINQ query
var context = new DataContext();
var query = context.Users.Where(u => u.Age > 21).OrderBy(u => u.Name);

var breakdown = LinqQueryBreakdown.Analyze(query);

// Access extracted components
Console.WriteLine($"Entity Type: {breakdown.EntityType}");
Console.WriteLine($"SELECT: {breakdown.SelectClause}");
Console.WriteLine($"FROM: {breakdown.FromClause}");
Console.WriteLine($"WHERE: {breakdown.WhereClause}");
Console.WriteLine($"ORDER BY: {breakdown.OrderByClause}");

// Get method chain
var methodChain = breakdown.GetMethodChain();
Console.WriteLine($"Method Chain: {string.Join(" -> ", methodChain)}");
```

**Output:**
```
Entity Type: User
SELECT: *
FROM: Users
WHERE: (Age > 21)
ORDER BY: Name ASC
Method Chain: Where -> OrderBy
```

---

## Core Components

### LinqQueryBreakdown Class

The main class for analyzing LINQ queries.

#### Static Analysis Methods

```csharp
// SELECT Query Analysis
public static LinqQueryBreakdown Analyze<T>(IQueryable<T> query)
public static bool TryAnalyze<T>(IQueryable<T> query, out LinqQueryBreakdown? breakdown)

// INSERT Operations
public static InsertBreakdown AnalyzeInsert<T>(T entity) where T : class
public static InsertBreakdown AnalyzeInsertRange<T>(IEnumerable<T> entities) where T : class

// DELETE Operations
public static DeleteBreakdown AnalyzeDelete<T>(Expression<Func<T, bool>> filterExpression) where T : class

// UPDATE Operations
public static UpdateBreakdown AnalyzeUpdate<T>(
    Expression<Func<T, bool>> filterExpression,
    Expression<Func<T, T>> updateExpression) where T : class

// PROCEDURE Operations
public static ProcedureBreakdown AnalyzeProcedure(string procedureName, params object[] parameters)

// TRACE Operations
public static string AnalyzeTrace<T>(IQueryable<T> query, string? executionContext = null) where T : class
```

#### Properties

```csharp
public Expression? OriginalExpression { get; }      // Original LINQ expression tree
public string EntityType { get; }                   // Entity type name (e.g., "User")
public List<string> MethodCallChain { get; }        // List of LINQ method calls
```

#### Methods

```csharp
public string GetQuerySummary()                     // Human-readable query summary
public List<string> GetMethodChain()                // LINQ method call sequence
```

---

## LinqExpressionVisitor

The expression visitor that traverses LINQ expression trees to extract SQL components.

### Supported LINQ Methods

| LINQ Method | SQL Clause | Example |
|-------------|------------|---------|
| `Where()` | WHERE | `users.Where(u => u.Age > 21)` |
| `Select()` | SELECT | `users.Select(u => new { u.Id, u.Name })` |
| `OrderBy()` | ORDER BY | `users.OrderBy(u => u.Name)` |
| `OrderByDescending()` | ORDER BY DESC | `users.OrderByDescending(u => u.Age)` |
| `GroupBy()` | GROUP BY | `users.GroupBy(u => u.Department)` |
| `ThenBy()` | ORDER BY (multiple) | `users.OrderBy(u => u.Name).ThenBy(u => u.Age)` |

### Expression Types Handled

- **Binary Expressions**: `>`, `<`, `>=`, `<=`, `==`, `!=`, `&&`, `||`
- **Member Access**: Property/field access (e.g., `u.Age`)
- **Constants**: Literal values
- **Method Calls**: LINQ extension methods

---

## Markdown Visualization

The `Strata.SqlTools.Markdown` package includes specialized generators for LinqToSql.

### Installation

```bash
dotnet add package Strata.SqlTools.Markdown
```

### QueryBreakdownGenerator

Generates Mermaid diagrams showing query structure and LINQ method chains.

```csharp
using Strata.SqlTools.Markdown.LinqToSql;

var generator = new QueryBreakdownGenerator();
var query = context.Users
    .Where(u => u.Age > 21)
    .OrderBy(u => u.Name)
    .Select(u => new { u.Id, u.Name });

var breakdown = LinqQueryBreakdown.Analyze(query);

// Generate SQL structure diagram
string sqlDiagram = generator.GenerateMermaidDiagram(breakdown, "User Query");

// Generate LINQ method chain diagram
string methodDiagram = generator.GenerateMethodChainDiagram(breakdown, "Method Flow");

// Generate combined diagram (both SQL structure and method chain)
string combined = generator.GenerateCombinedDiagram(breakdown, "Complete Analysis");
```

**Example Method Chain Diagram:**

```mermaid
flowchart LR
    Start[IQueryable] --> Where[Where]
    Where --> OrderBy[OrderBy]
    OrderBy --> Select[Select]
    Select --> Result[Result]
```

### SqlStatementGenerator

Generates sequence diagrams showing LINQ execution pipeline.

```csharp
var stmtGenerator = new SqlStatementGenerator();

// Generate LINQ execution pipeline diagram
string pipeline = stmtGenerator.GenerateLinqPipelineDiagram(breakdown, "Query Execution");

// Generate sequence diagram
string sequence = stmtGenerator.GenerateSequenceDiagram(breakdown, "Execution Flow");

// Generate ER diagram
string erDiagram = stmtGenerator.GenerateEntityRelationshipDiagram(breakdown, "Entity Model");
```

**Example LINQ Pipeline Diagram:**

```mermaid
sequenceDiagram
    participant Client as Client Application
    participant LINQ as LINQ Provider
    participant ET as Expression Tree
    participant SQL as SQL Generator
    participant DB as Database

    Client->>LINQ: LINQ Query
    activate LINQ
    LINQ->>ET: Where Predicate
    activate ET
    LINQ->>ET: Select Projection
    ET->>SQL: Expression Tree
    deactivate ET
    SQL->>DB: Generate SQL
    activate DB
    DB-->>SQL: Result Set
    deactivate DB
    SQL-->>LINQ: Mapped Objects
    LINQ-->>Client: IEnumerable Result
    deactivate LINQ
```

---

## Advanced Usage

### Complex Query Analysis

```csharp
// Multi-clause query
var complexQuery = context.Orders
    .Where(o => o.Amount > 1000)
    .Where(o => o.Status == "Pending")
    .OrderBy(o => o.OrderDate)
    .ThenByDescending(o => o.Amount)
    .Select(o => new
    {
        o.Id,
        o.CustomerName,
        o.Amount
    });

var breakdown = LinqQueryBreakdown.Analyze(complexQuery);

Console.WriteLine(breakdown.GetQuerySummary());
// Output: "SELECT projection FROM Orders WHERE (Amount > 1000) AND (Status = 'Pending') ORDER BY OrderDate ASC, Amount DESC"

var methods = breakdown.GetMethodChain();
// Output: ["Where", "Where", "OrderBy", "ThenByDescending", "Select"]
```

### Safe Analysis with TryAnalyze

```csharp
if (LinqQueryBreakdown.TryAnalyze(query, out var breakdown))
{
    Console.WriteLine($"Successfully analyzed: {breakdown.GetQuerySummary()}");

    // Access components safely
    if (!string.IsNullOrEmpty(breakdown.WhereClause))
    {
        Console.WriteLine($"WHERE clause: {breakdown.WhereClause}");
    }
}
else
{
    Console.WriteLine("Unable to analyze query");
}
```

### Accessing Inherited SqlServer Properties

`LinqQueryBreakdown` inherits from `SqlServer.QueryBreakdown`, providing access to all standard query breakdown features:

```csharp
var breakdown = LinqQueryBreakdown.Analyze(query);

// Standard QueryBreakdown properties
Console.WriteLine($"SELECT: {breakdown.SelectClause}");
Console.WriteLine($"FROM: {breakdown.FromClause}");
Console.WriteLine($"WHERE: {breakdown.WhereClause}");
Console.WriteLine($"GROUP BY: {breakdown.GroupByClause}");
Console.WriteLine($"HAVING: {breakdown.HavingClause}");
Console.WriteLine($"ORDER BY: {breakdown.OrderByClause}");

// Generate SQL
string sql = breakdown.GetSql();

// Clone breakdown
var clone = (LinqQueryBreakdown)breakdown.Clone();
```

---

## Statement Type Analysis

Beyond SELECT queries, `LinqQueryBreakdown` provides comprehensive analysis for other statement types.

### INSERT Analysis

```csharp
// Single entity insert
var user = new User { Id = 1, Name = "John Doe", Email = "john@example.com", Age = 30 };
var insertBreakdown = LinqQueryBreakdown.AnalyzeInsert(user);

Console.WriteLine($"Table: {insertBreakdown.TableName}");     // User
Console.WriteLine($"Columns: {insertBreakdown.InsertIntoClause}");
Console.WriteLine($"Values: {insertBreakdown.ValuesClause}");

// Bulk insert
var users = new List<User>
{
    new User { Id = 1, Name = "John Doe", Email = "john@example.com", Age = 30 },
    new User { Id = 2, Name = "Jane Smith", Email = "jane@example.com", Age = 28 }
};

var bulkInsertBreakdown = LinqQueryBreakdown.AnalyzeInsertRange(users);

Console.WriteLine($"Inserting {bulkInsertBreakdown.ValuesClause.Count(c => c == '(')} rows");
```

### DELETE Analysis

```csharp
// Analyze deletion with filter expression
var deleteBreakdown = LinqQueryBreakdown.AnalyzeDelete<User>(u => u.Age < 18);

Console.WriteLine($"Table: {deleteBreakdown.FromClause}");    // User
Console.WriteLine($"WHERE: {deleteBreakdown.WhereClause}");   // (Age < 18)

// Complex filter
var complexDelete = LinqQueryBreakdown.AnalyzeDelete<Order>(o => o.Status == "Cancelled" && o.OrderDate < DateTime.Now.AddYears(-1));
Console.WriteLine($"Deleting old cancelled orders: {complexDelete.WhereClause}");
```

### UPDATE Analysis

```csharp
// Analyze update with filter and SET expressions
var updateBreakdown = LinqQueryBreakdown.AnalyzeUpdate<User>(
    u => u.Department == "Sales",
    u => new User { IsActive = false, UpdatedDate = DateTime.Now }
);

Console.WriteLine($"Table: {updateBreakdown.TableName}");      // User
Console.WriteLine($"WHERE: {updateBreakdown.WhereClause}");    // (Department = 'Sales')
Console.WriteLine($"SET: {updateBreakdown.SetClause}");        // Column assignments

// Practical example: Deactivate inactive users
var deactivateBreakdown = LinqQueryBreakdown.AnalyzeUpdate<User>(
    u => u.LastLoginDate < DateTime.Now.AddDays(-90),
    u => new User { IsActive = false }
);
```

### PROCEDURE Analysis

```csharp
// Simple procedure call
var procBreakdown = LinqQueryBreakdown.AnalyzeProcedure("sp_GetUsers");

Console.WriteLine($"Procedure: {procBreakdown.ProcedureName}");
Console.WriteLine($"Parameters: {procBreakdown.Parameters.Count}");

// Procedure with parameters
var procWithParamsBreakdown = LinqQueryBreakdown.AnalyzeProcedure(
    "sp_GetUsersByAgeRange",
    18, 65
);

Console.WriteLine($"Procedure: {procWithParamsBreakdown.ProcedureName}");
Console.WriteLine($"Parameter count: {procWithParamsBreakdown.Parameters.Count}");

foreach (var param in procWithParamsBreakdown.Parameters)
{
    Console.WriteLine($"  {param.Key}: {param.Value}");
}
```

### TRACE Analysis

```csharp
// Analyze query execution context
var query = _context.Users.Where(u => u.IsActive);

var traceInfo = LinqQueryBreakdown.AnalyzeTrace(query, "Initial User Load");

Console.WriteLine(traceInfo);
// Output:
// Trace Context for User
// Entity Type: Namespace.User
// Query Provider: EntityQueryProvider
// Expression: Where(Where(...))
// Execution Context: Initial User Load
// Timestamp: 2026-02-24T10:30:45.1234567Z

// Use in logging
_logger.LogInformation("Query trace:\n{Trace}", traceInfo);
```

---

## Use Cases

### 1. Query Performance Analysis

```csharp
var query = context.Products
    .Where(p => p.Price > 100)
    .Where(p => p.InStock)
    .OrderBy(p => p.Name);

var breakdown = LinqQueryBreakdown.Analyze(query);

// Check for multiple WHERE clauses (could be combined)
var whereCount = breakdown.MethodCallChain.Count(m => m == "Where");
if (whereCount > 1)
{
    Console.WriteLine($"Warning: {whereCount} separate WHERE clauses detected. Consider combining.");
}
```

### 2. Documentation Generation

```csharp
var queries = new Dictionary<string, IQueryable>
{
    ["ActiveUsers"] = context.Users.Where(u => u.IsActive),
    ["RecentOrders"] = context.Orders.Where(o => o.OrderDate > DateTime.Now.AddDays(-30)),
    ["TopProducts"] = context.Products.OrderByDescending(p => p.SalesCount).Take(10)
};

var generator = new QueryBreakdownGenerator();
var documentation = new StringBuilder();

foreach (var (name, query) in queries)
{
    var breakdown = LinqQueryBreakdown.Analyze(query);
    var diagram = generator.GenerateCombinedDiagram(breakdown, name);

    documentation.AppendLine($"## {name}");
    documentation.AppendLine(breakdown.GetQuerySummary());
    documentation.AppendLine(diagram);
    documentation.AppendLine();
}

File.WriteAllText("queries.md", documentation.ToString());
```

### 4. Data Modification Auditing

```csharp
public class AuditLogger
{
    public void LogInsert<T>(T entity) where T : class
    {
        var breakdown = LinqQueryBreakdown.AnalyzeInsert(entity);
        var audit = new AuditEntry
        {
            Operation = "INSERT",
            Table = breakdown.TableName.Clause,
            Columns = breakdown.InsertIntoClause.Clause,
            Timestamp = DateTime.UtcNow
        };
        _auditContext.SaveAudit(audit);
    }

    public void LogDelete<T>(Expression<Func<T, bool>> filter) where T : class
    {
        var breakdown = LinqQueryBreakdown.AnalyzeDelete(filter);
        var audit = new AuditEntry
        {
            Operation = "DELETE",
            Table = breakdown.FromClause.Clause,
            Condition = breakdown.WhereClause?.Clause,
            Timestamp = DateTime.UtcNow
        };
        _auditContext.SaveAudit(audit);
    }

    public void LogUpdate<T>(Expression<Func<T, bool>> filter, Expression<Func<T, T>> updates) where T : class
    {
        var breakdown = LinqQueryBreakdown.AnalyzeUpdate(filter, updates);
        var audit = new AuditEntry
        {
            Operation = "UPDATE",
            Table = breakdown.TableName.Clause,
            Updates = breakdown.SetClause?.Clause,
            Condition = breakdown.WhereClause?.Clause,
            Timestamp = DateTime.UtcNow
        };
        _auditContext.SaveAudit(audit);
    }
}
```

### 5. Dynamic Query Logging

```csharp
public class QueryLogger
{
    public void TraceExecution<T>(IQueryable<T> query, string context) where T : class
    {
        var traceInfo = LinqQueryBreakdown.AnalyzeTrace(query, context);

        _logger.LogInformation("Query Execution Trace:\n{TraceInfo}", traceInfo);

        if (LinqQueryBreakdown.TryAnalyze(query, out var breakdown))
        {
            _logger.LogDebug("Query Summary: {Summary}", breakdown.GetQuerySummary());
            _logger.LogDebug("Methods: {Methods}", string.Join(" -> ", breakdown.GetMethodChain()));
        }
    }
}

// Usage
var query = _context.Users.Where(u => u.IsActive).OrderBy(u => u.Name);
_queryLogger.TraceExecution(query, "Active Users Report");
```

---

## Architecture

### Class Hierarchy

```
IQueryBreakdown (Interface)
    ↑
QueryBreakdown (Strata.SqlTools)
    ↑
SqlServer.QueryBreakdown
    ↑
LinqQueryBreakdown
```

### Component Interaction

```mermaid
flowchart TD
    A[IQueryable<T>] --> B[LinqQueryBreakdown.Analyze]
    B --> C[LinqExpressionVisitor]
    C --> D{Expression Type}
    D -->|MethodCall| E[VisitMethodCall]
    D -->|Binary| F[VisitBinary]
    D -->|Member| G[VisitMember]
    D -->|Constant| H[VisitConstant]
    E --> I[Extract WHERE/SELECT/ORDER BY]
    F --> I
    G --> I
    H --> I
    I --> J[LinqQueryBreakdown Instance]
    J --> K[QueryBreakdownGenerator]
    K --> L[Mermaid Diagrams]
```

---

## Limitations

### Current Limitations

1. **Limited LINQ Method Support**: Currently supports Where, Select, OrderBy, OrderByDescending, ThenBy, GroupBy
   - Not yet supported: Join, GroupJoin, Skip, Take, First, Last, etc.

2. **Simple Expressions Only**: Complex lambda expressions may not be fully parsed
   - Example: Nested method calls in predicates

3. **No Subquery Analysis**: Subqueries in LINQ are not yet analyzed

4. **Entity Framework Specific**: Optimized for LINQ to SQL/Entity Framework patterns
   - May not work with all IQueryable providers

5. **No Query Reconstruction**: The `GetQuery<T>()` method returns null because breakdowns are analyzed one-way
   - Breakdown analysis cannot reconstruct the original LINQ query without the data provider

### What's Now Supported

✅ **INSERT Analysis** - Extract column names and values from entity instances
✅ **DELETE Analysis** - Extract filter conditions for deletion
✅ **UPDATE Analysis** - Extract filter conditions and SET clauses
✅ **PROCEDURE Analysis** - Parse procedure names and parameters
✅ **TRACE Analysis** - Capture query execution context with timestamps

### Workarounds

For unsupported methods, you can still access the base `QueryBreakdown` properties:

```csharp
var query = context.Users.Take(10); // Take() not explicitly tracked

var breakdown = LinqQueryBreakdown.Analyze(query);
// SelectClause, FromClause still available
// MethodCallChain may be incomplete
```

For query reconstruction, use the original IQueryable directly rather than attempting to reconstruct from the breakdown.

---

## Best Practices

### 1. Use TryAnalyze for Dynamic Queries

```csharp
// Good: Handle analysis failures gracefully
if (LinqQueryBreakdown.TryAnalyze(userProvidedQuery, out var breakdown))
{
    ProcessBreakdown(breakdown);
}
else
{
    LogError("Unable to analyze query");
}

// Avoid: Analyze() throws on failure
var breakdown = LinqQueryBreakdown.Analyze(userProvidedQuery); // May throw
```

### 2. Check for Null Components

```csharp
var breakdown = LinqQueryBreakdown.Analyze(query);

// Good: Check before using
if (!string.IsNullOrEmpty(breakdown.WhereClause))
{
    Console.WriteLine($"Filter: {breakdown.WhereClause}");
}

// Avoid: Direct access without checking
Console.WriteLine(breakdown.WhereClause.Length); // NullReferenceException if no WHERE
```

### 3. Combine with Logging

```csharp
public IQueryable<User> GetFilteredUsers(int minAge)
{
    var query = _context.Users.Where(u => u.Age >= minAge);

    // Log query structure for debugging
    if (LinqQueryBreakdown.TryAnalyze(query, out var breakdown))
    {
        _logger.LogDebug("Query: {Summary}", breakdown.GetQuerySummary());
        _logger.LogDebug("Methods: {Methods}", string.Join(", ", breakdown.GetMethodChain()));
    }

    return query;
}
```

---

## Testing

The LinqToSql package includes comprehensive unit tests for all analysis types:

### SELECT Query Tests

```csharp
[Test]
public void Analyze_SimpleSelectQuery_ExtractsTableName()
{
    var query = _context.Users;
    var breakdown = LinqQueryBreakdown.Analyze(query);

    Assert.That(breakdown.EntityType, Is.EqualTo("User"));
    Assert.That(breakdown.FromClause, Is.EqualTo("Users"));
}

[Test]
public void Analyze_WhereClause_ExtractsCondition()
{
    var query = _context.Users.Where(u => u.Age > 21);
    var breakdown = LinqQueryBreakdown.Analyze(query);

    Assert.That(breakdown.WhereClause, Does.Contain("Age"));
    Assert.That(breakdown.WhereClause, Does.Contain(">"));
    Assert.That(breakdown.WhereClause, Does.Contain("21"));
}

[Test]
public void GetMethodChain_MultipleOperations_ReturnsCorrectSequence()
{
    var query = _context.Users
        .Where(u => u.IsActive)
        .OrderBy(u => u.Name)
        .Select(u => new { u.Id, u.Name });

    var breakdown = LinqQueryBreakdown.Analyze(query);
    var chain = breakdown.GetMethodChain();

    Assert.That(chain, Is.EqualTo(new[] { "Where", "OrderBy", "Select" }));
}
```

### Statement Type Tests

```csharp
[Test]
public void AnalyzeInsert_SingleEntity_CreatesInsertBreakdown()
{
    var entity = new User { Id = 1, Name = "John Doe", Email = "john@example.com", Age = 30 };
    var breakdown = LinqQueryBreakdown.AnalyzeInsert(entity);

    Assert.That(breakdown.TableName.Clause, Is.EqualTo("User"));
    Assert.That(breakdown.InsertIntoClause.Clause, Does.Contain("Id"));
    Assert.That(breakdown.InsertIntoClause.Clause, Does.Contain("Name"));
}

[Test]
public void AnalyzeInsertRange_MultipleEntities_CreatesInsertBreakdown()
{
    var entities = new List<User>
    {
        new User { Id = 1, Name = "John Doe", Email = "john@example.com", Age = 30 },
        new User { Id = 2, Name = "Jane Smith", Email = "jane@example.com", Age = 28 }
    };

    var breakdown = LinqQueryBreakdown.AnalyzeInsertRange(entities);

    Assert.That(breakdown.TableName.Clause, Is.EqualTo("User"));
    Assert.That(breakdown.ValuesClause.Clause, Does.Contain("("));
}

[Test]
public void AnalyzeDelete_WithFilterExpression_CreatesDeleteBreakdown()
{
    var breakdown = LinqQueryBreakdown.AnalyzeDelete<User>(u => u.Age < 18);

    Assert.That(breakdown.FromClause.Clause, Is.EqualTo("User"));
    Assert.That(breakdown.WhereClause.Clause, Is.Not.Empty);
}

[Test]
public void AnalyzeUpdate_WithFilterAndUpdateExpressions_CreatesUpdateBreakdown()
{
    var breakdown = LinqQueryBreakdown.AnalyzeUpdate<User>(
        u => u.Department == "Sales",
        u => new User { IsActive = false }
    );

    Assert.That(breakdown.TableName.Clause, Is.EqualTo("User"));
    Assert.That(breakdown.WhereClause.Clause, Is.Not.Empty);
}

[Test]
public void AnalyzeProcedure_WithName_CreatesProcedureBreakdown()
{
    var breakdown = LinqQueryBreakdown.AnalyzeProcedure("sp_GetUsers");

    Assert.That(breakdown.ProcedureName.Clause, Is.EqualTo("sp_GetUsers"));
}

[Test]
public void AnalyzeProcedure_WithParameters_CreatesProcedureBreakdownWithParams()
{
    var breakdown = LinqQueryBreakdown.AnalyzeProcedure("sp_GetUsersByAge", 18, 65);

    Assert.That(breakdown.ProcedureName.Clause, Is.EqualTo("sp_GetUsersByAge"));
    Assert.That(breakdown.Parameters.Count, Is.EqualTo(2));
}

[Test]
public void AnalyzeTrace_WithValidQuery_ReturnsTraceString()
{
    var query = _context.Users.Where(u => u.Age > 18);
    var trace = LinqQueryBreakdown.AnalyzeTrace(query, "Test Context");

    Assert.That(trace, Does.Contain("User"));
    Assert.That(trace, Does.Contain("Query Provider"));
    Assert.That(trace, Does.Contain("Test Context"));
}
```

---

## Troubleshooting

### Query Analysis Returns Empty Results

**Problem**: `LinqQueryBreakdown.Analyze()` returns a breakdown with null/empty clauses.

**Solution**: Ensure your query is an `IQueryable<T>`. LINQ to Objects (`IEnumerable<T>`) won't work:

```csharp
// Wrong: IEnumerable (LINQ to Objects)
var list = new List<User>();
var query = list.Where(u => u.Age > 21); // IEnumerable<User>

// Right: IQueryable (LINQ to SQL)
var query = _context.Users.Where(u => u.Age > 21); // IQueryable<User>
```

### Method Chain Missing Methods

**Problem**: `GetMethodChain()` doesn't show all LINQ methods used.

**Solution**: Only supported methods are tracked. Check the [Supported LINQ Methods](#supported-linq-methods) table.

### Expression Too Complex

**Problem**: Complex lambda expressions aren't fully parsed.

**Solution**: Simplify expressions or break into multiple LINQ calls:

```csharp
// Complex (may not parse fully)
var query = users.Where(u => CalculateScore(u.Age, u.Experience) > threshold);

// Simpler (parses better)
var query = users.Where(u => u.Age > minAge).Where(u => u.Experience > minExp);
```

---

## API Reference

### Namespaces

- `Strata.SqlTools.Breakdowns.LinqToSql` - Core breakdown classes
- `Strata.SqlTools.Visitors.LinqToSql` - Expression tree visitors
- `Strata.SqlTools.Markdown.LinqToSql` - Markdown/Mermaid generators

### Key Classes

| Class | Purpose |
|-------|---------|
| `LinqQueryBreakdown` | Main analysis class, analyzes IQueryable expressions |
| `LinqExpressionVisitor` | Expression tree visitor for extracting SQL components |
| `QueryBreakdownGenerator` | Generates Mermaid diagrams from breakdowns |
| `SqlStatementGenerator` | Generates sequence/pipeline diagrams |

---

## Related Documentation

- [SqlUtilities.Core.md](SqlUtilities.Core.md) - Core library documentation
- [SqlUtilities.SqlServer.md](SqlUtilities.SqlServer.md) - SQL Server base classes
- [EFCore_Integration_Guide.md](EFCore_Integration_Guide.md) - Entity Framework Core integration

---

**Version**: 1.1.0
**Last Updated**: February 2026
**Package**: Strata.SqlTools.LinqToSql

**Changelog**:
- v1.1.0: Added statement type analysis methods (INSERT, UPDATE, DELETE, PROCEDURE, TRACE)
- v1.0.0: Initial release with SELECT query analysis