Idan Shechter Idan Shechter - 4 months ago 9
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

List<string>
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
List<string>
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
name
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
hardware_id
's that each id, has its all
name
's matching the ones in the
List<string>
.

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

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

table.GroupBy(x=>x.hardware_id)
     .Where(x=> x.All(s=> phrases.Contains(s.name))
     .Select(x=>x.Key);