I have come across some interesting situations in attempting to utilize stored procedures in the N-Tier Entity Framework. I would like to confirm that these situations are NOT unique to our implementation, and would like to find out if our solutions are the best or if there are better solutions.
MAPPING REQUIREMENTS: When using context.SaveChanges() with mapped stored procedures, ALL stored procedures MUST be mapped for a given entity; meaning, if an Insert SP is mapped but an Update SP is not mapped, an exception will occur in trying to process an update because the framework “Cannot find the UpdateFunctionMapping for EntityType 'PriceFileCustomerPrice' in the mapping file.” We would like to only map those SPs that are necessary, and let the framework handle the rest of the insert/update/delete processing. How can we map an Insert but not an Update or Delete?
MISSING DATA IN CHANGESET: There is a problem in trying to use a mapped update stored procedure, in that the framework only provides data for the properties which have changed, and all other properties (aside from keys) have null or zero values due to the ChangeSet which omits all unchanged records and properties. So, the values for properties of an entity that have not been changed are not passed, but the mapped stored procedure requires values for most properties/sp params; this causes the update to mistakenly replace valid data with null or zero. (This problem does not occur when NOT using a stored procedure to update; meaning, the framework uses its own update method successfully with the missing data.)
a. Our solution to this is to call the Stored Procedure from OnChange() instead of mapping it, but… after calling the OnChange() method the framework still calls its own Update method and then throws an exception (on an insert) due to key violations; so, to prevent this exception we have mapped mock/dummy stored procedures that do nothing.
b. Is there a way to map an update stored procedure and receive all property values, changed and unchanged?
DETACHING ENTITYSETS: Calling SaveChanges() will process any and all modifications to ALL EntitySets; so, if an EntitySet has been modified but should not be saved, it must be detached ( Context.EntitySet.DetachAll() )prior to calling SaveChanges(), and then reattached one at a time ( Context.Attach(entity) ). Similarly, a single entity may be detached and reattached as well. Is this the best way to handle this situation? (I believe this is just a general EF question, not N-Tier specific.)
@ChristofSenn I would appreciate your response. Thank you!
MAPPING REQUIREMENTS: As far as I know EF doesn't fall back to default behavior if you map any of create, update or delete function. Hence, unless you know for a given entity e.g. you never delete records, you are required to map the delete function if you have mapped insert or update. I find this post useful when deciding on whether to use stored procs.
MISSING DATA IN CHANGESET: As you have already found out, by default N-Tier Entity Framework only transmits changes (or otherwise required data like PKs) from client to server. Properties that should always be included can be annotated with IncludeOnUpdateAttribute and IncludeOnDeleteAttribute as required.
SELECTIVELY SAVING CHANGES: context.SaveChanges submits all pending changes of the given context to the data service as a unit-of-work. If you only want specific entities to be saved you have the possibility of either removing (-> detach) others from the context or add (-> attach) the entities to be saved into a separate instance of a context which you can create temporarily only for this purpose. There is nothing that prevents you from having an entity attached to more than one context at the same time. However, this should be used with caution as it is easy to get things wrong, especially if it comes to entities with relations.