How to use Linq where condition to check if a list of strings contains any string

Question

I have a LINQ query like this:

var data = from user in _context.Users
           select new
           {
               UserId = user.Id,
               Username = user.UserName,
               RoleNames = (from userRole in _context.UserRoles
                            join role in _context.Roles on userRole.RoleId
                            equals role.Id
                            where userRole.UserId == user.Id
                            select role.Name).ToList()
           };

if (!string.IsNullOrEmpty(searchText))
    data = data.Where(x => x.Username.Contains(searchText) || x.RoleNames.Any(r => r.Contains(searchText)));

The result are something like this:

User Id | Username      | RoleNames
1       | Matt          | [User, Admin]
2       | Jennifer      | [User]
3       | John          | []

But the

x.RoleNames.Any(r => r.Contains(searchText))

is not working, it's causing InvalidOperationException: The LINQ expression '...' could not be translated.

I want to pass in a searchText to search for either "Username" and "RoleNames" columns. E.g. if I pass in searchText = 'Jen' it will return User Id 2, and if I pass in searchText = 'user' it will return User Id 1 and 2.

Any help would be appreciated.

Do you have navigation property `User.Roles`, or something like that? — Svyatoslav Danyliv, Oct 26 '21 at 08:47
I'm guessing that `Users` is not a simple `IEnumerable`, and that `_context` is a `DbContext` and you are using an ORM like Entity Framework. The problem is, your ORM doesn't know how to translate your Lambda Expression into a database query so, this not actually a Linq problem. — Jodrell, Oct 26 '21 at 08:49
What LINQ are you using: LINQ to Objects / SQL / EF 6.x / EF Core 2.0 / 2.1 / 3.x / 5.x / 6.x? What database provider? — NetMage, Oct 26 '21 at 20:43
Using [LINQKit](https://github.com/scottksmith95/LINQKit) you can use Predicate Builder to build an expression that can be translated. — NetMage, Oct 26 '21 at 20:43
@NetMage, nothing can help here. Custom projection makes difficult to reuse collections in predicates. — Svyatoslav Danyliv, Oct 26 '21 at 21:20
@SvyatoslavDanyliv, I don't have navigation property `User.Roles` — Pendi Hu, Oct 27 '21 at 03:41
@NetMage, I'm using System.Linq and System.Linq.Dynamic.Core. Tried in mySQL8.0 and SQL Server 2019 both have the same error. Using .NET Core 3.1 framework. — Pendi Hu, Oct 27 '21 at 04:09
Isn't this an example of ["Linq can't use string.contains?"](https://stackoverflow.com/questions/19791350/linq-cant-use-string-contains) Microsoft have a page that describes which string functions map to SQL and which do not. Sorry, I cannot find it right now. I expect `data = data.ToList().Where(x => x.Username.Contains(searchText) || x.RoleNames.Any(r => r.Contains(searchText)));` will work because you are enumerating the objects in the original `data` query before you assign a new query within the if statement which is now using in-memory objects. — Daniel Dearlove, Oct 28 '21 at 11:47
The fact that you have `_context` implies you are using EF Core or EF of some version. — NetMage, Oct 28 '21 at 21:26

score 0 · Answer 1 · answered Oct 26 '21 at 09:47

While theoretically it is possible to translate this condition to the SQL, your EF Core version do not supports that. Consider to make filter before defining custom projection:

var users = _context.Users.AsQueryable();

if (!string.IsNullOrEmpty(searchText))
    users = users.Where(x => x.Username.Contains(searchText) || x.Roles.Any(r => r.Contains(searchText)));

var data = 
    from user in users
    select new
    {
        UserId = user.Id,
        Username = user.UserName,
        RoleNames = (from userRole in _context.UserRoles
                    join role in _context.Roles on userRole.RoleId
                    equals role.Id
                    where userRole.UserId == user.Id
                    select role.Name).ToList()
    };

score -1 · Answer 2 · answered Oct 26 '21 at 08:58

This may not be the answer you want now but you'll probably look back on this and see it as the right answer later.

Your ORM (Probably Entity Framework) can't translate your Linq Expressions into a query. If your project will have a small database and you don't need your queries to perform well then, tweak your expression so that the ORM can generate a functioning, albeit sub-optimal, query.

If data will be a significant part of your project then switch to a light ORM like Dapper and learn the query language of your database. Write optimal, parameterised queries in that query language and yield the long term benefits.

How to use Linq where condition to check if a list of strings contains any string

2 Answers2