Partial match LINQ query-CodePudding

In my query

    public IEnumerable<RepInfo> GetRepsByCountry(string country)
    {
    var reps = RepInfoProvider.GetReps()
        .WhereLike("CountriesCovered", $"%{country}%")
        .ToList();
 return reps;
    }

Countries Covered in the database is a semicolon separated list (Example: Germany; Norway; Spain; Romainia)

When the country passed in is Oman, the list returned contains data related to Romania (because OMAN is in Romania), is there a way to use wherelike but filter out if it's not an exact match?

CodePudding user response：

Do a in-memory search afterwards. Only recommended with a few rows

var reps = RepInfoProvider.GetReps()
        .WhereLike("CountriesCovered", $"%{country}%")
        .ToList()
        .Where(x => Regex.IsMatch(x.CountriesCovered, $"\\b{country}\\b"))
        .ToList()

CodePudding user response：

Better idea: Normalize your database design so you can directly query that country-list without needing to do string processing in SQL, which is horrible.

Anyway,

If all of the substrings in CountriesCovered are semicolon-separated then and if you want to an exact match then you need to put the semicolons in your search string inside the % wildcards.

I assume there is a space after the semicolon, before the country name.

I also assume the CountriesCovered string doesn't have a semicolon at the start and end of the entire string - which means you need to test for those cases with an OR...

In SQL this would be this (using literals, not parameters for clarity):

WHERE
    ( CountriesCovered LIKE '%; Oman;%' )    -- Word is inside string.
    OR
    ( CountriesCovered LIKE 'Oman;%' )       -- Word is at start of string.
    OR
    ( CountriesCovered LIKE '%; Oman' )      -- Word is at end of string.

In Linq-to-Entities, this would be something like this:

List<Rep> reps = await dbContext.Reps
    .Where( r =>
        r.CountriesCovered.Contains( "; Oman;" )
        ||
        r.CountriesCovered.StartsWith( "Oman;" )
        ||
        r.CountriesCovered.EndsWith( "; Oman" )
    )
    .ToListAsync()
    .ConfigureAwait(false);

EF will convert the String.Contains, StartsWith, and EndsWith function expressions to T-SQL LIKE operators with the % wildcards added automatically.