LINQ to SQL: ExecuteQuery not working when performing a parameterized query

Question

I have a weird problem with ExecuteQuery in that it isn't working when performing a parameterized query.

The following returns 1 record:

db.ExecuteQuery<Member>(@"SELECT * 
                          FROM Member
                          INNER JOIN aspnet_Users ON Member.user_id = aspnet_Users.UserId
                          WHERE [aspnet_Users].[UserName] = 'Marina2'");

However, the parameterized version returns no results:

db.ExecuteQuery<Member>(@"SELECT * 
                          FROM Member
                          INNER JOIN aspnet_Users ON Member.user_id = aspnet_Users.UserId
                          WHERE [aspnet_Users].[UserName] = '{0}'", "Marina2");

What am I doing wrong?

Argh, found the problem. It's the single quotes around {0}. I would still like to know why this causes a problem, however. — ajbeaven, May 27 '10 at 04:46
Having the single quotes causes a problem because Linq to SQL is determining the type of parameter and putting them in for you, if required. Thus removing the need to worry about whether or not to add them. — Jeff Schumacher, May 27 '10 at 04:49

Kelsey · Accepted Answer · 2010-05-27T04:52:28.477

Try:

db.ExecuteQuery<Member>(@"SELECT *  
    FROM Member 
    INNER JOIN aspnet_Users ON Member.user_id = aspnet_Users.UserId 
    WHERE [aspnet_Users].[UserName] = {0}", "Marina2");

Notice no quotes on the param. Linq to SQL will automatically know to format it with the quotes.

As per MSDN:

The parameters are expressed in the query text by using the same curly notation used by Console.WriteLine() and String.Format(). In fact, String.Format() is actually called on the query string you provide, substituting the curly braced parameters with generated parameter names such as @p0, @p1 …, @p(n).

So based on that if you left the quotes in you would have been matching on [Username] = '@p0' but you could run profiler and capture the exact SQL to verify.

LINQ to SQL: ExecuteQuery not working when performing a parameterized query

1 Answers1