Read data from database #202

Activity

1. jogibear9988 commented on Oct 7, 2026

   

   jogibear9988

   on Oct 7, 2026

   Member

   More actions

   there exists a select api
2. jogibear9988 commented on Oct 7, 2026

   

   jogibear9988

   on Oct 7, 2026

   Member

   More actions

   Suggestion: Simplify SQL queries using interpolated strings

   We could introduce a new query API based on C# interpolated strings (FormattableString or a custom interpolated string handler).

   This would make SQL queries much easier to write while automatically handling database-specific identifier quoting and SQL parameters.

   Example

   Instead of manually quoting identifiers and using string.Format:

   using (IDataReader reader = Database.ExecuteQuery(
       cmd,
       string.Format(
           "SELECT {0}, {1}, {2} FROM {3} WHERE {1} = 0",
           Database.QuoteColumnNameIfRequired("Id"),
           Database.QuoteColumnNameIfRequired("Archive"),
           Database.QuoteColumnNameIfRequired("GenericDefinition"),
           Database.QuoteTableNameIfRequired("Common_Screens"))))
   {
       // Manually iterate over results
   }

   We could write:

   var screens = Database.Query(
       $"""
       SELECT {Sql.Col("Id")}, {Sql.Col("GenericDefinition")}
       FROM {Sql.Table("Common_Screens")}
       WHERE {Sql.Col("Archive")} = {false}
       """,
       r => (
           Id: r.GetInt32(0),
           Definition: r.GetString(1)
       ));

   Proposed implementation

   Use FormattableString initially:

   public IEnumerable<T> Query<T>(
       FormattableString sql,
       Func<IDataReader, T> mapper)
   {
       // Generate SQL and parameters
       // Execute query
       // Map results and materialize before disposing reader
   }

   Introduce lightweight wrappers for SQL identifiers:

   public readonly record struct SqlColumn(string Name);
   public readonly record struct SqlTable(string Name);
   public static class Sql
   
   {
   
   public static SqlColumn Col(string name) => new(name);
   
   public static SqlTable Table(string name) => new(name);
   
   }

   The query implementation would handle interpolated arguments differently depending on their type:

   Argument	Behavior
   Sql.Col("Id")	Quote column name using the current database dialect
   Sql.Table("Common_Screens")	Quote table name using the current database dialect
   false	Generate a SQL parameter
   someString	Generate a SQL parameter
   Other values	Generate SQL parameters

   Benefits

   • Much cleaner and more readable SQL queries.

   • Automatic database-specific quoting (SQL Server, PostgreSQL, Oracle, SQLite, etc.).

   • Automatic parameterization of values.

   • Protection against SQL injection for interpolated values.

   • Strongly typed results through generics or tuples.

   • No manual IDataReader iteration or disposal required.

   FormattableString vs. custom interpolated string handler

   FormattableString would probably be sufficient for the initial implementation. It already provides access to the format string and its arguments without losing their types.

   A custom interpolated string handler could be introduced later for better performance, fewer allocations, or more specialized compile-time handling.

   Recommendation: Start with FormattableString and a generic Query<T> method. This should solve both issues: cumbersome SQL construction and manual result mapping, without introducing unnecessary complexity.
