Блог

У меня есть список идентификаторов (динамически генерируемых), и я хочу найти, какие из них не существуют в таблице.

Я использую Dapper и Microsoft SQL Server.

Dapper позволяет передавать параметры IEnumerable в запросы и использовать их в качестве переменных, что приятно, но я не могу заставить его работать для сценария выбора объектов из этого списка.

Вот мой код, показывающий две вещи, которые я пытался (обе из которых не работают), и попытку 3, которая является уродливым решением, которое я рассматриваю (будет плохо работать).

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

@asawyer, @Charlieface — вот как выглядит решение TVP. Больше кода, который я хотел бы, но не ужасно. Если я создам несколько типов таблиц PLACEHOLDERListTableType для разных типов идентификаторов (UNIQUEIDENTIFIER, INT, что угодно), а затем буду рассматривать их как «стандартные», я думаю, я могу назвать это многоразовым.

Я все еще хотел бы иметь более многоразовое решение, которое не требует создания пользовательских типов в каждой базе данных, в которой я его использую. Так что я не буду отмечать это как ответ. Я надеюсь, что кто-то знает некоторые хитрости 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]");
}