Dynamic Paramter Count for SQL with C#

Question

So I was thinking about creating a dynamic sql question, meaning that i want the amount of parameters to be dynamic.

Having looked at this: http://stackoverflow.com/questions/337704/parameterizing-a-sql-in-clause#337725 i was thinking that using like '%x%' is SLOW and not good.

What i actually would like to do is using the IN keyword and have it somewhat like this:

select UserId from NameTable where Name IN (@List_of_names)

Where the @List_of_names could contain i.e.

• Filip Ekberg
• Filip
• Ekberg Filip
• Ekberg
• Johan Ekberg
• Johan

( my second name is johan, thats why it's in there ,) )

So all these should match up with Johan Filip Ekberg.

I want to be using either LINQ to SQL or LINQ to SQL ( Stored Procedure ) using C#.

Suggestions and thoughts please!

----------------- Clearification of scenario and tables -------------------------

Imagine i have the following: A table with Users, A table with Names and a Table that connects the Names to a certain user.

So by having

[User Table]
User ID   Full Name
1         Johan Filip Ekberg
2         Anders Ekberg

[Names]
Name ID   Name
1         Filip
2         Ekberg
3         Johan
4         Anders

[Connect Names]
Name ID   User ID
1         1
2         1
3         1
2         4
2         2

So if i want to look for: Ekberg

The return should be:

• Johan Filip Ekberg
• Anders Ekberg

If i want to search for Johan Ekberg

• Johan Filip Ekberg

If i want to search for Anders

• Anders Ekberg

The amount of "search names" can be infinite, if i have the name: Johan Anders Carl Filip Ekberg ( This is just 1 person with many names ) and i just pass "Carl Filip" in, i want to find that User.

Does this make everything clearer?

Answer 1

I hear that SQL Server 2008 has a feature called Table Parameters, so you can pass a Table as a parameter to a Function or Stored Procedure.

Until then, you could use the classic:

SELECT UserId FROM NameTable WHERE CHARINDEX( '|' + Name + '|', '|Filip Ekberg|Filip|Ekberg Filip|') > 0

This means that column name can be any of the values you have in the list.

You can also pass an XML parameter into the stored procedure and then use it as a table in your code, via the OPENXML command.

Answer 2

You say you want to use IN ? IN looks for exact matches so if you use IN('Ekberg')

You will only find Ekberg and not Anders Ekberg.

If you want the above to find Anders Ekberg you need Like '%Ekberg'

In fact in your second example where you have name A B C and you pass A C you want it to find A B C.

Really you want a Like on every word you pass. There are other ways to achieve what it sounds like you require. Mind if I ask the database you are running against first of all ? :) Thanks

Answer 3

It looks like SQL 2008 is an option, so passing a table parameter is probably your best bet. Another possible solution for those who can't move to 2008 yet, is to write a table-valued UDF that generates a table from a delimited string. Then you can do something like this:

SELECT DISTINCT
     CN.user_id
FROM
     dbo.Names N
INNER JOIN dbo.Connect_Names CN ON CN.name_id = N.name_id
INNER JOIN dbo.GetTableFromNameList(@names) T ON T.name = N.name

This will give you user IDs where ANY of the passed names match ANY of the user's names. If you want to change it so that it only gives a match when ALL of the passed names match one of the user's names then you could do something like this:

SELECT
     CN.user_id
FROM
     dbo.Names N
INNER JOIN dbo.Connect_Names CN ON CN.name_id = N.name_id
INNER JOIN dbo.GetTableFromNameList(@names) T ON T.name = N.name
GROUP BY CN.user_id
HAVING COUNT(*) = (SELECT COUNT(*) FROM dbo.GetTableFromNameList(@names))

I haven't tested that second query, so you may need to fiddle with it. Also, you might want to create a local table and fill it with the function so that you don't have to run the function multiple times.

I can post my own version of the string to table function if you need it. Also, if you want to use LIKE rather than exact matches, you can do that.

Answer 4

From the similar SQL-only question here: http://stackoverflow.com/questions/163887/sql-query-simulating-an-and-over-several-rows-instead-of-sub-querying#163907

Try this.

List<string> targets = new List<string>() {"Johan", "Ekberg"};
int targetCount = targets.Count;
//
List<Users> result = 
  dc.Names
    .Where(n => targets.Contains(n.Name))
    .SelectMany(n =>
      dc.ConnectNames.Where(cn => cn.NameId == n.NameId)
    )
    .GroupBy(cn => cn.UserId)
    .Where(g => g.Count() == targetCount)
    .SelectMany(g =>
      dc.Users.Where(u => u.UserId == g.Key)
    )
    .ToList();

This is freehand code, so there's probably some syntax errors in there.