LINQ to SQL calling stored procedure that uses dynamic SQL

I am trying to connect to SQL stored procedure using LINQ. It works pretty good for stored procedures that have开发者_开发技巧 static SQL query.

I want to connect to a stored procedure that has dynamic SQL.

At the end of stored procedure it has an exec statement.

exec(@srchQuery) 

When I do that it doesn't work because it is dynamic SQL.

If I use print @srchQuery and copy that stored procedure and use that static SQL in stored procedure, it works with no problem.

---

LINQ to SQL analyses the direct SELECT statements to see what model type it has to project as the result of executing said SQL stored procedure. Because you are using a dynamic statement using EXEC, it can't determine what model type to generate.

Simple answer would be to either not use a Stored Procedure, and instead use LINQ to SQL to generate the SQL, or don't use a dynamic built statement within your stored procedure.

The fact that you are generating a SQL statement must mean you have a strong use case for it, as it can achieve what you want without a stored procedure. Always favour simplicity... ask yourself, do you need to use a dynamically built statement within a stored procedure?

---

Just call the stored procedure directly within the DataContext:

using (YourDataContext dc = new YourDataContext())
{
    ...
    dc.ExecuteCommand("EXEC MyStoredProcedure");
    ...
}