none
Web API with OData Example RRS feed

  • Question

  • Hi,

    Currently I am in process of enhancing the REST API to OData Services. 

    I have Edms for Few Tables and 1 Stored Procedure (to start with). I can successfully call a Table data in Odata.

    Now, How do i call a Stored Procedure in the same Controller where i am calling one of my Table.  Below is my Code

    Controller 

    [EnableQuery(PageSize = 1000)]
            public IEnumerable<TimesheetMaster> Get()
            {
                return _context.TimesheetMaster.AsQueryable();
            }

            [EnableQuery(PageSize = 1000)]
            public IEnumerable<PROC_GET_PERIOD_Result> GetPeriods([FromODataUri] Guid ResourceUID, [FromODataUri] String Role)
            {
                return _context.PROC_GET_PERIOD(ResourceUID, Role).AsQueryable();
            }

    And WebApiConfig

     public static void Register(HttpConfiguration config)
            {
                config.Routes.MapODataRoute("ODataTest", "OData", GenerateEdmModel());
            }
            private static IEdmModel GenerateEdmModel()
            {
                var builder = new ODataConventionModelBuilder(); 
                builder.EntitySet<TimesheetMaster>("Timesheet");
                ActionConfiguration rateProduct = builder.Entity<PROC_GET_PERIOD_Result>().Action("GetPeriods");
                rateProduct.Parameter<Guid>("ResourceUID");
                rateProduct.Parameter<String>("Role");
                rateProduct.Returns<IEnumerable>();
                return builder.GetEdmModel();
            }

    When I call http://localhost:65100/OData/Timesheet - It Works Fine.

    How do I call GetPeriods (Stored Procedure) in Url ? Is my code right ??

     


    Thanks, Parth



    Friday, May 12, 2017 8:00 AM

Answers

  • To be honesty, I am not good at Web API OData.

    This forum is used to discuss about WCF Developing issue, for your questions, I would suggest you go to Web API forum, they are familiar with it, and I think they could share you the depth knowledge.

    Reference:https://forums.asp.net/1246.aspx


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    • Marked as answer by Parth Rawal Wednesday, May 17, 2017 5:42 AM
    Wednesday, May 17, 2017 5:37 AM

All replies

  • It seems you could expose your stored procedure as an unbound OData function, and I suggest you refer below links.

    # Call stored procedure with parameters using Web Api Odata

    http://stackoverflow.com/questions/36182937/call-stored-procedure-with-parameters-using-web-api-odata

    # Example: Adding an Unbound Function

    https://docs.microsoft.com/en-us/aspnet/web-api/overview/odata-support-in-aspnet-web-api/odata-v4/odata-actions-and-functions#example-adding-an-unbound-function


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Monday, May 15, 2017 3:05 AM
  • when i am using your first link i am unable to call the Odata Service. 

    and Based on the 2nd link, how do i bind 2 OData Services in same WebApiConfig file? 


    Thanks, Parth

    Monday, May 15, 2017 4:51 AM
  • >> Based on the 2nd link, how do i bind 2 OData Services in same WebApiConfig file?

    What do you mean by this? Could you share us more information about this link limit?

    Have you make GetPeriods worked for one OData Service?

    In addition, what is your OData version?

    It would be helpful if you could share us a simple demo which could reproduce your issue.


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.


    Tuesday, May 16, 2017 8:13 AM
  • Hi,

    How can one controller have multiple Get withany [FormUri] ??? and what will be i writing in WebAPI.cs file??


    Thanks, Parth

    Tuesday, May 16, 2017 8:55 AM
  • Will below code meet your requirement?

    WebApiConfig.cs
        public static class WebApiConfig
        {
            public static void Register(HttpConfiguration config)
            {
                ODataModelBuilder builder = new ODataConventionModelBuilder();
                builder.EntitySet<ProductRating>("ProductRatings");     
    
                var fun = builder.Function("GetTest");            
                fun.Parameter<Guid>("ResourceUID");
                fun.Parameter<String>("Role");
                fun.Returns<IEnumerable>();         
    
    
                config.MapODataServiceRoute(
                    routeName: "ODataRoute",
                    routePrefix: null,
                    model: builder.GetEdmModel());
            }      
        }
    ProductRatingsController.cs
        public class ProductRatingsController : ODataController
        {
            [EnableQuery(PageSize = 1000)]
            public IEnumerable<ProductRating> Get()
            {
                List<ProductRating> values = new List<ProductRating>() {
                    new ProductRating() { ID=1, ProductID=1 },
                    new ProductRating() { ID=2, ProductID=2 },
                    new ProductRating() { ID=3, ProductID=3 },
    
                };
                return values.AsEnumerable();
            }
            
            [EnableQuery(PageSize = 1000)]
            [ODataRoute("GetTest(ResourceUID={ResourceUID},Role={Role})")]
            public IEnumerable<ProductRating> GetTest([FromODataUri] Guid ResourceUID, [FromODataUri] String Role)
            {
                List<ProductRating> values = new List<ProductRating>()
                {
                    new ProductRating() { ID=1, ProductID=1 },
                    new ProductRating() { ID=2, ProductID=2 },
                    new ProductRating() { ID=3, ProductID=3 },
                };
                return values;
            }
    
        }

    Requests:

    http://localhost:61505/ProductRatings

    http://localhost:61505/GetTest(ResourceUID=00000000-e90f-4938-b8f6-000000000000,Role='test')


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Wednesday, May 17, 2017 3:09 AM
  • Thanks for replying. I need one clarification In WebApiConfig.cs file on this like - builder.EntitySet<ProductRating>("ProductRatings");  

    Now My Question is can i have something like this - builder.EntitySet<TotaldifferentEntitySet>("ProductRatings");  Certainly this will thrown an error as we are registering the same controller with the different entity set. 

    Now, How can i have 2 Get Methods both of them are different Entity set i.e. ProductRating and TotalDifferentEntitySet 

    My 2nd requirement is to call Stored Procedure (which i will try now) which  you have mentioned in your example. 

    Another clarification which i need is , How does OData know that he has to go to certain controller and invoke the stored procedure, is it due to the ODataRoute?

    Can you please explain this in little detail please?

    Sorry For Too many Questions, it is that it's bit confusing and i could not find any tutorials on the same.


    Thanks, Parth

    Wednesday, May 17, 2017 4:52 AM
  • To be honesty, I am not good at Web API OData.

    This forum is used to discuss about WCF Developing issue, for your questions, I would suggest you go to Web API forum, they are familiar with it, and I think they could share you the depth knowledge.

    Reference:https://forums.asp.net/1246.aspx


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    • Marked as answer by Parth Rawal Wednesday, May 17, 2017 5:42 AM
    Wednesday, May 17, 2017 5:37 AM