While working with Entity Framework (EF), sometimes the EF doesn't recognize the columns returned by the Stored Procedure.
Reason
1) If the db procedure is using temporary table (e.g. #tempTable), EF don't recognize the MetaData from the procedure.
2) If multiple select ... statements exists in db procedure.
Solutions
Before executing procedure or beginning of db procedure set the FMTONLY to ON like below line:
SET FMTONLY ON
This will force EF to read the metadata from Temp tables
Disadvantage
There would be performance issues with approach because the way EF read the metadata from temp tables is to execute them multiple times.
Workaround
Instead of using the temp tables, create table variables and use them to store data temporarily. While using this option you don't need to set the FMTONLY setting.
Performance
Using the table variables will be faster than FMTONLY option.
Find more detailed information at
http://munishbansal.wordpress.com/2009/02/18/set-fmtonly-on-useful-in-tsql-ssis-packages-for-using-temp-tables/
Thanks
No comments:
Post a Comment