ASP.Net Core Web API implementation with OData fails for a single entity URI

user_1978099 0
Viewed 2623

No matter what I try, I cannot get OData 7.3.0 to return a single resource using a simple URL like https://localhost:44316/odata/Widget(5)...

Steps to reproduce:

  1. I created a database called 'WidgetDB' is SQL Server.

  2. I used the following SQL script to add a single table with some data:

create table widget
(
   widget_id int identity(1, 1) not null,
   widget_name varchar(100) not null,
   constraint PK_widget primary key clustered (widget_id)
)
GO

insert into widget (widget_name) 
values
('Thingamabob'), ('Thingamajig'), ('Thingy'),
('Doomaflotchie'), ('Doohickey'), ('Doojigger'), ('Doodad'),
('Whatchamacallit'), ('Whatnot'), ('Whatsit'),
('Gizmo'), ('Nicknack')
GO
  1. In Visual Studio 2019, I created a new Web API solution using ASP.Net Core 3.1 called "WidgetWebAPI".

  2. I added Nuget Packages for the following:

    • Microsoft.EntityFrameworkCore 3.1.3
    • Microsoft.EntityFrameworkCore.SqlServer 3.1.3
    • Microsoft.EntityFrameworkCore.Tools 3.1.3
    • Microsoft.Extensions.DependencyInjection 3.1.3
    • Microsoft.AspNetCore.OData 7.4.0

  3. I removed the Weatherforecast.cs class and WeatherforecastController.cs classes from the default scaffold project that is created by Visual Studio.

  4. I went to the package manager console and typed in the following line to scaffold a DbContext for Entity Framework core:

    PM> Scaffold-DbContext -Connection "Server=.;Database=WidgetDB;Trusted_Connection=True;" -Provider Microsoft.EntityFrameworkCore.SqlServer -OutputDir Models
Build started...
Build succeeded.
PM>
  5. I opened the appsettings.json file and added a ConnectionStrings section:
{
 "ConnectionStrings": {
   "Default": "Server=.;Database=WidgetDB;Trusted_Connection=True;"
 },
 "Logging": {
   "LogLevel": {
     "Default": "Information",
     "Microsoft": "Warning",
     "Microsoft.Hosting.Lifetime": "Information"
   }
 },
 "AllowedHosts": "*"
}
  1. I opened the Models\WidgetDBContext.cs file that step 6 created, and took out the OnConfiguring method:
using Microsoft.EntityFrameworkCore;

namespace WidgetWebAPI.Models
{
   public partial class WidgetDBContext : DbContext
   {
       public WidgetDBContext() { }
       public WidgetDBContext(DbContextOptions<WidgetDBContext> options) : base(options) { }
       public virtual DbSet<Widget> Widget { get; set; }

       protected override void OnModelCreating(ModelBuilder modelBuilder)
       {
           modelBuilder.Entity<Widget>(entity =>
           {
               entity.ToTable("widget");
               entity.Property(e => e.WidgetId).HasColumnName("widget_id");
               entity.Property(e => e.WidgetName)
                   .IsRequired()
                   .HasColumnName("widget_name")
                   .HasMaxLength(100)
                   .IsUnicode(false);
           });

           OnModelCreatingPartial(modelBuilder);
       }

       partial void OnModelCreatingPartial(ModelBuilder modelBuilder);
   }
}
  1. I opened the Startup.cs file, and piecing together what complete examples I could find, I cleaned up the code to the following:
using Microsoft.AspNet.OData.Builder;
using Microsoft.AspNet.OData.Extensions;
using Microsoft.AspNetCore.Builder;
using Microsoft.AspNetCore.Hosting;
using Microsoft.EntityFrameworkCore;
using Microsoft.Extensions.Configuration;
using Microsoft.Extensions.DependencyInjection;
using Microsoft.Extensions.Hosting;
using Microsoft.OData.Edm;
using WidgetWebAPI.Models;

namespace WidgetWebAPI
{
   public class Startup
   {
       public Startup(IConfiguration configuration)
       {
           Configuration = configuration;
       }

       public IConfiguration Configuration { get; }

       // This method gets called by the runtime. Use this method to add services to the container.
       public void ConfigureServices(IServiceCollection services)
       {
           // See note on https://devblogs.microsoft.com/odata/experimenting-with-odata-in-asp-net-core-3-1/
           // Disabling end-point routing isn't ideal, but is required for the current implementation of OData 
           // (7.4.0 as of this comment).  As OData is further updated, this will change.
           //services.AddControllers();
           services.AddControllers(mvcOoptions => mvcOoptions.EnableEndpointRouting = false);

           services.AddDbContext<Models.WidgetDBContext>(optionsBuilder =>
           {
               if (!optionsBuilder.IsConfigured)
               {
                   optionsBuilder.UseSqlServer(Configuration.GetConnectionString("Default"));
               }
           });

           services.AddOData();
       }

       // This method gets called by the runtime. Use this method to configure the HTTP request pipeline.
       public void Configure(IApplicationBuilder app, IWebHostEnvironment env)
       {
           if (env.IsDevelopment())
           {
               app.UseDeveloperExceptionPage();
           }

           app.UseHttpsRedirection();
           app.UseRouting();
           app.UseAuthorization();

           // Again, this is temporary due to current OData implementation.  See note above.
           //app.UseEndpoints(endpoints =>
           //{
           //    endpoints.MapControllers();
           //});

           app.UseMvc(routeBuilder =>
           {
               routeBuilder.MapODataServiceRoute("odata", "odata", GetEdmModel());
           });
       }

