none
Strategy for calling stored procedure with non-mapped parameters

    Question

  • I have a situation where business requirements dictate that all data access is done via stored procs. I chose to use Entity Framework because I knew the Stored Procedure support was greatly improved in 4.0

    However, I have a set of procedures which include some extra params (UserName, ReasonCode, etc...) that will be used for additional logic in the proc. This is a pretty common scenario so I am astounded that EF makes it so incredibly difficult to achieve this.

    As I see it my only options for getting around this are as follows:

    1. Create a view in the database that queries the underlying table, and includes the extra column names with null values.
    2. Handle the SaveChanges event, or override the SaveChanges method on the context and manually handle the function calls there.
    Option 1 is out of the question on my current project, although it would be the easiest.

    Option 2 is an incredible amount of work. The whole point of EF is to save me from having to write mind-numbingly tedious data access code that will be error prone. Let's look at the potential amount of work involved here:

    1. Create a function import for each entity needing this info, and for each modification action necessary (Insert, Update, Delete)
    2. Create some common type that holds all that information, and apply it to each entity via partial classes.
    3. Write a mapping function for each method for every entity (3*N) Note that this also involves getting original values for Update and Delete
    4. Override the SaveChanges method to inspect each Entity to see if it is the correct IFoo interface, check the state of the entity, and then call the appropriate method for that entity type.
    Am I missing something??? Why would such a common scenario be so difficult to perform.

    I know someone is probably thinking I could write a T4 template to generate all this code for me, but that just brings me back to the point that it is tedious boilerplate code that adds no real value. There simply has to be an easier way to just say: 

    "Yo EF! I am calling a proc! I know what i'm doing and I want to add these extra values in capiche?"

    Extra info for the curious:
    • I'm using the Self-Tracking entities template with WCF
    • The additional params do not exist anywhere that can be linked to by the Entity
    • The additional params are always the same
    • The stored procedures are as is, they cannot be modified from their current contract
    Any help or insight into this matter would be most welcome!

    Cheers,
    Josh

    Tuesday, November 23, 2010 4:50 PM

