Entity Framework Function Import not returning Columns data


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

Advertisements

About dotnetcoderoom

Working as an Onsite coordinator in USA. Handling UAT, CR (Change Requests), Product Support/Development, Build Management. Supervise and assist in live product implementation (at client side) and training. Providing technical direction for the development, design, and system integration. Closely monitoring and working with offshore team for Enhancements, Bug Fixing, Enforce coding standards, perform code reviews and mentors junior developers. Proven track of timely delivery of enterprise level web based applications. Skilled in a variety of software languages, tools, and methodologies, with a special expertise in Microsoft .NET technologies. MCTS .Net 4.0 Web applications MCTS .Net 2.0 Web applications GNIIT, MCA, B.Com Specialties 8+ Years of experience in .NET Technologies. Expertise in .NET, ASP.NET, MVC, C#, VB.NET, WCF, Web Services, MS SQL, JQuery. Excellent R&D and troubleshooting skills. Extensive knowledge in E-Commerce and Healthcare domain. View all posts by dotnetcoderoom

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: