> ## Documentation Index
> Fetch the complete documentation index at: https://docs.ditto.live/llms.txt
> Use this file to discover all available pages before exploring further.

# C# Legacy→DQL Migration Guide

> Guide for migrating C# apps from legacy query builder APIs to DQL (Ditto Query Language) syntax.

## Overview

This guide will help you successfully migrate your Ditto C# application from the legacy query builder APIs to the modern DQL (Ditto Query Language). After reviewing this documentation, you'll understand how to convert method chaining patterns to DQL syntax and systematically update your data operations.

## AI Agent Prompt

Use this prompt when working with an AI coding assistant to migrate your Ditto C# app from legacy query builder to DQL.

<Accordion title="Copy AI Migration Prompt (Click to Expand)">
  ````text theme={null}
  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.
  ````
</Accordion>

## Syntax Change Reference

### Document Query Syntax

**Document Query All**

<CodeGroup>
  ```csharp C# DQL (v5) theme={null}
  await ditto.Store.ExecuteAsync(
      "SELECT * FROM cars"
  );
  ```

  ```csharp C# LEGACY (v4) theme={null}
  ditto.Store.Collection("cars").Exec();
  ```
</CodeGroup>

**Document Query by ID**

<CodeGroup>
  ```csharp C# DQL (v5) theme={null}
  await ditto.Store.ExecuteAsync(
      "SELECT * FROM cars WHERE _id = '123'"
  );
  ```

  ```csharp C# LEGACY (v4) theme={null}
  ditto.Store.Collection("cars")
      .FindByID("123")
      .Exec();
  ```
</CodeGroup>

**Document Query with Predicate**

<CodeGroup>
  ```csharp C# DQL (v5) theme={null}
  await ditto.Store.ExecuteAsync(
      "SELECT * FROM cars WHERE color = 'blue'"
  );
  ```

  ```csharp C# LEGACY (v4) theme={null}
  ditto.Store.Collection("cars")
      .Find("color == 'blue'")
      .Exec();
  ```
</CodeGroup>

**Document Query with Arguments**

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

  ```csharp C# LEGACY (v4) theme={null}
  ditto.Store.Collection("cars")
      .Find("color == $args.color", new Dictionary<string, object> { { "color", "red" } })
      .Exec();
  ```
</CodeGroup>

**Document Insert**

<CodeGroup>
  ```csharp C# DQL (v5) theme={null}
  await ditto.Store.ExecuteAsync(
      "INSERT INTO cars DOCUMENTS (:car)",
      new Dictionary<string, object> { { "car", carData } }
  );
  ```

  ```csharp C# LEGACY (v4) theme={null}
  ditto.Store.Collection("cars")
      .Upsert(new Dictionary<string, object> { { "_id", id }, { "color", "blue" } });
  ```
</CodeGroup>

**Document Update**

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

  ```csharp C# LEGACY (v4) theme={null}
  // Upsert document
  ditto.Store.Collection("cars")
      .Upsert(new Dictionary<string, object> { { "_id", id }, { "color", "green" } });

  // Update with action
  ditto.Store.Collection("cars")
      .FindByID(id)
      .Update(doc =>
      {
          doc["color"].Set("green");
      });
  ```
</CodeGroup>

**Document Delete**

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

  ```csharp C# LEGACY (v4) theme={null}
  ditto.Store.Collection("cars").FindByID(id).Remove();
  ```
</CodeGroup>

**Document Local Eviction**

<CodeGroup>
  ```csharp C# DQL (v5) theme={null}
  // Evict by ID
  await ditto.Store.ExecuteAsync(
      "EVICT FROM cars WHERE _id = :id",
      new Dictionary<string, object> { { "id", id } }
  );

  // Evict all matching documents
  await ditto.Store.ExecuteAsync(
      "EVICT FROM cars WHERE color = :color",
      new Dictionary<string, object> { { "color", "red" } }
  );
  ```

  ```csharp C# LEGACY (v4) theme={null}
  // Evict by ID
  ditto.Store.Collection("cars").FindByID(id).Evict();

  // Evict all matching documents
  ditto.Store.Collection("cars").FindAll().Evict();
  ```
