ASP.Net Core 2.0 API OData without Entity Framework but stored procedures

Question

Quite new to ASP.Net Core, and specially with API's. In the past I was used to create simple api's using the default controller like:

    [Produces("application/json")]
    [Route("api/TestCust")]
    public class TestCustController : Controller
    {
        // GET: api/TestCust
        [HttpGet]
        public IEnumerable<Customer> Get()
        {
            IEnumerable<Customer> customer = null;
            .... 
            return customer;
        }



  // GET: api/TestCust/5
        [HttpGet("{id}", Name = "Get")]
        public IEnumerable<Customer> Get(int id)
        {
            IEnumerable<Customer> customer = null;
            return customer;
        }

Now I am currently running into a new challenge as I am making an API but the client-side is already made by a third party. Which means, that I am forced to do it their way.

Luckily it is well documented, and they provide samples of the request they will be sending to my API. One of those request is as follow: /Customers?$filter=ID+eq+guid'1D225D75-A587-4AE4-BA9A-2224B2484EA5' and in order to get all customers: /Customers?$orderby=Code&$skip=100

Now, I am totally brand new to OData, I just found out about those yesterday and I have been following some tutorials about it. Although, most of them are using the Entity Framework, while I am using Dapper in combination with Stored Procedures.

Tutorials followed: https://damienbod.com/2018/10/12/odata-with-asp-net-core/, https://dotnetthoughts.net/perform-crud-operations-using-odata-in-aspnet-core/

So I've been trying to make a request using [EnableQuery] but that didn't work out just yet. I will link the error at the bottom.

So, what is it that I've done exactly? Well I changed the Startup.cs

  public void ConfigureServices(IServiceCollection services)
        {
            ...
            services.AddOData();
            services.AddODataQueryFilter()
            services.AddMvc();
            services.AddOptions();
        }

And inside my customer controller:

 public class CustomersController : ODataController{   
    ...

[EnableQuery]
        public IEnumerable<Customer> Get()
        {
            IEnumerable<Customer> allCustomers = null;
            IEnumerable<Addresses> allAddresses = null;
            IEnumerable<Contacts> allContacts = null;

            //Execute the procedures to fetch our objects.
            using (var connection = new SqlConnection(config.Value.ConnectionString.ToString()))
            {
                allCustomers = connection.Query<Customer>("someproc");
                allAddresses = connection.Query<Addresses>("someproc");
                allContacts = connection.Query<Contacts>("someproc");
            }
            //Loop through our customer object
            foreach(var item in allCustomers)
            {
                //Initialize a collection of address + contact
                ICollection<Contacts> CustomerContact = null;
                ICollection<Addresses> CustomerAddress = null;

                //Bind the Contact and Address to the correct Customer using the CustomerID 
                //Tijdelijk uitgezet omdat customer nu even geen GUID is..

                //CustomerContact = allContacts.Where(x => x.Customer == item.Id).ToList();
                //CustomerAddress = allAddresses.Where(x => x.Customer == item.Id).ToList();
                item.Contacts = CustomerContact;
                item.Addresses = CustomerAddress;
            }
            return allCustomers;
}

And here is the message it returns in the browser/postman as 'error 400 bad request':

The query specified in the URI is not valid. Cannot find the services container for the non-OData route. This can occur when using OData components on the non-OData route and is usually a configuration issue. Call EnableDependencyInjection() to enable OData components on non-OData routes. This may also occur when a request was mistakenly handled by the ASP.NET Core routing layer instead of the OData routing layer, for instance the URL does not include the OData route prefix configured via a call to MapODataServiceRoute()

Full message - https://pastebin.com/QtyuaQv1

FYI you don't need [Produces("application/json")], returning format is determined by the Accept in the HTTP header. so if you send Accept:"text/xml" you get back xml otherwise json. That's just a side note — XAMlMAX
– XAMlMAX, Commented Feb 20, 2019 at 14:37

Robert Perry · Accepted Answer · 2019-02-20 14:33:45Z

7

Theres a couple of things here.. Firstly, to work with OData you need to be returning IQueryable, not IEnumerable. Also, I'm not sure that Dapper works with OData, since OData is an MS tech that is primarily designed to work with EF and "entities". I think you might be able to get it to work, but you'll need to hack together some sort of solution that takes the ODataQueryOptions inputs and maps them to IQueryable for the EF side. (Basically, its not easy)

answered Feb 20, 2019 at 14:33

Robert Perry

1,9741 gold badge14 silver badges14 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

Falcon Over a year ago

Are there other ways to achieve this without OData maybe? That filter seems to be quite important as I need it in every request.

Robert Perry Over a year ago

If your filter is fixed, thats proprietary OData.. You need to write your own parser if you dont want to use the Microsoft OData backend

Robert Perry Over a year ago

The easiest way to get round this is to make a method on your server side that gets all the data you need. The make its return type IQueryable. Use the .AsQueryable() extension method on your IEnumerable as a cheat - its not ideal (and certainly not best practise) but its the easiest/quickest way to do it

Falcon Over a year ago

Could you provide me a sample or some documentation about how to do this?

Robert Perry Over a year ago

Yeah, I'll sort out an example for you when I get home later ;-)

|

KK_3 · Accepted Answer · 2019-06-17 17:11:10Z

1

If you still need a way to make OData work with Dapper, I've found a useful link that could help with decoding the filter options to raw sql that you can, later on, execute at the DB layer part: https://github.com/cuongle/DynamicOdata/blob/master/DynamicOdata.Service/SqlQueryBuilder.cs You can use it as a static class, and just take the part that builds up the WHERE and ORDERBY clause, and do the SELECT part concatenated with it in the repository or wherever you take the data from.

answered Jun 17, 2019 at 17:11

KK_3

212 bronze badges

Comments

Simmetric · Accepted Answer · 2020-06-18 10:50:52Z

1

I ran into this same problem, though on ASP.NET Core 3.1. Robert Perry mentions that OData is strongly tied to Entity Framework, but I find this is not the case.

In my case I'm using OData mixed with MVC View controllers.

In Startup.cs, you'll need to add:

public void Configure(IApplicationBuilder app, IWebHostEnvironment env)
{
    app.UseEndpoints(endpoints => {
        endpoints.MapControllers(); //for regular MVC controllers
        endpoints.EnableDependencyInjection(); //to inject the ODataQueryOptions
 endpoints.Select().Filter().Expand().OrderBy().Count().MaxTop(20); //adjust to your needs
        endpoints.MapODataRoute("odata", "odata", GetEdmModel());
    })
}

private void GetEdmModel()
{
    var builder = new ODataConventionModelBuilder({
      Namespace = "odata",
      ContainerName = "YourDataModelName"
    };
    builder.EntitySet<YourClass>("YourClassName"); //EntitySet seems to indicate it requires an EF entity, but really you can map any POCO object as long as one property is decorated with [Key]
    return builder.GetEdmModel();
}

Then your controller looks like:

public class MyController : ODataController
{
    [EnableQuery(PageSize = 20, AllowedQueryOptions = AllowedQueryOptions.All)]
    [ODataRoute("YourClassName")]
    public PageSet<YourClass> Get(ODataQueryOptions<YourClass> options)
    {
        //YourClass must be properly mapped in StartUp.GetEdmModel(), otherwise this will just return plain JSON and no OData wrapper

        //Set Request.ODataFeature().TotalCount to enable the Count option
    }
}

This approach does not require the custom model binding framework linked by Katarina. It just uses standard Microsoft Nuget packages.

edited Jun 18, 2020 at 10:50

answered Jun 18, 2020 at 10:15

Simmetric

1,6992 gold badges15 silver badges24 bronze badges

2 Comments

Lars Kemmann Over a year ago

This looks promising, but I'm not seeing the ODataRoute attribute in ASP.NET Core's OData v8.0.x. Do you happen to know what changed, or what I'm missing?

Simmetric Over a year ago

See devblogs.microsoft.com/odata/…, it uses the common AspNetCore attributes now.

Collectives™ on Stack Overflow

ASP.Net Core 2.0 API OData without Entity Framework but stored procedures

3 Answers 3

6 Comments

Comments

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

6 Comments

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related