none
Use of TFV with Entity Framework and OData RRS feed

  • Question

  • Hello

    I have a question related with a specific feature that we need to apply, currently I am working with Asp.net Web Api with Entity Framework and OData, but in this moment we are experimented performance problem related with the querying against this table with OData.  My question is simple but not easy, is there any approach to combine the use of Entity Framework with Table Function valued or Stored Procedures for better performance without lost the benefits of ODATA?

    In short words, Could I use a tech mix of EF+SP+OData and continue with the potential of them.

    Thanks !!!!


    "How many years can some people exist before they're allowed to be free" Bob Dylan Email: info@geohernandez.com Blog: geeks.ms/blogs/ghernandez

    Thursday, December 10, 2015 8:22 PM

Answers

  • Hi Geovanny -Gio- Hernandez,

    Please follow steps below:

    1.First define some parameters in stored procedure.

    2.Then you could try to get the Odata’s Url and split the Odata’s Filter. 

    3.And then put the filter to parameters. 

    4.Last access the stored procedure with parameters.

    Hope that is helpful to you.

    Best Regards,


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.




    Tuesday, December 15, 2015 3:33 AM
    Moderator

All replies

  • Hi Geovanny -Gio- Hernandez,

    You could use a technical mix of EF+SP+Odata, But I don’t think ef + sp is a good choice. If the project has a lot of sp, you could try to use ado.net + sp. But I think the business logic written in c# code is easier maintenance. On performance issues, you could set some breakpoint to check if that step is slower.

    And check if the performance problem occurs on web api, or in the entity framework. You could also check the time-consuming of stored procedures.

    Hope that helps.

    Best Regards,

    Friday, December 11, 2015 8:33 AM
    Moderator
  • Hi Fred

    I understand the point, but my question was related directly with the fact that if it is possible to apply EF+SP+OData, because the SP expose a specific logic but when the filter of OData trying to send them I don't know if this can be able to solve the query with the same filter that OData passed.

    Thanks.


    "How many years can some people exist before they're allowed to be free" Bob Dylan Email: info@geohernandez.com Blog: geeks.ms/blogs/ghernandez

    Saturday, December 12, 2015 11:11 PM
  • Hi Geovanny -Gio- Hernandez,

    You could use Entity Framework to import your stored procedures and then use the entities based on those stored procedures with the Web API OData ODataController / EntitySetController.

    The following is an example for you reference.

    private ProductsContext db = new ProductsContext();
    
            // GET: odata/Products
    
            [EnableQuery]
    
            public IQueryable<Product> GetProducts()
    
            {
    
                var prs = db.Products.SqlQuery("dbo.GetProducts").AsQueryable();
    
                return prs;
    
            }
    

    the result of http://localhost:1598/Products?$select=Name,Price

    {
    
      "@odata.context":"http://localhost:1598/$metadata#Products(Name,Price)","value":[
    
        {
    
          "Name":"P1","Price":10.00
    
        },{
    
          "Name":"P2","Price":15.00
    
        },{
    
          "Name":"P3","Price":11.50
    
        },{
    
          "Name":"P4","Price":110.00
    
        },{
    
          "Name":"P5","Price":1015.00
    
        },{
    
          "Name":"Yo-yo","Price":4.95
    
        }
    
      ]
    
    }
    

    Best Regards,

    Monday, December 14, 2015 6:08 AM
    Moderator
  • Hi Fred

    One question about this answer, in this case my main doubt is related with the fact that I feel the SP that you put like an example will return some like:

    CREATE PROCEDURE MySP

    SELECT * FROM MyTable

    And with this OData obviously can apply his filters, but the Execution Plan and the performance of this object will not be scalable with a huge table with a lot of results, or am I wrong?

    Thanks !!!


    "How many years can some people exist before they're allowed to be free" Bob Dylan Email: info@geohernandez.com Blog: geeks.ms/blogs/ghernandez

    Monday, December 14, 2015 1:15 PM
  • Hi Geovanny -Gio- Hernandez,

    Please follow steps below:

    1.First define some parameters in stored procedure.

    2.Then you could try to get the Odata’s Url and split the Odata’s Filter. 

    3.And then put the filter to parameters. 

    4.Last access the stored procedure with parameters.

    Hope that is helpful to you.

    Best Regards,


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.




    Tuesday, December 15, 2015 3:33 AM
    Moderator