using oData to connect Sitecore to Excell, Lightswitch, Sharepoint...

Sitecore 7 is buzzing!
While unsure about the release data, MVP's around the world are taking the deep dive.
So am I, diving in the new Sitecore version, that Sitecore call a "developer release".

What's good about oData!
oData is the "open data protocol" that microsoft came up with. It's like Rest, but with some additions.
Several products can connect with oData data sources, some examples are  Excell, Lightswitch, Sharepoint...

Why now?
Sitecore 7 brings new API's that make this possible with far less effort than before!
The new search API is responsible: The searchmanager exposes an IQueryable.
Why do we need this? oData converts query arguments to Linq expressions, thus enabling us to automagically, using oData, query Sitecore indexes! how neat.

Show me, Show me!
Was this intro to long for you :-) Time for some code!
I'll explain more, after the code.

1. [ServiceBehavior(IncludeExceptionDetailInFaults = true, InstanceContextMode = InstanceContextMode.PerSession)]
2.    public class WcfDataService : DataService<ReturnItemContext>
3.    {
4.       public static void InitializeService(DataServiceConfiguration pConfig)
5.       {
6.          pConfig.DataServiceBehavior.AcceptAnyAllRequests = true;
7.          pConfig.DataServiceBehavior.MaxProtocolVersion = DataServiceProtocolVersion.V3;
8.          pConfig.UseVerboseErrors = true;
9.          pConfig.SetEntitySetAccessRule("*", EntitySetRights.AllRead);
10.          pConfig.SetServiceOperationAccessRule("*", ServiceOperationRights.AllRead);
11.       }
13.       protected override ReturnItemContext CreateDataSource()
14.       {
15.          return new ReturnItemContext();     
16.       }
17.    }
19.    public class ReturnItemContext
20.    {
21.       public IQueryable<ContentItem> ContentItems
22.       {
23.          [WebGet]
24.          get
25.          {
26.             using (IProviderSearchContext lContext = SearchManager.GetIndex("sitecore_web_index").CreateSearchContext())
27.             {
28.                WebOperationContext lOp = WebOperationContext.Current;
29.                NameValueCollection lParameters =
30.                   (lOp == null) ? null :
31.                      (lOp.IncomingRequest.UriTemplateMatch == null) ? null : lOp.IncomingRequest.UriTemplateMatch.QueryParameters;
32.                var lParser = new ParameterParser<ContentItem>();
33.                IModelFilter<ContentItem> lParsed = lParser.Parse(lParameters);        
34.                IQueryable<ContentItem> lSearchQuery = lContext.GetQueryable<ContentItem>();
35.                IQueryable<object> lFiltered = lParsed.Filter(lSearchQuery);
36.                return lFiltered.Cast<ContentItem>().ToList().AsQueryable();
37.             }
38.          }
39.       }
40.    }

What i did, is create a WCF Data Service. These have the possibility to expose oData.

The InitializeService sets things up for this. You can read more on the WCF data service here.

As you can see, the main thing is the ReturnItemContext and it's property called ContentItems.
The ContentItems property provides a IQueryable<ContentItem>.

When an oData consumer accesses this property, the search engine is called (line #34).
The oData query that the client generates, is applied to the Linq query (line #35) by using an open source project called Linq2Rest. 

When finally the Linq query is executed (line #36), the magic happens and the query is run.

Example oData
The following call to the WCFDataService using the following URL: http://.../WcfDataService.svc/ContentItems?$filter=ParentId eq '11111111111111111111111111111111'
Trigger the query where the oData part [$filter=ParentId eq '11111111111111111111111111111111'] is parsed by Linq2Rest and added to the linq statement. oData clients know to create these URL arguments and will do this.

Using Excell as oData Client
Excell becomes an oData client, when a free business intelligence product from Microsoft called PowerPivot is installed. It adds a new Ribbon that enables you to use a oData datasource.

Powerpivot

In the screenshot above, you can see how simple it is to connect to an oData datasource.
1. button to start Table import wizard
2. location for oData service URL.

Using Lightswitch as oData Client
Lightswitch is a tool from microsofts that (so they say) "...is a simplified, self-service development tool that enables you to build business applications quickly and easily for the desktop and cloud.".
Lightswitch  provides an wizard to connect to an oData service and generates CRUD screens for this.
To test Lightswitch as a client for the service, I followed some simple steps.

1. start a lightswitch (windows) project
2. add a datasource

After this wizard, the tool inspects metadata, and generates a datamodel, in our case with one table.

3. Next, I added a screen

In this screen I configured a filter, "where parentid=111111111111111111":

Pressed F5 and voila, a windows client for sitecore (prototyped):

Conclusion
Of course, you can use oData for self written clients. This makes perfect sense!
And know, with all the new stuff in sitecore 7, you can do this!
The code above is written to get it working, understanding how it works -> Don't use it!

Sharepoint should be able to connect, but I did not test it. For a complete list of oData clients, check http://www.odata.org/ecosystem/.

Resources
http://www.odata.org/
http://linq2rest.codeplex.com/
http://sdn.sitecore.net/
http://msdn.microsoft.com/en-us/data/odata.aspx
http://www.microsoft.com/en-us/bi/powerpivot.aspx
http://msdn.microsoft.com/en-us/vstudio/ff796201.aspx
http://blogs.msdn.com/b/officeapps/archive/2012/10/24/using-an-odata-service-in-apps-for-sharepoint.aspx