Support TVP directly InList as IEnumerable

[osexpert]

Hi,

I have a branch here https://github.com/osexpert/Dapper/tree/TVPParameters I have added support for TVP directly as argument.

Example:

SqlMapper.Settings.InListTVPCount = 1;

var list = new List<int>();
list.Add(42);
// Add some extras, to see that it works with many arguments:
for (int i = 100; i < 100000; i++)
	list.Add(i);

var res = conn.Query("select * from persons where id in @ids", new { ids = list });

The table types must currently be created outside of Dapper. A few types are handled by default (byte, short, int, long, Guid). Other types can be added, eg: SqlMapper.Settings.InListTVPHandlers.Add(typeof(DateOnly), new() { IdColumn = "Id", TypeName = "Dapper_DateOnly" });

And create the type is ms sql: CREATE TYPE dbo.Dapper_DateOnly AS TABLE (Id date NOT NULL)

There is no tests written yet, but I will write some if this is something that can be merged. It is also possible to add a way to get the table types created. Must still be done manually, but then at least can call a method in Dapper to create them (or get the sql for them) instead of doing this all manually.

Thanks.

[mgravell]

My perspective is that if we depend on very external things like:

CREATE TYPE dbo.Dapper_Byte AS TABLE (Id tinyint NOT NULL)
CREATE TYPE dbo.Dapper_Int16 AS TABLE (Id short NOT NULL)
CREATE TYPE dbo.Dapper_Int32 AS TABLE (Id int NOT NULL)
CREATE TYPE dbo.Dapper_Int64 AS TABLE (Id bigint NOT NULL)
CREATE TYPE dbo.Dapper_Guid AS TABLE (Id uniqueidentifier NOT NULL)
CREATE TYPE dbo.Dapper_String AS TABLE (Id nvarchar(max) NOT NULL)

then this is a bit outside what should be considered the "core" library. I think there is probably a place for something like this in your own external package, however. At the API level, I think an extension method like

public static DataTable AsDapperTable(this IEnumerable<int> values)
public static DataTable AsDapperTable(this IEnumerable<long> values)

etc, which does the thing internally, just returning the correctly configured DataTable, might be preferable.

I don't think we'd merge this into the core, however.

[osexpert]

The StringSplit \ string_split seems very tied to ms sql as well? I think this is not worse than that :-D But it would be better to have some generic\core mechanism to hook into that would allow to do the same, just with the code in a different place. There are probably endless ways to "pack" values for InList so it would be nice if this could be extendable.

"etc, which does the thing internally, just returning the correctly configured DataTable, might be preferable." Yes, this got me close, but had to rewrite "in @var" to "in (select id from @var)" and want to avoid that

[mgravell]

Ultimately, somebody needs to make the call on what "fits" in the core library, and what doesn't. There is a bit of a "degree" change for features that need specific database setup, vs opt-in features that are server-specific but don't need specific setup. In some ways I wished SQL Server shipped with some basic columnar types built-in, or allowed some kind of generic "column of T", but: it doesn't. So: we need to make a call about it, and my opinion is that this would be great in some other package, but isn't ideal for the core.

[osexpert]

What do you think about some sort of generic hook\handler to hook into PackListParameters? It could be as generic as public static Func<IDbConnection, string, object, bool> InListCustomHandler = null; Pro: Can do "anything" Con: need to replicate much of the work done in PackListParameters, eg. regEx.

Or more specific, with a hook\handler into a RegEx replace loop and a hook\handler for creating the parameter. Pro: much of the "dirty work" already done, only need to supply a replacement string (select) and a parameter Con: may not fit all future needs

[osexpert]

And it would maybe be cool if StringSplit could fit into this somehow, maybe StringSplit could be the default custom handler. I created a new branch here that show the idea: https://github.com/osexpert/Dapper/tree/InListCustomHandler

[mgravell]

I think we're approaching this from the wrong angle. The aim shouldn't be "make inlist work in weird and wonderful ways"; IMO leave that well alone. I think a better aim in this case is:

make it easy to use the correct and TVP-aware TSQL, and conveniently pass the value as a UDF

This sounds more like a job for a type handler, with zero changes to the core. This could be, for example, declaring some custom wrapper type that enables it.

[osexpert]

I am not sure what you mean. What would the type handler do? Enables it?

[mgravell]

I'll put an example together

[mgravell]

In fact, we don't even need that; here's a working version with zero library changes and not even a custom type-handler:

using Dapper;
using Microsoft.Data.SqlClient;
using System.Data;


using var conn = new SqlConnection("Server=.;Database=master;Trusted_Connection=True;TrustServerCertificate=True");

// reset server state
conn.Execute("DROP TYPE IF EXISTS dbo.Dapper_String");
conn.Execute("CREATE TYPE dbo.Dapper_String AS TABLE ([Value] nvarchar(max) NOT NULL)");
var names = new[] { "bitpos", "colguid", "nordlock", "rowsetid" };

var rows = conn.Query<Column>("""
    select *
    from sys.columns
    where name in (select [Value] from @names)
    """, new
{
    names = names.AsTableValuedParameter("dbo.Dapper_String")
});

foreach (var row in rows)
{
    Console.WriteLine($"{row.Object_ID}\t{row.Column_ID}\t{row.Name}");
}

class Column
{
    public int Object_ID { get; set; }
    public string? Name { get; set; }
    public int Column_ID { get; set; }
}

static class MyExtensions
{
    public static SqlMapper.ICustomQueryParameter AsTableValuedParameter<T>(this IEnumerable<T> values, string typeName)
    {
        var dt = new DataTable();
        dt.Columns.Add("Value", typeof(T));
        var row = new object[1];
        foreach (var value in values)
        {
            row[0] = (object?)value ?? DBNull.Value;
            dt.Rows.Add(row);
        }
        return dt.AsTableValuedParameter(typeName);
    }
}

[mgravell]

personally, I'm in favor of steering people towards this approach rather than further complicating the core

[osexpert]

I think we are going in circles here:-)

"In fact, we don't even need that; here's a working version with zero library changes and not even a custom type-handler:" Yes I know, I already mentioned that:-D I wrote: "Yes, this got me close, but had to rewrite "in @var" to "in (select id from @var)" and want to avoid that"

So the code I currently have, is basically the code in your example. And this is what I am trying to get away from:-)

[mgravell]

Right. I am extremely reluctant to make big feature changes to the core at this point. The extension APIs, if you call them that, are weak, are we made poor choices re interfaces. I honestly don't want to introduce more variables.

I'd rather put this forward as a candidate extension in AOT (see https://github.com/DapperLib/Dapper/issues/1909), where we have much better ability to make changes without destroying the world.

[osexpert]

"big feature changes" I don't see this as a big change thou. Its a list, an interface with 1 method, a few lines of code:-) It can help to keep things out of the core, even allowing to move the ms sql specific string_split handler out of the core, if anyone would want that:-) But at least the idea is out there.

"I'd rather put this forward as a candidate extension in AOT" Not sure what this involves, but if it can solve the problem, then great:-) Maybe I can help in some way.