All replies

  • Hi Josh

    Thanks for posting the question.

    This is a common scenario that an EF user might face. I am looking to see if there are any easy ways to accomplish this. I will be updating this post soon with my findings.

     


    Bindesh -Microsoft Online Community Support
    Thursday, December 23, 2010 8:43 AM
  • On 11/23/2010 11:50 AM, jw_carroll wrote:
    > I have a situation where business requirements dictate that all data
    > access is done via stored procs. I chose to use Entity Framework because
    > I knew the Stored Procedure support was greatly improved in 4.0
    >
    > However, I have a set of procedures which include some extra params
    > (UserName, ReasonCode, etc...) that will be used for additional logic in
    > the proc. This is a pretty common scenario so I am astounded that EF
    > makes it so incredibly difficult to achieve this.
    >
    > As I see it my only options for getting around this are as follows:
    >
    > 1. Create a view in the database that queries the underlying table,
    > and includes the extra column names with null values.
    > 2. Handle the SaveChanges event, or override the SaveChanges method
    > on the context and manually handle the function calls there.
    >
    > *Option 1* is out of the question on my current project, although it
    > would be the easiest.
    >
    > *Option 2* is an incredible amount of work. The whole point of EF is to
    > save me from having to write mind-numbingly tedious data access code
    > that will be error prone. Let's look at the potential amount of work
    > involved here:
    >
    > 1. Create a function import for each entity needing this info, and
    > for each modification action necessary (Insert, Update, Delete)
    > 2. Create some common type that holds all that information, and apply
    > it to each entity via partial classes.
    > 3. Write a mapping function for each method for every entity (3*N)
    > *Note that this also involves getting original values for Update
    > and Delete*
    > 4. Override the SaveChanges method to inspect each Entity to see if
    > it is the correct IFoo interface, check the state of the entity,
    > and then call the appropriate method for that entity type.
    >
    > Am I missing something??? Why would such a common scenario be so
    > difficult to perform.
    >
    > I know someone is probably thinking I could write a T4 template to
    > generate all this code for me, but that just brings me back to the point
    > that it is tedious boilerplate code that adds no real value. There
    > simply has to be an easier way to just say:
    >
    > *"Yo EF! I am calling a proc! I know what i'm doing and I want to add
    > these extra values in capiche?"*
    >
    > Extra info for the curious:
    >
    > * I'm using the Self-Tracking entities template with WCF
    > * The additional params do not exist anywhere that can be linked to
    > by the Entity
    > * The additional params are always the same
    > * The stored procedures are /as is, /they cannot be modified from
    > their current contract
    >
    > Any help or insight into this matter would be most welcome!
     
     
    1) You can do this which you can do it with sproc or inline and get the
    reults back using SQL Command objects and datareader.
     
    Sometimes you need the backdoor. EF can't do everything.
     
    <http://blogs.msdn.com/b/alexj/archive/2009/11/07/tip-41-how-to-execute-t-sql-directly-against-the-database.aspx>
     
    http://blogs.msdn.com/b/alexj/archive/2009/03/26/index-of-tips.aspx
     
    You can make your own serializable DTO(s) and send them to the WCF
    client and back to the WCF service, after first populating them in the
    datareader loop. Or you can use an entity on the model.
     
    <http://stackoverflow.com/questions/3545106/wcf-serialization-and-value-object-pattern-in-domain-driven-design>
     
     
     
     
     
     
     
     
     
    Thursday, December 23, 2010 12:56 PM
  • I was trying to reproduce this inhouse to see what all we can do about this. Could you paste your sproc definition ?

     

    Thanks


    Bindesh -Microsoft Online Community Support
    Thursday, December 23, 2010 2:21 PM
  • Bindesh,

    Thanks for the reply (and the interest), on this one. I've actually already solved this problem on my end by basically doing exactly what I mentioned above in option #2. I'll elaborate more on my approach, but it was twofold. In a nutshell:

    1. Modify the "Self Tracking Entities" T4 template.
    2. Override SaveChanges to call the appropriate mapped functions.

    Ok, now a more detailed explanation.

    Every single stored procedure contained a few parameters that had nothing to do with the underlying table, but were necessary for auditing, security, business logic, etc... We could debate the legitimacy of such a design, but that is another topic all together.

    Lets say I had a table that looked something like this

    Customer 
    ID
    Name

    The procs are basically straight CRUD operations, but the UPDATE, and DELETE procs take extra information as well. For the sake of this example I'll simplify it and say that they take the following additional parameters:

    @UserName
    @ReasonCode
    @ShouldAudit

    The first step was to modify the T4 Template to generate an interface that encapsulated the additional properties and a mapping function, and ensure that the configured* entities implemented the interface. The interface looked something like this:

    public interface IAuditableEntity
    {
     String UserName {get; set;}
     String ReasonCode {get; set;}
     Boolean ShouldAudit {get; set;}
    
     List<ObjectParameter> GetParams(); 
    }
    

    The seconds step involved using a bit of convention and overriding the SaveChanges method. The following psuedocode describes the logic:

    1. Check to see if the entity being saved implements the IAuditableEntity interface.
    2. IF it does, then determine which function to call by using the following covention:
      1. [OperationName] + [EntityName]
    3. Once the function name had been constructed, extract the necessary parameters needed to call the mapped stored procedure by calling the GetParams() function.
    4. Call the function in the following manner:

     

    ExecuteFunction(functionName, entity.GetParams());
    

    So it wasn't terribly difficult to implement in the end, and using the T4 templates cut down on writing the same code over and over again, but it still seems like a common enough scenario that it could be incorporated by an official MS solution that is baked into the designer etc... I don't know if this approach helps or not, but at least it gives you some insight into what I did to solve the problem. I plan to write up a detailed blog post on it eventually, but you know how that goes :)

    *By configured I simply mean that in the T4 template I included a very low tech way of only modifying certain entities. My easy solution was to include an Array of entity names that the custom templating code should be executed on, and check each entity against that in the main loop.

    Friday, December 24, 2010 1:37 AM
  • Unfortunately, the Entity Framework currently has no built in support for this scenario.   The Entity Framework team is aware that such scenarios do arise in practice, especially when one is constrained by a pre-existing database.   Better support for this scenario may be considered for inclusion in a future version of the product.

    The approach you take seems reasonable; however, it doesn't take advantage of the the support provided by the Entity Framework for ordering updates so that foreign key constraints may be met.   This means that it may not work reliably in situatations where graphs of interrelated entities need to be updated in a single transaction.  If this isn't an issue for your application, then I think you should be in good shape.

    Another possible approach would be a variation of your proposed solution 1 in which you used a DefiningQuery in your SSDL to introduce the extra columns, rather than relying on a database view.   You will probably still have to take special steps to arrange that the appropriate values are used for extra properties.   This also requires that you be able to read the database through queries.   It does have the advantage that it is able to leverage the Entity Framework to co-ordianate the calls to the modification stored procedures.

    Will.

     

    Friday, December 31, 2010 10:06 PM