Sync’ing OData to Local Storage in Windows Phone (Part 1)


My First T4 Template

This is the first blog post in a new series that focuses on my work to develop prescriptive guidance for synchronizing clients with cloud services—more specifically, how to best create and maintain a local cache of OData entities on a Windows Phone client. This post deals specifically with my first attempt at creating a local cache by generating client objects that support both the OData client for Windows Phone and local database on the device.


Both to support my writing work for OData and Windows Phone and to improve the performance of my PASS Event Browser app for Windows Phone 7.5 (“Mango”), I’ve been deep into figuring out a “best practice” for synchronizing data from an OData feed into local storage on the device. There are several reasons why storing a cache of OData object data on the device is a good idea:

  1. Reduced amount of network traffic. This is the most important benefit to caching data locally. Otherwise, every time the app starts it has to hit the OData feed to load initial data, which is a huge waste on OData feeds that are relatively static.
  2. Improved load time. It’s much faster to load data from the device than to make a call over the Web to the OData service.
  3. App don’t need network access to start. When the app is dependent on remote data, it can’t start without a connection.
  4. Puts the user in control. Users can decide on if and when to sync the local data to the OData service, since most of them pay for their data.

Windows Phone 7.5 includes a new local storage feature called local database. Local database is a LINQ-to-SQL implementation to access a local SQL Server CE instance running on the device. (It seems like the death of beloved LINQ-to-SQL—introduced in .NET Framework 3.5 as a key cornerstone of language-integrated query (LINQ)—has been greatly exaggerated.) This is clearly where we should be persisting our OData entities.

The Challenge

LINQ-to-SQL (L2S) was developed in parallel by the LINQ folks at the same time as LINQ-to-Entities (L2E), which shipped with the Entity Framework in .NET Framework 3.5 SP1. L2S was very good at what it was designed to do, namely to provide a lightweight, 1:1 object-relational mapper (ORM) for SQL Server to support LINQ queries. While Entity Framework is, by design, a much more powerful mapping engine based around concepts of the Entity Data Model (EDM), along with an ORM; L2E has only recently caught-up with some of the most popular ORM functionality of L2S.

In looking at the problem space, it seems obvious why L2S was chosen to support relational data storage on a device: a) the LINQ assembly was already being added in Mango, in part to support the OData client; b) it’s lightweight; and c) it only needs to support a single kind of database, SQL Server CE. The addition of a local database and L2S access is great news for Windows Phone developers, and it provides a great place to store entities downloaded from an OData service.

OData, like Entity Framework, is based on the tenets of EDM, and it also uses a similar approach to generating a client proxy as L2E in EF v1. Unfortunately, the client proxy generated from an OData service’s metadata by using DataSvcUtil.exe or Add Service Reference in Visual Studio is incompatible with local database (L2S), which requires that a different set of attributes be applied to stored types and members.

The Solution: Generate a Hybrid Proxy

Fortunately, an entity type, in general, looks the same in both local database and OData. This means that to be able to store entity data from an OData service, all we need to do is attribute the generated classes and properties of an OData client with the Table, Column, and Association mapping attributes required by local database. Then we can use this “hybrid” proxy to store our OData feeds in a local database as shown in the topic How to: Create a Basic Local Database Application for Windows Phone.

The trouble with this approach is that once you manually add attributes to a generated proxy to support local database, you are stuck with it that code page. Updates will overwrite the customizations. (As such, you might just as well use DataSvcutil.exe instead of Add Service Reference, which updates too easily). The good news is that Visual Studio does provide to us a way to generate code pages from templates.

What the Heck is T4?

Not another Terminator movie, T4 is short for Text Template Transformation Toolkit. It is “a mixture of text blocks and control logic that can generate a text file.” If you have used, say, a server-side Web scripting language to generate ASP pages, it works a bit like that. Using T4, you can define a template that parses the $metadata endpoint of an OData service to generate a hybrid proxy. Unlike Entity Framework, which now has several T4 templates, there are (as of yet) no official T4 templates for the WCF Data Services (OData) clients that I could find. Fortunately, I came across a great blog post by Alexey Zakharov on Silverlight Show that describes how to write a basic T4 template to generate a functional set of client proxy classes by parsing the metadata returned by an OData service. He even posted his T4 source code—nice!

Here’s a (partial) example of what the template code looks like for generating a C# code page:


When executed by Visual Studio, this template calls the linked template, which actually access the OData service to load the metadata into an instance of the Data class. This class is then used to generate the types and members needed to represent the OData entities on the client. The generated proxy code then looks like this:


The original metadata loader was very elegantly coded, but I had to make some (less elegant) modifications so that the metadata parser now doesn’t choke on complex types (and Edm.Decimal was also missing for some reason). Plus, I had to capture some additional metadata needed to generate the L2S proxy. Because the original template generated a pure OData proxy, I needed to also update the template to add the local database mapping attributes and generate a strongly-typed DataContext class.

Some Limitations

OData itself is based on the EDM, which supports complex types. However, because LINQ to SQL only supports 1:1 mapping between types, there is no concept in local database of a complex type. At this point, I don’t think that there is a workaround for storing entities with complex properties in local database. EDM also has a concept of a direct many-to-many (*:*) association between entities. This relationship can only exist in the database by using a mapping table, so I don’t think these kinds of associations can be created using L2S, but I’m not a L2S expert yet either.

When I get all of this figured out and my templates better tested, I plan to publishing them somewhere for general use, at least on MSDN Code Gallery, but also (hopefully) as a package on Nuget. There is also stuff like OData binary streams (media resources) that we need to handle outside of the generated proxy and that we don’t want to store in the local database.

In my next post, I solve the complex type problem and start to tackle media resources, so stay tuned…

Glenn Gailey
Comments (6)

  1. Sounds very interesting, Glenn.

    As a suggestion, try to generate DataContext from a local database with SqlMetal. It will produce a L2S context that you can use in Mango (with several adjustments).

    This code will help you improve your T4 template.

  2. Thanks for the tip. I did come across SqlMetal, which I used to connect to my Northwind database to see what client code it generated. It does create the "full set" of goodies, so I am paring it back to just the bare minimum for storage on the device.

    Thanks again for your comment.

  3. I should also comment that I have found a solution to mapping a complex type into local database through creative use of L2S attributing, and I am working on a post about this now.

    Stay tuned…

  4. Nice work!

    Can't wait to read your next post.

  5. Kakali Gupta says:

    Is it possible to use this solution with windows mobile 6.5 having SQL CE. I need to sync SQL CE with SQL Azure.

  6. I am working on a blog post about using the Microsoft Sync Framework Toolkit (, which uses an OData-based sync service that is based on the Microsoft Sync Framework 2.1.

    Although I've been focusing on WP7, there is a Windows Mobile 6.5 sample in this toolkit that demonstrates a SqlCeOfflineSyncProvider.


Skip to main content