Migration 1.x to 2.0

Migration: 1.x to 2.0

2.0 replaces the T-SQL stored-procedure engine with a SQL compiler written in C#. That is what makes the other four databases possible, and it removes the deployment step entirely.

The public API is almost unchanged. For most applications the migration is deleting one line.

---

The short version

1. Delete the Approach line from your AddQueryForgeDapper call.
2. Drop the usp_QueryForge* procedures from your database — nothing calls them any more.
3. Read Behaviour changes, because two of them are improvements that are also changes.

---

What changed

1.x	2.0
usp_QueryForge* procedures deployed into your database at startup	Nothing is deployed; SQL is compiled per call
DapperExecutionApproach (RawQuery / UseReadySp / DevelopAndUseSp)	Removed — there is one execution path
DDL permissions needed for the default mode	No schema permissions at all; SELECT is enough
DapperInitializationHostedService	Removed
SQL Server only	SQL Server, PostgreSQL, MySQL/MariaDB, Oracle, SQLite
DapperQueryObject.Schema defaulted to "dbo"	Defaults to empty, meaning "use the dialect's default"
Values concatenated into the generated SQL as text	Every value is a DbParameter
EF Core provider "in development"	Released
In-memory provider "planned"	Released as PepperX.QueryForge.InMemory

Unchanged: IDapperQueryService, DapperQuery, DapperQueryBuilder, QueryForgeAsync, Query, QueryResult<T>, HierarchyNode<T>, the whole validation surface, and the JSON contract.

---

Step 1 — registration

 builder.Services.AddQueryForgeDapper(options =>
 {
-    options.Approach = DapperExecutionApproach.DevelopAndUseSp;
     options.ConnectionFactory = sp =>
     {
         var config = sp.GetRequiredService<IConfiguration>();
         return new SqlConnection(config.GetConnectionString("DefaultConnection"));
     };
 });

DapperQueryForgeOptions now has exactly one member:

public class DapperQueryForgeOptions
{
    public Func<IServiceProvider, IDbConnection>? ConnectionFactory { get; set; }
}

If you were using DapperExecutionApproach.RawQuery, there is nothing to do beyond deleting the line — that mode is what 2.0 does for every query, only compiled properly.

Step 2 — clean up the database

These are no longer called and can be dropped:

DROP PROCEDURE IF EXISTS usp_QueryForge;
DROP PROCEDURE IF EXISTS usp_QueryForge_BuildWhere;
DROP PROCEDURE IF EXISTS usp_QueryForge_ExecGrouped;

You can also revoke DDL permission from the application's login. 2.0 needs SELECT on the objects you point it at, and nothing else.

Step 3 — check the schema default

DapperQueryObject.Schema used to default to "dbo". It now defaults to empty, which each dialect resolves to its own default: dbo on SQL Server, public on PostgreSQL, none on MySQL, Oracle or SQLite.

On SQL Server the resolved value is the same, so nothing changes. If you were relying on the literal default in your own code, name the schema explicitly:

.ForObject("Users", "dbo")

---

Behaviour changes

Two of these are improvements. They are listed as changes because a result that was previously wrong is now different.

Comparisons use the column's real type

The 1.x engine inlined every value as text, so [Age] > N'9' compared strings and ranked 9 above 30. Values are now parameters, coerced to the column's type before binding, so numbers, dates and booleans compare correctly.

Watch for: any code, test, or saved filter that was written around the old lexical ordering. A range filter that "worked" by accident against string comparison will now return different — correct — rows.

Stored-procedure results are filtered in the application

A procedure's result set cannot be composed into a larger SELECT portably, so for DapperObjectType.SP the rows are materialized and then filtered, sorted, paged and grouped in memory, using the same engine the in-memory provider uses.

The QueryResult<T> is identical. The data transfer is not: for a procedure returning a very large set, every row now crosses the wire before being filtered.

Watch for: a procedure returning tens of thousands of rows where the query only ever wanted a page. Move the logic to a view or a table-valued function, which push everything down to the database.

Tables, views and functions are unaffected.

Null ordering is now pinned

1.x inherited whatever the engine did. 2.0 standardises on nulls first ascending, nulls last descending on every provider and engine.

On SQL Server that matches the old default, so nothing changes. If you have saved expectations from another engine, they may now order differently — correctly, and consistently.

Text matching escapes wildcards in the value

A caller searching for 50% now finds the literal text rather than everything starting with 50. If any client was relying on injecting its own wildcards through a Contains value, it will need to change.

---

Optional: adopt the new providers

EF Core

dotnet add package PepperX.QueryForge.EFCore

await db.Users.AsNoTracking().ToQueryResultAsync<User>(query);

No registration. The same Query and the same QueryResult<T>. See EF Core Provider.

In-memory

dotnet add package PepperX.QueryForge.InMemory

users.ToQueryResult(query);

Useful immediately as a test double for code that currently needs a database to test. See In-Memory Provider.

Another database

Nothing to configure. AddQueryForgeDapper() registers all five dialects, and the engine is inferred from the connection you pass:

await svc.QueryAsync<User>(new NpgsqlConnection(pg), query);

---

Verifying the migration

1. Compile. The only break should be DapperExecutionApproach.

2. Run your existing tests. Filtering, sorting, paging and grouping behaviour is otherwise unchanged.

3. Look at the SQL. Compile a representative query and confirm values are parameters:

   var sql = new SqlQueryCompiler(new SqlServerDialect()).CompileRows(dq, whitelist);
   Console.WriteLine(sql.Text);

   See Dapper: Generated SQL.

4. Check any numeric or date range filter against the type-comparison change above.

5. Check any stored-procedure endpoint against the in-application-filtering change.

---

If something is missing

The 1.x engine had behaviour that was never documented, and some of it was a consequence of string concatenation rather than a decision. If you depended on something 2.0 does differently and it is not listed here, open an issue at https://github.com/PepperX-Dev/QueryForge/issues with the 1.x behaviour and what you get now — that is a documentation gap worth closing either way.