Posts Tagged ‘Entity Framework’

Posted by OmegaMan at June 12, 2015

Category: Entity Framework, Tribal Knowledge, Visual Studio

Tags:

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 __abENT__#8260; 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 using`Update 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 select`Add 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

Posted by OmegaMan at May 28, 2015

Category: .Net, Database, Entity Framework

Tags:

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
  • Recent Comments

    Thanks for this simple yet delightful code....
    Hey guys I'm having the same problem as Netanel KL { “Error HRESUL...
    Soluciono el problema con el primero (First step solved it for me)....
    Thank you! I was looking for this. You save my day!...
    Thank you! This was a big help. My requirement was a little differen...
    Thanks, after a small path adjustment it worked for Visual Studio 2015...
    Good call!...
    Thank you .. this helped...
    How about when you have a long string with no space? I tried it and it...
    Thank you, I ran into this same issues with Visual Studio 2015 after i...
  • Blog Stats

    • 138,573 hits