How to find List in a string field in an IQueryable in EF with C#?

Question

I'm using .Net 6 and EF Core 6. Suppose that I have an entity which is included a string property and I have a dynamic list of string variables which I want to use LINQ to find out which records of my table specific column included at least one of those words.

My entity is:

Public class Sample
{
    public int Id { get; set; }
    public string Caption { get; set; }
}

The list of string words is:

List<string> words;

I'm using this code to reach the result:

var query = _sampleRepository.AsQueryable().AsNoTracking(); // the type is IQueryable<Sample>

query = query.Where(x => words.Any(word => x.Caption.Contains(word)));

query = query.OrderByDescending(x => x.Id).Skip(50).Take(20);

query = query.ToList();

But while executing the above code, I would get an Exception which is saying that the part of code of :

query.Where(x => words.Any(word => x.Caption.Contains(word))) could not be translated by EF to such a query to get data from Database!

I actually want want and I should use LINQ to do this and it's not possible to use for example connection.QuerySingleOrDefaultAsync method or etc.

Please help me do that!

Does this answer your question? [Lambda/Linq with Contains criteria for multiple keywords](https://stackoverflow.com/questions/67666649/lambda-linq-with-contains-criteria-for-multiple-keywords) — Svyatoslav Danyliv, Feb 26 '23 at 07:27

Sina Bahmanpour · Accepted Answer · 2023-02-26T14:22:56.990

Finally I found the solution!

I should using the Nuget Package of LinqKit to help me do that!

This is the solution code:

var pb = PredicateBuilder.New<Sample>();
foreach (var word in words)
{
    pb = pb.Or(x => x.Caption.Contains(word));
}
query = query.Where(pb);

I needed LinqKit to use PredicateBuilder and the above code should be replaced with the query = query.Where(x => words.Any(word => x.Caption.Contains(word))); and it worked for me!

How to find List in a string field in an IQueryable in EF with C#?

1 Answers1