Check if text contains any string item from list of string c# linq

Question

I am trying to fetch a list of users after filtering by their name.

Query:

string filter="alex, faheem, Cohen";
var filterArr=filter.Split(new []{','},StringSplitOptions.RemoveEmptyEntries).Select(f=>f.Trim()).ToList();

var users= (from u in DbContext.Users
                where filterArr.Any(y=> u.Name.Contains(y)) select u);

This gives me the error:

Local sequence cannot be used in LINQ to SQL implementations of query operators except the Contains operator.

I can't use filterArr.Contains(x.Name) because Name column contains both first name and second name. Just Like in list above their is an item "alex" and I have a name "Alex Hales" combined in Name column. So If I use filterArr.Contains(x.Name) it will not give me the result.

Any help will be much appreciated.

1. make ToLower() before .Contains() (filterArr - all values should be lower) filterArr.Contains(x.Name.ToLower()) — Power Mouse
– Power Mouse, Commented Apr 29, 2019 at 17:55

GPW · Accepted Answer · 2019-04-29 13:28:51Z

0

I'm not sure this is possible in a single statement like this. It's too complicated for the poor parsing stuff to work out.

However, you can get an IQueryable(), then iterate over your filters append these as individual WHERE clauses, then these should get added to the SQL properly later.

Something like this:

//this just gets a reference the DbSet, which implements IQueryable<User>
var queryable = _dbContext.Users;

//iterate over the filters and add each as a separate WHERE clause
foreach(var f in filters)
{
    //this just adds to the existing expression tree..
    queryable = queryable.Where(u=>u.Name.Contains(f));
}
//this will actually hit the database.
var results = queryable.ToList();

This should generate something like this in SQL (entirely pseudo-code)

select 
  u.*
from 
  users u
where 
  (u.username like "%Sue%")
  or (u.username like "%Bob%")

Hope this helps...

answered Apr 29, 2019 at 13:28

GPW

2,6362 gold badges13 silver badges22 bronze badges

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

2 Comments

Ahmad Over a year ago

I was just about to post my own solution. Implemented exactly like this. But you had already posted it so will mark your answer as solution

jacroe Over a year ago

Maybe I'm missing something. But I'm pretty sure that C# code generates a WHERE AND statement.

Muhammad Hannan · Accepted Answer · 2019-04-29 14:28:34Z

0

I think you can do something like this

string filter = "alex, faheem, Cohen";
var filterArr = filter.Split(new[] { ',' }, StringSplitOptions.RemoveEmptyEntries).Select(f => f.Trim()).ToList();
var users = _dbContext.Users.Where(x => filterArr.Any(n => n.Contains(x.Name))).ToList();

UPDATE For your requirement following query will work fine.

string filter = "Alex, faheem, Cohen";
var filterArr = filter.Split(new[] {','}, StringSplitOptions.RemoveEmptyEntries).Select(f => f.Trim())
            .ToList();
var users = _dbContext.Users
            .Where(x => filterArr.Any(n => x.UserName.Contains(n))).ToList();

If user has searched for "alex" and in Name (database column) there is "Alex Hales". users query will return the user "Alex Hales".

edited Apr 29, 2019 at 14:28

answered Apr 29, 2019 at 12:14

Muhammad Hannan

2,60921 silver badges31 bronze badges

2 Comments

Ahmad Over a year ago

You misunderstood my requirement. If user has searched for "alex" and in Name (database column) there is "Alex Hales". Then as per your query still there will be no item in users

Muhammad Hannan Over a year ago

@Ahmad updated my answer, try now it will serve the purpose.

Collectives™ on Stack Overflow

Check if text contains any string item from list of string c# linq

2 Answers 2

2 Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related