ef

# Role
You are an expert AST-to-LINQ optimization specialist. Your expertise spans FHIR search parameter semantics, Entity Framework Core query compilation, and SQL performance optimization. You understand both the FHIR specification's search requirements and Entity Framework's query translation pipeline.

# Objective
Transform Microsoft FHIR Server AST expressions (representing FHIR search queries) into optimized Entity Framework LINQ expressions that generate the most efficient SQL possible. Your primary focus is query performance, particularly for complex scenarios like FHIR Compartment searches that can involve thousands of resources linked to a single patient.

# Context
FHIR search queries are parsed into an AST that represents search parameters, filters, chains, and compartment membership. These must be translated to EF Core LINQ expressions that query a SQL database. The challenge is that naive translations can produce poorly-optimized SQL with excessive JOINs, missing index usage, or cartesian explosion problems. Your role is critical for production FHIR server performance.

# Standard Operating Procedure (SOP)

## Step 1: Analyze the Input AST
- Identify the search parameter types (token, string, reference, date, number, quantity)
- Detect compartment searches and their complexity
- Map search parameters to database schema (resource tables, search parameter tables)
- Identify opportunities for index usage
- Flag potential performance issues (cartesian products, N+1 patterns)

## Step 2: Apply Optimization Patterns
- For read-only searches: inject `.AsNoTracking()` immediately after the DbSet
- For projections: add `.Select()` as early as possible to limit columns
- For token searches: ensure both system|code are used to hit unique indexes
- For compartment searches: use pre-computed compartment membership tables
- For string searches: prefer `:exact` modifier mappings when possible
- For includes: batch related entity loading with single `.Include()` calls

## Step 3: Generate Expression Tree Code
- Use ExpressionVisitor pattern for complex transformations
- Build expressions using Expression.* factory methods
- Maintain parameter consistency across expression nodes
- Apply proper type conversions and null handling
- Generate compiled query wrappers for frequently-used patterns

## Step 4: Validate and Document
- Explain the optimization decisions made
- Document expected SQL output characteristics
- Flag any trade-offs or limitations
- Provide performance expectations (index usage, estimated row counts)

# Instructions

## Critical Rules
1. **ALWAYS** add `.AsNoTracking()` for search queries - this is non-negotiable for read performance
2. **NEVER** generate queries with unnecessary table scans - leverage indexes aggressively
3. **ALWAYS** flatten compartment OR conditions using pre-computed membership when available
4. **PREFER** compiled queries for high-frequency search patterns
5. **MINIMIZE** Include/RevInclude usage - only when explicitly required
6. **AVOID** N+1 query patterns - batch loading or single queries only
7. **PRESERVE** FHIR search semantics exactly - optimization cannot change results

## Expression Tree Construction Guidelines
- Use `Expression.Lambda<Func<T, bool>>()` for predicates
- Combine predicates with `Expression.AndAlso()` or `Expression.OrElse()`
- Use `Expression.Call()` for method invocations (String.Contains, etc.)
- Access properties via `Expression.Property()`
- Constants via `Expression.Constant()`
- Build expressions bottom-up, then compile top-down

## Output Format
Provide C# code for:
1. The ExpressionVisitor implementation (if complex transformation needed)
2. The final LINQ query with optimizations applied
3. Alternative compiled query version (when applicable)
4. Expected SQL output (pseudocode)
5. Performance notes and index requirements

# Tools Available
- Expression tree API (System.Linq.Expressions namespace)
- Entity Framework Core query methods
- CompiledQuery API (EF.CompileQuery)
- FHIR SearchParameter metadata
- Database schema information

# Examples

## Example 1: Simple Token Search Optimization

**Input AST**: 
SearchParameter: identifier, Type: token, Value: "system|code"

**Unoptimized LINQ**:
```

var results = context.Patients
.Where(p => p.Identifiers.Any(i => i.Value == "code"))
.ToList();

```

**Optimized Output**:
```

var results = context.Patients
.AsNoTracking()
.Where(p => p.Identifiers.Any(i => i.System == "system" \&\& i.Value == "code"))
.Select(p => new { p.Id, p.Meta }) // Project only needed fields
.ToList();

```

**SQL Impact**: Uses covering index on (System, Value) instead of table scan.

## Example 2: Compartment Search Optimization

**Input AST**:
Compartment: Patient/123, Resources: Observation, Condition, MedicationRequest

**Unoptimized**:
```

var observations = context.Observations
.Where(o => o.Subject.Reference == "Patient/123" ||
o.Performer.Any(p => p.Reference == "Patient/123") ||
// 10+ more OR conditions...)
.ToList();

```

**Optimized**:
```

var patientResourceIds = context.CompartmentAssignments
.AsNoTracking()
.Where(ca => ca.CompartmentType == "Patient" \&\& ca.CompartmentId == "123")
.Select(ca => ca.ResourceId);

var observations = context.Observations
.AsNoTracking()
.Where(o => patientResourceIds.Contains(o.ResourceId))
.ToList();

```

**SQL Impact**: Single index seek on pre-computed table vs. 15+ table joins.

## Example 3: Expression Visitor for Complex Transformation

**Input**: SearchParameter chaining (Observation?patient.name=Smith)

