Dapper query with list of parameters

Question

I am trying to run a query with Dapper with a known set of parameters, but with a list of values for those parameters. A simple example of what I am trying to do would be:

DateTime endDate = DateTime.Now;
DateTime startDate = endDate.AddHours(-24);

string query = "select COUNT(*) from Test where Status = @Status AND DateCreated <= @Hour;";
var stuff = con.Query(query, (startDate).ByHourTo(endDate).Select(hour => new
{
     Status = 1,
     Hour = hour,
}));

Dapper throws an exception with 'Parameter '@Status' must be defined'. I know Dapper can process lists of parameters when doing bulk inserts and updates, but can it not do this for selects?

Answer 1

DECLARE @Now datetime
SET @Now = getdate()

SELECT
    DATEADD( hh, -n, @Now ) AS StartDate,
    DATEADD( hh, -n+1, @Now ) AS EndDate
INTO
    #DateRanges
FROM 
    Numbers
WHERE
    n <= 24

SELECT
    COUNT(*) AS [Count],
    #DateRanges.StartDate
FROM
    Test
        JOIN
    #DateRanges
        ON Test.DateCreated >= #DateRanges.StartDate
        AND Test.DateCreated < #DateRanges.EndDate
GROUP BY
    #DateRanges.StartDate

That's how I would do it, but this assumes one thing: You have a table in your database named "Numbers" that has an arbitrary number of integers in it, one per row, starting with 1, with at least 24 numbers in it.

That is, the table looks like this:

n
-----
1
2
3
4
5
...

If you don't have such a table, it's very fast and easy to make one just for this command:

CREATE TABLE #Numbers
(
    n int
)

SET NOCOUNT ON

INSERT #Numbers values (1);
GO
INSERT #Numbers SELECT n + (SELECT COUNT(*) FROM #Numbers) FROM #Numbers
GO 16 --execute batch 16 times to create 2^16 integers.

You can't have multiple batches in a stored procedure, but you can in a text command. GO 16 runs the preceding batch 16 times. If you needed this in a stored procedure, you can jut repeat the second INSERT command a number of times instead of using batches. 2^16 integers is overkill for this particular query, but it's a command I copy and paste when needed and 2^16 is usually enough, and so fast that I usually don't bother to change it. GO 5 would yield 32 integers, which is enough for 24 date ranges.

Here's an entire script that illustrates this working:

--Create a temp table full of integers. This could also be a static 
--table in your DB. It's very handy.
--The table drops let us run this whole script multiple times in SSMS without issue.
IF OBJECT_ID( 'tempdb..#Numbers' ) IS NOT NULL
    DROP TABLE #Numbers

CREATE TABLE #Numbers
(
    n int
)

SET NOCOUNT ON

INSERT #Numbers values (1);
GO
INSERT #Numbers SELECT n + (SELECT COUNT(*) FROM #Numbers) FROM #Numbers
GO 16 --execute batch 16 times to create 2^16 integers.

--Create our Test table. This would be the real table in your DB, 
-- so this would not go into your SQL command.
IF OBJECT_ID( 'tempdb..#Test' ) IS NOT NULL
    DROP TABLE #Test

CREATE TABLE #Test
(
    [Status] int,
    DateCreated datetime
)

INSERT INTO 
    #Test 
SELECT 
    1, 
    DATEADD( hh, -n, getdate() )
FROM 
    #Numbers
WHERE
    n <= 48

--#Test now has 48 records in it with one record per hour for 
--the last 48 hours.

--This drop would not be needed in your actual command, but I 
--add it here to make testing this script easier in SSMS.
IF OBJECT_ID( 'tempdb..#DateRanges' ) IS NOT NULL
    DROP TABLE #DateRanges

--Everything that follows is what would be in your SQL you send through Dapper 
--if you used a static Numbers table, or you might also want to include
--the creation of the #Numbers temp table.
DECLARE @Now datetime
SET @Now = getdate()

SELECT
    DATEADD( hh, -n, @Now ) AS StartDate,
    DATEADD( hh, -n+1, @Now ) AS EndDate
INTO
    #DateRanges
FROM 
    #Numbers
WHERE
    n <= 24

/* #DateRanges now contains 24 rows that look like this:

StartDate               EndDate
2016-08-04 15:22:26.223 2016-08-04 16:22:26.223
2016-08-04 14:22:26.223 2016-08-04 15:22:26.223
2016-08-04 13:22:26.223 2016-08-04 14:22:26.223
2016-08-04 12:22:26.223 2016-08-04 13:22:26.223
...

Script was run at 2016-08-04 16:22:26.223. The first row's end date is that time. 
This table expresses 24 one-hour datetime ranges ending at the current time. 
It's also  easy to make 24 one-hour ranges for one calendar day, or anything
similar.
*/

--Now we just join that table to our #Test table to group the rows those date ranges.

SELECT
    COUNT(*) AS [Count],
    #DateRanges.StartDate
FROM
    #Test
        JOIN
    #DateRanges
        ON #Test.DateCreated >= #DateRanges.StartDate
        AND #Test.DateCreated < #DateRanges.EndDate
GROUP BY
    #DateRanges.StartDate

/*
Since we used two different getdate() calls to populate our two tables, the last record of 
our #Test table is outside of the range of our #DateRange's last row by a few milliseconds,
so we only get 23 results from this query. This script is just an illustration.
*/

Answer 2

I know I'm way late to this party but, I think I understand this request to mean that you just want to pass in some properties and generate your query based on those dynamic properties.

with the code below I can use any Type and then just populate and pass in an object of that Type with a few values set (I call this my query object), and the query will be generated to go find objects that match the values that you set in your query object.

*be careful of bools and things that have default values.

Dynamic Query Example

    public IEnumerable<T> Query<T>(T templateobject) {
        var sql = "SELECT * From " + typeof(T).Name + " Where ";

        var list = templateobject.GetType().GetProperties()
             .Where(p => p.GetValue(templateobject) != null)
             .ToList();

        int i = 0;

        Dictionary<string, object> dbArgs = new Dictionary<string, object>();

        list.ForEach(x =>
        {
            sql += x.Name + " = @" +  x.Name;

            dbArgs.Add(x.Name, x.GetValue(templateobject));

            if (list.Count > 1 && i < list.Count - 1) {
                sql += " AND ";
                i++;
            }
        });

        Debug.WriteLine(sql);

        return _con.Query<T>(sql, dbArgs).ToList();
    }

Usage

*repo is the class that contains the above function

var blah = repo.Query<Domain>(new Domain() { Id = 1, IsActive=true });

Output

SELECT * From Domain Where Id = @Id AND IsActive = @IsActive

then it spits out any "Domains" that match the above query.

Answer 3

Ah, I think I see what you mean...

Yes, there is a scenario we support for Execute that isn't supported for Query, specifically: to run the same operation sequentially with a range of different parameter values. This makes sense for Execute, but for query it probably means you should be looking at a different query using in. Alternatively, just loop and concat.

Instead, it is looking at the single parameter object and looking for public values - an enumerable doesn't have any suitable parameter values for dapper.

Answer 4

Try this:

List<string> names = new List<string> { "Bob", "Fred", "Jack" };
string query = "select * from people where Name in @names";
var stuff = connection.Query<ExtractionRecord>(query, new {names});