Array expansion

[smddzcy]

First of all, thanks a lot for maintaining this great library.

We're using MariaDB with Dapper & DapperQueryBuilder and currently having some issues with array expansion. Dapper expands each element in the IN query like col IN (@p1, @p2) but DapperQueryBuilder just leaves it like col IN @parray1, which doesn't work for MariaDB/MySQL.

@Drizin @jehhynes Do you have any suggestions?

[Drizin]

Hi @smddzcy . That should be an easy change: check out how we're doing for the Parameters Prefix to allow other databases (e.g. Oracle) and you can do something similar to have a public lambda function returning the string of how IN parameters should be rendered. If you're not ok with doing the PR I can certainly work on that next week.

[smddzcy]

I already checked the source code but couldn't find an easy way to achieve this, and I'm going on a 2-week vacation tomorrow, so I won't have much time to look into it 😄

As a quick fix, I monkey patched the IN query in our code like this:

[Drizin]

Actually I think I need more context here. Basically our library just pass the arrays to Dapper so it should expand exactly like Dapper do. We don't add any parentheses (except for grouping multiple different where predicates).

If you send without parentheses ($ "where column IN {array}) Dapper should get the query without parentheses ( IN @parray0 ), and then I assume Dapper PackListParameters should do whatever it does for the different databases.

Can you send the full code snippet (comparing Dapper and DapperQueryBuilder) and more details about what Dapper is receiving?

[Drizin]

@smddzcy I created a MariaDB database and a unit test for array expansion in MariaDB/MySQL: It seems to be working fine.

When Dapper PackListParameters is invoked it adds the parentheses IN (@parray01,@parray02) but even though it seems to be working fine with my MariaDB version. Maybe you're doing something different?

[smddzcy]

@Drizin the field we're using is a nullable array:

Can you try with a test case where the parameter is an array of enums like this, instead of a List? Maybe the List expansion is working fine but an array (or a nullable array) is not. It can simply be a Dapper issue as well, or maybe our usage is not supported.

[Drizin]

Like this?

public enum AuthorsEnum
{
    Kafka = 1,
    MachadoDeAssis = 2,
}
[Test]
public void TestNullableEnumArrays()
{
    AuthorsEnum[]? authorIds = new AuthorsEnum[] { AuthorsEnum.Kafka, AuthorsEnum.MachadoDeAssis };
    var authors = cn.QueryBuilder($"SELECT * FROM authors WHERE author_id IN {authorIds}").Query<Author>();
    Assert.That(authors.Any());
    Assert.AreEqual(cn.PreviousCommands.Last().CommandText, 
        "SELECT * FROM authors WHERE author_id IN (@parray01,@parray02)");
}

It's working for me. Check unit tests.

Would you like to share your full source code and the error you're getting? In Unit Tests you should find UnitTestsDbConnection.cs which might be helpful to capture what exactly is being sent to Dapper.

[smddzcy]

The only difference from our source code is now the "WHERE" part. We use /**where**/ and populate the where query dynamically.

Here's the error we get:

You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '@parray4 AND...'

[Drizin]

I'm afraid you'll have to provide more details here. Either you can share a minimal reproducible example (where I can just paste and reproduce your error) or you'll have to troubleshoot yourself. You can wrap your connection using UnitTestsDbConnection.cs and then you can check what exactly is being sent to Dapper. (cn.PreviousCommands.Last().CommandText and cn.PreviousCommands.Last().Parameters)

[Drizin]

@smddzcy By the lack of response I'm assuming it's working, so I'm closing this. Please reopen (with more details) if it's not working.