CODEWITHSUNDEEP
c#linq
user1144596
user1144596

Reputation: 2088

using split in linq query

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?

Upvotes: 3

Views: 24775

Answers (2)

brainless coder
brainless coder

Reputation: 6430

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,

  1. A prefix match - starting with
  2. Inner Match - contains with
  3. Suffix Match - ends with
  4. 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

Upvotes: 1

Habib
Habib

Reputation: 223217

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.

Upvotes: 7

Related Questions