C# Legacy→DQL Migration Guide

I need help migrating a Ditto C# application from the legacy query builder APIs to modern DQL (Ditto Query Language). This migration involves converting method chaining patterns to SQL-like DQL syntax.

CRITICAL RULES:
1. All query builder method chains (.Collection().Find()) must be replaced with ditto.Store.ExecuteAsync() using DQL
2. Use parameterized queries with :paramName syntax - NEVER string interpolation
3. Counter operations must use PN_INCREMENT BY in APPLY clause - do NOT initialize counter fields
4. Sync subscriptions must use ditto.Sync.RegisterSubscription() instead of .Find().Subscribe()
5. ObserveLocal must be replaced with RegisterObserver and await foreach iteration

---

CORE MIGRATION AREAS:

1. QUERY SYNTAX MIGRATION

BEFORE (Legacy Query Builder):
```csharp
ditto.Store.Collection("cars")
    .Find("color == $args.color", new Dictionary<string, object> { { "color", "red" } })
    .Exec();
```

AFTER (DQL):
```csharp
await ditto.Store.ExecuteAsync(
    "SELECT * FROM cars WHERE color = :color",
    new Dictionary<string, object> { { "color", "red" } }
);
```

2. INSERT OPERATIONS

BEFORE (Legacy Query Builder):
```csharp
ditto.Store.Collection("cars")
    .Upsert(new Dictionary<string, object> { { "_id", id }, { "color", "blue" } });
```

AFTER (DQL):
```csharp
await ditto.Store.ExecuteAsync(
    "INSERT INTO cars DOCUMENTS (:car)",
    new Dictionary<string, object> { { "car", carData } }
);
```

3. UPDATE OPERATIONS

BEFORE (Legacy Query Builder):
```csharp
ditto.Store.Collection("cars")
    .FindByID(id)
    .Update(doc =>
    {
        doc["color"].Set("green");
    });
```

AFTER (DQL):
```csharp
await ditto.Store.ExecuteAsync(
    "UPDATE cars SET color = :color WHERE _id = :id",
    new Dictionary<string, object> { { "color", "green" }, { "id", id } }
);
```

4. DELETE OPERATIONS

BEFORE (Legacy Query Builder):
```csharp
ditto.Store.Collection("cars").FindByID(id).Remove();
```

AFTER (DQL):
```csharp
await ditto.Store.ExecuteAsync(
    "DELETE FROM cars WHERE _id = :id",
    new Dictionary<string, object> { { "id", id } }
);
```

5. EVICTION OPERATIONS

BEFORE (Legacy Query Builder):
```csharp
ditto.Store.Collection("cars").FindByID(id).Evict();
```

AFTER (DQL):
```csharp
await ditto.Store.ExecuteAsync(
    "EVICT FROM cars WHERE _id = :id",
    new Dictionary<string, object> { { "id", id } }
);
```

6. COUNTER OPERATIONS (PN_COUNTER)

BEFORE (Legacy Query Builder):
```csharp
ditto.Store.Collection("cars")
    .FindByID(id)
    .Update(doc =>
    {
        doc["numUpdates"].Counter?.Increment(1.0);
    });
```

AFTER (DQL with PN_INCREMENT):
```csharp
await ditto.Store.ExecuteAsync(
    "UPDATE cars APPLY numUpdates PN_INCREMENT BY :increment WHERE _id = :id",
    new Dictionary<string, object> { { "increment", 1 }, { "id", id } }
);
```

IMPORTANT: Do NOT initialize counter fields in documents:
```csharp
// WRONG - Creates a register, not a counter
new Dictionary<string, object> { { "counter", 0 } }

// CORRECT - Omit counter field, it's created on first PN_INCREMENT
new Dictionary<string, object> { { "_id", id }, { "color", "blue" } }
```

7. DOCUMENT FIELD ACCESS MIGRATION

