How to create arguments for a Dapper query dynamically

Question

I have a dictionary of values Eg "Name": "Alex"

Is there a way to pass this to Dapper as arguments for a query?

Here is an example showing what I want to do.

IDictionary<string, string> args = GetArgsFromSomewhere();
string query = "select * from people where Name = @Name";
var stuff = connection.Query<ExtractionRecord>(query, args);

score 168 · Accepted Answer · answered Feb 28 '12 at 12:17

168

Yes:

var dbArgs = new DynamicParameters();
foreach(var pair in args) dbArgs.Add(pair.Key, pair.Value);

Then pass dbArgs in place of args:

var stuff = connection.Query<ExtractionRecord>(query, dbArgs);

Alternatively, you can write your own class that implements IDynamicParameters.

Note that if you are starting from an object (the usual approach with dapper), you can also use this template with DynamicParameters as a starting point:

var dbArgs = new DynamicParameters(templateObject);

answered Feb 28 '12 at 12:17

Marc Gravell

1,026,079
266
2,566
2,900

32

Note that you can do `new DynamicParameters(dictionary)` and it will work just fine. – asgerhallas Dec 05 '12 at 10:19
15

in order for new DynamicParameters(dictionary) to work, dictionary must be a IEnumerable>, for instance Dictionary. Dictionary didn't work. – Zar Shardan Mar 26 '14 at 21:13

score 25 · Answer 2 · edited Nov 21 '21 at 13:21

I know this is an old question (like, 5 years old) but I was struggling with the same thing. The complete answer is in the comments to the other answer, but I thought I would offer a full example here.

string query = "SELECT * FROM MyTableName WHERE Foo = @Foo AND Bar = @Bar";

Dictionary<string, object> dictionary = new Dictionary<string, object>();
dictionary.Add("@Foo", "foo");
dictionary.Add("@Bar", "bar");

var results = connection.Query<MyTableName>(query, new DynamicParameters(dictionary));

Or, to be fully dynamic, you can create a method like this, which will take any model, any query, and any set of query parameters:

public static IEnumerable<T> Get<T>(string query, Dictionary<string, object> dictionary)
{
    IEnumerable<T> entities = connection.Query<T>(query, new DynamicParameters(dictionary));
    return entities;
}

And then to call this method:

var results = Get<MyTable>(query, dictionary)

EDIT LONG AFTER

This answer continues to get upvotes, so this is apparently still a need. I took this solution and created an entire data access NuGet package built on top of Dapper. It reduces your CRUD and query operations to a single line of code.

Here's the NuGet package.

Unfortunately every new version of Dapper seems to break this in a slightly different way in some circumstances. I'm continuing being forced to go back and fix legacy Dapper queries after package updates, and almost every time it's due to the params not working consistently in all scenarios. I'm fixing the same code today for the 4th time in 2 years, due to Dapper updates. — Bryan Williams, Jul 07 '23 at 22:15

score 3 · Answer 3 · answered Oct 09 '18 at 11:13

One can also use an ExpandoObject as the parameters of a query, instead of the Dapper-specific class DynamicParameters:

ExpandoObject param = new ExpandoObject();

IDictionary<string, object> paramAsDict = param as IDictionary<string, object>;
paramAsDict.Add("foo", 42);
paramAsDict.Add("bar", "test");

MyRecord stuff = connection.Query<MyRecord>(query, param);

How to create arguments for a Dapper query dynamically

3 Answers3

Linked