Using Visual Studio 2013, I created an Entity Model over an existing database. Each table has a GUID for it's primary key. I created an MVC Web API project with related OData Bindings and Controllers.
Here is how I create the OData Binding;
ODataConventionModelBuilder builder = new ODataConventionModelBuilder();
builder.EntitySet<HRPosition>("HRPositions").EntityType.HasKey(p=>p.HTPositionGuid);
Here is a sample controller for the HRPositions Entity.
public class HRPositionsController : ODataController
{
private EFSEntities db = new EFSEntities();
// GET: odata/HRPositions
[EnableQuery]
public IQueryable<HRPosition> GetHRPositions()
{
return db.HRPositions;
}
// GET: odata/HRPositions(5)
[EnableQuery]
public SingleResult<HRPosition> GetHRPosition([FromODataUri] Guid key)
{
return SingleResult.Create(db.HRPositions.Where(hRPosition => hRPosition.HTPositionGuid == key));
}
// PUT: odata/HRPositions(5)
public async Task<IHttpActionResult> Put([FromODataUri] Guid key, Delta<HRPosition> patch)
{
Validate(patch.GetEntity());
if (!ModelState.IsValid)
{
return BadRequest(ModelState);
}
HRPosition hRPosition = await db.HRPositions.FindAsync(key);
if (hRPosition == null)
{
return NotFound();
}
patch.Put(hRPosition);
try
{
await db.SaveChangesAsync();
}
catch (DbUpdateConcurrencyException)
{
if (!HRPositionExists(key))
{
return NotFound();
}
else
{
throw;
}
}
return Updated(hRPosition);
}
// POST: odata/HRPositions
public async Task<IHttpActionResult> Post(HRPosition hRPosition)
{
if (!ModelState.IsValid)
{
return BadRequest(ModelState);
}
db.HRPositions.Add(hRPosition);
try
{
await db.SaveChangesAsync();
}
catch (DbUpdateException)
{
if (HRPositionExists(hRPosition.HTPositionGuid))
{
return Conflict();
}
else
{
throw;
}
}
return Created(hRPosition);
}
// PATCH: odata/HRPositions(5)
[AcceptVerbs("PATCH", "MERGE")]
public async Task<IHttpActionResult> Patch([FromODataUri] Guid key, Delta<HRPosition> patch)
{
Validate(patch.GetEntity());
if (!ModelState.IsValid)
{
return BadRequest(ModelState);
}
HRPosition hRPosition = await db.HRPositions.FindAsync(key);
if (hRPosition == null)
{
return NotFound();
}
patch.Patch(hRPosition);
try
{
await db.SaveChangesAsync();
}
catch (DbUpdateConcurrencyException)
{
if (!HRPositionExists(key))
{
return NotFound();
}
else
{
throw;
}
}
return Updated(hRPosition);
}
// DELETE: odata/HRPositions(5)
public async Task<IHttpActionResult> Delete([FromODataUri] Guid key)
{
HRPosition hRPosition = await db.HRPositions.FindAsync(key);
if (hRPosition == null)
{
return NotFound();
}
db.HRPositions.Remove(hRPosition);
await db.SaveChangesAsync();
return StatusCode(HttpStatusCode.NoContent);
}
protected override void Dispose(bool disposing)
{
if (disposing)
{
db.Dispose();
}
base.Dispose(disposing);
}
private bool HRPositionExists(Guid key)
{
return db.HRPositions.Count(e => e.HTPositionGuid == key) > 0;
}
}
Once the OData Service is deployed and using Fiddler I am able to query the Service endpoints and retrieve the full list of data as well as single entity data.
I then created a SharePoint App in which I create an External Content Type by referencing the OData service. This creates the ECT Model definitions for each endpoint.
Here is the ECT for HRPositions;
<?xml version="1.0" encoding="utf-16"?>
<Model xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema" Name="EFSData" xmlns="http://schemas.microsoft.com/windows/2007/BusinessDataCatalog">
<LobSystems>
<LobSystem Name="EFSODATA" Type="OData">
<Properties>
<Property Name="ODataServiceMetadataUrl" Type="System.String">https://efsodataapi.azurewebsites.net/OData/$metadata</Property>
<Property Name="ODataServiceMetadataAuthenticationMode" Type="System.String">PassThrough</Property>
<Property Name="ODataServicesVersion" Type="System.String">2.0</Property>
</Properties>
<AccessControlList>
<AccessControlEntry Principal="STS|SecurityTokenService|http://sharepoint.microsoft.com/claims/2009/08/isauthenticated|true|http://www.w3.org/2001/XMLSchema#string">
<Right BdcRight="Edit" />
<Right BdcRight="Execute" />
<Right BdcRight="SelectableInClients" />
<Right BdcRight="SetPermissions" />
</AccessControlEntry>
</AccessControlList>
<LobSystemInstances>
<LobSystemInstance Name="EFSODATA">
<Properties>
<Property Name="ODataServiceUrl" Type="System.String">https://efsodataapi.azurewebsites.net/OData</Property>
<Property Name="ODataServiceAuthenticationMode" Type="System.String">PassThrough</Property>
<Property Name="ODataFormat" Type="System.String">application/atom+xml</Property>
<Property Name="HttpHeaderSetAcceptLanguage" Type="System.Boolean">true</Property>
</Properties>
</LobSystemInstance>
</LobSystemInstances>
<Entities>
<Entity Name="HRPositions" DefaultDisplayName="HRPositions" Namespace="EFSData" Version="1.0.0.0" EstimatedInstanceCount="2000">
<Properties>
<Property Name="ExcludeFromOfflineClientForList" Type="System.String">False</Property>
</Properties>
<AccessControlList>
<AccessControlEntry Principal="STS|SecurityTokenService|http://sharepoint.microsoft.com/claims/2009/08/isauthenticated|true|http://www.w3.org/2001/XMLSchema#string">
<Right BdcRight="Edit" />
<Right BdcRight="Execute" />
<Right BdcRight="SelectableInClients" />
<Right BdcRight="SetPermissions" />
</AccessControlEntry>
</AccessControlList>
<Identifiers>
<Identifier Name="HTPositionGuid" TypeName="System.Guid" />
</Identifiers>
<Methods>
<Method Name="CreateHRPosition" DefaultDisplayName="Create HRPosition" IsStatic="false">
<Properties>
<Property Name="ODataEntityUrl" Type="System.String">/HRPositions</Property>
</Properties>
<AccessControlList>
<AccessControlEntry Principal="STS|SecurityTokenService|http://sharepoint.microsoft.com/claims/2009/08/isauthenticated|true|http://www.w3.org/2001/XMLSchema#string">
<Right BdcRight="Edit" />
<Right BdcRight="Execute" />
<Right BdcRight="SelectableInClients" />
<Right BdcRight="SetPermissions" />
</AccessControlEntry>
</AccessControlList>
<Parameters>
<Parameter Name="@HTPositionGuid" Direction="In">
<TypeDescriptor Name="HTPositionGuid" DefaultDisplayName="HTPositionGuid" TypeName="System.Guid" IdentifierName="HTPositionGuid" CreatorField="true" />
</Parameter>
<Parameter Name="@PosistionCode" Direction="In">
<TypeDescriptor Name="PosistionCode" DefaultDisplayName="PosistionCode" TypeName="System.String" CreatorField="true" />
</Parameter>
<Parameter Name="@PositionName" Direction="In">
<TypeDescriptor Name="PositionName" DefaultDisplayName="PositionName" TypeName="System.String" CreatorField="true" />
</Parameter>
<Parameter Name="@Description" Direction="In">
<TypeDescriptor Name="Description" DefaultDisplayName="Description" TypeName="System.String" CreatorField="true" />
</Parameter>
<Parameter Name="@CreateHRPosition" Direction="Return">
<TypeDescriptor Name="CreateHRPosition" DefaultDisplayName="CreateHRPosition" TypeName="Microsoft.BusinessData.Runtime.DynamicType">
<TypeDescriptors>
<TypeDescriptor Name="HTPositionGuid" DefaultDisplayName="HTPositionGuid" TypeName="System.Guid" IdentifierName="HTPositionGuid" ReadOnly="true" />
<TypeDescriptor Name="PosistionCode" DefaultDisplayName="PosistionCode" TypeName="System.String" />
<TypeDescriptor Name="PositionName" DefaultDisplayName="PositionName" TypeName="System.String" />
<TypeDescriptor Name="Description" DefaultDisplayName="Description" TypeName="System.String" />
</TypeDescriptors>
</TypeDescriptor>
</Parameter>
</Parameters>
<MethodInstances>
<MethodInstance Name="CreateHRPosition" Type="Creator" ReturnParameterName="@CreateHRPosition" ReturnTypeDescriptorPath="CreateHRPosition">
<AccessControlList>
<AccessControlEntry Principal="STS|SecurityTokenService|http://sharepoint.microsoft.com/claims/2009/08/isauthenticated|true|http://www.w3.org/2001/XMLSchema#string">
<Right BdcRight="Edit" />
<Right BdcRight="Execute" />
<Right BdcRight="SelectableInClients" />
<Right BdcRight="SetPermissions" />
</AccessControlEntry>
</AccessControlList>
</MethodInstance>
</MethodInstances>
</Method>
<Method Name="ReadSpecificHRPosition" DefaultDisplayName="Read Specific HRPosition" IsStatic="false">
<Properties>
<Property Name="ODataEntityUrl" Type="System.String">/HRPositions(HTPositionGuid=guid'@HTPositionGuid')</Property>
</Properties>
<AccessControlList>
<AccessControlEntry Principal="STS|SecurityTokenService|http://sharepoint.microsoft.com/claims/2009/08/isauthenticated|true|http://www.w3.org/2001/XMLSchema#string">
<Right BdcRight="Edit" />
<Right BdcRight="Execute" />
<Right BdcRight="SelectableInClients" />
<Right BdcRight="SetPermissions" />
</AccessControlEntry>
</AccessControlList>
<Parameters>
<Parameter Name="@HTPositionGuid" Direction="In">
<TypeDescriptor Name="HTPositionGuid" DefaultDisplayName="HTPositionGuid" TypeName="System.Guid" IdentifierName="HTPositionGuid" />
</Parameter>
<Parameter Name="@HRPosition" Direction="Return">
<TypeDescriptor Name="HRPosition" DefaultDisplayName="HRPosition" TypeName="Microsoft.BusinessData.Runtime.DynamicType">
<TypeDescriptors>
<TypeDescriptor Name="HTPositionGuid" DefaultDisplayName="HTPositionGuid" TypeName="System.Guid" IdentifierName="HTPositionGuid" ReadOnly="true" />
<TypeDescriptor Name="PosistionCode" DefaultDisplayName="PosistionCode" TypeName="System.String" />
<TypeDescriptor Name="PositionName" DefaultDisplayName="PositionName" TypeName="System.String" />
<TypeDescriptor Name="Description" DefaultDisplayName="Description" TypeName="System.String" />
</TypeDescriptors>
</TypeDescriptor>
</Parameter>
</Parameters>
<MethodInstances>
<MethodInstance Name="ReadSpecificHRPosition" Type="SpecificFinder" Default="true" ReturnParameterName="@HRPosition" ReturnTypeDescriptorPath="HRPosition">
<AccessControlList>
<AccessControlEntry Principal="STS|SecurityTokenService|http://sharepoint.microsoft.com/claims/2009/08/isauthenticated|true|http://www.w3.org/2001/XMLSchema#string">
<Right BdcRight="Edit" />
<Right BdcRight="Execute" />
<Right BdcRight="SelectableInClients" />
<Right BdcRight="SetPermissions" />
</AccessControlEntry>
</AccessControlList>
</MethodInstance>
</MethodInstances>
</Method>
<Method Name="ReadAllHRPosition" DefaultDisplayName="Read All HRPosition" IsStatic="false">
<Properties>
<Property Name="ODataEntityUrl" Type="System.String">/HRPositions?$top=@LimitHRPositionss</Property>
</Properties>
<AccessControlList>
<AccessControlEntry Principal="STS|SecurityTokenService|http://sharepoint.microsoft.com/claims/2009/08/isauthenticated|true|http://www.w3.org/2001/XMLSchema#string">
<Right BdcRight="Edit" />
<Right BdcRight="Execute" />
<Right BdcRight="SelectableInClients" />
<Right BdcRight="SetPermissions" />
</AccessControlEntry>
</AccessControlList>
<FilterDescriptors>
<FilterDescriptor Name="LimitFilter" DefaultDisplayName="LimitFilter" Type="Limit" />
</FilterDescriptors>
<Parameters>
<Parameter Name="@LimitHRPositionss" Direction="In">
<TypeDescriptor Name="LimitHRPositionss" DefaultDisplayName="LimitHRPositionss" TypeName="System.Int32" AssociatedFilter="LimitFilter">
<Properties>
<Property Name="LogicalOperatorWithPrevious" Type="System.String">None</Property>
<Property Name="Order" Type="System.String">0</Property>
</Properties>
<DefaultValues>
<DefaultValue MethodInstanceName="ReadAllHRPosition" Type="System.Int32">100</DefaultValue>
</DefaultValues>
</TypeDescriptor>
</Parameter>
<Parameter Name="@HRPositions" Direction="Return">
<TypeDescriptor Name="HRPositions" DefaultDisplayName="HRPositions" TypeName="Microsoft.BusinessData.Runtime.IDynamicTypeEnumerator" IsCollection="true">
<TypeDescriptors>
<TypeDescriptor Name="HRPosition" DefaultDisplayName="HRPosition" TypeName="Microsoft.BusinessData.Runtime.DynamicType">
<TypeDescriptors>
<TypeDescriptor Name="HTPositionGuid" DefaultDisplayName="HTPositionGuid" TypeName="System.Guid" IdentifierName="HTPositionGuid" ReadOnly="true" />
<TypeDescriptor Name="PosistionCode" DefaultDisplayName="PosistionCode" TypeName="System.String" />
<TypeDescriptor Name="PositionName" DefaultDisplayName="PositionName" TypeName="System.String" />
<TypeDescriptor Name="Description" DefaultDisplayName="Description" TypeName="System.String" />
</TypeDescriptors>
</TypeDescriptor>
</TypeDescriptors>
</TypeDescriptor>
</Parameter>
</Parameters>
<MethodInstances>
<MethodInstance Name="ReadAllHRPosition" Type="Finder" Default="true" ReturnParameterName="@HRPositions" ReturnTypeDescriptorPath="HRPositions">
<AccessControlList>
<AccessControlEntry Principal="STS|SecurityTokenService|http://sharepoint.microsoft.com/claims/2009/08/isauthenticated|true|http://www.w3.org/2001/XMLSchema#string">
<Right BdcRight="Edit" />
<Right BdcRight="Execute" />
<Right BdcRight="SelectableInClients" />
<Right BdcRight="SetPermissions" />
</AccessControlEntry>
</AccessControlList>
</MethodInstance>
</MethodInstances>
</Method>
<Method Name="UpdateHRPosition" DefaultDisplayName="Update HRPosition" IsStatic="false">
<Properties>
<Property Name="ODataEntityUrl" Type="System.String">/HRPositions(HTPositionGuid=guid'@HTPositionGuid')</Property>
</Properties>
<AccessControlList>
<AccessControlEntry Principal="STS|SecurityTokenService|http://sharepoint.microsoft.com/claims/2009/08/isauthenticated|true|http://www.w3.org/2001/XMLSchema#string">
<Right BdcRight="Edit" />
<Right BdcRight="Execute" />
<Right BdcRight="SelectableInClients" />
<Right BdcRight="SetPermissions" />
</AccessControlEntry>
</AccessControlList>
<Parameters>
<Parameter Name="@HTPositionGuid" Direction="In">
<TypeDescriptor Name="HTPositionGuid" DefaultDisplayName="HTPositionGuid" TypeName="System.Guid" IdentifierName="HTPositionGuid" UpdaterField="true" />
</Parameter>
<Parameter Name="@PosistionCode" Direction="In">
<TypeDescriptor Name="PosistionCode" DefaultDisplayName="PosistionCode" TypeName="System.String" UpdaterField="true" />
</Parameter>
<Parameter Name="@PositionName" Direction="In">
<TypeDescriptor Name="PositionName" DefaultDisplayName="PositionName" TypeName="System.String" UpdaterField="true" />
</Parameter>
<Parameter Name="@Description" Direction="In">
<TypeDescriptor Name="Description" DefaultDisplayName="Description" TypeName="System.String" UpdaterField="true" />
</Parameter>
</Parameters>
<MethodInstances>
<MethodInstance Name="UpdateHRPosition" Type="Updater">
<AccessControlList>
<AccessControlEntry Principal="STS|SecurityTokenService|http://sharepoint.microsoft.com/claims/2009/08/isauthenticated|true|http://www.w3.org/2001/XMLSchema#string">
<Right BdcRight="Edit" />
<Right BdcRight="Execute" />
<Right BdcRight="SelectableInClients" />
<Right BdcRight="SetPermissions" />
</AccessControlEntry>
</AccessControlList>
</MethodInstance>
</MethodInstances>
</Method>
<Method Name="DeleteHRPosition" DefaultDisplayName="Delete HRPosition" IsStatic="false">
<Properties>
<Property Name="ODataEntityUrl" Type="System.String">/HRPositions(HTPositionGuid=guid'@HTPositionGuid')</Property>
</Properties>
<AccessControlList>
<AccessControlEntry Principal="STS|SecurityTokenService|http://sharepoint.microsoft.com/claims/2009/08/isauthenticated|true|http://www.w3.org/2001/XMLSchema#string">
<Right BdcRight="Edit" />
<Right BdcRight="Execute" />
<Right BdcRight="SelectableInClients" />
<Right BdcRight="SetPermissions" />
</AccessControlEntry>
</AccessControlList>
<Parameters>
<Parameter Name="@HTPositionGuid" Direction="In">
<TypeDescriptor Name="HTPositionGuid" DefaultDisplayName="HTPositionGuid" TypeName="System.Guid" IdentifierName="HTPositionGuid" />
</Parameter>
</Parameters>
<MethodInstances>
<MethodInstance Name="DeleteHRPosition" Type="Deleter">
<AccessControlList>
<AccessControlEntry Principal="STS|SecurityTokenService|http://sharepoint.microsoft.com/claims/2009/08/isauthenticated|true|http://www.w3.org/2001/XMLSchema#string">
<Right BdcRight="Edit" />
<Right BdcRight="Execute" />
<Right BdcRight="SelectableInClients" />
<Right BdcRight="SetPermissions" />
</AccessControlEntry>
</AccessControlList>
</MethodInstance>
</MethodInstances>
</Method>
</Methods>
</Entity>
</Entities>
</LobSystem>
</LobSystems>
</Model>I uploaded the ECT into SharePoint Online BCS and all looks fine;
From there I create an external list and reference the HRPositions ECT, which creates and SP List but is missing the primary key (which is the GUID).
This view shows the proper data;
I am able to add a new item to the list;
And it shows in the read all view;
But I can't edit, delete or view any list item as I get this error for each operation;
I attached to the OData Web Service and could see why the issue is occurring. Turns out the Auto-Generated External Control Types (ECT) within Visual Studio that were reflected off the OData Service have an issue in that for some reason it is formulating the request as /HRPositions(HTPositionGuid=guid'@HTPositionGuid');
It should really only be /HRPositions(guid'@HTPositionGuid');
Can anyone tell me why it's including the HTPositionGuid= within the Parameter list?
I can manually edit the code-generated ECT files for each entity but that seems silly.