Entity Framework doesn't recognise datatype

I have two temp tables, storing the output  results from two different sp's.Finally I union two temp table and shows the final output.

But when i called it from Entity framework, it doesn't returns the table instead it's return int. What is happening here.

If i use the data contract , then its work. I have a solution but i like to know is there any other method to fix it apart introducing the faux data contract for the result set.What is the root cause behind this.I want to fix this issue from EF and not to provide the workareound solution.
Link helped me fixed the issue :
https://jacstech.wordpress.com/2013/09/27/getting-entity-framework-to-recognise-the-return-type-of-a-stored-procedure-that-returns-a-temp-table/
SQL GuruAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Najam UddinCommented:
Try putting
SET FMTONLY OFF

Open in new window

in you stored proc then retry adding in entity frameowork.

Per MSDN
Returns only metadata to the client. Can be used to test the format of the response without actually running the query.
SQL GuruAuthor Commented:
My concerns, why does Entity Framework doesn't take the datatype by default?
Najam UddinCommented:
EF cannot get metadata from a stored proc that uses dynamic queries or temp tables, The reason is that to import the procedure EF must execute it, executing  them may effect you database as they may be doing some changes.

So EF uses SET FMTONLY ON to get meta data,  due to this command stored procedure will return only "metadata" about columns in its result set and it will not execute its logic. But because the logic wasn't executed there is no temporary table (or built dynamic query) so metadata contains nothing.

Hope it explains

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
SQL GuruAuthor Commented:
import the procedure EF must execute it, executing  them may effect you database as they may be doing some changes.

What changes does it do? How does will it impact the database?
Najam UddinCommented:
Whatever changes you have coded in your stored proc.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
.NET Programming

From novice to tech pro — start learning today.