Cannot execute query against cache

Hi everybody,

Here's the query I'm trying to execute :

query = _manager.Customers.Where(p =>                     p.CompanyName.Contains(_searchKeyword)                         ||                     p.ContactName.Contains(_searchKeyword)                         ||                     p.ContactTitle.Contains(_searchKeyword)                         ||                     p.City.Contains(_searchKeyword)                         ||                     p.Country.Contains(_searchKeyword)                         ||                     p.Region.Contains(_searchKeyword)                         ||                     p.Phone.Contains(_searchKeyword)                         ||                     p.Fax.Contains(_searchKeyword)                     );

Here's the exception :

Execution of query against the server was successful but execution of query against the local cache failed

Why?

Note: In a C# WinForm Application, on NorthwindIB database, DevForce Universal Express.

Originally posted by JeeZ query = _manager.Customers.Where(p =>                     p.CompanyName.Contains(_searchKeyword)                         ||                     p.ContactName.Contains(_searchKeyword)                         ||                     p.ContactTitle.Contains(_searchKeyword)                         ||                     p.City.Contains(_searchKeyword)                         ||                     p.Country.Contains(_searchKeyword)                         ||                     p.Region.Contains(_searchKeyword)                         ||                     p.Phone.Contains(_searchKeyword)                         ||                     p.Fax.Contains(_searchKeyword)                     );

Nice that you were using the NorthwindIB database: that made debugging this a bunch easier, since I have not only the same schema but virtually the same data to test with.

Your query fails because the columns Region and Fax have nulls in them, and .Contains() isn't defined on a null in LINQ. (The query against the server succeeds because EF translates it to SQL that works.)

If your copy of NorthwindIB is like mine, you'll also find two records that have nulls in some of the other columns besides Region and Fax; but those two records have nulls in all of the columns on which you're doing the test, so they don't meet the condition you've posed and are never brought down to the client. Therefore they don't cause the query to fail when it's run on the client!

THE FIX

You can make your query work by changing the syntax of your tests to the following:

(p.Region ?? "").Contains(_searchKeyword)

That converts any null to an empty string before executing the Contains test.

As it so happens, we had already changed DevForce (I now know) to do the above translation automatically; so as of the next release, the syntax you used to begin with will work. But you'll need the above syntax for now.