DynamicParameters fails to add template if ForEach is chained after ToList

Question

DynamicParameters fails to add template if ForEach is chained after ToList

c# dapper

Question

I have a function that dynamically builds sql statements based on an existing model and then adds some common fields that exist in every table.

The problem is that I'm experiencing some odd behaviour when I try to add the common fields.

using System;
using System.Collections.Generic;
using System.Linq;
using System.Data.SqlClient;
using Dapper;

public class Program
{
    private static readonly dynamic[] Data = new dynamic[] { new {Foo = 1, Bar = 2}, new {Foo = 3, Bar = 4} }; 
    public static void Main()
    {
        Console.WriteLine("Hello World");
    }

    public static void Broken()
    {
        var parameters = ((IEnumerable<dynamic>)Data).Select(x => new DynamicParameters(x));
        parameters.ToList().ForEach(x => x.AddDynamicParams(new {Bax = 7}));
        using (var connection = new SqlConnection(""))
        {
            // This fails with a "Must declare scalar variable @Bax".
            connection.Execute("INSERT INTO DataTable(Foo, Bar, Bax) VALUES(@Foo, @Bar, @Bax)", parameters);
        }
    }

    public static void Working()
    {
        var parameters = ((IEnumerable<dynamic>)Data).Select(x => new DynamicParameters(x)).ToList();
        parameters.ForEach(x => x.AddDynamicParams(new {Bax = 7}));
        using (var connection = new SqlConnection(""))
        {
            // This works fine.
            connection.Execute("INSERT INTO DataTable(Foo, Bar, Bax) VALUES(@Foo, @Bar, @Bax)", parameters);
        }
    }
}

If you'll notice, the only difference is the position of my call to .ToList(). In the Broken function, I call it as part of a chain before .ForEach and in the the Working function, I call it on the same line as the assignment.

I've tried replacing the call to AddDynamicParams with individual Add calls for each parameter, but it doesn't seem to matter. I've also tried doing for(var parameter in parameters) { parameter.AddDynamicParams(new {Bax = 7}) } with no luck.

Unfortunately, there's no good way that aware of to show the templates without setting a breakpoint and examining the .templates attribute of the object in Visual Studio. If someone can provide a better way to demonstrate the template disappearing, I'm all ears.

Morgan Thrapp

View more on Stack Overflow

Federico Dipuma · Accepted Answer · 6/27/2017 6:18:10 PM

Inside your Broken method you are effectively enumerating your collection twice. This is due to LINQ Deferred Execution.

This line:

 parameters.ToList().ForEach(x => x.AddDynamicParams(new {Bax = 7}));

Will enumerate your IEnumerable<dynamic> by creating a List<dynamic>, which you are modifying using ForEach, but then you do not use this list at all, and again pass your parameters object to the connection.Execute method:

connection.Execute("**your query**", parameters);

Whenever Dapper enumerates the parameter object it will again project each object (which are not the same objects found inside the above list) and thus creating a new instance which is not modified using AddDynamicParams.

To be more clear, your broken method is equivalent to the following:

public static void Broken()
{
    var parameters = ((IEnumerable<dynamic>)Data).Select(x => new DynamicParameters(x));
    var parametersList = parameters.ToList(); // you create a list
    parametersList.ForEach(x => x.AddDynamicParams(new {Bax = 7})); // then you modify that list

    // parametersList != parameters

    using (var connection = new SqlConnection(""))
    {
        // This fails with a "Must declare scalar variable @Bax".
        connection.Execute("INSERT INTO DataTable(Foo, Bar, Bax) VALUES(@Foo, @Bar, @Bax)", parameters); // but here you are passing the old parameters collection
    }
}

To solve your issue either use your Working version, or use again LINQ to create a working enumerable version:

var parameters = ((IEnumerable<dynamic>)Data).Select(x =>
{
    var p = new DynamicParameters(x);
    p.AddDynamicParams(new {Bax = 7});
    return p;
});

DynamicParameters fails to add template if ForEach is chained after ToList

Question

Fastest Dapper Plus Extensions

Accepted Answer

Performance

Expression Evaluator