LINQ to SQL to find match in results that have multiple pipe separated values

Question

I have a table that has multiple columns, one of which contains pipe separated values.

I found an answer that's partially what I'm looking for here but that assumes you're searching in one CSV-type list.

What I have is rows of data, one column (called serviceIDs) has data like 2|45|5|6

I want to be able to pass a value into a query something like this:

Select all rows where serviceIDs contains '5'

or

Select all rows where serviceIDs like '%5%'

But obviously neither of those are going to work properly. Is there a way to do this in LINQ?

No, that's the problem, if I use contains or like I get incorrect data because I need to ONLY get 5, not 15, 45 or any other combo if 5 is the search term. — Full Time Skeleton, Mar 25 '14 at 14:27

score 1 · Accepted Answer · answered Mar 25 '14 at 14:37

Unfortunately Split isn't supported in LINQ to SQL. You could do something like the following where you tack on a leading and trailing pipe and then use contains with the leading and trailing pipe. That would solve the issue where finding 45 when searching for 5. Be aware that this could kill your performance because the calculated column value would block index usage in the database. If you store the original values with the leading and trailing pipe, the indexing would be better (but not as good as using a full text search).

var yourvalue = "5";
var expectedResult = "|" + yourvalue + "|";
var resultset = rows.Where(row => ("|" + row.serviceIDs + "|").Contains(expectedResult);

score 0 · Answer 2 · answered Mar 25 '14 at 14:24

0

var yourvalue = "5";
var resultset = rows.Where(row => row.serviceIDs.Split('|').Contains(yourvalue));

answered Mar 25 '14 at 14:24

nvoigt

75,013
26
93
142

You have a problem here. You are comparing strings and 45 also contains 5. I'm pretty sure, thats not accurate – Marco Mar 25 '14 at 14:25
Yes, that's the crux of the problem. – Full Time Skeleton Mar 25 '14 at 14:26
1

@Serv: Err... no. The split will yield an array of { "2", "45", "5", "6" }, which contains exactly one "5". – nvoigt Mar 25 '14 at 14:27
Ok, I'll try this and will let you know how I get on. Great if it works as it'll really neat. – Full Time Skeleton Mar 25 '14 at 14:28

score 0 · Answer 3 · answered Mar 25 '14 at 14:35

0

disclaimer: i never used linq for DB stuff, but something along these lines might work

 yourTable.AsEnumerable()
.Select(row => row["serviceIDs"].ToString().Split('|'))
.ToList()
.Where(s => s == 5);

answered Mar 25 '14 at 14:35

Juraj Petrik

895
1
10
25

LINQ to SQL to find match in results that have multiple pipe separated values

3 Answers3