Using OData and ECTs in SharePoint 2013

  • Article

One of the nice enhancements in SharePoint 2013 BCS world is that SharePoint can now consume OData in BDC applications. There are a couple of gaps I ran across recently though when going through this process so I thought I'd cover them here in case anyone else gets similarly stuck. To start with, I recommend starting with this document to walk you through the basics of creating an application for OData: https://msdn.microsoft.com/en-us/library/sharepoint/jj163967.aspx. The main takeaway here is that you can NOT create a BDC application in SharePoint Designer that connects to an OData source - to do that you need to create an External Content Type (ECT) using a tool like Visual Studio.

The document I linked to above walks you through the process of creating the ECT. It follows that by showing how to use those ECTs in a SharePoint App and deploying it in that manner, but it does NOT show what you do if you want to add it to the BDC catalog so that it can be used many site collections, and that's where this post comes in. The first thing to understand is that when you go through the process described in the article above, it will create one ECT for each entity (like a table). The reason why that's important to know is because they will use a shared name in the ECT file, which will prevent you from uploading more than one to the BDC catalog. In order to use each of these entities in SharePoint here's what you need to do:

  1. Right-click on the ECT file in Visual Studio and select Open With... then select XML (Text) Editor. At the top of the document in the Model element you will see a Name attribute. This value has to be unique between all the ECTs that you upload to the BDC, so you should change each one to a descriptive value for that entity, like "Customers Table".
  2. You can, but don't have to, change the Namespace of the Entity element, which is about 20 lines down in the document. I changed mine to be consistent with the model name, but that's just a style choice, it's not required.
  3. Once you've made the changes and saved the file, you can upload the .ect file directly to the BDC. Just use the default options - it's a model - then click the OK button and you're good to go.
  4. Once you've imported the models, don't forget to grant permissions to folks to use them; kind of pointless without that.

One final thing worth noting here - out of the box you don't get OData metadata endpoints over things like SQL databases, Azure Table Storage, etc. Adding it for SQL is fortunately relatively easy. In a nutshell you:

  1. Create a new Empty ASP.NET web application
  2. Add an ADO.NET Entity Data Model
  3. Add a WCF Data Service
  4. In your WCF Data Service you need to set the Type in the class constructor; this may be a litle confusing at first. What you want to do is look for a file (that should be in the App_Code folder) that is named something like myDataConnection.Context.tt. IMPORTANT: There are usually TWO ".tt" files; find the "blah.Context.tt" file or you will end up using the wrong thing! If you expand that, underneath you should see a myDataConnection.Context.cs class. If you open that up you will see the two pieces of information you need for your WCF Data Service: 1) the class name, which you will use as the Type for the WCF Data Service class constructor. 2) The names of the entities it is supporting, implemented like get; set; properties. You will need the entity names in the WCF Data Service as well, because at a minimum you need to create "SetEntitySetAccessRules" for each entity you want to expose. This is explained in more detail in the comments when you add a WCF Data Service - I'm just trying to tell you where you go find the entity name to use when you create one of those rules.