DevForce Community Forum : Error when trying to query case insensitive http://www.ideablade.com/forum/ This is an XML content feed of; DevForce Community Forum : DevForce 2009 : Error when trying to query case insensitive Wed, 29 Apr 2026 00:42:13 -700 Fri, 20 Nov 2009 13:23:32 -700 http://blogs.law.harvard.edu/tech/rss Web Wiz Forums 9.69 360 www.ideablade.com/forum/RSS_post_feed.asp?TID=1564 DevForce Community Forum http://www.ideablade.com/forum/forum_images/IdeaBlade_logo_tm.png http://www.ideablade.com/forum/ Error when trying to query case insensitive : Thanks Greg, I think I will dig... http://www.ideablade.com/forum/forum_posts.asp?TID=1564&PID=5841#5841 Author: Niels Verkaart
Subject: 1564
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,]]> Fri, 20 Nov 2009 13:23:32 -700 http://www.ideablade.com/forum/forum_posts.asp?TID=1564&PID=5841#5841 Error when trying to query case insensitive : The non-support of StartsWith(System.String,... http://www.ideablade.com/forum/forum_posts.asp?TID=1564&PID=5840#5840 Author: GregD
Subject: 1564
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]]> Fri, 20 Nov 2009 13:06:16 -700 http://www.ideablade.com/forum/forum_posts.asp?TID=1564&PID=5840#5840 Error when trying to query case insensitive : Thanks for your reply Greg, Funtionaly... http://www.ideablade.com/forum/forum_posts.asp?TID=1564&PID=5839#5839 Author: Niels Verkaart
Subject: 1564
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:
http://justgeeks.blogspot.com/2009/05/making-linq-to-entities-do-case.html
 
It states that LINQ to Entities could use the StringComparison, but is this then somehow related to something not implemented in DevForce?
 
best regards,
 
]]> Fri, 20 Nov 2009 01:07:00 -700 http://www.ideablade.com/forum/forum_posts.asp?TID=1564&PID=5839#5839 Error when trying to query case insensitive : When I do that in a simple console... http://www.ideablade.com/forum/forum_posts.asp?TID=1564&PID=5836#5836 Author: GregD
Subject: 1564
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"));

]]> Thu, 19 Nov 2009 16:57:37 -700 http://www.ideablade.com/forum/forum_posts.asp?TID=1564&PID=5836#5836 Error when trying to query case insensitive : Hi, I want to query an object... http://www.ideablade.com/forum/forum_posts.asp?TID=1564&PID=5833#5833 Author: Niels Verkaart
Subject: 1564
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]]> Thu, 19 Nov 2009 03:30:31 -700 http://www.ideablade.com/forum/forum_posts.asp?TID=1564&PID=5833#5833