27

I have C# application (.NET Core 3.1) and I have written the following LINQ expression.

public ActionResult<bool> GetItems(string title)
{
     var items = _service.All.GetItems().OrderByDescending(o => o.Id).Where(w => w.Portal_Id == 1);

     if (!string.IsNullOrWhiteSpace(title))
     {
            var terms = title.Split(' ').ToList();
            items = items.Where(w => terms.Any(a => w.ItemName.Contains(a)));
     }
     // Some Other code
     return Ok();
}

whenever this expression is executed i get the following error

The LINQ expression 'DbSet<PosItem>\r\n    .Where(p => !(p.IsDeleted))\r\n    
.OrderByDescending(p => p.CreatedAt)\r\n    .Where(p => p.Portal_Id == 1)\r\n    .Where(p => __terms_1\r\n      
.Any(a => p.ItemName.Contains(a)))' 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 either AsEnumerable(), AsAsyncEnumerable(), ToList(), or ToListAsync().
See https://go.microsoft.com/fwlink/?linkid=2101038 for more information."

I cannot add ToList() and switch to client evaluation because the data set is too big to do so.

Please advise how can I resolve this issue without switching to client evaluation.

Thanks

Sheheryar Sajid
  • 395
  • 2
  • 5
  • 15
  • 1
    Certain function calls can be translated to SQL according to this [table](https://learn.microsoft.com/en-us/ef/core/providers/sql-server/functions#string-functions). In your particular case and without any coding whatsoever, I think you need the problem is with `items.Where(w => terms.Any(a => w.ItemName.Contains(a)))`. You are testing a client-side array. – Daniel Dearlove Aug 11 '21 at 08:03
  • 2
    It is likely failing to translate the `terms.Any()`. You could create an `Expression` using an `ExpressionBuilder` and a for/foreach and then use the newly created `Expression` in the `items.Where(your_new_expression)` – Cleptus Aug 11 '21 at 08:05
  • 1
    This answer has an example on how you could do it [How to create an Expression builder in .NET](https://stackoverflow.com/a/54698694/2265446) – Cleptus Aug 11 '21 at 08:16
  • Most likely it does not work, because of Contains method – Piotr Szuflicki Aug 11 '21 at 08:22
  • You mentioned that the data is large so you want to avoid doing any client-side processing. As has been pointed out in multiple places here, using `Contains()` will result in one or more `LIKE '%item%'` terms in the final SQL query. In turn, this could easily execute as a [table scan](https://en.wikipedia.org/wiki/Full_table_scan) on the server. That might or might not be a price worth paying. – Daniel Dearlove Aug 11 '21 at 09:27

2 Answers2

28

The issue is that you are trying to do a string.Contains within an Any expression which EF will choke on trying to compose down to SQL. Cleptus is on the nose, to build a predicate for the Where clause OR-ing the term comparisons. Otherwise your code should work without the contains check, but rather an equality check:

Without Contains: (equality check rather than LIKE %name%)

var terms = title.Split(' ').ToList();
items = items.Where(w => terms.Contains(w.ItemName)); // IN clause.

Built expression:

var terms = title.Split(' ').ToList();
Expression<Func<Item, bool>> predicate = (Item) => false;
foreach(var term in terms)
    predicate = predicate.Or(x => x.ItemName.Contains(term));

items = items.Where(predicate);

So for each term in the title, we OR a match using a LIKE %term% on the ItemName.

Steve Py
  • 26,149
  • 3
  • 25
  • 43
  • Thank you for your time and suggestion. I made some changes to your solution a bit and used var predicate = PredicateBuilder.New(false); and i worked – Sheheryar Sajid Aug 11 '21 at 11:07
1

This problem can also occur when using multiple ParameterExpression in one single Expression. Always transmit den ParameterExpression instance to all Expression.Property(argPara, "Name") values.

Lena
  • 21
  • 1