</CodeGroup>

### Query Response Handling

**Legacy Query Builder → DQL Response**

<CodeGroup>
  ```csharp C# DQL (v5) theme={null}
  var result = await ditto.Store.ExecuteAsync(dqlString);
  using var item = result.Items.FirstOrDefault();
  var color = item?.Value["color"];
  ```

  ```csharp C# LEGACY (v4) theme={null}
  var document = ditto.Store.Collection("cars").FindByID(id).Exec();
  var color = document?.Value["color"];
  ```
</CodeGroup>

**Legacy Query Builder → Modern Document Conversion**

<CodeGroup>
  ```csharp C# DQL (v5) theme={null}
  // Extract data and dispose immediately — do not pass QueryResultItem outside this scope
  Car DocumentToCar(DittoQueryResultItem item)
  {
      var id = item.Value["_id"]?.ToString();
      var color = item.Value["color"]?.ToString();

      item.Dematerialize();
      item.Dispose();

      return new Car { Id = id, Color = color };
  }
  ```

  ```csharp C# LEGACY (v4) theme={null}
  Car DocumentToCar(DittoDocument doc)
  {
      return new Car
      {
          Id = doc.Value["_id"].ToString(),
          Color = doc.Value["color"].ToString()
      };
  }
  ```
</CodeGroup>

### Observer Migration

**Legacy Query Builder → DQL Store Observer Migration**

<CodeGroup>
  ```csharp C# DQL (v5) theme={null}
  await foreach (var result in ditto.Store.RegisterObserver(
      "SELECT * FROM cars WHERE _id.locationId = :locationId",
      new Dictionary<string, object> { { "locationId", Constants.LocationId } }
  ))
  {
      // Deserialize and dispose items within loop scope — do not pass QueryResultItems outside
      var cars = result.Items.Select((item) =>
      {
          var itemJsonString = item.JsonString();
          item.Dispose();
          return JsonSerializer.Deserialize<Car>(itemJsonString);
      }).ToList();

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

  ```csharp C# LEGACY (v4) theme={null}
  var liveQuery = ditto.Store.Collection("cars")
      .Find($"_id.locationId == '{Constants.LocationId}'")
      .ObserveLocal((docs, ev) =>
      {
          Console.WriteLine($"Live query handler called. Event: {ev} Docs count: {docs.Count}");
          if (ev is DittoLiveQueryEvent.Update update)
          {
              // Handle deletions, insertions, updates
          }
          else if (ev is DittoLiveQueryEvent.Initial)
          {
              // Handle initial data
          }
      });
  ```
</CodeGroup>

<Warning>
  **Performance Consideration**: DQL observers provide more advanced return results including aggregates and projections. This requires more database full scans to ensure consistent results compared to the legacy query builder.

  **Use indexes on query fields** to maintain and improve observer performance. Indexes ensure your observers remain functional with optimal query performance.
</Warning>

**Best Practice: Create Indexes for Observer Queries**

```csharp theme={null}
// Create index on frequently queried fields
await ditto.Store.Execute("""
    CREATE INDEX idx_cars_locationId
    ON cars (_id.locationId)
""");

