I have been working exclusively with WCF Data Services, EntityFramework and OData for sometime now and it would appear I have hit a limitation that I hope the team at MS can fix in a future release. Heck, they may have already fixed it or it’s not a problem, just something I am missing.

Either way here is the issue I am having, how I work around it and hopefully someone will read this and point me in the direction of a better workaround or the ‘right’ way to do what I want to do.

On to the OData, WCF Data Service & EntityFramework loveliness.

Best way to illustrate this is with a simple example so here goes.

The Model

I have in my EntityFramework Model an Entity called Countries which is mapped to my Country table in a MS SQL Server 2008 R2 database.

Countries Entity

Nothing out of the ordinary here, just a table of countries for a lookup on my forms.

The WCF Data Service

I have exposed the countries entity via a ServiceOperation method:

Code Snippet
1. config.SetServiceOperationAccessRule("GetCountries", ServiceOperationRights.All);

Again nothing out of the ordinary here although there is a slight deviation if you look at the implementation of this method:

[WebGet] public IQueryable GetCountries() { try { if (!DataCache.Contains(DataCacheItem.Countries)) { DataCache.Insert>(DataCacheItem.Countries, LoadCountries()); } return DataCache.Get>(DataCacheItem.Countries).AsQueryable(); } catch (Exception e) { ExceptionProcessor.Instance.LogException(e); returnnull; } }

I am using a custom caching class to hold the List in memory for 30 minutes. I don’t want to be hitting the database for every request for this data when let’s face it, it’s hardly going to change that often.

Ok so that’s all good and ultimately I am returning the data as IQueryable so I can filter on the client side which is the next bit.

The OData Magic

Ok so by the power of jQuery and OData I am making the following call to my service:

function GetCountries() { if (countries != null && countries.length > 0) { return; } var url = "/data/Hwy125Service.svc/GetCountries?$orderby=DisplayOrder desc";

$.ajax({ type: "GET", url: url, async: false, contentType: "application/json; charset=utf-8", dataType: "json", success: function (msg) { countries = msg.d; }, error: function (xhr) { returnfalse; } }); }

and that all works great and returns me my list of countries sorted just how I want them and retrieves them from the MemCache rather than going off to the database.

However, that’s not ideal as I am (in this case) returning a whole bunch of data I don’t want, namely 4 columns instead of 2 as I am only interested in the CountryId and Name fields.

So you would think I can just add a ‘$select’ option to my query and get only the fields I want right? Well no, unfortunately that will not work when my List is served from the Cache.

As soon as I change the URL to this and add the projection:

Code Snippet
1. var url = "/data/Hwy125Service.svc/GetCountries?$select=CountryId,Name&$orderby=DisplayOrder desc";

I end up with the Exception ‘Operation could destabilize the runtime’ which sounds pretty hairy:

Exception in Firebug

Eeeeewww, that’s not pretty.

Now just to prove that it is the Caching that is the problem I can write the URL like so to achieve the desired result:

Code Snippet
1. var url = "/data/Hwy125Service.svc/Countries?$select=CountryId,Name&$orderby=DisplayOrder desc";

In this case I am talking directly to the EntityFramework and this is hitting the database and it all works just fine thanks:


Now in the grand scheme of things this might not be all that important but in a high traffic website ‘Caching is King’ so I want to squeeze every bit of performance out of this sucker that I can.

In my case, and if this would work from the Cache, it is a saving 40k across the wire, a trip to the database every time I need the list of countries and reduction in size and complexity of my code base. It all adds up.

The Workaround

Ok so all is not lost. To workaround this issue I can just create a View of only the fields I want to return and have the view do the sorting.

However this is not ideal as I may need the other fields elsewhere in my app and I may need to order differently.

If I want to do that then I will need to go back to accessing the Entity directly, hitting the database and losing the advantages of using the MemCache. Booooo Sad smile

Hopefully this is a imitation that can be fixed by the dudes at MS in a future release.