Idan Shechter Idan Shechter - 1 year ago 42
SQL Question

Full match against a List of strings per id

I need a Linq query that will return null if not all the rows have matching string from within a

for each hardware_id column.

I have the following table:

  • id (int) - Primary Key

  • name (string)

  • user_id (int)

  • hardware_id (int)

I have a
that contain phrases. I want the query to return the hardare_id number if all the phrases in the List have matching strings in the name row. If there one of the phrases doesn't have a
match, to return null and if all the phrases exist per each hardware_id for all the phrases, the query should return the list of hardware_id's that each one of those hardware_id's, have full match with all the phrases within the List.

Or in other words, return a list of
's that each id, has its all
's matching the ones in the

I thought about iterating each Id in a different query but it's not an effective way to do it. Maybe you know a good query to tackle this.
I'm using Entity Framework 6 / C# / MySQL

Note: the query is done only per user id. So I filter the table first by the User Id and then need to find the matching hardare_id's that satisfy the condition.

Answer Source

Group on hardware_id and then look for all phrases existence in the List

     .Where(x=> x.All(s=> phrases.Contains(