Linq & String.ToLower() strange behavior

I have a query on the server side that returns a list of distinct cities from a zipcode table.

I'm using WCF RIA Service.

The following query successfully returns 228 cities when provincename == ""

    pu开发者_运维问答blic IQueryable<CityPM> GetCities(string provinceName)
    {
        return this.ObjectContext.ZipCodes.Where(z => z.Province.Contains(provinceName))
                                          .GroupBy(z => z.City)
                                          .Select(g => g.FirstOrDefault())
                                          .Select(zc => new CityPM() { ID = zc.ID, Name = zc.City });
    }

but if I use ToLower() method as below, the query returns 0 cities when provincename == "".

    public IQueryable<CityPM> GetCities(string provinceName)
    {
        return this.ObjectContext.ZipCodes.Where(z => z.Province.ToLower().Contains(provinceName.ToLower()))
                                          .GroupBy(z => z.City)
                                          .Select(g => g.FirstOrDefault())
                                          .Select(zc => new CityPM() { ID = zc.ID, Name = zc.City });
    }

Why isn't the query returning anything?

Try checking the SQL generated, either by using DB management tools, or calling .ToTraceString() at the end of the query expression.

Reference: http://blog.aggregatedintelligence.com/2010/06/viewing-entity-framework-generated-sql.html

We use ToTraceString at work using an extension:

public static IQueryable<T> TraceSql<T>(this IQueryable<T> query)
{
    var sql = ((System.Data.Objects.ObjectQuery)query).ToTraceString();

    // do whatever logging of sql you want here, eg (for web)
    // (view by visiting trace.axd within your site)
    HttpContext.Current.Trace.Write("sql", sql);

    return query;
}

It can then be used as follows:

public IQueryable<CityPM> GetCities(string provinceName)
{
    return this.ObjectContext.ZipCodes.Where(z => z.Province.ToLower().Contains(provinceName.ToLower()))
                                      .GroupBy(z => z.City)
                                      .Select(g => g.FirstOrDefault())
                                      .Select(zc => new CityPM() { ID = zc.ID, Name = zc.City })
                                      .TraceSql();
}

Please forgive me for any typos, this is from memory. Hopefully it will help you understand your problem.

The Explanation

I was having the same problem and I found out why this is occurring. Running SQL Profiler I saw that the WHERE statements generated from LINQ to SQL are very different in each case.

.Where(z => z.Province.Contains(provinceName))

would render in SQL as:

WHERE [Province] LIKE N'%%'

As you have experienced, LIKE '%%' would match any non null results.

.
However,

.Where(z => z.Province.ToLower().Contains(provinceName.ToLower()))

would render in SQL as:

WHERE ( CAST( CHARINDEX(LOWER(N''), LOWER([Province])) AS int)) > 0

This is very different than LIKE '%%'. SQL is essentially looking to see what character string.Empty is in the string Province. The result of the CHARINDEX on an empty string is 0 which is why there are no results being returned.

.

The Workaround

This is a little hackish but it'll work. Only call .ToLower() if the the string is not empty. The following code is an example of something that should work for you.

public IQueryable<CityPM> GetCities(string provinceName)
{
    var lowerProvinceName = String.IsNullOrEmpty(provinceName) ? string.Empty : provinceName.ToLower();

    return this.ObjectContext.ZipCodes.Where(z => z.Province.ToLower().Contains(lowerProvinceName))
                                      .GroupBy(z => z.City)
                                      .Select(g => g.FirstOrDefault())
                                      .Select(zc => new CityPM() { ID = zc.ID, Name = zc.City });
}

By structuring your code like this, LINQ to SQL will render as LIKE '%%' if provinceName is an empty string, otherwise it'll render as CHARINDEX. It also helps if there is a null passed in.

This worked for me, give it a try if you like it

context.MyEntities.Where(p => p.Email.ToUpper().Equals(muser.Email.ToUpper()));

Note: I am querying it against Oracle