Create Fake Query in LINQ to avoid unknown return type error while drag and drop sp in dbml

In this tutorial you will learn how to create Fake Query in LINQ to avoid unknown return type error while drag and drop sp in dbml. Now many of you will be thinking what does it means? Let me explain you, using linq when you drag and drop stored procedure in dbml file and get following alert then it means LINQ is unable to create class of your stored procedure in designer.cs (dbml). unknown return type
When you can face this problem?
  • When you will be selecting columns based on conditions, means in one condition you are getting five columns and in other conditions you are getting seven columns
  • When you will use temporary tables in stored procedure.
  • In string based stored procedure.
You can face this problem only in those SPs in which you are retrieving records. So if LINQ will not generate class of your stored procedure then it means you cannot retrieve records.

Steps to get rid of this problem and to create fake query
  • After getting aforementioned error, you have to delete stored procedure from your dbml file.
  • You have to alter SP, comment whole code written in SP, write fake query, in that fake query you have to mention all columns that you want to retrieve in SELECT command in a way illustrated in following picture

Original SP

Comment the original SP, Write Fake Query and Alter the SP

Execute the SP, it will give you result
Once SP is altered with fake query, drag and drop SP again in dbml file, now this time you will not get any error and class of the SP will be created in designer.cs. In the last you have to alter the SP again but this time you will have to revert the whole SP, comment the Fake Query code and bring back the SP into its original state that it has before the Fake Query. After this alteration in SP there is no need to drag and drop it again.

There is one more thing that I will need to discuss with you guys in next post about fake query which is very important. So stay tuned. So that's it. Cheers!!!!