Posts Tagged Entity Framework

Entity Framework Stored Procedure Instructions

This is a how-to on getting Entity Framework (EF) version 5 and 6 to include stored procs and how to consume the resulting entities in code.

  1. In the EF designer choose Update Model From Database.
  2. When the page to Choose Your Database Objects and Settings comes up which allows one to add new tables/views/stored procs, select the stored proc of interest. Remember the name for the resulting data mapping entity will be the name with the extension _Result.2015-06-12_19-10-39
  3. Once the wizard is finished EF will contain the stored proc in the Model Browser. The model browser can be displayed by right clicking the EF design surface and selecting Model Browser.2015-06-12_19-51-20
  4. Here is an explanation of what has happened.
    (1) You have added the stored proc into the Stored Procedures / Functions as an item of interest.
    (2) EF has created a function import of the stored proc and placed it into Function Imports.
    (3) If EF was able to determine the *result set entity* it will most likely be in the Complex Types folder.
  5. If the mapping has gone right you should be able to call the stored proc off of the EF context in code and it will return a list of the complex type xxx_Result. If it works you will know it, but there could be problems with the mapping.

Mapping Problems and How to Resolve

  • One can delete at anytime the any object in the folders of 1/2 or 3 shown above and regenerate or create a custom mapping. Don’t be afraid to delete.
  • Sometimes very complex stored procs will not divulge the right mapping of the entity in the result set and the resulting complex type will cause failures. One way around that is to create a faux data return in the stored proc which leaves no ambiguity for Ef.
          1. In the database change the stored proc as follows.  Comment out the meat of the result select and replace it with a one-to-one column stub faux select such as this example: “SELECT 1 AS ResultId, ‘Power1’ AS GroupName, ‘Test’ AS Description”. Note to be clear you will need to match every column and name.
          2. In EF’s Model Browser delete all things associated with the stored proc in folders 1, 2 and 3 above.
          3. Regenerate all by usingUpdate Model From Database.
          4. Check the results.
  • If the above steps fail one can always create a function mapping by hand. Be careful not to create duplicates, if so delete all and start over.
        • Open up and find the stored proc you inserted into folder #3 above. Right click and selectAdd Function Import…2015-06-12_20-09-09
        • One can get the column information, change items on the above screen; it is trial and error.
        • You will need to play around with this until you have the right columns for your function import. Be wary of numbered copiesof the complex types which may be created from the mapping.

Remember to reset the stored proc back to its original state instead of the faux stub mentioned.

Share

Tags:

Entity Framework Cascading Deletes; Set it from the database.

To achieve cascading deletes, one must specify the cascading deletes on the FK relationships from the top level table in the database. The default is not to cascade.

Here is the visual Process in SQL Server Management Studio.

  1. Select the top level table which will handle the delete and right click.
  2. Select design mode.
  3. Right click any row in the design mode.
  4. Select Relationships.
  5. Find all the FK relationships and set them to cascade.

Then in Entity Framework update the edmx file after these changes are made so entity framework knows about the cascading constraint.  Once all this is done a cascaded delete is possible using Entity Framework.

 

omegacoder_dot_com_EF

Share

Tags: