DevForce 2009: Error when trying to query case insensitive

Error when trying to query case insensitive

Printed From: IdeaBlade
Category: DevForce
Forum Name: DevForce 2009
Forum Discription: For .NET 3.5
URL: http://www.ideablade.com/forum/forum_posts.asp?TID=1564
Printed Date: 21-Sep-2025 at 3:13pm

Topic: Error when trying to query case insensitive

Posted By: Niels Verkaart
Subject: Error when trying to query case insensitive
Date 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.

-------------
Niels Verkaart

Replies:

Posted By: GregD
Date 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"));

Posted By: Niels Verkaart
Date 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 - 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,

-------------
Niels Verkaart

Posted By: GregD
Date 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 �).

Posted By: Niels Verkaart
Date 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