LINQ questions using contain

Question

LINQ questions using contain

JSH 0

Hello, the source code is shown below.

private async Task<IEnumerable<SearchResource>> GetSearchItemByIdAsync(IEnumerable<string> querys, int offset, int maxResults)
{
	var allQueries = querys.SelectMany(q => q.Split(',')).ToList();

	var songs = await (from sing in context.Sings 

					   join inst in context.Instrumental on sing.InstrumentalId equals inst.Id

					   where allQueries.Contains(inst.NormalizedTitle!)

					   select new { Sing = sing, Instrumental = inst })

					   .OrderBy(s => s.Instrumental.Id).Skip(offset).Take(maxResults).ToListAsync()
	...
}

What I want is to get the words that contain the query, so I used contain, but it only returns if it's an exact match to the words entered in querys.

where allQueries.Any(q => inst.NormalizedTitle.Contains(q))

I tried replacing the where as above, but received the error below.

System.InvalidOperationException: The LINQ expression 'query => EntityShaperExpression: 
    CodeRabbits.KaoList.Song.Instrumental
    ValueBufferExpression: 
        ProjectionBindingExpression: Inner
    IsNullable: False
.NormalizedTitle.Contains(query)' could not be translated. Either rewrite the query in a form that can be translated, or switch to client evaluation explicitly by inserting a call to 'AsEnumerable', 'AsAsyncEnumerable', 'ToList', or 'ToListAsync'.

What should I do in this case?

thanks - jsh

Wenbin Geng 741 Microsoft External Staff

This error is usually because EF Core cannot directly translate the inst.NormalizedTitle.Contains(q) method to SQL.

You can try the following code:

private async Task<IEnumerable<SearchResource>> GetSearchItemByIdAsync(IEnumerable<string> queries, int offset, int maxResults)
{
     var allQueries = queries.SelectMany(q => q.Split(',')).ToList();
     var songsQuery = from sing in context.Sings
                      join inst in context.Instrumental on sing.InstrumentalId equals inst.Id
                      select new { Sing = sing, Instrumental = inst };
     var songs = await songsQuery. ToListAsync();
     var filteredSongs = songs
         .Where(s => allQueries.Any(q => s.Instrumental.NormalizedTitle.Contains(q)))
         .OrderBy(s => s.Instrumental.Id)
         .Skip(offset)
         .Take(maxResults)
         .ToList();
}

If this code doesn't work try adding more information like your entity class information and other key ef core configuration

JSH 0 Reputation points

2023-08-23T04:47:09.6233333+00:00

Thank you for your response. However, it still only returns if the word is an exact match.
Wenbin Geng 741 Reputation points Microsoft External Staff

2023-08-23T08:06:14+00:00

Hello, is it convenient to add your entity class information? Or did Bruce's answer help you?
JSH 0 Reputation points

2023-08-23T10:17:38.2266667+00:00

Hello, It would be more helpful for me if you could add the entity class information.

1 answer

Your answer

Wenbin Geng 741 Reputation points Microsoft External Staff

2023-08-22T09:55:24.7933333+00:00

This error is usually because EF Core cannot directly translate the inst.NormalizedTitle.Contains(q) method to SQL.

You can try the following code:

private async Task<IEnumerable<SearchResource>> GetSearchItemByIdAsync(IEnumerable<string> queries, int offset, int maxResults) { var allQueries = queries.SelectMany(q => q.Split(',')).ToList(); var songsQuery = from sing in context.Sings join inst in context.Instrumental on sing.InstrumentalId equals inst.Id select new { Sing = sing, Instrumental = inst }; var songs = await songsQuery. ToListAsync(); var filteredSongs = songs .Where(s => allQueries.Any(q => s.Instrumental.NormalizedTitle.Contains(q))) .OrderBy(s => s.Instrumental.Id) .Skip(offset) .Take(maxResults) .ToList(); }

If this code doesn't work try adding more information like your entity class information and other key ef core configuration
JSH 0 Reputation points

2023-08-23T04:47:09.6233333+00:00

Thank you for your response. However, it still only returns if the word is an exact match.
Wenbin Geng 741 Reputation points Microsoft External Staff

2023-08-23T08:06:14+00:00

Hello, is it convenient to add your entity class information? Or did Bruce's answer help you?
JSH 0 Reputation points

2023-08-23T10:17:38.2266667+00:00

Hello, It would be more helpful for me if you could add the entity class information.

Answer 1

Bruce (SqlWork.com) 74,936

The lambda expression in EF queries are expression trees, not code. when the query is executed, the expression tree is converted to sql. only limited amount of c# syntax and function calls can be converted to sql.

the .Contains() confusingly has two translations

<string1>.Contains(<string2>) => string1 like string2

ex:

col1.Contains("x%") => col1 line 'x%'

<enumeriable>.Contains(<string>) => string in (<enumeriable values as comma separated list>)

ex:

var list = new int[] {1,2,3,4}
...
list.Contains(col1) => col1 in (1,2,3,4)

the following can not be translated to sql

allQueries.Any(q => inst.NormalizedTitle.Contains(q))

because allQueries is a c# List<>, and can not be translated to sql except as an "in clause"

when you ToList() an EF query, the sql is generated and executes. the request is c# objects, so any linq to object queries will work.

JSH 0 Reputation points

2023-08-23T10:13:11.7933333+00:00

Hello. Thank you for your response. When you advise to switch to ToList in EF, do you mean not to process asynchronously?
Bruce (SqlWork.com) 74,936 Reputation points

2023-08-23T17:37:18.6666667+00:00

no, you can use ToListAsync() which will also execute the query and return objects

Share via

LINQ questions using contain

1 answer

Your answer