// Then register observer - queries will use the index
await foreach (var result in ditto.Store.RegisterObserver(
    "SELECT * FROM cars WHERE _id.locationId = :locationId",
    new Dictionary<string, object> { { "locationId", Constants.LocationId } }
))
{
    // Process results
}
```

For more information on creating and managing indexes, see the [DQL Indexing documentation](/dql/indexing).

### Sync Subscriptions Migration

**Legacy Query Builder → DQL Sync Subscriptions**

**Subscribe with Query**

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

  ```csharp C# LEGACY (v4) theme={null}
  var subscription = ditto.Store.Collection("cars")
      .Find("color == $args.color", new Dictionary<string, object> { { "color", "red" } })
      .Subscribe();
  ```
</CodeGroup>

**Subscribe with Parameters**

<CodeGroup>
  ```csharp C# DQL (v5) theme={null}
  var subscription = ditto.Sync.RegisterSubscription(
      "SELECT * FROM cars WHERE _id.locationId = :locationId",
      new Dictionary<string, object> { { "locationId", Constants.LocationId } }
  );
  ```

  ```csharp C# LEGACY (v4) theme={null}
  var subscription = ditto.Store.Collection("cars")
      .Find($"_id.locationId == '{Constants.LocationId}'")
      .Subscribe();
  ```
</CodeGroup>

**Multiple Subscriptions**

<CodeGroup>
  ```csharp C# DQL (v5) theme={null}
  var subscriptions = new List<DittoSyncSubscription>();
  subscriptions.Add(
      ditto.Sync.RegisterSubscription(
          "SELECT * FROM cars WHERE color = :color",
          new Dictionary<string, object> { { "color", "red" } }
      )
  );
  subscriptions.Add(
      ditto.Sync.RegisterSubscription(
          "SELECT * FROM cars WHERE year > :year",
          new Dictionary<string, object> { { "year", 2020 } }
      )
  );
  ```

  ```csharp C# LEGACY (v4) theme={null}
  var subscriptions = new List<DittoSubscription>();
  subscriptions.Add(
      ditto.Store.Collection("cars")
          .Find("color == 'red'")
          .Subscribe()
  );
  subscriptions.Add(
      ditto.Store.Collection("cars")
          .Find("year > 2020")
          .Subscribe()
  );
  ```
</CodeGroup>

**Cancel Subscription**

<CodeGroup>
  ```csharp C# DQL (v5) theme={null}
  subscription.Cancel();
  ```

  ```csharp C# LEGACY (v4) theme={null}
  subscription.Cancel();
  ```
</CodeGroup>

**Subscribe to All Documents**

<CodeGroup>
  ```csharp C# DQL (v5) theme={null}
  var subscription = ditto.Sync.RegisterSubscription(
      "SELECT * FROM cars"
  );
  ```

  ```csharp C# LEGACY (v4) theme={null}
  var subscription = ditto.Store.Collection("cars")
      .FindAll()
      .Subscribe();
  ```
</CodeGroup>

### Counter Type Migration

<Note>
  **PN\_COUNTER is the DQL equivalent of the legacy `DittoCounter` type.** When migrating counter operations from the legacy query builder's counter methods, use `PN_INCREMENT BY` in the `APPLY` clause. This maintains full compatibility with existing counter data created by `DittoCounter`.
</Note>

**Counter Increment**

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

  ```csharp C# LEGACY (v4) theme={null}
  ditto.Store.Collection("cars")
      .FindByID(id)
      .Update(doc =>
      {
          doc["numUpdates"].Counter?.Increment(1.0);
      });
  ```
</CodeGroup>

**Counter Decrement**

<CodeGroup>
  ```csharp C# DQL (v5) theme={null}
  await ditto.Store.ExecuteAsync(
      "UPDATE cars APPLY viewCount PN_INCREMENT BY :decrement WHERE _id = :id",
      new Dictionary<string, object> { { "decrement", -1 }, { "id", id } }
  );
  ```

  ```csharp C# LEGACY (v4) theme={null}
  ditto.Store.Collection("cars")
      .FindByID(id)
      .Update(doc =>
      {
          doc["viewCount"].Counter?.Increment(-1.0);
      });
  ```
</CodeGroup>

**Initialize Counter in Document**

<CodeGroup>
  ```csharp C# DQL (v5) theme={null}
  // Counter fields are automatically created on first PN_INCREMENT use
  await ditto.Store.ExecuteAsync(
      "INSERT INTO cars DOCUMENTS (:car)",
      new Dictionary<string, object> {
          { "car", new Dictionary<string, object> {
              { "_id", id },
              { "color", "blue" }
              // Do NOT initialize counter fields - they are created on first PN_INCREMENT
          }}
      }
  );

  // Then use PN_INCREMENT with APPLY clause to create and increment the counter
  await ditto.Store.ExecuteAsync(
      "UPDATE cars APPLY numUpdates PN_INCREMENT BY 1 WHERE _id = :id",
      new Dictionary<string, object> { { "id", id } }
  );
  ```

  ```csharp C# LEGACY (v4) theme={null}
  var carData = new Dictionary<string, object>
  {
      { "_id", id },
      { "color", "blue" },
      { "numUpdates", new DittoCounter() }
  };
  ditto.Store.Collection("cars").Upsert(carData);
  ```
</CodeGroup>

**Multiple Counter Operations**

<CodeGroup>
  ```csharp C# DQL (v5) theme={null}
  await ditto.Store.ExecuteAsync(
      @"UPDATE cars
        APPLY likes PN_INCREMENT BY :likeIncrement,
              dislikes PN_INCREMENT BY :dislikeDecrement,
              views PN_INCREMENT BY :viewIncrement
        WHERE _id = :id",
      new Dictionary<string, object>
      {
          { "likeIncrement", 1 },
          { "dislikeDecrement", -1 },
          { "viewIncrement", 1 },
          { "id", id }
      }
  );
  ```

  ```csharp C# LEGACY (v4) theme={null}
  ditto.Store.Collection("cars")
      .FindByID(id)
      .Update(doc =>
      {
          doc["likes"].Counter?.Increment(1.0);
          doc["dislikes"].Counter?.Increment(-1.0);
          doc["views"].Counter?.Increment(1.0);
      });
  ```
</CodeGroup>

### Attachment Operations with DQL

**Attachment Creation and Storage**

<CodeGroup>
  ```csharp C# DQL (v5) theme={null}
  // Create attachment using store — use `using` to dispose when done
  using var attachment = await ditto.Store.NewAttachmentAsync(
      filePath,
      metadata
  );

  // Store attachment with DQL
  await ditto.Store.ExecuteAsync(
      "INSERT INTO COLLECTION cars (image ATTACHMENT) DOCUMENTS (:doc)",
      new Dictionary<string, object> { { "doc", docWithAttachment } }
  );
  ```
</CodeGroup>

**Attachment Fetching**

<CodeGroup>
  ```csharp C# DQL (v5) theme={null}
  // Fetch attachment with progress callback — use `using` to dispose when done
  using var fetchResult = await ditto.Store.FetchAttachmentAsync(
      attachmentToken,
      (ev) =>
      {
          if (ev is DittoAttachmentFetchEvent.Progress progress)
          {
              UpdateProgress(progress.DownloadedBytes, progress.TotalBytes);
          }
      }
  );
  ```
</CodeGroup>

***

## Performance Enhancements

### Indexes for Improved Query Performance

DQL observers and queries benefit significantly from proper indexing. When migrating from the legacy query builder to DQL, creating indexes on frequently queried fields is essential for maintaining optimal performance.

**Why Indexes Matter for DQL:**

* DQL observers support advanced features like aggregates and projections
* These advanced features require full database scans to ensure consistent results
* Indexes dramatically reduce query execution time by avoiding full scans
* Combining indexes with observers provides better performance than legacy query builder

**Creating Indexes:**

```csharp theme={null}
// Create index on single field
await ditto.Store.Execute("""
    CREATE INDEX idx_cars_color
    ON cars (color)
""");

