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

Answer 1

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)