Все/любая реализация в SQL наряду с IN

Подобные запросы довольно неудобно выражать в SQL (примечание: я бы избегал здесь LEFT OUTER JOIN, так как это может привести к дублированию строк).

«содержит любой» — это нормально (обычно через EXISTS (SELECT 1 FROM BlogPostTags tags WHERE tags.blogId = blogPost.Id AND tags.Tags IN @tags), используя автоматическое расширение @tags в Dapper) — однако вы запрашиваете «содержит все» и «содержит все и ничего больше»; они намного сложнее; «Содержит все» обычно представляет собой несколько операций EXISTS, а «содержит все и ничего больше» может быть тем же самым «содержит все» с дополнительным NOT EXISTS (SELECT 1 FROM BlogPostTags tags WHERE tags.blogId = blogPost.Id AND tags.Tags NOT IN @tags)

Однако! Я бы еще раз подчеркнул: эта операция не идеальна в SQL. Для разметки постов Stack Overflow (которая по сути идентична этой) мы перевернули это с ног на голову, добавив отдельный индекс/сервер, который существовал исключительно для выполнения операций с тегами, включая предварительную загрузку всех данных постов/тегов в ОЗУ в оптимизированном режиме. способы и индексация по тегу (обработка дельта-обновлений); это позволяет выполнять множество операций в памяти, используя различные трюки. Гораздо больше усилий и работы, чем простой запрос SQL, но и гораздо более эффективно.

«Содержит любой» — это простой запрос EXISTS.

var tagIds = new[]{ 1, 2, };

using connection = GetConnection();
const string query = @"
SELECT 
    bp.*
FROM BlogPost bp
WHERE EXISTS (SELECT 1
    FROM BlogPostTags tags
    WHERE tags.blogId = bp.Id
      AND tags.Id IN @tags
);

var results = await connection.QueryAsync<BlogPost>(query, new { tags, });

Для двух других требований это классическое Реляционное деление, одно — с остатком, другое — без остатка.

Для With Remainder просто выполните EXISTS объединение и сгруппированную HAVING проверку, что все они существуют.

var tagIds = new[]{ 1, 2, };

using connection = GetConnection();
const string query = @"
SELECT 
    bp.*
FROM BlogPost bp
WHERE EXISTS (SELECT 1
    FROM BlogPostTags tags
    WHERE tags.blogId = bp.Id
      AND tags.Id IN @tags
    HAVING COUNT(*) = @count   -- are all input matched?
);

var results = await connection.QueryAsync<BlogPost>(query, new { tags, count = tags.Length });

Для «Без остатка» это аналогично, но вам нужно найти все теги, а затем проверить HAVING, что совпадают только те, которые вы ищете.

var tagIds = new[]{ 1, 2, };

using connection = GetConnection();
const string query = @"
SELECT 
    bp.*
FROM BlogPost bp
WHERE EXISTS (SELECT 1
    FROM BlogPostTags tags
    WHERE tags.blogId = bp.Id
    HAVING COUNT(*) = @count   -- are all input matched?
       AND COUNT(*) = COUNT(CASE WHEN tags.Id IN @tags THEN 1 END)   -- are all tags in the list
);

var results = await connection.QueryAsync<BlogPost>(query, new { tags, count = tags.Length });

Обратите внимание, что Dapper автоматически параметризирует списки как (@p1, @p2) и т. д.

Вы также можете использовать параметр с табличным значением, который более эффективен для больших списков.

Определите тип таблицы, лучше всего иметь при себе несколько полезных стандартных.

CREATE TYPE dbo.IntList AS TABLE (value int PRIMARY KEY);

Затем поместите значения в DataTable и используйте .AsTableValuedParameter.

Запрос также немного отличается, поскольку здесь вы бы использовали соединение.

var tagIds = new[]{ 1, 2, };
var table = new DataTable { Columns = { { "value", typeof(int) } } };
foreach (var tag in tagIds)
    table.Add(tag);

using connection = GetConnection();
const string query = @"
SELECT 
    bp.*
FROM BlogPost bp
WHERE EXISTS (SELECT 1
    FROM BlogPostTags tags
    LEFT JOIN @tags input ON input.value = tags.Id
    WHERE tags.blogId = bp.Id
    HAVING COUNT(*) = @count   -- are all input matched?
       AND COUNT(*) = COUNT(input.value)   -- did the join match all tags?
);

var results = await connection.QueryAsync<BlogPost>(query, new { tags = tags.AsTableValuedParameter("dbo.IntList"), count = tags.Length });