BEFORE (Legacy Query Builder):
```csharp
var document = ditto.Store.Collection("cars").FindByID(id).Exec();
var color = document?.Value["color"];
```

AFTER (DQL):
```csharp
var result = await ditto.Store.ExecuteAsync(dqlString);
var item = result.Items.FirstOrDefault();
var color = item?.Value["color"];
```

8. OBSERVER MIGRATION (ObserveLocal → RegisterObserver)

BEFORE (Legacy ObserveLocal):
```csharp
var liveQuery = ditto.Store.Collection("cars")
    .Find($"_id.locationId == '{Constants.LocationId}'")
    .ObserveLocal((docs, ev) =>
    {
        if (ev is DittoLiveQueryEvent.Update update)
        {
            // Handle changes
        }
        else if (ev is DittoLiveQueryEvent.Initial)
        {
            // Handle initial data
        }
    });
```

AFTER (DQL with RegisterObserver):
```csharp
await foreach (var result in ditto.Store.RegisterObserver(
    "SELECT * FROM cars WHERE _id.locationId = :locationId",
    new Dictionary<string, object> { { "locationId", Constants.LocationId } }
))
{
    // Process result items
    var items = result.Items;

    // Update UI
    UpdateUI(items);
}
```

9. SYNC SUBSCRIPTIONS MIGRATION

BEFORE (Legacy Query Builder):
```csharp
var subscription = ditto.Store.Collection("cars")
    .Find("color == $args.color", new Dictionary<string, object> { { "color", "red" } })
    .Subscribe();
```

AFTER (DQL):
```csharp
var subscription = ditto.Sync.RegisterSubscription(
    "SELECT * FROM cars WHERE color = :color",
    new Dictionary<string, object> { { "color", "red" } }
);
```

---

COMMON PITFALLS TO AVOID:

1. DQL Syntax Errors
   - Use :paramName for parameters, not $args.paramName or string interpolation

2. Missing Parameter Binding
   - NEVER use string interpolation in queries
   - Always use parameterized queries with Dictionary<string, object>

3. Counter Type Errors
    - Use COUNTER annotation: `COLLECTION my_collection (my_count COUNTER)`
   - Do NOT use SET with COUNTER
   - Use APPLY with INCREMENT BY and RESET
   - Pass negative values for decrements

4. Memory Management with Observers
   - Use await foreach for automatic cleanup
   - Break from loop to stop observation
   - Use indexes for improved memory and performance

5. Attachment Handling
   - Use ATTACHMENT annotation: `COLLECTION my_collection (image ATTACHMENT)`
   - Create attachments with ditto.Store.NewAttachmentAsync()

---

MIGRATION CHECKLIST:

Search for these legacy patterns and replace:
- [ ] .Collection( → await ditto.Store.ExecuteAsync("SELECT * FROM
- [ ] .Find( → Convert to DQL WHERE clause with Dictionary arguments
- [ ] .FindByID( → Convert to DQL WHERE _id = :id
- [ ] .Upsert( → Convert to DQL INSERT INTO
- [ ] .Update( → Convert to DQL UPDATE SET
- [ ] .Remove( → Convert to DQL DELETE FROM
- [ ] .Evict( → Convert to DQL EVICT FROM
- [ ] .Counter?.Increment( → Convert to PN_INCREMENT BY in APPLY clause
- [ ] new DittoCounter() → Remove initialization, use PN_INCREMENT
- [ ] .ObserveLocal( → Convert to RegisterObserver with await foreach
- [ ] .Subscribe() → Convert to ditto.Sync.RegisterSubscription()

---

Please help me convert all legacy query builder patterns in my codebase to DQL syntax. Focus on:
1. Maintaining the same functionality
2. Using proper parameterized queries with Dictionary<string, object>
3. Handling counter operations correctly with PN_INCREMENT
4. Implementing proper observer cleanup with await foreach
5. Converting all sync subscriptions to DQL

Start by identifying all uses of .Collection() in my codebase and systematically converting each one to the appropriate DQL pattern.