SQL查询工作在azure数据工作室，但没有返回任何通过vs代码

Question

当我在Azure Data Studio中尝试时，我有以下SQL查询返回几行:

SELECT 
A.ReportRefreshDate, COUNT(ActivityName) AS TotalActivity, 
ActivityName as ActivityType
FROM
WidgetActivity A
INNER JOIN 
WidgetUsers ON LOWER(WidgetUsers.UserPrincipalName) = LOWER(A.UserId)
WHERE 
A.TMID = 4
AND WidgetUsers.Branch = '52'
AND A.ReportRefreshDate BETWEEN DATEADD(DD, -300, GETDATE()) AND GETDATE() 
AND A.ActivityName IN ('Action1', 'Action2')
GROUP BY
A.ActivityName, A.ReportRefreshDate

返回如下几行:

ReportRefreshDate         TotalActivity ActivityType
-----------------------------------------------------
2022-05-16 07:18:06.000   9             Action1
2022-05-16 07:18:06.000   3             Action2

但是当我在VS Code中尝试这个时，它返回一个空的结果集。

下面是代码片段:

Console.WriteLine(activityFilter);
activityByActivityType = conn.Query<WidgetActivityDetailsByActivityTypes>(
@"
SELECT A.ReportRefreshDate, COUNT(ActivityName) AS TotalActivityCount, ActivityName AS ActivityType
FROM WidgetActivity A
INNER JOIN WidgetUsers 
ON LOWER(WidgetUsers.UserPrincipalName) = LOWER(A.UserId)
WHERE 
A.TMID = @TM
AND WidgetUsers.Branch = @Branch
AND A.ReportRefreshDate BETWEEN DATEADD(DD, -@RefreshDate, GETDATE()) AND GETDATE() 
AND A.ActivityName IN (@ActivityList)
GROUP BY A.ActivityName, A.ReportRefreshDate
",
new { Branch=branchId, TM = tmid, RefreshDate = dateRange, ActivityList = activityFilter}).ToList();
}
return activityByActivityType;

我认为这个问题与@ActivityList变量有关，因为当我删除AND子句时，返回一堆数据。

activityFilter上的Console.Writeline返回:

'Activity1', 'Activity2'

在我看来是正确的。

我肯定是错过了什么简单的东西，但我看不见。

如有任何帮助，不胜感激。

Answer 1

我必须改变sql看起来像这样:

activityByActivityType = conn.Query<WidgetActivityDetailsByActivityTypes>(
$@"
SELECT A.ReportRefreshDate, COUNT(ActivityName) AS TotalActivityCount, ActivityName AS ActivityType
FROM WidgetActivity A
INNER JOIN WidgetUsers 
ON LOWER(WidgetUsers.UserPrincipalName) = LOWER(A.UserId)
WHERE 
A.TMID = @TM
AND WidgetUsers.Branch = @Branch
AND A.ReportRefreshDate BETWEEN DATEADD(DD, -@RefreshDate, GETDATE()) AND GETDATE() 
AND A.ActivityName IN ({activityFilter})
GROUP BY A.ActivityName, A.ReportRefreshDate
",
new { Branch=branchId, TM = tmid, RefreshDate = dateRange}).ToList();
}