Print Page | Close Window

2010->2012 query generation issue

Printed From: IdeaBlade
Category: DevForce
Forum Name: DevForce 2012
Forum Discription: For .NET 4.5
URL: http://www.ideablade.com/forum/forum_posts.asp?TID=4032
Printed Date: 29-Apr-2025 at 12:27pm


Topic: 2010->2012 query generation issue
Posted By: katit
Subject: 2010->2012 query generation issue
Date Posted: 12-Mar-2013 at 8:38pm
I had code like this:

case FilterChoice.Contains:
                        predicates.Add(PredicateBuilder.Make(typeof(T), filterBoxRow.FieldName, FilterOperator.Contains, filterBoxRow.GetFilterFrom()));
                        break;

Basically, I'm building filter manually. If there is empty string it generates WHERE statement like this:

WHERE ( CAST(CHARINDEX(LOWER(N''), LOWER([Extent1].[UserName])) AS int)) > 0

And it effectively filter's out whole thing.. I think it should do "LIKE '%%'" ?


Replies:
Posted By: sbelini
Date Posted: 14-Mar-2013 at 10:13am
Hi katit,

I was able to verify the behavior.
I'm filing a bug report and will let you know once we have it fixed.

sbelini.

Posted By: kimj
Date Posted: 18-Mar-2013 at 4:00pm
katit, FilterOperator.Contains will be translated into a SQL LIKE clause if the "IgnoreCase" option on the PredicateDescription is turned off.  By default it's on, so a ToLower method is added to both the property and value clauses, which in turn causes EF to  translate the expression using the SQL CharIndex method. 
 
To avoid unexpected results when the filter is empty you can disable the option.  When the filter is not empty you may want the option enabled, depending on your database. 
 
var pd = PredicateBuilder.Make(typeof(T), filterBoxRow.FieldName, FilterOperator.Contains, filterBoxRow.GetFilterFrom()));
pd.IgnoreCase = false;
predicates.Add(pd);


Print Page | Close Window