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?

Answer 1

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.