CODEWITHSUNDEEP
c#dapper
rolandow
rolandow

Reputation: 1099

Using two dimensional array as Dapper parameter list

In Dapper it is possible to query a list with where in using this code:

var sql = "SELECT * FROM Invoice WHERE Kind IN @Kind;";

using (var connection = My.ConnectionFactory())
{
    connection.Open();

    var invoices = connection.Query<Invoice>(sql, new {Kind = new[] {InvoiceKind.StoreInvoice, InvoiceKind.WebInvoice}}).ToList();
}

But I'd like to query a list of products by multiple suppliers and their product code. So to do this, I tried creating an array of arrays. This is my method in my repository:

public Dictionary<int, Dictionary<string, Product>> GetGroupedListByRelationIdAndProductCode(Dictionary<int, List<string>> productKeysByRelationId)
{
    Dictionary<int, Dictionary<string, Product>> list = new Dictionary<int, Dictionary<string, Product>>();

    string sql = "SELECT * FROM Products WHERE 1=1 ";

    int i = 0;
    foreach (KeyValuePair<int, List<string>> productKeys in productKeysByRelationId)
    {
        sql = sql + " AND (ManufacturerRelationId = " + productKeys.Key + " AND ManufacturerProductCode in @ProductCodeList[" + i + "] )";
        ++i;
    }

    using (var conn = _connectionFactory.CreateConnection())
    {
        conn.Open();

        var param = new { ProductCodeList = productKeysByRelationId.Select(x => x.Value.ToArray()).ToArray() };

        var productsList = conn.Query<Product>(sql, param).ToList();

        if (productsList.Count > 0)
        {
            foreach (var product in productsList)
            {
                list[product.ManufacturerRelationId][product.ManufacturerProductCode] = product;
            }
        }
    }


    return list;
}

This gives me this error though: System.ArgumentException: 'No mapping exists from object type System.String[] to a known managed provider native type.'

Any suggestions on how to do this?

Upvotes: 1

Views: 1371

Answers (1)

Palle Due
Palle Due

Reputation: 6292

You have several problems with your code.

If you have more than one item in your productKeysByRelationId you will end up getting SQL like:

WHERE 1=1 AND ManufacturerRelationId = 1 ... AND ManufacturerRelationId = 2

That's not likely to return any results, you need to sprinkle some OR's in there.

The error you are getting is because you have something like:

AND ManufacturerProductCode in @ProductCodeList[0]

Dapper cannot handle that. It mostly expects the query parameters to be an object with aptly named members of a few simple types or array.

Thankfully Dapper has a solution to that, DynamicParameters to the rescue!

You can construct your query like this:

var queryParams = new DynamicParameters();
foreach (var productKeys in productKeysByRelationId)
{
    sql = sql + $" ... AND ManufacturerProductCode in @ProductCodeList{i} )";
    queryParams.Add($"ProductCodeList{i}", productKeys.Value);
    i++;
}

Now you have your query parameters in the right format, so you can just do this:

var productsList = conn.Query<Product>(sql, queryParams).ToList();

That ought to fix it, but you really should try to parameterize the ManufacturerRelationId also. It's not just about SQL injection, there might be some SQL cache performance related things. You might also gain some clarity in code by using the SqlBuilder from Dapper.Contrib.

Upvotes: 2

Related Questions