Thursday, March 16, 2017

DbFunctions class of Entity Framework.

Playing with date time in both dotnet and sql server (t-sql) is always challenging and painful for me. Sometimes I forget that, entity framework cannot translate expressions into sql if I convert type using Convert class. like, Convert.ToDateTime(). If I do so then entity framework throws error like:
     "The specified type member 'Date' is not supported in LINQ to Entities. Only initializers, entity members, and entity navigation properties are supported. "

To rescue from  this, Microsoft introduces DbFunctions class from EF6 version. Prior of EF6 it was EntityFunctions. There are lot of built-in functions related to datetime type has been implemented there. Check them out.






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