Creating a SharePoint 2016 External Content Type trough OData in VS 2015 with EF6
By Vlad Catrinescu Microsoft MVP and MCT · Pluralsight Author
A few weeks ago, I started playing with Hybrid SharePoint Server 2016 scenarios, to see if there are any differences with SharePoint 2013. As I am mostly an IT Pro, usually I never did the OData Source myself, but had a developer set that up for me. This time, I was in my lab, and I was both the DEV and the SharePoint Admin that had to do the IT part. (Talk about DevOps). That and the fact that I had quite a lot of problems with getting it to work with Entity Framework 6, I decided to do a blog post for all the SharePoint Admins and Developers out there that want to create a SharePoint 2016 External Content Type with an OData Source.
This blog will really be a step by step that everyone could follow, so if you are a more experienced dev, you can probably skip most of the screenshots, but I am sure that if you’re an IT Pro and first time doing this, you will find it valuable.
Intro
Our Goal for this blog post is to get the following table, in SharePoint Server 2016. The Hybrid configuration will be done in another blog post, for this one, we simply want to make it work in SharePoint 2016 On-Premises.
You will need to have access to a SharePoint Development machine with Visual Studio, as well as a IIS server where you can deploy your WebService at after (This can be done on the SharePoint Server).
Note, and this one is especially for people (like me) who will simply do this for testing. You need to have a Primary Key in your table for this to work. Without a Primary Key, you will get strange errors. For production, I hope this will not be a problem, but if you create a quick Database for testing, make sure your table has a PK defined.
Creating the OData Source
Create a new Project of type ASP.NET Web Application and name it as you wish. (For this scenario, we disabled the checkbox for Application Insights , since we don’t want to use /configure it).
In the next page, select Empty, since we want an Empty Web Application where we will add our own stuff.
Now, we will need to start adding items in this Project, so right click on the project name, Add, and Add New Item.
Select ADO.Net Entity Data Model under the Data tab, and give it a proper name such as “ContosoModel”
In the Entity Data Model Wizard, choose EF Designer from Database.
On the “Choose your Data Connection” page, click on “New Connection”
Enter the Connection Information for your business needs. If you use Windows Authentication, it will use the account that the Application Pool you run your Web Services Site runs it to access the Database. (Once deployed). There are multiple other ways to configure it depending on your business needs, but for this demo, we will use Windows Authentication.
After you configure it, click on Test Connection to make sure that everything is configured correctly.
Back to the “Choose Your Data Connection” Page, you will have your connection selected, and you can optionally change the connection setting in the Web Config.
Choose Entity Framework 6.x so we use the latest version available for our project.
On the next page, choose all the tables that you want included in the OData Service and give a good name to your Model Namespace.
If everything worked correctly, you will see a next page, with the columns of your table(s) in a designer. If you don’t, check out the error log at the bottom of your Visual Studio
After this is done, we need to add another item to our Project!
This one is the WCF Data Service, which should be under the Web Tab. Make sure to give it a proper name.
A page will appear, mostly Pre-populated
And here is where it gets a bit tricky. The first thing we have to do is replace the “public
class
WcfDataService1 : DataService< /* TODO: put your data source class name here */ >”
Where we will have to replace it with DataService
In the Top Bar, under Tools > NuGet Package Manager, open the Package Manager Console
In the Console, run the following command to get the Entity Framework Provider Package “Install-Package Microsoft.OData.EntityFrameworkProvider –Pre”. It should successfully install as seen in the following screenshot.
At the top of your file, add the “using System.Data.Services.Providers;” line to load the proper classes.
A bit lower in the file, change the DataService
Lower again, in this example, I used the “UserVerboseErros = true”, for debugging, but you can delete that line for production. More important, make sure to do a config.SetEntityAccesRules(“TableName”) EntitySetRights.(rights); . In my case, I gave them AllRead.
The Project is now ready, so I created a IIS Site called WebServices, as well as an Application called Contoso.
From Visual Studio, Click on “Publish ProjectName”
From now on, the procedure might vary depending on where you’re publishing your project to. The screenshots I will put here are for deploying on localhost. On the First Page, select “Custom”
Enter a Profile Name for this custom Profile
Enter the Server where you wish to deploy it (in my case localhost) and the Site name in format IISSiteName\ApplicationName
Also enter what the destination name will be, depending on the bindings you use on your IIS Site. Once you click on Validate Connection, you should see a green checkbox that appears near the button.
In the Database, select the available Connection String.
On the next page, you will see the items that will be deployed, and since it’s a new deployment we will see a bunch of them. Simply click on Publish and it should only take a few seconds.
If Everything works correctly, You should be able to navigate to the URL where you deployed your webservice /Servicename.svc and see something similar to this. An XML with your tables inside!
Next step, is to go to webservice/servicename.svc/TableName?TOP10 (Tablename IS Case Sensitive!) For example. https://webservices.learn-sp2016.com:55124/Contoso/Contoso.svc/CUSTOMERS?top10 . The result will depend on the browser. On IE you will see a RSS Feed Screen, with as you see, 6 results, but we can’t see the results.
On Chrome, you will see an XML, and if you look closely, you will actually see the customer data in there!
Cool , so our OData Service works, and is able to show data from the SQL Database in the Browser. You can now close that Visual Studio solution, and start a new one! This time, is of type “SharePoint Add-in”. And don’t worry, we will not actually deploy it, so you don’t need to have Add-ins Configured for this to work. You will however need a Dev Site Collection!
Enter the URL of your Dev Debugging Site, and then SharePoint-Hosted.
For the API version, I will select SharePoint 2016, since that’s where I want to deploy this Add-in
We will now need to add a New Item, Content Type for an External Data Source. And strangely, this is not in the “New Item” pop up as before, but directly on the Add!
For the OData Service URl, enter the URL to your Service, and give it a name!
Select the Table(s) on which you want to create External Content Types and make sure to leave the checkbox at the bottom checked.
After this is done, you should have two .ECT files in your Project.
Open the ECT file with the built in XML text editor in Visual Studio
In the top of the document, within the Model element, you will see a Name attribute. This Name attribute is the name you selected when you connected to the OData source, such as NorthwindCustomersModel. The value of this name is the same in all of the ECT files created from the entities, but it has to be unique in order to use it in SharePoint. You will need to change the name based on the ECT you are using , for example CustomersTable
Now, navigate to your project, copy this ECT File to somewhere on your desktop
You can then go into Central Admin, BCS Service Application and upload the ECT File. Also, If you didn’t already do it, make sure you have set the permissions!
Afterwards, make sure to use “Set Metadata Store”, to give it the required permissions
After that is done, go to any Site Collection, and add an “External List”
Select your External Content Type
And everything should work!
You now managed to show data in SharePoint, by using Business Connectivity Services consuming an OData Source! If you want to configure Hybrid BCS , you will need to follow a future blog post that will start from this point. I will link to it once it’s live!
