Relax C# LINQ String Comparison (Trim, Case Insensitive, ??)

Question

Problem

Background Story: I am rewriting all SQL queries of legacy system into LINQ.

The database is not as clean as I expect. As many of these SQL record contains spaces or different cases which treated as the same.

SELECT * 
FROM fruit 
WHERE name = @fruitname;

Provided @fruitname is apple, this query will match any record ends with apple, _apple, APPLE_ (where _ is a whitespace character).

However, This is the expected behavior in my use cases.

On the otherhand, LINQ string comparison is more precise. Which annoys me because such issues keep surfacing to me.

Setup

FruitTableAdapter fruitsAdapter = new FruitTableAdapter();
MyGardenDataSet.FruitDataTable fruitsTable = fruitsAdapter.GetData();

Approaches

// Issue 1: Does not match, '_apple' or 'APPLE_'
var fruits1 = fruitsTable.Where(row=>row.name == fruitname);

// Issue 2: String Comparison with case insensitive (does not match 'APPLE')
var fruits2 = fruitsTable.Where(
    row=>row.nameEquals(fruitname, StringComparison.OrdinalIgnoreCase));

// Issue 3: Trailing space with case insensitive
var fruits2 = fruitsTable.Where(
    row=>row.name.Trim().Equals(fruitname.Trim(), 
                                StringComparison.OrdinalIgnoreCase));

I'm not sure but there could be many issues which SQL query are different from String Comparison.

Is there any SQL aware StringComparison? How can I achieve the same string comparison as SQL in LINQ?

linq case insensitive (without toUpper or toLower) http://stackoverflow.com/questions/5312585/linq-case-insensitive-without-toupper-or-tolower — MichaelMao, Nov 25 '15 at 09:53
yes, I've seen many of these approaches, but none of them combine `CaseInsensitive` & `Trim`. But I have not seen the combination of both. Or perhaps there are more differences other than the 2 — Yeo, Nov 25 '15 at 09:54
If you use LINQ to entities, your comparison [should already work as you want](http://stackoverflow.com/questions/3843060/linq-to-entities-case-sensitive-comparison), since your LINQ expression is tranlsated to SQL, and, thus, uses SQL comparisons. If you use LINQ to object, this would be a good time to start worrying about performance... — Heinzi, Nov 25 '15 at 10:02
@Heinzi thanks for the link. I believe the problem now could be this `fruitsTable` is no longer `SQL` but already becoming an `Object`. Which causes the comparison of `==` does not work accordingly. I'm looking into the issue now. — Yeo, Nov 25 '15 at 10:06
Rewriting SQL queries to LINQ is *not a good idea*. LINQ is *not* a replacement for SQL. It's a query language for ORMs. You need to rearchitect your application so that it can use an ORM first, then worry about the query language. In fact, complex queries should stay in the database (as views, functions, etc). You already encountered a big trap of ORMs - accidentally loading stuff in memory instead of querying in the database. — Panagiotis Kanavos, Nov 25 '15 at 10:18
@PanagiotisKanavos thanks for the suggestion. My concern of having SQL is because it is harder to maintain for application programmer. And the Business Logic Layer (BLL) is abstracted into SQL Queries. But I just aware about the loading into memory, so i will consider putting all these back to SQL query and put it into the DataSet in the DataAccessLayer (DAL). — Yeo, Nov 25 '15 at 10:25

score 1 · Answer 1 · answered Jun 07 '19 at 17:23

Here's a nice String Extension method that builds on the solutions from a similiar question about casing StackOverflow

Keep in mind, we want to allow for NULL strings in our trim scenarios, so this extension will do a Case Insensitive compare on Trimmed strings after checking for null values

public static class StringExtension
{
    // Trim strings and compare values without casing
    public static bool SqlCompare(this string source, string value)
    {
        // Handle nulls before trimming
        if (!string.IsNullOrEmpty(source))
            source = source.Trim();

        if (!string.IsNullOrEmpty(value))
            value = value.Trim();

        // Compare strings (case insensitive)
        return string.Equals(source, value, StringComparison.CurrentCultureIgnoreCase);
    }
}

Here's how to use the Extension in your LINQ statement:

(SysUserDisplayFavorites table is composed of char() fields with space filled results. These will get trimmed and compared (case insensitive) to the user provided values in displayFavorite object)

                    var defaultFavorite = _context.SysUserDisplayFavorites
                    .Where(x => x.UserId.SqlCompare(displayFavorite.UserId))
                    .Where(x => x.ModuleCode.SqlCompare(displayFavorite.ModuleCode))
                    .Where(x => x.ActivityCode.SqlCompare(displayFavorite.ActivityCode))
                    .Where(x => x.ActivityItemCode.SqlCompare(displayFavorite.ActivityItemCode))
                    .Where(x => x.IsDefault);

This should work in memory... not directly to SQL, isn't it? — Romias, Mar 07 '21 at 12:55

score 0 · Answer 2 · answered Jun 27 '18 at 14:17

This is a very late answer.

You can use Regex to solve your problem Here's what I have tried, hope it helps

I created a sample class

 public class SampleTable
 {
     public string Name { get; set; }

     public SampleTable(string name)
     {
        Name = name;
     }
 }

Populated sample data

List<SampleTable> sampleTblList = new List<SampleTable>();
sampleTblList.Add(new SampleTable(" Apple"));
sampleTblList.Add(new SampleTable(" APPLE"));
sampleTblList.Add(new SampleTable("Apple"));
sampleTblList.Add(new SampleTable("apple"));
sampleTblList.Add(new SampleTable("apple "));
sampleTblList.Add(new SampleTable("apmangple"));

Solution:-

string fruitName = "apple";
List<SampleTable> sortedSampleTblList = sampleTblList.Where(x => 
Regex.IsMatch(fruitName, x.Name, RegexOptions.IgnorePatternWhitespace | RegexOptions.IgnoreCase)).ToList();

Output:-

string ans = String.Join(",", sortedSampleTblList.Select(x => x.Name.Replace(" ","_")).ToArray());
Console.Write(ans);

_Apple,_APPLE,Apple,apple,apple_

Yeah, but this shouldn't be done in memory. OP should have been told to use an OR-mapper that translates LINQ statements into SQL. Usually SQL is case-insensitive and it trims string values. — Gert Arnold, Jun 27 '18 at 14:28

score 0 · Answer 3 · answered Jun 27 '18 at 15:25

fruitsTable.Where(row => row.name.Trim().Equals(fruitname, StringComparison.OrdinalIgnoreCase)); should do what you need, but I'm confused because you've listed almost the same under Issue 3. Were you not realising it was working because you are reusing fruits2?

This little NUnit test is passing

[Test]
public void FruitTest()
{
    var fruitsTable = new List<string> { " Apple", " APPLE", "Apple", "apple", "apple ", " apple", "APPLE " };
    var fruitname = "apple ".Trim();

    var fruits = fruitsTable.Where(row => row.Trim().Equals(fruitname, StringComparison.OrdinalIgnoreCase));

    Assert.AreEqual(fruitsTable.Count(), fruits.Count());
}

Relax C# LINQ String Comparison (Trim, Case Insensitive, ??)

Problem

Setup

Approaches

3 Answers3