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
精彩评论