CODEWITHSUNDEEP
c#sql
General Grey
General Grey

Reputation: 3688

I wish to Select all rows which contain any element from an array

I am trying to do a search engine, something like stackoverflow here. I need to select all rows that contain any of selected tags.

Table

RowID    Question    Tags
  1        'who'     'C#'
  2       'what'     'SQL'
  3       'where'    'C#,PHP'
  4       'when'     'PHP,SQL'

string[] myTags=new string{"c#","PHP'};

then my sql select statement something like this

Select * from table where Tags like myTags[]

I wish to have results like 

RowID    Question    Tags
  1        'who'     'C#'
  3       'where'    'C#,PHP'
  4       'when'     'PHP,SQL'

Of course I know this is syntactically incorrect that is why I am here.

Upvotes: 1

Views: 384

Answers (2)

Saeed Amiri
Saeed Amiri

Reputation: 22555

Linq version:

myTable.Where(x=>myTags.Contains(x.Tag));

Sql Version:

Select * from table where Tag in {'c#','SQL','PHP'}

If one item can have too many tags, you should change a little your database design (adding relational table), it's not a good way to have a string which contains too many tags.

Update: For your current DB sample you can do this with linq:

myTable.Where(x=>myTags.Any(y=>x.Tags.Contains(y)));

Upvotes: 2

WhoaItsAFactorial
WhoaItsAFactorial

Reputation: 3558

Are you against making multiple SQL calls? If not you could do a for loop to run a query for each item in your array of tags?

Upvotes: 0

Related Questions