New Posts New Posts RSS Feed: Error when trying to query case insensitive
  FAQ FAQ  Forum Search   Calendar   Register Register  Login Login

Error when trying to query case insensitive
 Post Reply Post Reply
Author
  Share Topic Share Topic  Topic Search Topic Search  Topic Options Topic Options
Niels Verkaart View Drop Down
Newbie
Newbie
Avatar

Joined: 05-Aug-2009
Location: Netherlands
Posts: 9 		Post Options Post Options   Quote Niels Verkaart Quote  Post ReplyReply Direct Link To This Post Topic: Error when trying to query case insensitive
    Posted: 19-Nov-2009 at 3:30am
Hi,

I want to query an object but ignoring the case in the database (the database is case insensitive, it is the Ideablade Prism example database). But I get an error when using a StringComparer in the Linq query:

There was an error while trying to serialize parameter http://ideablade.com/EntityModel:entityQuerySurrogate. The InnerException message was 'Type 'System.StingComparison'with data contract name 'StringComparison:http://schemas.datacontract...'

The query I changed was the second one:

Queries.Add("Get Customers starting with A",
                  Manager.Customers
                    .Where(c => c.CompanyName.StartsWith("A",StringComparison.CurrentCultureIgnoreCase)));

What am I doing wrong here?

thanks.

Edited by Niels Verkaart - 19-Nov-2009 at 3:31am
Niels Verkaart
Back to Top
GregD View Drop Down
IdeaBlade
IdeaBlade
Avatar

Joined: 09-May-2007
Posts: 374 		Post Options Post Options   Quote GregD Quote  Post ReplyReply Direct Link To This Post Posted: 19-Nov-2009 at 4:57pm
When I do that in a simple console app, the message I get is

    LINQ to Entities does not recognize the method 'Boolean StartsWith(System.String, System.StringComparison)' method,
    and this method cannot be translated into a store expression.

Would this work for you?

            var query = _mgr.Customers
                .Where(c => c.CompanyName.ToLower().StartsWith("a"));
Back to Top
Niels Verkaart View Drop Down
Newbie
Newbie
Avatar

Joined: 05-Aug-2009
Location: Netherlands
Posts: 9 		Post Options Post Options   Quote Niels Verkaart Quote  Post ReplyReply Direct Link To This Post Posted: 20-Nov-2009 at 1:07am
Thanks for your reply Greg,
 
Funtionaly this works, but, as far as I know, is not optimized on the SQL backend. With a larger table this would result in very slow responses because the SQL indexes wouldn't be used.
 
My screens have a search view portion, when a user enters a character in a search field, the query would be send right away (although others would maybe use a search button first). This is my preferred way and I use it in my programs for 15 years. This can only work when we use some indexing, and if we avoid constructions that would not make use of it, like ToLower() on the attribute/fieldname.
 
But isn't there a simpler option to make all queries search case insensitive by default? I mean, somewhere in EF there must be some logic that says to SQL to use case sensitive instead, because the SQL database itself is case insensitive (so any direct query on it, is case insensitive). Maybe there is an option to turn that off?
 
When saying that I decided to look for this 'supposed' setting.... I couldn't find one. I stumbled across a small blog:
 
It states that LINQ to Entities could use the StringComparison, but is this then somehow related to something not implemented in DevForce?
 
best regards,
 
Niels Verkaart
Back to Top
GregD View Drop Down
IdeaBlade
IdeaBlade
Avatar

Joined: 09-May-2007
Posts: 374 		Post Options Post Options   Quote GregD Quote  Post ReplyReply Direct Link To This Post Posted: 20-Nov-2009 at 1:06pm
The non-support of

    StartsWith(System.String, System.StringComparison)

is EF's, not ours. A query like

    Manager.Customers.Where(c => c.CompanyName.StartsWith("A"))

against the database will of course be case-insensitive.  You can force the query to go against the database by adding a call to the With() extension method:

   Manager.Customers.Where(c => c.CompanyName.StartsWith("A")).With(QueryStrategy.DataSourceOnly)

But we don't know of a better solution for you (with the current version of EF) for writing a query that would be case-insensitive against both the database and the cache without using something like ToLower().

Additional comments from another developer in our shop:

EF doesn’t support StringComparison on the StartsWith operator, but interestingly does (as stated in the referenced blog post) with the Equals operator.  The error reported in the first post was due to a serialization problem since he was probably working in SL.  But no matter, EF doesn’t support this, so we can’t either.   StartsWith type of queries are translated by EF (or the provider) into bizarre charindex() sql queries, which likely won’t use the desired index anyway (although I don’t know if the poster was more interested in case insensitivity in general or specifically with StartsWith).   So yeah, I don’t think there’s a way to optimize this now (although I think there are improvements in EF 4 …).




Edited by GregD - 20-Nov-2009 at 1:19pm
Back to Top
Niels Verkaart View Drop Down
Newbie
Newbie
Avatar

Joined: 05-Aug-2009
Location: Netherlands
Posts: 9 		Post Options Post Options   Quote Niels Verkaart Quote  Post ReplyReply Direct Link To This Post Posted: 20-Nov-2009 at 1:23pm
Thanks Greg,
I think I will dig some deeper, but I think waiting for EF 4 will be better and pick up from there. In the meantime there is so much more for me to explore in DevForce, SL, Prism, etc

Cheers,
Niels Verkaart
Back to Top
 Post Reply Post Reply

Forum Jump Forum Permissions View Drop Down