I have created a locally hosted Web API OData service that I made using a tutorial. It makes use of a database which is also locally hosted and is created via code-first methods. It has two entities in the system, Suppliers and Products. When I access the api from a browser using localhostXXXX/Products or /Suppliersit displays the feed fine like so:
{
"@odata.context":"http://localhost:64469/$metadata#Products","value":[
{
"Id":1,"Name":"Broken Age","Price":15.00,"Category":"Adventure","SupplierId":1
},{
"Id":2,"Name":"Final Fantasy IX","Price":10.00,"Category":"JRPG","SupplierId":2
},{
"Id":3,"Name":"Fallout 3","Price":15.00,"Category":"Action RPG","SupplierId":3
}
]
However when I try to access the feed in Excel via PowerQuery (by going toFrom Other Sources -> From OData Feed) it shows a prompt with the message :"Access to the resource is forbidden".
Can anyone understand why this is? Also how I may get around it? I've done no authentication on the web service and as it works in the web browser its clearly accessable.
Massive thanks to any help anyone can provide.
See below for source.
Controllers
public class SuppliersController : ODataController
{
ProductsContext db = new ProductsContext();
protected override void Dispose(bool disposing)
{
db.Dispose();
base.Dispose(disposing);
}
[EnableQuery]
public IQueryable<Supplier> Get()
{
return db.Suppliers;
}
[EnableQuery]
public SingleResult<Supplier> Get([FromODataUri] int key)
{
IQueryable<Supplier> result = db.Suppliers.Where(p => p.Id == key);
return SingleResult.Create(result);
}
[EnableQuery]
public IQueryable<Product> GetProducts([FromODataUri] int key)
{
return db.Suppliers.Where(m => m.Id.Equals(key)).SelectMany(m => m.Products);
}
}
public class ProductsController : ODataController
{
ProductsContext db = new ProductsContext();
private bool ProductExists(int key)
{
return db.Products.Any(p => p.Id == key);
}
protected override void Dispose(bool disposing)
{
db.Dispose();
base.Dispose(disposing);
}
[EnableQuery]
public IQueryable<Product> Get()
{
return db.Products;
}
[EnableQuery]
public SingleResult<Product> Get([FromODataUri] int key)
{
IQueryable<Product> result = db.Products.Where(p => p.Id == key);
return SingleResult.Create(result);
}
[EnableQuery]
public SingleResult<Supplier> GetSupplier([FromODataUri] int key)
{
var result = db.Products.Where(m => m.Id == key).Select(m => m.Supplier);
return SingleResult.Create(result);
}
}
Models:
public class Supplier
{
public int Id { get; set; }
public string Name { get; set; }
public ICollection<Product> Products { get; set; }
}
public class Product
{
public int Id { get; set; }
public string Name { get; set; }
public decimal Price { get; set; }
public string Category { get; set; }
[ForeignKey("Supplier")]
public int? SupplierId { get; set; }
public virtual Supplier Supplier { get; set; }
}
Web.Config
<connectionStrings>
<add name="ProductsContext" connectionString="Data Source=(localdb)\v11.0;
Initial Catalog=ProductsContext; Integrated Security=True; MultipleActiveResultSets=True;
AttachDbFilename=|DataDirectory|ProductsContext.mdf"
providerName="System.Data.SqlClient" />
</connectionStrings>
WebApiConfig
public static class WebApiConfig
{
public static void Register(HttpConfiguration config)
{
// New code:
ODataModelBuilder builder = new ODataConventionModelBuilder();
builder.EntitySet<Product>("Products");
builder.EntitySet<Supplier>("Suppliers");
config.MapODataServiceRoute(
routeName: "ODataRoute",
routePrefix: null,
model: builder.GetEdmModel());
}
}
Configuration.cs
internal sealed class Configuration : DbMigrationsConfiguration<ProductService.Models.ProductsContext>
{
public Configuration()
{
AutomaticMigrationsEnabled = false;
ContextKey = "ProductService.Models.ProductsContext";
}
protected override void Seed(ProductService.Models.ProductsContext context)
{
context.Suppliers.AddOrUpdate(x => x.Id,
new Supplier() {Id = 1, Name = "Double Fine" },
new Supplier() { Id = 2, Name = "Square Enix" },
new Supplier() { Id = 3, Name = "Bethesda" });
context.Products.AddOrUpdate(x => x.Id,
new Product() { Id = 1, SupplierId = 1, Name = "Broken Age", Price=15.00M, Category = "Adventure"},
new Product() { Id = 2, SupplierId = 2, Name = "Final Fantasy IX", Price = 10.00M, Category = "JRPG" },
new Product() { Id = 3, SupplierId = 3, Name = "Fallout 3", Price = 15.00M, Category = "Action RPG" });
}
}
ProductServiceContext.cs
public class ProductsContext : DbContext
{
public ProductsContext()
: base("name=ProductsContext")
{
}
public DbSet<Product> Products { get; set; }
public DbSet<Supplier> Suppliers { get; set; }
}
Update
As asked by Curt Hagenlocher, below is the output if I go to localhost64469/$metadata in a browser:
This XML file does not appear to have any style information associated with it. The document tree is shown below.
<edmx:Edmx xmlns:edmx="http://docs.oasis-open.org/odata/ns/edmx" Version="4.0">
<edmx:DataServices>
<Schema xmlns="http://docs.oasis-open.org/odata/ns/edm" Namespace="ProductService.Models">
<EntityType Name="Product">
<Key>
<PropertyRef Name="Id"/>
</Key>
<Property Name="Id" Type="Edm.Int32" Nullable="false"/>
<Property Name="Name" Type="Edm.String"/>
<Property Name="Price" Type="Edm.Decimal" Nullable="false"/>
<Property Name="Category" Type="Edm.String"/>
<Property Name="SupplierId" Type="Edm.Int32"/>
<NavigationProperty Name="Supplier" Type="ProductService.Models.Supplier"/>
</EntityType>
<EntityType Name="Supplier">
<Key>
<PropertyRef Name="Id"/>
</Key>
<Property Name="Id" Type="Edm.Int32" Nullable="false"/>
<Property Name="Name" Type="Edm.String"/>
<NavigationProperty Name="Products" Type="Collection(ProductService.Models.Product)"/>
</EntityType>
</Schema>
<Schema xmlns="http://docs.oasis-open.org/odata/ns/edm" Namespace="Default">
<EntityContainer Name="Container">
<EntitySet Name="Products" EntityType="ProductService.Models.Product">
<NavigationPropertyBinding Path="Supplier" Target="Suppliers"/>
</EntitySet>
<EntitySet Name="Suppliers" EntityType="ProductService.Models.Supplier">
<NavigationPropertyBinding Path="Products" Target="Products"/>
</EntitySet>
</EntityContainer>
</Schema>
</edmx:DataServices>
</edmx:Edmx>
The issue lay with my version of PowerQuery plugin for Excel. It was one version out of date (2.21 instead of 2.22 I believe) and in the newest version it added OData v4 support, which is the version of OData I was using.
Thanks for your help.