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
– Habib, Commented Nov 18, 2013 at 22:46

Habib · Accepted Answer · 2013-11-18 19:54:45Z

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, 2013 at 19:54

Habib

224k30 gold badges420 silver badges446 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Rahul Uttarkar Over a year ago

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'

brainless coder · Accepted Answer · 2014-05-07 10:11:59Z

1

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

edited May 7, 2014 at 10:11

answered May 7, 2014 at 9:58

brainless coder

6,4501 gold badge23 silver badges37 bronze badges

1 Comment

brainless coder Over a year ago

A possible duplicate - stackoverflow.com/questions/23511565/linq-select-using-split/…

Collectives™ on Stack Overflow

using split in linq query

2 Answers 2

1 Comment

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related