// Create compound index on multiple fields
await ditto.Store.Execute("""
    CREATE INDEX idx_cars_color_year
    ON cars (color, year)
""");

// Create index on nested field
await ditto.Store.Execute("""
    CREATE INDEX idx_cars_location
    ON cars (_id.locationId)
""");
```

**Best Practices:**

1. Create indexes on fields used in `WHERE` clauses
2. Create indexes before registering observers for those queries
3. Use compound indexes for queries with multiple filter conditions
4. Monitor query performance and add indexes as needed

For comprehensive information on indexing strategies, syntax, and best practices, see the [DQL Indexing documentation](/dql/indexing).

***

## Common Pitfalls & Solutions

### Missing Parameter Binding

**Problem**: Not properly binding parameters in DQL queries

**Solution**: Always use parameterized queries:

<CodeGroup>
  ```csharp C# DQL (v5) theme={null}
  await ditto.Store.ExecuteAsync(
      "SELECT * FROM cars WHERE color = :color",
      new Dictionary<string, object> { { "color", "red" } }
  );
  ```
</CodeGroup>

### Attachment Handling

**Problem**: Incorrect attachment handling in DQL queries

**Solution**: Use proper ATTACHMENT annotation:

<CodeGroup>
  ```csharp C# DQL (v5) theme={null}
  await ditto.Store.ExecuteAsync(
      "INSERT INTO COLLECTION cars (image ATTACHMENT) DOCUMENTS (:doc)",
      new Dictionary<string, object> { { "doc", docWithAttachment } }
  );
  ```
</CodeGroup>

### Counter Type Migration

**Problem**: Legacy counter operations not working with DQL

**Solution**: Use PN\_INCREMENT function for counter operations:

<CodeGroup>
  ```csharp C# DQL (v5) theme={null}
  // Wrong: Using legacy counter methods
  doc["counter"].Counter?.Increment(1.0);

  // Wrong: Initializing counter with a number (creates a register, not a counter!)
  await ditto.Store.ExecuteAsync(
      "INSERT INTO items DOCUMENTS (:doc)",
      new Dictionary<string, object> { { "doc", new Dictionary<string, object> { { "counter", 0 } } } }
  );

  // Correct: Using PN_INCREMENT with APPLY clause (creates counter on first use)
  await ditto.Store.ExecuteAsync(
      "UPDATE items APPLY counter PN_INCREMENT BY :value WHERE _id = :id",
      new Dictionary<string, object> { { "value", 1 }, { "id", itemId } }
  );
  ```
</CodeGroup>

### QueryResultItem Materialization

**Problem**: Holding `DittoQueryResultItem` references beyond their intended scope leads to memory issues because each item maps to native Rust memory via FFI.

**Solution**: Always extract the data you need and `Dispose()` the item within the same scope. Use one of the two recommended patterns:

<CodeGroup>
  ```csharp C# DQL (v5) - Field Extraction theme={null}
  // Option 1: pull out fields, dematerialize, then use
  var cars = allCarsQueryResult.Items.Select((item) =>
  {
      var model = item.Value["model"] as string;
      var mileage = item.Value["mileage"];

      item.Dematerialize();
      item.Dispose();

      return new Car(model, mileage);
  }).ToList();
  ```

  ```csharp C# DQL (v5) - JSON Deserialization theme={null}
  // Option 2: serialize to JSON string and deserialize (cleaner, slightly slower)
  var cars = allCarsQueryResult.Items.Select((item) =>
  {
      var itemJsonString = item.JsonString();
      item.Dispose();
      return JsonSerializer.Deserialize<Car>(itemJsonString);
  }).ToList();
  ```
</CodeGroup>

<Warning>
  Do **not** store `QueryResult.Items` or individual `DittoQueryResultItem` objects outside the scope where they were created. Disposing promptly frees the underlying native memory.
</Warning>

### Memory Management with Store Observers

**Problem**: Not properly cleaning up observers

**Solution**: Stop iteration to cleanup:

<CodeGroup>
  ```csharp C# DQL (v5) theme={null}
  // Iterator automatically cleans up when broken
  await foreach (var result in ditto.Store.RegisterObserver(query, args))
  {
      if (shouldStop)
      {
          break; // Automatically cleans up
      }
  }
  ```
</CodeGroup>