none
Entity Framework vs. stored procedures RRS feed

  • Question

  • Hello,

    How does Entity Framework handle stored procedures? We have a stored procedure that deletes a whole bunch of data from the database but it doesn't update the EF cache.

    Our application is database first, which means that we create our entities by updating the model from the database. Updating the model from the database also creates methods in the database context to call any existing stored procedures. For example, we end up with this method in the database context for calling DeleteProjectData, the stored procedure in question:

    public virtual int DeleteProjectData(Nullable<int> projectId, string deleteType, string username)
            {
                var projectIdParameter = projectId.HasValue ?
                    new ObjectParameter("projectId", projectId) :
                    new ObjectParameter("projectId", typeof(int));
        
                var deleteTypeParameter = deleteType != null ?
                    new ObjectParameter("deleteType", deleteType) :
                    new ObjectParameter("deleteType", typeof(string));
        
                var usernameParameter = username != null ?
                    new ObjectParameter("username", username) :
                    new ObjectParameter("username", typeof(string));
        
                return ((IObjectContextAdapter)this).ObjectContext.ExecuteFunction("DeleteProjectData", projectIdParameter, deleteTypeParameter, usernameParameter);
            }



    When this method is called, does Entity Framework take note? That is, is it aware that the stored procedure is called? If so, what does it do to ensure the data it has cached is still in sync with the state of the database after the stored procedure is complete? Is it even able to keep track of what changes the stored procedure is making to the database?

    If not, is there a way to make Entity Framework aware of the changes and to do something about it?
    Monday, April 23, 2018 4:20 PM

All replies

  • When this method is called, does Entity Framework take note? That is, is it aware that the stored procedure is called? If so, what does it do to ensure the data it has cached is still in sync with the state of the database after the stored procedure is complete? Is it even able to keep track of what changes the stored procedure is making to the database?

    If not, is there a way to make Entity Framework aware of the changes and to do something about it?

    The ORM is an abstraction away from the underlying database technology. The ORM is not the database engine no more than ADO.NET is the database engine when you  use ADO.NET without EF being involved and using ADO.NET . with SQL Command Objects and the stored procedure. So, no I don't think the ORM is keeping track of what the database engine is doing nor should it have to keep track, because that's not the ORM's job. 

    Just because one is executing a stored procedure via  the ORM does that put the burden of what the stored procedure is doing on the ORM.

    The job of any ORM....

    https://en.wikipedia.org/wiki/Object-relational_mapping

    <copied>

    Object-relational mapping (ORM, O/RM, and O/R mapping tool) in computer science is a programming technique for converting data between incompatible type systems using object-oriented programming languages. This creates, in effect, a "virtual object database" that can be used from within the programming language.

    <end>

     

    Monday, April 23, 2018 5:24 PM
  • Hi gib898,

    About entity framework, I would suggest that you could read the following document.

    The Entity Framework has the following forms of caching built-in:

    1. Object caching – the ObjectStateManager built into an ObjectContext instance keeps track in memory of the objects that have been retrieved using that instance. This is also known as first-level cache.
    2. Query Plan Caching - reusing the generated store command when a query is executed more than once.
    3. Metadata caching - sharing the metadata for a model across different connections to the same model.

    >>If not, is there a way to make Entity Framework aware of the changes and to do something about it?

    I would suggest that you could retrieve the records from database again.

    https://msdn.microsoft.com/en-us/library/hh949853(v=vs.113).aspx

    Best regards,

    Zhanglong


    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, April 24, 2018 1:06 AM
    Moderator
  • Thanks both for your responses. This is helpful.

    Does the query planning cache store the results of queries or just the queries themselves?

    • Edited by gib898 Tuesday, April 24, 2018 2:51 PM
    Tuesday, April 24, 2018 2:26 PM
  • Hi gib898,

    >>Thanks both for your responses. This is helpful.

    If the reply is helpful, please mark it as answer, it will be beneficial other conmmunties who have the similar issue.

    >>Does the query planning cache store the results of queries or just the queries themselves?

    If you do a query, it will be cached in entity framework Dbcontext.

    Best regards,

    Zhanglong


    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, April 25, 2018 7:09 AM
    Moderator