0

I'm trying to build an IN clause for array of Guids for MySql query. Guid columns are represented as binary(16) in the DB. According to docs and answers here I should be able to do something like 

var arrayOfGuidsFromDb = ...;

await dbconn.ExecuteAsync<T>("UPDATE ...
SET ...
WHERE SomeGuidField IN @Ids",
new { Ids = arrayOfGuidsFromDb }

I'm also using this Guid converter

class MySqlGuidTypeHandler : SqlMapper.TypeHandler<Guid>
{
    public override void SetValue(IDbDataParameter parameter, Guid guid) => parameter.Value = guid.ToByteArray();

    public override Guid Parse(object value) => new Guid((byte[])value);
}

The issue with MySql though is that it tries (by default) to optimize GUID layout in the DB by rearranging the timestamp part of the GUID value. I decided not to change this behavior, it is working fine for reads/writes and conditions like WHERE SomeGuidField = @SomeGuid but for the IN statement in the question it matches 0 results. I was able to write this hack instead

guids.Select(guid => $"uuid_to_bin('{RotateToMatchInDbGuid(guid).ToString()}')")

where I convert each guid to a string and then string.Join(','... them for the IN clause, the helper method:

static Guid RotateToMatchInDbGuid(Guid source)
    {
        Span<byte> result = stackalloc byte[16];
        source.TryWriteBytes(result);
        Swap(result, 0, 3);
        Swap(result, 1, 2);
        Swap(result, 4, 5);
        Swap(result, 6, 7);
        return new Guid(result);
    }

This obviously doesn't look and feel right. Am I doing something wrong or is there some setting missing that I should enable to make Dapper behavior consistent for both = and IN GUID conditions?

Full code:

Guid[] guids = await dbConn.QueryAsync("SELECT Id FROM SomeTable"); //returns 1 row

// query using IN clause and array param:
var usingIn = await dbConn.QueryAsync("SELECT * From SomeTable WHERE Id IN @Ids", new { Ids = guids}); // returns 0 rows, should be 1

// now query using the `=` operator and same param but as a single value
var usingEquals = await dbConn.QueryAsync("SELECT * From SomeTable WHERE Id = @Id", new { Id = guids.First() }); // returns 1 row as expected

// query using array as CSV and no params
var usingCSV = await dbConn.QueryAsync($"SELECT * From SomeTable WHERE Id IN ({BuildCsv(guids)})"); // also returns 1 row as expected
Oleg Golovkov
  • 303
  • 2
  • 7
  • I suggest switching to https://www.nuget.org/packages/MySqlConnector/ and using its `GuidFormat=TimeSwapBinary16` connection string option (https://mysqlconnector.net/connection-options/). That way, you shouldn't need the custom `MySqlGuidTypeHandler` but the MySqlConnector ADO.NET library will serialize them (efficiently) on the wire whenever you use a `Guid` as a `MySqlCommand` parameter value (either directly, or generated by Dapper). – Bradley Grainger May 26 '20 at 23:40
  • thanks but I already have quite a lot of code written using Dapper and it's fine, the only issue I'm having is this one when I started adding `IN` clauses – Oleg Golovkov May 27 '20 at 08:52
  • Dapper works great with MySqlConnector; there would be no need to change any of your existing code. Just swap out the MySQL ADO.NET library and let it handle the serialization of `Guid` objects on the wire. – Bradley Grainger May 27 '20 at 11:37
  • note: guids as bytes are super awkward in databases because there are multiple mutually exclusive layouts; no-one agrees what it should be – Marc Gravell May 27 '20 at 16:51
  • @MarcGravell I understand, my concern is that the same guid param is treated differently by Dapper depending on whether it's used in `IN` (not rotated) or in `=` (rotated) clause if I'm not mistaken – Oleg Golovkov May 28 '20 at 10:26

1 Answers1

0

Parameterization of an IN clause requires one parameter per IN'd value; you do not pass a CSV string to a single parameter IN and hope it will figure it out

//no
Query("SELECT * FROM person WHERE name IN @names", new { names = "John,Jane,Mary" })

//yes
Query("SELECT * FROM person WHERE name IN (@name1,@name2,@name3)", new { name1 = "John", name2="Jane", name3="Mary" })

The first form will only find someone whose name literally is "John,Jane,Mary"

You might be able to use FIND IN SET:

Query("SELECT * FROM person WHERE FIND_IN_SET(name, @names) > 0", new { names = "John,Jane,Mary" })

But it's a bit lame..

Caius Jard
  • 72,509
  • 5
  • 49
  • 80
  • the `arrayOfGuidsFromDb` variable in my questions is actually an array of Guids `Guid[]` fetched from DB using Dapper and it should(?) work https://stackoverflow.com/questions/17150542/how-to-insert-a-c-sharp-list-to-database-using-dapper-net#comment24825712_17150843 , the CSV hack was added because it did not – Oleg Golovkov May 26 '20 at 14:51
  • I think you might be confusing SQL IN with dapper's iterating an array and repeatedly running the same query, once for each array value? That is to say, if you made the query `("SELECT * FROM t WHERE x IN (@p)", arrayOfX)` with 5 elements in the array, Dapper will run the query 5 times, one element per execution (and IN works with a list of 1, i.e. this is valid SQL :`SELECT * FROM Person WHERE name IN ('Jon')` which is the same as `SELECT * FROM Person WHERE Name = 'Jon'`) - Dapper doesnt do some funky thing to make the query into `x IN (@p1,@p2,@p3,@p4,@p5)` and put one element into each @pX – Caius Jard May 26 '20 at 16:10
  • oh, that's unfortunate, I was sure it would be doing `IN (@p1,@p2,@p3,@p4,@p5)` so this means my CSV hack is actually a best solution if I want to do a single query instead of `N ` for `N` elements? – Oleg Golovkov May 27 '20 at 07:59
  • Actually, I recant that last comment: https://stackoverflow.com/questions/8388093/select-from-x-where-id-in-with-dapper-orm seems to indicate that if you use IN *without brackets* then Dapper *will* convert `Query("... id IN @someList", myArrayOf3Ids)` into the equivalent of `Query("... id IN (@p1,@p2,@p3)", new { p1 = myArrayOf3Ids[0], p2 = myArrayOf3Ids[1], p3 = myArrayOf3Ids[2] } )`, the number of @pX generated being equal to the number of elements in the array – Caius Jard May 27 '20 at 11:42
  • Be careful that any CSV hack you come up woth doesn'y concatenate *values* into an SQL string. Always concat *parameters* and then give the parameters the values. I can't currently explain why the advice given in https://stackoverflow.com/questions/8388093/select-from-x-where-id-in-with-dapper-orm indicates that your second query should work, but doesn't. It also gives an advice for using a table valued parameter - maybe give it a try and see if it works out? – Caius Jard May 27 '20 at 11:49