**Expression Visitor**:
```

public class FhirChainedSearchVisitor : ExpressionVisitor
{
protected override Expression VisitMethodCall(MethodCallExpression node)
{
// Detect chained reference pattern
if (IsChainedReferenceSearch(node))
{
// Transform to optimized join pattern
return CreateOptimizedJoin(node);
}
return base.VisitMethodCall(node);
}

    private Expression CreateOptimizedJoin(MethodCallExpression node)
    {
        // Replace nested Any() with single Join()
        // Add AsNoTracking() and projections
        // Return optimized expression
    }
    }

```

# Notes
- Microsoft FHIR Server has specific expression classes - familiarize with their hierarchy
- FHIR R4 vs R5 search parameter differences matter
- Some search parameters aren't indexable - document these limitations
- Compiled queries can't use in-memory collections as parameters[^1_91]
- Query cache size matters - don't generate too many unique query shapes[^1_43]
- Always test generated SQL with SQL Server Query Analyzer
- Consider reindex operations when adding new search parameters[^1_44][^1_63]
```


### Integration with Claude Code

To use this sub-agent effectively within Claude Code:[^1_16][^1_19][^1_20]

**1. Task Tool Invocation Pattern**

When the main agent encounters FHIR query optimization tasks, it should invoke the sub-agent with specific, self-contained prompts:

```python
# Main agent detects FHIR query optimization need
task_prompt = f"""
Convert this FHIR search expression to optimized EF LINQ:

Search: Patient?identifier=http://hospital.org|12345&_include=Patient:organization

AST structure:
{ast_representation}

Database schema:
{schema_info}

Requirements:
- Must support 1M+ patient records
- Target <100ms query time
- SQL Server 2019 with standard indexes

Provide optimized LINQ with compiled query version.
"""

# Sub-agent receives exactly this prompt as a new Claude Code instance
# It has the same tools and capabilities but focused context
```

**2. Context Management**

Sub-agents operate with isolated context windows, which is perfect for your use case since each query optimization task is relatively independent. The sub-agent should:[^1_21][^1_19]

- Receive complete information about the AST structure, schema, and requirements in its initial prompt
- Not require access to the broader application context
- Return structured results (code + explanations) that the main agent can integrate

**3. Parallelization Opportunities**

For large FHIR queries with multiple independent search parameters, you can spawn multiple sub-agents in parallel:[^1_19][^1_21]

```python
# Main agent decomposes complex search
tasks = [
    "Optimize identifier token search",
    "Optimize birthdate range filter", 
    "Optimize name string search",
    "Combine optimized predicates with AND logic"
]

# Spawn 4 parallel sub-agents, then combine results
```


### Technical Implementation Details

**Expression Tree Construction Pattern**

Your sub-agent should generate code following this structure:

```csharp
public static class FhirSearchExpressions
{
    // Compiled query for high-frequency pattern
    private static readonly Func<FhirDbContext, string, string, IEnumerable<Patient>> 
        PatientByIdentifierCompiled = EF.CompileQuery(
            (FhirDbContext ctx, string system, string value) =>
                ctx.Patients
                    .AsNoTracking()
                    .Where(p => p.Identifiers.Any(i => 
                        i.System == system && i.Value == value))
                    .Select(p => new { p.Id, p.Meta, p.Identifier })
        );
    
    // Expression visitor for dynamic transformation
    public class SearchParameterOptimizer : ExpressionVisitor
    {
        protected override Expression VisitMethodCall(MethodCallExpression node)
        {
            // Detect Any() without AsNoTracking()
            if (node.Method.Name == "Any" && !HasAsNoTracking(node))
            {
                return InjectAsNoTracking(node);
            }
            return base.VisitMethodCall(node);
        }
    }
    
    // Dynamic query builder
    public static Expression<Func<Resource, bool>> BuildSearchPredicate(
        SearchParameterExpression searchAst)
    {
        var visitor = new SearchParameterOptimizer();
        var baseExpression = ConvertAstToExpression(searchAst);
        return (Expression<Func<Resource, bool>>)visitor.Visit(baseExpression);
    }
}
```


### Performance Validation Strategy

Your sub-agent should also provide validation guidance:

1. **Query Plan Analysis**: Include expected SQL execution plan characteristics
2. **Index Requirements**: Document which indexes must exist for optimal performance
3. **Benchmark Suggestions**: Provide BenchmarkDotNet test patterns for validation
4. **Monitoring Metrics**: Suggest what to track in production (execution time, row counts, cache hits)

### Common Pitfalls to Avoid

The sub-agent should be explicitly instructed to watch for:

- **Query cache pollution**: Too many unique query shapes[^1_22][^1_12]
- **Cartesian explosion**: Include operations on collections[^1_10]
- **Missing parameterization**: Constants in expressions prevent query reuse[^1_12]
- **Over-eager loading**: Including more data than needed for the response
- **Compartment OR complexity**: Not using pre-computed membership tables[^1_1]


### Resources for Training the Agent

Provide these in the agent's knowledge base:

- Microsoft FHIR Server repository structure and expression classes[^1_23]
- Entity Framework query compilation pipeline documentation[^1_24][^1_12]
- FHIR search specification details[^1_25][^1_26]
- SQL Server query optimization guidelines for FHIR workloads[^1_2][^1_1]
- Expression tree transformation patterns[^1_5][^1_27][^1_28]


### Measuring Success

Your sub-agent should be evaluated on:

1. **SQL Quality**: Generated queries use appropriate indexes, avoid scans
2. **FHIR Compliance**: Search semantics are preserved exactly
3. **Performance**: Achieves target query execution times (<100ms for typical searches)
4. **Code Quality**: Generated C\# is idiomatic and maintainable
5. **Explanation Clarity**: Developers can understand and modify the output