New Posts New Posts RSS Feed: 2010->2012 query generation issue
  FAQ FAQ  Forum Search   Calendar   Register Register  Login Login

2010->2012 query generation issue

 Post Reply Post Reply
Author
katit View Drop Down
Senior Member
Senior Member


Joined: 09-Sep-2011
Posts: 146
Post Options Post Options   Quote katit Quote  Post ReplyReply Direct Link To This Post Topic: 2010->2012 query generation issue
    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 '%%'" ?
Back to Top
sbelini View Drop Down
IdeaBlade
IdeaBlade
Avatar

Joined: 13-Aug-2010
Location: Oakland
Posts: 786
Post Options Post Options   Quote sbelini Quote  Post ReplyReply Direct Link To This Post 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.
Back to Top
kimj View Drop Down
IdeaBlade
IdeaBlade
Avatar

Joined: 09-May-2007
Posts: 1391
Post Options Post Options   Quote kimj Quote  Post ReplyReply Direct Link To This Post 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);
Back to Top
 Post Reply Post Reply

Forum Jump Forum Permissions View Drop Down