Print Page | Close Window

Rather large issue with cache retrieval

Printed From: IdeaBlade
Category: DevForce
Forum Name: DevForce 2010
Forum Discription: For .NET 4.0
URL: http://www.ideablade.com/forum/forum_posts.asp?TID=1967
Printed Date: 05-May-2025 at 9:50pm


Topic: Rather large issue with cache retrieval
Posted By: jsobell
Subject: Rather large issue with cache retrieval
Date Posted: 15-Jul-2010 at 1:12am
We've just been chasing the worlds simplest query against a table of users with four fields; userid, firstname, lastname, mobile. The view is marked as having it's key on userid (which is unique).
We have a query that selects all the users where firstname = 'David'.
There are two records in the database meeting this criteria, yet DF only returns one, no matter whether there is an order or not (so it's not just the first record, it's the only record), yet both records are indeed in the context's cache.
In addition, the query seems to be case sensitive, so 'david' fails but 'David' succeeds.
Tracing the server shows that the two records are returned, and specifying no filter returns all 27 users.
However, setting the context's default to QueryStrategyDatasourceOnly resolves all the problems and the system behaves as expected, two records returned, no case sensitivity.
This is running in an MVC2 app, not Silverlight.

A last second check of our database (just before posting this) showed:
[David ]
[David]

So it appears we have a spare space, but the crucial thing is that SQL allows for this in a WHERE, but the cache doesn't.
For now we've disabled the cache, but having the cache give back different data to the database is a significant issue. Any ideas? Is there an option to make the cache string comparisons SQL compatible?


Replies:
Posted By: ting
Date Posted: 16-Jul-2010 at 6:20pm

We don't currently have a setting that allows you to configure string comparisons against the DevForce cache, although this is something we are looking into.  You can use ToUpper() and Trim() in the query which will enable you to specify case insensitivity and to ignore whitespace.  For the whitespace, you can also use property interceptors to trim on the get and/or set.

Edit: We will be addressing this issue, but here is a better way to perform a case insensitive string comparison:
entityManager.Employees.Where(emp => emp.FirstName.Equals("Kathy", StringComparison.CurrentCultureIgnoreCase);

Posted By: jsobell
Date Posted: 17-Jul-2010 at 6:54pm
Unless I'm missing the point here, this seems to be a quite serious bug/error/feature, certainly with regard to SQL queries.
The idea that turning on caching would return inconsistent results is a worry, primarily because every developer expects a cache to be transparent. In this situation there is no way to test for this issue in advance unless your testers know they have to include trailing spaces, experiment with case, and presumably experiment with extended characters?
Switching between online and offline modes also becomes extremely risky, and I know that we will have to turn off all the client-side caching until we can find a workaround; and insisting that every call to the database is wrapped in trim and touppercase certainly doesn't sound an acceptable option, particularly given that presumably these will pass through to SQL on the first call as UPPER(TRIM(fieldname)), probably removing index functionality.

Re property interceptors, what if (as in our case and probably many others) the entity is not written to the database by our application?

I would argue that a cache should be 100% transparent to the underlying dataset, and that this is a critical issue to be addressed.

One thing I do wonder (but have not tested) is what happens with other datasources in EF? Is the case-sensitivity and whitespace ignoring common to all EF datasources, SQL only, or MS-SQL only? If so, can you ask EF whether a datasource is ignoring case/trailing whitespace?

Posted By: ting
Date Posted: 19-Jul-2010 at 9:34pm

Point made.  Thanks for keeping us on our toes!

This has been fixed for the upcoming release.  I didn't mean to downplay the significance of the issue.
 


Print Page | Close Window