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?

Dominoes

Did you see the custom "in" examples on the dapper homepage? This is indeed a well-supported scenario – Genesa

@Marc Yeah, I know how to do the example above with Dapper easily using 'IN'. Let me modify my question to hopefully show why IN doesn't work in my situation. I tried to distill what I am wanting to do but obviously my example is too simplistic. – Dominoes

@Marc, I have modified the example to be closer to what I am actually trying to do, which is generate reporting data over a timespan. – Dominoes

The reason Dapper supports lists in an Insert is because each insert is a separate command. Would you really want to execute 24 separate Selects? My go-to answer for partitioning data using date ranges is to generate a temp table with my ranges and join to it. This is just plain T-SQL and nothing to do with Dapper, but Dapper can obviously map the results of the final Select for you. If I find the time, I'll add an example as an answer. I realize this is a three-year old question, but I got here from Google. Other people might appreciate the answer. – Golly

I added my answer, as promised. – Golly

Answer 1

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.

Genesa

Yeah that's what I was afraid of. My actual query is pretty complicated but involves generating a result set where each row contains COUNTs of various things based on a timestamp. The timestamp has to be used in a couple of JOINs, so I don't think there as any way to use an IN. I don't know of any way to create a SQL query that will select on every hour between two timestamps, so I had to use C# to generate them. My current solution executes multiple independent queries like you suggest. Thanks – Dominoes

It would be cool if Dapper could convert an IEnumerable param to a derived table (to be used in JOINs). Column name(s) could be based on parameter name. – Urbai

@RedFilter do you mean like a table variable? – Genesa

@MarcGravell I am thinking of a list of values (e.g., 1,2,3) that gets turned into: (select 1 as val union all select 2 union all select 3). Syntax could be select * from MyTable t inner join @vals v on t.id = v.val – Urbai

@RedFilter you can do that with the specialized "in" - "where t.id in @vals", passing new { vals } as the args – Genesa

@MarcGravell Think of a situation where you want to provide Dapper a list of values (e.g., dates) to be used as the left table in a LEFT OUTER JOIN, and where there is no existing table to query them from (or that is known to contain all the dates), so using IN is not possible. That's the case I would love to be able to handle. This is why you would need to construct the table using UNION ALL. Unfortunately this would not be platform-neutral as Oracle would require from dual. – Urbai

@redfilter would a TVP help there? – Genesa

@MarcGravell Sure, for databases that support them. – Urbai

Answer 2

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});

Gondi

Should where "where Name in @names", yes? – Genesa

See comments above. As I stipulated in my original question, ID doesn't work in my query. – Dominoes

It looks like your question is wrong in a few ways now, but your intent is not that clear (e.g., you are comparing DateCreated to an @Hour?). I am not really sure where you are stuck. Start by reading the Dapper docs, you're not passing params properly. – Urbai

I think Marc's answer verifies the conclusion I had, in that there is a difference between the way Dapper will process parameters between Execute and Query. I made an additional edit to hopefully be clearer. – Dominoes

@Dominoes I think providing sample data and desired output would help. You may be able to use GROUP BY to get what you want. – Urbai

@RedFilter If you think it's possible, I will take the time to set up a question providing schema and test data for my scenario, however, based on other questions I have seen, I don't think it's possible to craft a single query in MySQL to do everything I want. Here are the two queries I run. gist.github.com/4040312 The first gets me the max and min times, and I use C# to calculate every hour between the two times to execute the second query against. – Dominoes

let us continue this discussion in chat – Dominoes

If you want to use it with PostgreSQL and get error similar to operator does not exist: integer = integer[] then try this: select * from people where Name = ANY(@names) – Wite

I wanted to effectively join on two values, so I concatenated them with a colon separator, matched this in the c# code and used single parameter check – Stalag

Answer 3

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.

Panhellenism

instead of fussing with when to add the AND in, try string.Join(" AND ", listOfParamEqualValue) I would still advise against this pattern because it does use dynamic sql, and the potential for later mis-use or abuse is increased. – Oscitant

Answer 4

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.
*/

Golly

I see no "Dapper" here per the OP question – Emaemaciate