0

I build a NetCore 3.1 application who use Postgres as database engine, and I have an entity SomeEntity who contains:

  • Id : int
  • Name: text
  • Tags: text[]

I try to build a filter on the attribute Tags. It must only select rows where the Tags array contains one or more elements of an unknown length array argument.

Let's use an example !

We have this SomeEntity with the data set bellow:

Id Name Tags
1 n1 array ("tag1", "tag2", "tag3")
2 n2 array ("tag2", "tag3")
3 n2 array ("tag3")

I want to get all rows where the attribute Tags contains the value "Tag1" or "Tag2" (The Tags can contain more than one values at the same time).

I've tried the code bellow:

List<string> terms = new List<string> { "Tag1", "Tag2" };

IQueryable<FileEntity> query = Context.SomeEntity;

foreach (string term in terms) {
    query = query.Where(w => w.Tags.Contains(term));
}

List< SomeEntity > result = query.ToList();

But when you chained the "where" statement with linq, it will result to a list of AND operator. This is the SQL request resulting from the above code.

SELECT f."Id", f."name", f."Tags"
FROM " SomeEntity " AS f
WHERE ((@__andEl_0 = ANY (f."Tags") AND @__andEl_1 = ANY (f."Tags"));

So, how can I build this request with OR operator in place of AND ?

Waldo
  • 1,070
  • 1
  • 11
  • 30
  • Does this answer your question? [Dynamic query with OR conditions in Entity Framework](https://stackoverflow.com/questions/20054742/dynamic-query-with-or-conditions-in-entity-framework) – Guru Stron Jan 11 '21 at 15:12
  • Or `PredicateBuilder ` from [LINQKit](https://github.com/scottksmith95/LINQKit#predicatebuilder) – Guru Stron Jan 11 '21 at 15:14

1 Answers1

0

As proposed by Guru Stron, I've give a try to LINQKit, that I didn't know.

The code I searched for hours was waiting for me right here!
IQueryable<Product> SearchProducts (params string[] keywords)
{
  var predicate = PredicateBuilder.New<Product>();

  foreach (string keyword in keywords)
  {
    string temp = keyword;
    predicate = predicate.Or (p => p.Description.Contains (temp));
  }
  return dataContext.Products.Where (predicate);
}

Thanks a lot! You saved my day.

But it's really strange that we can't do it without an external library.

Waldo
  • 1,070
  • 1
  • 11
  • 30
  • There is a lot of missing translation capabilities in EF Core 3.x and greater as they re-wrote to avoid client side processing but have not equaled EF 6.x or LINQ to SQL capabilities, and have little interest in doing so in some cases. – NetMage Jan 11 '21 at 19:57