LINQ Query for searching with Input Array to a database column where it has multiple item with comma seperate value

In my database, I have a table containing a country list with region ids. One country can belong to multiple region Ids. In that case, Region IDs are put as a comma-separate value as a string. Here is an example:

Now I need to write a LINQ query to filter data based on input. For example, the input can be like 3 or ‘3’ or '6', '7','3'. For input 3 or ‘3’, I should get all 14 rows as shown above. To get the rows with the search string I wrote the below LINQ Query:

int result;
    
var arrRegionId = input.Split(',');
    
var countries = countries.WhereIf(arrRegionId.Length > 0,
    	x => arrRegionId.Select(item => int.TryParse(item.Trim('\''), out result) ? result.ToString() : "0")
    		.Contains(x.RegionId) || arrRegionId.Contains(x.RegionId));

But with this Query, I can only get the 13 records apart from the first record (Afghanistan). Because Afghanistan has multiple Region IDs.

Now I would like to have some suggestions, on how can I improve the LINQ Query so that I can get the records which have multiple Region IDs.

If you want to search across records by one or more regions, then the best thing you can do is introduce a many-to-many relationship using a CountryRegion table with CountryId and RegionId. The reason for this is that these FKs can be indexed where you can build queries properly in situations where you want to find countries for a single or for multiple regions.

If you have control over what gets written and don't want to make schema changes, I would recommend opting for a standard bracketed format for multiple values. For instance using square brackets around values, whether single or multiple, I.e:

 [3]

or

[3][11][7]

This way if you pass a criteria of "3" you can write:

 var region = $"[{input}]";

.Where(x => x.Regions.Contains(region));

However that only works for searching for a singular region. (input) It looks like you are passing a comma delimited list of possible regions, so then the question is building a query based on what multiple input region IDs means. If you pass "3,7" does that mean you want all countries that have either 3 or 7, or do you want all countries that have both 3 and 7?

Regardless of your approach whether using a normalized many-to-many or hacking the multiple regions into a single field, you will need to address the "either" or "all" aspect of the query, and that means a dynamic filter expression to either OR the conditions or AND them together. This gets a bit messy, but there are expression builder helpers out there that might make it a bit easier. Assuming you want either 3 or 7:

var regionIds = input.Split(',').Select(x => $"[{x}]").ToList();
Expression<Func<Country, bool>> expr = null;

foreach(var regionId in regionIds)
{
    if (expr == null)
        expr = (country) => country.RegionIds.Contains(regionId);
    else
        expr = Expression<Func<Country, bool>>
            .OrElse(expr, (country) => country.RegionIds.Contains(regionId));
}

var countries = context.Countries.Where(expr).ToList();

Bracketing the delimited list, and enforcing it to be consistent can make searching expressions simpler, but keep in mind that this approach should only be used for very small sets. Something like countries and regions is likely Ok given the limited number of rows and relationships, but in much larger tables/sets this will have a very negative performance impact as searches like this cannot leverage indexes properly, resulting in more expensive table scans. If you absolutely cannot even change the contents of this RegionId string to bracket the values consistently, then the same approach can be used, but your search expressions will be a lot more complicated. Something like:

expr = (country) => country.RegionIds == regionId || country.RegionIds.Contains("'" + regionId + "'");

... might do it.