       private IEdmModel GetEdmModel()
       {
           var builder = new ODataConventionModelBuilder();
           builder.Namespace = "WidgetData";   // Hide Model Schema from $metadata
           builder.EntitySet<Widget>("Widgets").EntityType
               .HasKey(r => r.WidgetId)
               .Filter()   // Allow for the $filter Command
               .Count()    // Allow for the $count Command
               .Expand()   // Allow for the $expand Command
               .OrderBy()  // Allow for the $orderby Command
               .Page()     // Allow for the $top and $skip Commands
               .Select();  // Allow for the $select Command;

           return builder.GetEdmModel();
       }
   }
}

  1. A created the class Controllers\WidgetsController.cs by right-clicking on the Controllers folder, selecting Add Controller..., and choosing the API Controller with actions, using Entity Framework option in the wizard dialog:

    Add Controller Dialog

    This was my first mistake. See step 13.

  2. I added the [EnableQuery] attribute to the GetWidget() method of the Controller class that the scaffold created, and changed the class inheritance from ControllerBase to ODataController. Other than ensuring my namespaces were properly resolved, I did nothing else to the existing file.

  3. I changed the debug settings to set the URL to odata/Widgets rather than weatherforecast and ran the application.

NOTHING WORKED! After hours of cursing and puzzlement and trial and error, I finally figured out that by default OData hates plural named objects and controllers.

  1. (Or 9 revisted) I went back into my Startup.cs class and changed this line of code to use the singular form:
builder.EntitySet<Widget>("Widget").EntityType

  1. (Or 10 revisted) I ran the Add Controller... wizard again and this time, I set the Controller Name to WidgetController, and then reapplied the changes mentioned in step 11.

  2. I updated the Launch Browser Debug setting in the project properties to odata/Widget and ran the application again:

All the widgets are returned, so we've made progress!

However, any attempt to get a single entity using a well-formed OData Url such as https://localhost:44316/odata/Widget(4) simply returns the whole data set rather than the single entity whose Id is 4. In fact, a SQL Profiler trace shows that the constructed SQL query doesn't contain anything other than a select from the entire table:

SELECT [w].[widget_id], [w].[widget_name]
FROM [widget] AS [w]

I have looked all over the Internet and my Google Fu is failing me. I cannot find a reason this isn't working, nor a current example demonstrating where it is working and what I am missing! I can find many examples demonstrating $filter, $expand, etc. but not a single example of just returning a single entity from a set.

I've tried things like changing the method signatures. This has no effect either:

        [HttpGet]
        [EnableQuery]
        public IQueryable<Widget> GetWidget() => _context.Widget.AsQueryable();

        [HttpGet("{id}")]
        [EnableQuery]
        public IQueryable<Widget> GetWidget([FromODataUri] int id) => _context.Widget.Where(r => r.WidgetId == id);

I know the end-point is capable of returning a single entity. I can get it to do so by entering the URL: https://localhost:44316/odata/Widget?$filter=WidgetId eq 5, which works fine and appropriately causes the correct SQL to be generated against the database.

entity-framework-core asp.net-core odata asp.net-web-api c#
2 Answers

After three days of frustration, I stumbled across the solution to the problem - a solution that is maddening in its simplicity, and infuriating that it doesn't appear to be documented anywhere as a critical necessity in any examples I can find.

When it comes to the method signature for a single entity, this method signature doesn't work. The routing middleware never matches to it, so the method never gets called:

 [EnableQuery]
[ODataRoute("({id})", RouteName = nameof(GetWidget))]
public async Task<IActionResult> GetWidget([FromODataUri] int id)
{
    var widget = await _context.Widget.FindAsync(id);
    if (widget == null) return NotFound();
    return Ok(widget);
}

Either of the following options works fine however:

[EnableQuery]
public async Task<IActionResult> GetWidget([FromODataUri] int key)
{
     var widget = await _context.Widget.FindAsync(key);
     if (widget == null) return NotFound();
     return Ok(widget);
}

[EnableQuery]
public async Task<IActionResult> GetWidget([FromODataUri] int keyWidgetId)
{
     var widget = await _context.Widget.FindAsync(keyWidgetId);
     if (widget == null) return NotFound();
     return Ok(widget);
}

The key to the mystery (pun intended) is using the word key for the id...

Why isn't this written somewhere in giant bold type? So stupid... #fumes #aggravation

user_19780990

Here are some suggestions:

In your Startup.cs:

app.UseMvc(routeBuilder =>
{
    // the following will not work as expected
    // BUG: https://github.com/OData/WebApi/issues/1837
    // routeBuilder.SetDefaultODataOptions(new ODataOptions { UrlKeyDelimiter = Microsoft.OData.ODataUrlKeyDelimiter.Parentheses });
    var options = routeBuilder.ServiceProvider.GetRequiredService<ODataOptions>();
    options.UrlKeyDelimiter = Microsoft.OData.ODataUrlKeyDelimiter.Parentheses;
    routeBuilder.MapODataServiceRoute("odata", "odata", GetEdmModel());
});

At the top of your controller, add:

[ODataRoutePrefix("Widget")]

Remove the [EnableQuery] attribute if you want to retrieve a single entity. Instead, use:

[ODataRoute("({id})", RouteName = nameof(GetWidget))]
public async Task<IActionResult> GetWidget([FromODataUri] int id)
{
    var widget = await _context.Widget.SingleOrDefaultAsync(x => x.WidgetId == id);
    return Ok(widget);
}

You also don't need the [HttpGet("{id}")] attribute.

user_25576820
Related