0

I'm trying to implement filter to my IQueryable which will checks if list of objects contains some element.

var ratings = repository.GetAll<Rating>()
              .AsNoTracking()
              .Select(e => new RatingGridDto()
              {
                  Id = e.Id,
                  Sites = e.Locations.Select(x => x.Name).ToList(),
                  PurchaseOrderNumbers= e.PurchaseOrderNumbers//this is IEnumerable of string
              });

OrderNumber in db looks like this: "[12345]","[5432]","[18859]".

I was trying to implement it in below way but nothing works... any linq expression and below implementation of own Expression throws error that it's not translatable to SQL.

Where is the problem and how I can resolve it?

ratings = ratings.Where(e => e.PurchaseOrderNumbers.AsQueryable().Any(pon => pon.Contains(purchaseOrderFilter)));

ratings = ratings.Where(e => e.PurchaseOrderNumbers.Where(pon => pon.Contains(purchaseOrderFilter)).Any());
ratings = ratings.Where(e => e.PurchaseOrderNumbers.Contains("222"));
ratings = ratings.Where(e => e.PurchaseOrderNumbers.Any(pon => pon == "234234"));

ratings = ratings.Where(e => e.Sites.Any(s => s.Contains("a"))); WORKS
ratings = ratings.Where(e => e.PurchaseOrderNumbers.Any(pon => pon.Contains("a")));

public static IQueryable<T> OwnWhereIsContainedBy<T, K>(IQueryable<T> source, K elementToContain, string propertyName)
{
    if (string.IsNullOrEmpty(propertyName))
    {
        throw new ArgumentException("Property name cannot be null or empty.", nameof(propertyName));
    }

    ParameterExpression parameter = Expression.Parameter(typeof(T));
    MemberExpression property = Expression.Property(parameter, propertyName);
    ConstantExpression element = Expression.Constant(elementToContain);

    MethodInfo containsMethod = typeof(Enumerable).GetMethods()
        .Where(m => m.Name == "Contains" && m.GetParameters().Length == 2)
        .Single()
        .MakeGenericMethod(typeof(K));

    MethodCallExpression containsCall = Expression.Call(containsMethod, property, element);

    Expression<Func<T, bool>> lambda = Expression.Lambda<Func<T, bool>>(containsCall, parameter);

    return source.Where(lambda);
}
GSerg
  • 76,472
  • 17
  • 159
  • 346
ruddnisrus
  • 187
  • 5
  • Does this answer your question? [Check if list contains item from other list in EntityFramework](https://stackoverflow.com/questions/21641016/check-if-list-contains-item-from-other-list-in-entityframework) – GSerg May 14 '23 at 23:06
  • IMHO it's much easier to start with a template expression like `x => x.Contains(element)` and replace `x` with `x.Prop` with an expression visitor (or use ef core's `ReplacingExpressionVisitor`). – Jeremy Lakeman May 15 '23 at 02:55
  • GSerg no ;/ I need to put it into IQueryable ;/ – ruddnisrus May 15 '23 at 07:48
  • Jeremy Lakeman I'm little bit confusing with it, could You please provide some more informations? – ruddnisrus May 15 '23 at 07:49
  • @ruddnisrus So? – GSerg May 15 '23 at 07:59
  • GSerg I already tried to implement it that way as it is in my question example, looks like it works for enumerable, but I;m not able to translate it from IQueryable to SQL when I – ruddnisrus May 15 '23 at 08:03
  • Question is why you need this method? And mark all version which works, I mean not dynamic versions. – Svyatoslav Danyliv May 15 '23 at 08:03
  • Svyatoslav Danyliv There is no version which works... that's the problem, I can't filter this that way... – ruddnisrus May 15 '23 at 08:05
  • So, there is no reason to do it dynamically. You have spot EF Core limitation, when it cannot translate filter from custom projected details. – Svyatoslav Danyliv May 15 '23 at 08:20
  • Svyatoslav Danyliv But for example for Sites it works... both are IEnymerable.. if I will make simple ratings = ratings .Where(e => e.Sites.Any(s => s.Contains(siteMockedFilter))); it works here – ruddnisrus May 15 '23 at 08:33
  • @ruddnisrus Like the duplicate explains, you must provide a `List` to EF, not an `IEnumerable`. Then EF will convert it to SQL's `IN ()`. – GSerg May 15 '23 at 09:14
  • GSerg with list it also not working ... – ruddnisrus May 15 '23 at 09:23
  • Svyatoslav Danyliv In https://stackoverflow.com/questions/76235618/how-to-use-materialized-collection-in-iqueryable-query You said that I need here some expression tree, could You explain why? result is the same ;/ what is benefit of using it? – ruddnisrus May 15 '23 at 10:29
  • @ruddnisrus Then you are not doing it correctly. Please do. You are not going to magically circumvent the EF's SQL translation abilities by constructing the lambda manually. The EF is able to translate a `List<>.Contains` into SQL, so use that as opposed to everything else. – GSerg May 15 '23 at 10:48
  • GSerg It's not rocket science to add ToList(), as I said, sites works correctly, but purchaseOrderNumbers with ToList(), asEnumerable(), asQueryable() doesn't want to be translated into sql.. thats why I'm asking here... – ruddnisrus May 15 '23 at 12:16
  • What database are you using? What is `purchaseOrderFilter`? Can you provide a [mre]? – Guru Stron May 15 '23 at 13:50
  • @ruddnisrus, probably it is some kind of misunderstanding. I told if you need `StartsWith` - it needs dynamic generation to make correct answer. But in your case if it do not work without dynamic generation, with dynamic it will not work either. – Svyatoslav Danyliv May 15 '23 at 14:41
  • Svyatoslav Danyliv So there is no resolution for that case? I dont understand it... PurchaseOrderNumbers are column in Rating table.. it is stored as varchar500... why EF can't just make contains method to the same table? – ruddnisrus May 15 '23 at 20:32

0 Answers0