String matching problem in Entity framework. Works for a string literal but not for a string variable

Question

I am trying to get a row from a table using the entity framework in C#. I have a table called "TipoPlanta" with a primary key called "Tipo" which is of type String.

When I try to get a row from the table usng a String I can only find something if I use a string literal. If I use the string passed to the method I find no rows.

I have the following method which has a bit of added stuff that I have been trying to debug. I pass the String tipoString which in this example has a value of "Arbol persistente". Here's the code:

        private TipoPlanta getTipoPlanta(String tipoString)
    {
        try
        {
            if (tipoString == "Arbol persistente")
                Console.WriteLine("They are the same");
            else
                Console.WriteLine("They are different");

            var result = (from tar in plantaContext.TipoPlanta where tar.Tipo.Contains(tipoString) select tar);
            var sql = ((System.Data.Objects.ObjectQuery)result).ToTraceString();
            Console.WriteLine("SQL = " + sql);
            Console.WriteLine("RESULT COUNT = " + result.Count());
            Console.WriteLine();

            var resultLiteral = (from tar in plantaContext.TipoPlanta where tar.Tipo.Contains("Arbol persistente") select tar);
            var sql2 = ((System.Data.Objects.ObjectQuery)resultLiteral).ToTraceString();
            Console.WriteLine("SQL2 = " + sql2);
            Console.WriteLine("RESULT LITERAL COUNT = " + resultLiteral.Count());

            TipoPlanta tipo = result.First<TipoPlanta>();
            //              TipoPlanta tipo = (from tar in plantaContext.TipoPlanta where tar.Tipo.Contains("Arbol persistente") select tar).First();
            //TipoPlanta tipo = (from  in plantaContext.TipoPlanta where t.Tipo.CompareTo(tipoString.Trim()) == 0 select t).First();
            return tipo;
        }
        catch (Exception ex)
        {
            Console.WriteLine(ex);
            log("Tipo", tipoString, "no existe.");
            return null;
        }
    }

The output is: They are the same

SQL = SELECT
[Extent1].[Tipo] AS [Tipo]
FROM [TipoPlanta] AS [Extent1]
WHERE (CHARINDEX(@p__linq__1, [Extent1].[Tipo])) > 0
RESULT COUNT = 0

SQL2 = SELECT 
[Extent1].[Tipo] AS [Tipo]
FROM [TipoPlanta] AS [Extent1]
WHERE (CHARINDEX(N'Arbol persistente', [Extent1].[Tipo])) > 0
RESULT LITERAL COUNT = 1

As can be seen when I use the string literal it finds the row but not when I use the string I passed even though they appear to be the same.

Any ideas?

Answer 1

You seem have an encoding issue. tipoString and your string constant may look the same in debugger and == may return true, but have some characters in different encodings.

When you compare tipoString to a string constant, please use string.Compare(tipoString, "Arbol persistente", StringComparison.CurrentCulture); instead of == operator.

As stated in C# Programming Guide :

When you compare strings, you should use the methods that explicitly specify what kind of comparison you intend to perform. This makes your code much more maintainable and readable. Whenever possible, use the overloads of the methods of the System.String and System.Array classes that take a StringComparison enumeration parameter, so that you can specify which type of comparison to perform. It is best to avoid using the == and != operators when you compare strings. Also, avoid using the String.CompareTo instance methods because none of the overloads takes a StringComparison.

If this does not help, then I'm agree with Daniel - please see what actual SQL statement is executed using SQL Server Profiler (assuming you have your data in SQL Server).