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");
    ...
}