Where I work it is a requirement for us to go through stored procedures as a mechanism to access our data through code. I am using LINQ2SQL to minimize this pain so that I can work with objects instead of ADO.NET directly. I have a situation Linq2SQL is consuming one of my stored procedures an generating code where the return type from the stored proc call is an int. The stored procedure actually returns a dataset. After doing a little research I have found that this is because the SQLClient library can not properly parse the sproc to generate the expected metadata that Linq2SQL uses to create the object graph. My question is how can sprocs (even complex ones) be structured so that you get an object graph out of linq2sql, or in other words what should you avoid having in your stored procedure that will create confusion for the SQLClient library to not understand how to generate the metadata that linq2sql consumes in order to create an object graph?
Sql – Optimizing Stored Procedures so they will be processed properly by Linq2SQL
linq-to-sqlstored-procedures
Best Answer
This is not actually a limitation of LINQ to SQL but rather of SQL Server which can not always tell a client what the return type would be without actually running it when it contains temporary tables, cursors or dynamic SQL.
As running it with invalid parameters could be potentially catastrophic it doesn't try.
You can either set it by hand using the designer or if it is absolutely okay to run the stored procedure with invalid data (i.e. it is purely passive) then you can add SET FMTOPT OFF to the start of the stored procedure.