using split in linq query

Question

in one of my database fields i have data as follows:

value1, value2, value3

my search parameter could be value1 or value2 or value3

what i want to do is use a linq query to get a list of entities where for e.g. value2 is in "value1, value2, value3"

also, values are seperated by space after the commma.

i tried to use the following linq query but got an error that stated Array operation not permitted.

List<Players> c = (from p in db.Players
                      where (p.Users == "Everyone" || p.Users.Split()[','].Trim().Contains(username))
                      orderby p.Category ascending
                      select p).ToList();

any ideas how this can be accomplished?

You should post the comment on the relevant answer so that the answerer gets a notification.. What is the error you are getting now, it can't be same error. — Habib, Nov 18 '13 at 22:46

score 7 · Answer 1 · answered Nov 18 '13 at 19:54

7

You are not doing the Split properly, it should be like:

p.Users.Split(',').Contains(username)

If you want to Trim each split value then:

p.Users.Split(',').Select(r=> r.Trim).Contains(username)

If you are using LINQ to Entities, then you might end up with an exception that string.Split can't be translated in to SQL query. In that case you might have to iterate (ToList) your results first and then compare it against the split array.

answered Nov 18 '13 at 19:54

Habib

219,104
29
407
436

This is no longer working in ASP.NETCore it will throw 'An expression tree may not contain a call or invocation that uses optional arguments' – Rahul Uttarkar Jul 31 '20 at 07:54

brainless coder · Answer 2 · 2014-05-07T10:11:59.307

Fist of all it is not a good practice to use a datamodel where you need string split match. Because it leads to inefficient systems and not to mention slow queries. But yet, if you really need a solution why not try this -.

There are four occasions where you will get a match,

A prefix match - starting with
Inner Match - contains with
Suffix Match - ends with
The only match - only one item and this is it

considering the scenario I am suggesting the solution below -

username is the value looking for say "1"
string prefixMatch = username + ",";
string suffixMatch = ", " + username;
string innerMatch = ", " + username + ",";

List<Players> c = (from p in db.Players
                  where (p.Users == "Everyone" || (p.StartsWith(prefixMatch) ||
                                                   p.Contains(innerMatch) || p.EndsWith(suffixMatch) || 
                                                   (!p.Contains(",") && p == username)))
                  orderby p.Category ascending
                  select p).ToList();

This query will support LINQ-TO-SQL Conversion

A possible duplicate - http://stackoverflow.com/questions/23511565/linq-select-using-split/23514199#23514199 — brainless coder, May 07 '14 at 09:59

using split in linq query

2 Answers2

Linked