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.

score 1 · Accepted Answer · answered Jan 22 '21 at 07:03

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.

answered Jan 22 '21 at 07:03

Maciej Los

8,468
1
20
35

Thanks for your answer attempt. In order to present my question more clearly, I stripped away all irrelevant parts from my query. So, in reality, the query is much more complicated. And I am not sure I want to send all of it to EF in raw sql. – romar Jan 22 '21 at 07:10
@romar, take a look [here](https://stackoverflow.com/questions/60092959/efcore-3-1-exists-query-via-any-query-cannot-be-translated). – Maciej Los Jan 22 '21 at 07:13
Thanks again. Do you confirm that the only two options that I have is to either use client-side evaluation or raw sql? – romar Jan 22 '21 at 07:19
AFAIK, yes + `FromSqlInterpolated`, which is mentioned in MSDN documentation. – Maciej Los Jan 22 '21 at 07:22
1

Thanks. I will take your word for it, then. – romar Jan 22 '21 at 07:26
Good luck! Cheers! – Maciej Los Jan 22 '21 at 07:27

EF Core complex where clause

1 Answers1