Finding items from a list in an array stored in a DB field

Question

I have a legacy database that has data elements stored as a comma delimited list in a single database field. (I didn't design that, I'm just stuck with it.)

I have a list of strings that I would like to match to any of the individual values in the "array" in the DB field and am not sure how to do this in Linq.

My list:

List<string> items= new List<string>();
items.Add("Item1");  
items.Add("Item2");

The DB field "Products" would contain data something like:

"Item1,Item3,Item4"
"Item3,Item5,Item6"
"Item2,Item7,Item6"
"Item1,Item2"
"Item1"

My first pass at the Linq query was:

var results = (from o in Order
.Where(p=> items.Contains(p.Products)

But I know that won't work. because it will only return the records that contain only "Item1" or "Item2". So with the example data above it would return 0 records. I need to have it return two records.

Any suggestions?

score 1 · Accepted Answer · answered Mar 04 '13 at 21:06

1

There is a simple clever trick for searching comma-separated lists. First, add an extra , to the beginning and end of the target value (the product list), and the search value. Then search for that exact string. So for example, you would search ,Item1,Item3,Item4, for ,Item1,. The purpose of this is to prevent false positives, i.e., Item12,Item3 finding a match for Item1, while allowing items at the beginning/end of the list to be properly found.

Then, you can use the LINQ .Any method to check that any item in your list is a match to the product list, like the following:

var results = (from o in Order
    .Where(o => items.Any(i => (","+o.Products+",").Contains(","+i+",")))

answered Mar 04 '13 at 21:06

mellamokb

56,094
12
110
136

Are you sure that you're allowed to do `String.Contains` in Entity Framework? – Corey Adler Mar 04 '13 at 21:08
1

@IronMan84: I don't really know a lot about EF (I use mostly LINQ-to-SQL), but I know `String.Contains` is pretty easy to express in SQL (e.g., `LIKE` clause), so I'd be quite surprised if it wasn't supported. [**This**](http://stackoverflow.com/questions/1033007/like-operator-in-entity-framework#comment2470199_1033024) seems to indicate that it is allowed. – mellamokb Mar 04 '13 at 21:12
Using your suggestion I am running into an issue when the item is not the first "string" in the DB array. i.e. If I selected on item3 in the example above I would get record 2 but not 1 – John S Mar 04 '13 at 22:18
@JohnS: Please double-check you've implemented the logic correctly. There is a comma before and after both the `Products` value and the item. I tested it and it seems to work fine: http://ideone.com/aOFcom. Can you show your current working code? – mellamokb Mar 04 '13 at 22:22
I apologize. The data I had used for an example above didn't have the spaces in it like the live data did. I have added a o.Products.Replace(" ","") to the code and it works correctly. Thanks – John S Mar 04 '13 at 22:42

D Stanley · Answer 2 · 2013-03-04T21:10:17.297

One way would be to parse the list in the Products field:

var results = (from o in Order
    .Where(o => items.Any(i => o.Products.Split(',').Contains(i))

But that would parse the string multiple times for each record. You could try pulling back ALL of the records, parsing each record once, then doing the comparison:

var results = from o in Order
              let prods = o.Products.Split(',')
              where items.Any(i => prods.Contains(i))
              select o;

Finding items from a list in an array stored in a DB field

2 Answers2