CODEWITHSUNDEEP
c#.nett-sqlsql-server-2008linq-to-sql
Jake Foster
Jake Foster

Reputation: 107

LINQ-to-SQL .ExecuteCommand() doesn't work with parameterized object names

I'm a little flummoxed by this one and hoping that someone can clarify what's going on.

I'd like to programmatically disable a foreign key constraint using my LINQ-to-SQL data context. Seemingly this should be as easy as the following:

context.ExecuteCommand( "ALTER TABLE {0} NOCHECK CONSTRAINT {1}", "MyTable", "FK_MyTable_MyOtherTable" );

Unfortunately, this code bombs with the SQL error "Incorrect syntax near '@p0'."

When I fire up profiler I see that the SQL being generated is:

exec sp_executesql 
N'ALTER TABLE @p0 NOCHECK CONSTRAINT @p1',
N'@p0 varchar(4000),@p1 varchar(4000)',
@p0=N'MyTable',
@p1=N'FK_MyTable_MyOtherTable'

From everything I can find in SQL Books Online and in the LINQ-to-SQL documentation this should just work.

I've resorted to doing this instead:

context.ExecuteCommand( String.Format( "ALTER TABLE {0} NOCHECK CONSTRAINT {1}", "MyTable", "FK_MyTable_MyOtherTable" ) );

It works, but it sort of defeats the purpose of being able to pass in parameters to .ExecuteCommand().

So is this some kind of quirk in LINQ-to-SQL, SQL Server or am I just really confused?

Any insights would be much appreciated!

Upvotes: 4

Views: 4165

Answers (3)

David Steele
David Steele

Reputation: 3461

Linq to SQL is basically just translating what you have given it. The problem is that you can't have table names as parameters so the following does not work.

DECLARE @TableName varchar

SET @Tablename='MyTable'

SELECT * FROM @TableName

It looks like you already have a solution, and I think it's a good one.

Upvotes: 1

leppie
leppie

Reputation: 117220

You cant use SQL objects as parameters.

So the second version is the only option.

Upvotes: 1

marc_s
marc_s

Reputation: 754468

As you've found out - Linq-to-SQL will quite aggressively try to parametrize your queries. Which is a good thing, in most cases.

But in the case of an ALTER TABLE... statement, things like the table name etc. cannot be parametrized - so therefore, it fails.

I don't know if there's any way of getting Linq-to-SQL to not parametrize certain queries to be executed by ExecuteCommand - for the meantime, I think, statements that don't involve manipulating data (inserting, updating etc.) but rather manipulate the structure of your database, your best bet is to use the String.Format(...) approach to get the actual SQL query as a string, before calling .ExecuteCommand().

Upvotes: 7

Related Questions