我正在尝试使用Dapper运行一个已知参数集合的查询，但是这些参数需要使用值列表。一个简单的例子是：
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抛出了一个异常，显示“必须定义参数'@Status'”。我知道在执行批量插入和更新时，Dapper可以处理参数列表，但是它不能用于查询吗？
试一试：
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});
GROUP BY来获得你想要的结果。 - D'Arcy Rittichselect * from people where Name = ANY(@names)。 - rotman啊，我想我知道你的意思了...
是的，我们支持Execute的一种场景，而这种场景对于Query来说是不支持的，具体来说：使用一系列不同的参数值依次运行相同的操作。这在Execute中是有意义的，但对于查询而言，这可能意味着您应该考虑使用in来进行不同参数的查询。或者，只需循环和串联。
相反，它正在查看单个参数对象，并寻找公共值 - 枚举没有任何适合Dapper的参数值。
1,2,3），它会被转换为：(select 1 as val union all select 2 union all select 3)。语法可以是 select * from MyTable t inner join @vals v on t.id = v.val。 - D'Arcy Rittichnew { vals }作为参数传递。 - Marc Gravell 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();
}
用法
*repo是包含上述函数的类
var blah = repo.Query<Domain>(new Domain() { Id = 1, IsActive=true });
输出
SELECT * From Domain Where Id = @Id AND IsActive = @IsActive
string.Join(" AND ", listOfParamEqualValue)。我仍然建议避免使用这种模式，因为它使用了动态SQL，可能会增加后期误用或滥用的风险。 - StingyJackDECLARE @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
n
-----
1
2
3
4
5
...
如果你没有这样的表，那么为了这个命令创建一个表非常快速和简单：
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.
在存储过程中，您不能有多个批处理，但可以在文本命令中使用。 GO 16 运行前面的批处理 16 次。如果您需要在存储过程中使用此功能，可以重复第二个 INSERT 命令多次，而不是使用批处理。对于这个特定的查询来说，2^16 个整数已经足够了，但当需要时，我会复制并粘贴这个命令，2^16 通常已经足够快，以至于我通常不会改变它。 GO 5 将产生 32 个整数，这足以覆盖 24 个日期范围。
下面是一个完整的脚本，演示了这个功能的工作方式：
--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.
*/