3. jogibear9988 commented on Oct 7, 2026

   

   jogibear9988

   on Oct 7, 2026

   Member

   More actions

   Alternative: Custom Interpolated String Handler

   Instead of using FormattableString, we could implement a custom interpolated string handler for SQL queries.

   This provides additional flexibility, better performance, and more control over how interpolated values are processed.

   1. Type-specific handling

   A custom handler allows specialized AppendFormatted overloads for SQL identifiers and regular values.

   [InterpolatedStringHandler]
   public ref struct SqlQueryHandler
   {
       public void AppendLiteral(string text)
       {
           // Append raw SQL
       }
   public void AppendFormatted(SqlColumn column)
   {
       // Quote column name
   }
   
   public void AppendFormatted(SqlTable table)
   {
       // Quote table name
   }
   
   public void AppendFormatted<T>(T value)
   {
       // Add SQL parameter
   }
   
   }

   The compiler automatically selects the appropriate overload without requiring runtime inspection of an object[].

   2. Direct access to the database dialect

   The handler can receive the database instance through its constructor:

   public SqlQueryHandler(
   
   int literalLength,
   
   int formattedCount,
   
   Database database)
   
   {
   
   // Use database dialect for quoting
   
   }

   The query method could use:

   public IEnumerable<T> Query<T>(
   
   [InterpolatedStringHandlerArgument("")]
   
   SqlQueryHandler sql,
   
   Func<IDataReader, T> mapper)

   This allows database-specific quoting and parameter generation directly during interpolation.

   3. Custom formatting syntax

   A particularly interesting option would be to use format specifiers instead of explicit wrappers:

   var users = Database.Query(
   
   $"""
   
   SELECT {"Id":column}, {"Name":column}
   
   FROM {"Users":table}
   
   WHERE {"Active":column} = {true}
   
   """,
   
   r => new User(r.GetInt32(0), r.GetString(1)));

   The handler could interpret these format specifiers:

   public void AppendFormatted(
   
   string value,
   
   string? format = null)
   
   {
   
   switch (format)
   
   {
   
   case "column":
   
   AppendColumn(value);
   
   break;
   
   case "table":
   
   AppendTable(value);
   
   break;
   
   default:
   
   AppendParameter(value);
   
   break;
   
   }
   
   }

   This avoids calls such as Sql.Col("Id") or Sql.Table("Users") and makes the SQL much more readable.

   Note that FormattableString also preserves format specifiers, so this syntax alone does not require a custom handler.

   4. Performance advantages

   Feature	FormattableString	Custom handler
   Intermediate object[]	Typically yes	No
   Boxing of value types	Typically yes	Can avoid
   Specialized overloads	No	Yes
   Database dialect access	During processing	During interpolation
   Automatic parameterization	Yes	Yes
   Custom format specifiers	Yes	Yes
   Compile-time type restrictions	Limited	More control
   Implementation complexity	Low	Higher

   For migration operations, database execution time will usually dominate, so performance improvements may not be significant.

   5. Compile-time type checking

   A custom handler can provide overloads only for supported SQL argument types.

   This allows unsupported interpolation types to produce compilation errors instead of runtime errors.

   However, arbitrary SQL literals would still be possible, and custom format specifiers such as :column would not automatically be compile-time validated.

   Recommendation

   I would consider a custom interpolated string handler for Migrator.NET because it offers a clean API with database-specific identifier quoting and automatic parameterization.

   The proposed syntax would be:

   var screens = Database.Query(
       $"""
       SELECT {"Id":column}, {"GenericDefinition":column}
       FROM {"Common_Screens":table}
       WHERE {"Archive":column} = {false}
       """,
       r => (
           Id: r.GetInt32(0),
           Definition: r.GetString(1)
       ));

   Main advantages:

   • No manual quoting of table or column names.

   • Automatic SQL parameter generation.

   • Database-specific SQL handling.

   • Reduced allocations and potentially less boxing.

   • Type-specific interpolation handling.

   • Cleaner and more readable query code.

   The main drawback is increased implementation complexity. Since handlers are ref struct types, asynchronous APIs also require some care around handler lifetimes.

   Both approaches are viable, but a custom handler offers more flexibility for future extensions.
4. JaBistDuNarrisch commented on Oct 8, 2026

   

   JaBistDuNarrisch

   on Oct 8, 2026

   Last edited by JaBistDuNarrisch

   Author

   More actions

   What I mean is sth. like

   provider.Select<MyEntity>(...
5. jogibear9988 commented on Oct 14, 2026

   

   jogibear9988

   on Oct 14, 2026

   Last edited by jogibear9988

   Member

   More actions

   The implementation is split into these two pull requests:

   • Add typed DTO selection, updates and deletes #206: normal predicate lambdas for typed Select<T>, Update<T> and Delete<T>, including string Contains, StartsWith and EndsWith.
   • Add interpolated SQL queries with DTO and tuple results #208: the SQL interpolation proposal using FormattableString, with both Sql.Col / Sql.Table wrappers and :column / :table formats, automatic parameters, and DTO or tuple/custom-mapper results.

   Typed API examples:

   var screens = provider.Select<ScreenDto>("Common_Screens",
       x => (!x.Archive && x.Id > 4) || x.Definition.Contains("aa"));
   
   int updated = provider.Update<ScreenDto>("Common_Screens",
       x => new ScreenDto { Archive = true },
       x => x.Definition.StartsWith("old"));
   
   int deleted = provider.Delete<ScreenDto>("Common_Screens",
       x => x.Archive && x.Definition.EndsWith(".obsolete"));

   Interpolation example:

   var screens = provider.Query<ScreenDto>($"""
       SELECT {"Id":column}, {"Archive":column}, {"GenericDefinition":column}
       FROM {"Common_Screens":table}
       WHERE {"Archive":column} = {false}
       """);

   Both PR descriptions contain API signatures, DTO definitions, usage examples, validation and supported limits. #208 builds on #206's shared mapping/parameter infrastructure: review/merge #206 first, then retarget #208 to master.