SQL search by multiple lists of values for multiple columns

Question

I may have a slightly naive question, but I have never worked with databases before. I am a .NET engineer and I use Dapper to access the SQL Server database.

The situation is the following: I have a denormalized table for persisting several types of entities. Each has a composite key (type, id, owner_id) and each row of the key is of string type (but it's not important). And, let's say, I'm writing to the database many interests for different users (Bulk post). In order for them to not repeat, I need to make a query and determine which are already present in the database.

So, I have this code in my InterestService class:

private IEnumerable<Interest> GetAlreadyExistingInterestsFor(IEnumerable<Interest> interestsForCreating) =>
    _interestRepository.GetInterests(interestsForCreating.Select(interest => interest.Id).ToList(),
                                     interestsForCreating.Select(interest => interest.UserId).ToList());

After that I have some logic and so on. It's not important.

InterestRepository method GetInterests looks like this:

public GetInterests(IList<string> interestIds, IList<string> userIds) 
{
    var query = @"SELECT type, id, owner_id
                  FROM entities
                  WHERE type = 'interest'
                   AND id IN @InterestIds
                   AND owner_id IN @UserIds";
    return _dbContext.ExecuteQuery(query, new { InterestIds = interestIds, UserIds = userIds });
}

The code may have mistakes because right now I don't have an ability to access a working environment but I think the idea is clear. So, the question is whether this is the best approach to making a query. And if there is a better, then what is it.

No that won't work like that. You should look into using table valued parameters. Then you can use an inner join to your two table parameters. — Sean Lange, Oct 09 '18 at 19:02
If you're looking for suggestions, I would look into Entity Framework. Simplifies the repository part of coding. Since you already have a database, you would go with the db-first approach. https://learn.microsoft.com/en-us/ef/ef6/modeling/designer/workflows/database-first — Bojo, Oct 09 '18 at 19:03
@DavidGreilach no. I was considering stored procedures and triggers but doesn't want to use them. — stnsfld, Oct 09 '18 at 19:04
@SeanLange I didn't get it. Can you describe your point wider? — stnsfld, Oct 09 '18 at 19:07
[Here](https://learn.microsoft.com/en-us/sql/relational-databases/tables/use-table-valued-parameters-database-engine?view=sql-server-2017) is a great article on table valued parameters. — Sean Lange, Oct 09 '18 at 19:10
Actually, According to [this SO post](https://stackoverflow.com/questions/8388093/select-from-x-where-id-in-with-dapper-orm), Dapper should have built in support for IEnumerable parameters. — Zohar Peled, Oct 09 '18 at 19:16
@SeanLange it looks very good. I will discuss it with my team. Thank you! — stnsfld, Oct 09 '18 at 19:23
@ZoharPeled yes, Dapper does. The question is whether my approach the best by performance or not. — stnsfld, Oct 09 '18 at 19:27
Performance is effected by many factors. In the words of Eric Lippert, [race your horses.](https://ericlippert.com/2012/12/17/performance-rant/). That post I've linked to does mention a size limit to the lists, so table valued parameters are the safer way. — Zohar Peled, Oct 09 '18 at 19:31
@ZoharPeled oh, I got it. And it's also a nice link because it has a pretty good example. Thank you! — stnsfld, Oct 09 '18 at 19:36
Yes, Eric Lippert's blog is one of the best professional blogs to read. — Zohar Peled, Oct 09 '18 at 19:38

score 1 · Answer 1 · answered Oct 09 '18 at 19:13

1

Essentially you can simply do exactly what was done in this post, but with two sets instead of 1 for the table valued parameter. Using Dapper, how do I pass in the values for a sql type as param?

It uses a stored procedure and a sql table valued parameter.

answered Oct 09 '18 at 19:13

Judy007

5,484
4
46
68

After that, I'll have the ability to write a query like that? `var query = @"SELECT * FROM entities e INNER JOIN @tmp t ON e.id = t.id" AND e.owner_id = t.owner_id;` Sorry if I'm making mistakes, I started working with databases on this level literally yesterday :) – stnsfld Oct 09 '18 at 19:44

Alex Kudryashev · Answer 2 · 2018-10-10T03:37:44.417

If stored procedure is not an option then you can use one of following methods.

Convert your interestIds and userIds into strings
string interests = "(1, 2, 3, 4)" if lists contains numbers only or ('a1', 'b1', 'c2') if they are strings.

Then just inline them into your query

var query = @"SELECT type, id, owner_id
              FROM entities
              WHERE type = 'interest'
               AND id IN "+ interests
               + " AND owner_id IN " + users;

This method is considered as bad practice and is an invitation to SQL injection attack (in the case of user input). Use it iff you are absolutely sure in your data.

SQL Server 2016+ has a built-in function string_split which can be used here. The function splits a string with separators into a table t(value).
Again convert lists into strings string interests="a1, a2, b3, c4"; (No single quot here)

and query

var query = @"SELECT type, id, owner_id
              FROM entities
              WHERE type = 'interest'
               AND id IN (select value from string_split(@interests,','))
               AND owner_id IN (select value from string_split(@users,','))";

For earlier versions you can create UDF with the same functionality.

create function dbo.split_string(@input varchar(max), @separator varchar(2))
returns table as
return
(with cte as(
select cast('<v>'+ REPLACE(@input, @separator, '</v><v>') +'</v>' as xml) x
)
select t.v.value('.[1]', 'varchar(max)') [value]
from cte
cross apply x.nodes('v') t(v))

SQL search by multiple lists of values for multiple columns

2 Answers2