Split string with LINQ

Question

I want to order by my results with the matches count in my string line.

So here is code

.ThenByDescending(p => p.Title.ToLower()
                              .Split(' ')
                              .Count(w => words.Any(w.Contains)));

But it bring me error and says that LINQ can't parse Split into SQL.

LINQ to Entities does not recognize the method 'System.String[] Split(Char[])' method, and this method cannot be translated into a store expression.

How can I implement Split via LINQ?

For example, for this array it must order in this way

words = { "a", "ab" }

ab a ggaaag gh //3 matches
ba ab ggt //2 matches
dd //0 matches

Muhammad Adeel Zahid · Accepted Answer · 2011-12-28 07:51:43Z

8

it means that Linq to entities failed to find translation of split method that can be written as sql query. if you want to perform split functions you have to bring the record in memory by calling ToList(), AsEnumerable() etc.

var result = (from t in db.Table
              select t).AsEnumerable().OrderBy(x=>x.Column).ThenByDescending(p=>p.Title.ToLower.Split(' ')....);

answered Dec 28, 2011 at 7:51

Muhammad Adeel Zahid

17.8k16 gold badges95 silver badges160 bronze badges

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

5 Comments

abatishchev Over a year ago

But that will cause loading all the data in memory and performing LINQ to Objects transformation against it, will it not?

Muhammad Adeel Zahid Over a year ago

yep that's true. Optimally you can apply conditions in where clause before calling AsEnumerable. you can't do it without bringing objects to memory

Chuck Norris Over a year ago

I guess it will crash with large tables. I agree with abatishchev.

Muhammad Adeel Zahid Over a year ago

As i said you won't always be interested in all data of the table you can filter it first using where clause

Muhammad Adeel Zahid Over a year ago

alternately, you can create a view and bring it on your emdx if you are more into performance than patterns.

abatishchev · Accepted Answer · 2011-12-28 08:21:18Z

3

You will need to perform the sorting in LINQ to Objects because LINQ to Entities cannot translate the C# code into SQL (or the language of whatever DB you're using).

You can do it like this.

var results = 
  objectContext
  .Where(a => a == b) //Whatever
  .AsEnumerable()
  .ThenByDescending(p=>p.Title.ToLower().Split(' ').Count(w=>words.Any(w.Contains)));

AsEnumerable() (along with ToArray() or ToList()) turn the LINQ to Entities back into LINQ to Objects.

edited Dec 28, 2011 at 8:21

abatishchev

101k88 gold badges304 silver badges443 bronze badges

answered Dec 28, 2011 at 7:53

DaveShaw

52.9k18 gold badges118 silver badges142 bronze badges

Comments

Community · Accepted Answer · 2017-05-23 12:17:29Z

2

One can't expect LINQ to Entities to be able to convert that to SQL.

The best solution is to change the schema such that each word in a post's title is stored as a separate row in a separate table (with the appropriate associations). The query can then use explicit (group) join operations or the FK association property.

If you can't do this and yet want the query to run on the database, you'll have to look into writing a user-defined function to work with delimited strings. Read this question for more info. This will be a lot of work though.

The easiest solution (if you can afford to do it) of course is to pull everything back to the client just use LINQ to Objects for that part of the query, as mentioned by @Muhammad Adeel Zahid.

edited May 23, 2017 at 12:17

CommunityBot

11 silver badge

answered Dec 28, 2011 at 8:02

Ani

114k27 gold badges269 silver badges313 bronze badges

Collectives™ on Stack Overflow

Split string with LINQ

3 Answers 3

5 Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

5 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related