Using dynamic SQL in stored procedure with Linq2SQL

January 9, 2009

I’m making heavy use of Linq2SQL right now, which for the main is working very well. I create my stored procedures, drag them onto the Linq2SQL surface designer, then use them in my code – All makes good sense and keeps the logic in sensible places. One problem I have noticed is that you cannot use a stored procedure with Linq2SQL that uses dynamic SQL.

Example:

Stored Procedure:

(I know, theres no reason for this to use dynamic SQL, its purely to demonstrate the issue!)

CREATE PROCEDURE [dbo].[GetSomeData]
	@myVar varchar(5)
AS
	BEGIN
	DECLARE @CMD VARCHAR(2000)
	SET NOCOUNT ON;
	SET @CMD = 'SELECT SomeTable.SomeColumn FROM SomeTable
		  'WHERE (SomeTable.SomeColumn = ''' + @myVar + ''');'
	EXEC(@CMD)
END

C# Code to loop data returned from stored procedure:

foreach (var foo in DB.GetSomeData(someVar))
{
	// Do Something...
}

Runtime error on the foreach loop:

	foreach statement cannot operate on variables of type 'int' because
	'int' does not contain a public definition for 'GetEnumerator'

The Fix?

Create your stored procedure with the same signiture and name, but with any static SQL (Ideally returning the same¬†columns). Drag it onto the Linq2SQL surface designer, compile and then amend your storedprocedure to suit (e.g. Add our dynamic SQL). This means the resultant LINQ method will have a data type of ‘auto-generated’ rather then unknown, which means your loop will work as expected. Its far from ideal, but it works and its the only solution I’ve found as yet. If you know of an easier solution, leave me a comment! I’m hoping this will be fixed in Visual Studio 2008 SP1.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: