C# Legacy→DQL Migration Guide

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.

Copy AI Migration Prompt (Click to Expand)

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
ditto.Store.RegisterObserver(
    "SELECT * FROM cars WHERE _id.locationId = :locationId",
    new Dictionary<string, object> { { "locationId", Constants.LocationId } },
    result =>
    {
        using (result)
        {
            // do not pass QueryResultItems outside
            var cars = result.Items.Select((item) => JsonSerializer.Deserialize<Car>(item.JsonString())).ToList();

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

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.

Syntax Change Reference

Document Query Syntax

Document Query All

await ditto.Store.ExecuteAsync(
    "SELECT * FROM cars"
);

ditto.Store.Collection("cars").Exec();

Document Query by ID

await ditto.Store.ExecuteAsync(
    "SELECT * FROM cars WHERE _id = '123'"
);

ditto.Store.Collection("cars")
    .FindByID("123")
    .Exec();

Document Query with Predicate

await ditto.Store.ExecuteAsync(
    "SELECT * FROM cars WHERE color = 'blue'"
);

ditto.Store.Collection("cars")
    .Find("color == 'blue'")
    .Exec();

Document Query with Arguments

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

ditto.Store.Collection("cars")
    .Find("color == $args.color", new Dictionary<string, object> { { "color", "red" } })
    .Exec();

Document Insert

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

ditto.Store.Collection("cars")
    .Upsert(new Dictionary<string, object> { { "_id", id }, { "color", "blue" } });

Document Update

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

// 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");
    });

Document Delete

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

ditto.Store.Collection("cars").FindByID(id).Remove();

Document Local Eviction

// 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" } }
);

// Evict by ID
ditto.Store.Collection("cars").FindByID(id).Evict();

// Evict all matching documents
ditto.Store.Collection("cars").FindAll().Evict();

Query Response Handling

Legacy Query Builder → DQL Response

var result = await ditto.Store.ExecuteAsync(dqlString);
using var item = result.Items.FirstOrDefault();
var color = item?.Value["color"];

var document = ditto.Store.Collection("cars").FindByID(id).Exec();
var color = document?.Value["color"];

Legacy Query Builder → Modern Document Conversion

// 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.Dispose();

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

Car DocumentToCar(DittoDocument doc)
{
    return new Car
    {
        Id = doc.Value["_id"].ToString(),
        Color = doc.Value["color"].ToString()
    };
}

Observer Migration

Legacy Query Builder → DQL Store Observer Migration

ditto.Store.RegisterObserver(
    "SELECT * FROM cars WHERE _id.locationId = :locationId",
    new Dictionary<string, object> { { "locationId", Constants.LocationId } },
    result =>
    {
        using (result)
        {
            // do not pass QueryResultItems outside
            var cars = result.Items.Select((item) => JsonSerializer.Deserialize<Car>(item.JsonString())).ToList();

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

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

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.

Best Practice: Create Indexes for Observer Queries

// Create index on frequently queried fields
await ditto.Store.ExecuteAsync("""
  CREATE INDEX idx_cars_locationId
  ON cars (_id.locationId)
""");

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

For more information on creating and managing indexes, see the DQL Indexing documentation.

Sync Subscriptions Migration

Legacy Query Builder → DQL Sync Subscriptions Subscribe with Query

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

var subscription = ditto.Store.Collection("cars")
    .Find("color == $args.color", new Dictionary<string, object> { { "color", "red" } })
    .Subscribe();

Subscribe with Parameters

var subscription = ditto.Sync.RegisterSubscription(
    "SELECT * FROM cars WHERE _id.locationId = :locationId",
    new Dictionary<string, object> { { "locationId", Constants.LocationId } }
);

var subscription = ditto.Store.Collection("cars")
    .Find($"_id.locationId == '{Constants.LocationId}'")
    .Subscribe();

Multiple Subscriptions

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

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

Cancel Subscription

subscription.Cancel();

subscription.Cancel();

Subscribe to All Documents

var subscription = ditto.Sync.RegisterSubscription(
    "SELECT * FROM cars"
);

var subscription = ditto.Store.Collection("cars")
    .FindAll()
    .Subscribe();

Counter Type Migration

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.

Counter Increment

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

ditto.Store.Collection("cars")
    .FindByID(id)
    .Update(doc =>
    {
        doc["numUpdates"].Counter?.Increment(1.0);
    });

Counter Decrement

await ditto.Store.ExecuteAsync(
    "UPDATE cars APPLY viewCount PN_INCREMENT BY :decrement WHERE _id = :id",
    new Dictionary<string, object> { { "decrement", -1 }, { "id", id } }
);

ditto.Store.Collection("cars")
    .FindByID(id)
    .Update(doc =>
    {
        doc["viewCount"].Counter?.Increment(-1.0);
    });

Initialize Counter in Document

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

var carData = new Dictionary<string, object>
{
    { "_id", id },
    { "color", "blue" },
    { "numUpdates", new DittoCounter() }
};
ditto.Store.Collection("cars").Upsert(carData);

Multiple Counter Operations

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

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

Attachment Operations with DQL

Attachment Creation and Storage

// Create attachment using store — use `using` to dispose when done
using var attachment = await ditto.Store.NewAttachmentAsync(
    filePath,
    metadata
);

var docWithAttachment = new Dictionary<string, object> { { "image", attachment } };

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

Attachment Fetching

var attachmentFetcher = ditto.Store.FetchAttachment(
    attachmentToken,
    (fetchEvent) =>
    {
        if (fetchEvent is DittoAttachmentFetchEvent.Progress progress)
        {
            UpdateProgress(progress.DownloadedBytes, progress.TotalBytes);
        }
        else if (fetchEvent is DittoAttachmentFetchEvent.Completed completed)
        {
            // Within `using` to dispose when done
            using (completed.Attachment)
            {
                ProcessAttachment(completed.Attachment);
            }
        }
    }
);

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:

// Create index on single field
await ditto.Store.ExecuteAsync("""
    CREATE INDEX idx_cars_color
    ON cars (color)
""");

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

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

Best Practices:

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

For comprehensive information on indexing strategies, syntax, and best practices, see the DQL Indexing documentation.

Common Pitfalls & Solutions

Missing Parameter Binding

Problem: Not properly binding parameters in DQL queries Solution: Always use parameterized queries:

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

Attachment Handling

Problem: Incorrect attachment handling in DQL queries Solution: Use proper ATTACHMENT annotation:

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

Counter Type Migration

Problem: Legacy counter operations not working with DQL Solution: Use PN_INCREMENT function for counter operations:

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

QueryResult Materialization

Problem: Holding DittoQueryResult and DittoQueryResultItem references beyond their intended scope leads to memory issues because the associated native memory is not freed. Solution: Make sure you wrap the DittoQueryResult in a using statement, or explicitly Dispose() when done. Use one of the two recommended patterns:

ditto.Store.RegisterObserver("SELECT * FROM CARS", (allCarsQueryResult) =>
{
    // Wrap the queryResult instance in an `using` statement. Map to App model.
    using (allCarsQueryResult)
    {
        var cars = allCarsQueryResult.Items.Select((item) =>
        {
            var model = item.Value["model"] as string;
            var mileage = item.Value["mileage"];

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

        UpdateUI(cars);
    }
});

// Option 2: serialize to JSON string and deserialize (cleaner)
ditto.Store.RegisterObserver("SELECT * FROM CARS", (allCarsQueryResult) =>
{
    // Wrap the queryResult instance in an `using` statement. Map to App model.
    using (allCarsQueryResult)
    {
        var cars = allCarsQueryResult.Items.Select((item) =>
            JsonSerializer.Deserialize<Car>(item.JsonString())).ToList();

        UpdateUI(cars);
    }
});

Do not store QueryResult.Items or individual DittoQueryResultItem objects outside the scope where they were created. Disposing promptly frees the underlying native memory.

Overview & Setup

SDKs

Accessing Data

Syncing Data

Authentication and Authorization

Managing the Mesh

Deploying your App

C# Legacy→DQL Migration Guide

Overview

AI Agent Prompt

Syntax Change Reference

Document Query Syntax

Query Response Handling

Observer Migration

Sync Subscriptions Migration

Counter Type Migration

Attachment Operations with DQL

Performance Enhancements

Indexes for Improved Query Performance

Common Pitfalls & Solutions

Missing Parameter Binding

Attachment Handling

Counter Type Migration

QueryResult Materialization

​Overview

​AI Agent Prompt

​Syntax Change Reference

​Document Query Syntax

​Query Response Handling

​Observer Migration

​Sync Subscriptions Migration

​Counter Type Migration

​Attachment Operations with DQL

​Performance Enhancements

​Indexes for Improved Query Performance

​Common Pitfalls & Solutions

​Missing Parameter Binding

​Attachment Handling

​Counter Type Migration

​QueryResult Materialization

Overview

AI Agent Prompt

Syntax Change Reference

Document Query Syntax

Query Response Handling

Observer Migration

Sync Subscriptions Migration

Counter Type Migration

Attachment Operations with DQL

Performance Enhancements

Indexes for Improved Query Performance

Common Pitfalls & Solutions

Missing Parameter Binding

Attachment Handling

Counter Type Migration

QueryResult Materialization