Dapper语言 - 查找不在表中的列表项

Question

我有一个id列表(动态生成)，我想找出其中哪些不存在于表中。

我使用的是Dapper和Microsoft SQL Server。

Dapper允许将IEnumerable参数传递到查询中并用作变量，这很好，但我似乎无法使它适用于从该列表中选择内容的场景。

下面是我的代码，展示了我尝试过的两件事(这两件事都不起作用)，以及我正在考虑的丑陋解决方案(将执行得很差)的Attempt3。

using Dapper;
using System;
using System.Data.SqlClient;
using System.Linq;
namespace DapperTest
{
class Program
{
static void Main(string[] args)
{
var connectionString = $"Initial Catalog=master;Integrated Security=True;";
using (SqlConnection con = new SqlConnection(connectionString))
{
//Here's my overall problem:
// Assume a table X exists with a lot of rows in it.
// Now I have a list of IDs, which IDs in that list are not in table X?
//Note: I will spoof X with a CTE in each query. Also, the IDs are uniqueidentifiers in the real table, 
// but I'll use ints for this simple demo.
Attempt1(con);
Attempt2(con);
Attempt3(con);
}
}
//This fails because I cannot select from the @Ids parameter (what I would like to do, but syntax does not support it)
//System.Data.SqlClient.SqlException (0x80131904): Incorrect syntax near ','
static void Attempt1(SqlConnection con)
{
int[] idsToCheck = new[] { 1, 42, 112 };
string sql = @"
WITH X(Id) AS (SELECT 112 UNION SELECT 200)
SELECT * FROM @Ids 
WHERE Id NOT IN (SELECT Id FROM X)
";
try
{
var result = con.Query(sql, new { Ids = idsToCheck });
Console.WriteLine("Attempt2: " + string.Join(",", result));
}
catch (Exception e)
{
Console.WriteLine("Attempt1 - " + e.Message);
}
}
//Here I tried to stuff the list into a table variable and then select from it.
//Fails with this error:
// An enumerable sequence of parameters (arrays, lists, etc) is not allowed in this context
static void Attempt2(SqlConnection con)
{
int[] idsToCheck = new[] { 1, 42, 112 };
string sql = @"
DECLARE @Tmp TABLE ([Id] int);
INSERT INTO @Tmp VALUES (@Id);
WITH X(Id) AS (SELECT 112 UNION SELECT 200)
SELECT Id FROM @Tmp WHERE Id NOT IN (SELECT Id FROM X)
";
try
{
//note I pass in an IEnumerable directly as the second param
var result = con.Query(sql, idsToCheck.Select(i => new { Id = i }));
//but in the "Execute" context the below call works just fine - but I can only get back an affected row count from Execute (this frustrates me).
//var result = con.Execute(sql, idsToCheck.Select(i => new { Id = i }));
Console.WriteLine("Attempt2: " + string.Join(",", result));
}
catch (Exception e)
{
Console.WriteLine("Attempt2 - " + e.Message);
}
}
//This works but is *very* undesirable because it fetches back a lot of data (everything in X).
static void Attempt3(SqlConnection con)
{
int[] idsToCheck = new[] { 1, 42, 112 };
string sql = @"
WITH X(Id) AS (SELECT 112 UNION SELECT 200)
SELECT Id FROM X
";
try
{
var allIdsInX = con.Query<int>(sql, null);
var result = idsToCheck.Except(allIdsInX);
Console.WriteLine("Attempt3: " + string.Join(",", result));
}
catch (Exception e)
{
Console.WriteLine("Attempt3 - " + e.Message);
}
}
}
}

Answer 1

@ aslawyer， @Charlieface - TVP解决方案最终看起来像这样。我想要更多的代码，但不是很糟糕。如果我为不同的ID类型(UNIQUEIDENTIFIER, INT，等等)创建一些PLACEHOLDERListTableType表类型，然后将它们视为"标准"，我想我可以称之为可重用。

我仍然想要一个更可重用的解决方案，不需要在我使用它的每个数据库中创建自定义类型。所以我就不把这个标记为答案了。我希望有人知道一些Dapper/SQL技巧，可以更简单地解决这个问题。

/*
Created this table valued type, now try using it.
CREATE TYPE [dbo].[IntListTableType] AS TABLE 
(
[Id] INT NOT NULL,
PRIMARY KEY ([Id])
)
*/
static void Attempt4(SqlConnection con)
{
int[] idsToCheck = new[] { 1, 42, 112 };
string sql = @"
WITH X(Id) AS (SELECT 112 UNION SELECT 200)
SELECT Id FROM @Ids WHERE Id NOT IN (SELECT Id FROM X)
";
try
{
var result = con.Query<int>(sql, new { Ids = GetIntListTableValuedParameter(idsToCheck) });
Console.WriteLine("Attempt4: " + string.Join(",", result));
}
catch (Exception e)
{
Console.WriteLine("Attempt4 - " + e.Message);
}
}
static SqlMapper.ICustomQueryParameter GetIntListTableValuedParameter(IEnumerable<int> ids)
{
var dt = new DataTable();
dt.Columns.Add("Id", typeof(int));
foreach (var id in ids)
{
dt.Rows.Add(id);
}
return dt.AsTableValuedParameter(" [dbo].[IntListTableType]");
}