CODEWITHSUNDEEP
c#asp.netapiasp.net-coreodata
Falcon
Falcon

Reputation: 857

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

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

Upvotes: 6

Views: 4397

Answers (3)

Simmetric
Simmetric

Reputation: 1681

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.

Upvotes: 1

KK_3
KK_3

Reputation: 21

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.

Upvotes: 1

Robert Perry
Robert Perry

Reputation: 1954

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)

Upvotes: 7

Related Questions