sql - EF Core complex where clause

Question

EF Core 3.1 throws when running the following query, complaining that it could not generate the right SQL for it.

var searchPatterns = new string[] { "a", "b", "c" };

var matches = from entity in _dbContext.Entity
              where searchPatterns.Any(x => entity.Column1.Contains(x))
              select entity;

In raw sql, this could translate to something like

select * from entity
where exists (select x from @SearchPatterns where entity.column1 like '%:' + x + '%'))

(where @SearchPatterns is a table parameter that holds the records a, b, and c)

How can I rewrite the query to make it possible for EF Core to accept it?

Edit The actual query that I am building is much more complicated than the simplified version I presented above. Thus, I am not considering FromSqlRaw() as an option that I am willing to use.

question from:https://stackoverflow.com/questions/65840571/ef-core-complex-where-clause

Answer 1

You can use raw sql. See: Raw SQL Queries

var blogs = context.Blogs
    .FromSqlRaw("SELECT * FROM dbo.Blogs")
    .ToList();

More at: Executing Raw SQL Queries

Other options are described here: Breaking changes included in EF Core 3.x - LINQ queries are no longer evaluated on the client.