WebAPI extended with OData, works with REST client but results in error in Excel

[superman-lopez]

I have a WebAPI project (my sample project source code is here) where I extended the WebAPI controllers with EnableQuery attribute in the same way as Hassan describes in his blogpost. When I query the endpoint with a REST client the API works and provides the OData functionality like OrderBy. However when I try to connect to the endpoint with Excel or PowerBI, I get a connection error. I have removed all authentication to rule out issues in authentication.

Assemblies affected

Microsoft.AspNetCore.OData version 7.5.1

Reproduce steps

I have deployed my sample project on Azure, the endpoint can be accessed on: https://odatatestdevelopment.azurewebsites.net/api/unauthenticated When I access this with a http client (browser or REST client) the API works as expected. When I access it in Excel, the URL is not recognised as a OData feed.

Expected result

Excel to be able to access the endpoint.

Actual result

Error from Excel:

Details: "OData: The given URL neither points to an OData service or a feed: 'https://odatatestdevelopment.azurewebsites.net/api/unauthenticated'."

Additional detail

Some excerpts of my code:

// Startup.cs
public void ConfigureServices(IServiceCollection services)
{
    // ...
    services.AddOData();
    services.AddControllers(mvcOptions => 
        mvcOptions.EnableEndpointRouting = false);
    // ...
}

public void Configure(IApplicationBuilder app, IWebHostEnvironment env)
{
    // ...
    app.UseMvc(builder =>
    {
        builder.EnableDependencyInjection();
        builder.Select().Expand().Filter().OrderBy().MaxTop(100).Count();
        builder.MapODataServiceRoute("odata", "odata", GetEdmModel());

    });
}

IEdmModel GetEdmModel()
{
    var odataBuilder = new ODataConventionModelBuilder();
    odataBuilder.EntitySet<Blog>("Blogs");

    return odataBuilder.GetEdmModel();
}

// Controller.cs
[EnableQuery()]
[HttpGet("unauthenticated")]
public async Task<IActionResult> GetAllResultsUnauthenticated()
{
    List<Blog> blogs = await dbContext.Blogs
        .AsNoTracking()
        .ToListAsync();

    return Ok(blogs);
}

[Sreejithpin]

PowerBI and Excel expects OData Complaint payload. Please inherit your controller from ODataController and let us know in case of any issues

[superman-lopez]

Thanks for your message! I have have changed the inheritance of the controller class, and also realised I made a mistake in my routing configuration which didn't trip a http client but did trip Excel.

(I still face some 404 issues, but not related to the error message for which I opened the case)