Saturday, January 28, 2017

Right Way for Editing EDMX File of Entity Framework (EF)

As an object relation mapper, Entity Framework enables user(in this case, developer) to work with relational data in object oriented manner. It helps user to do operations like insert, update, delete, search - in database without writing sql like string and getting help of intelligence of code editor like visual studio.

Updating Entity Framework from database could be hassle if user do not know where to do what. As for example, to update a stored procedure follow the following steps -

1. Open edmx file.
2. Right click and select Model Browser

Delete stored procedure
3. from Complex Type (if any)
4. from Function Imports
5. from Stored Procedure / Functions

Then save, then update from database.


If the Stored Procedure is complex type, that means, it is returning some rows (like table) then follow these steps - 

1. Write these two lines at the beginning - 
    SET NOCOUNT ON
    SET FMTONLY OFF


2. Use temporary table to store data and finally return that table.
    IF OBJECT_ID('tempdb..##PGM_MonthlySalaryStatement') IS NOT NULL
    BEGIN
        DROP TABLE ##PGM_MonthlySalaryStatement
    END


..........
..........
     INSERT INTO ##PGM_MonthlySalaryStatement
..........
..........

    SELECT * FROM ##PGM_MonthlySalaryStatement


Thats it. 

Happy coding