Linq ExecuteCommand doesn't understand nulls

Question

I'm having a problem when passing nulls to a ExecuteCommand() method using linq. My code is similar to the one that follows:

    public void InsertCostumer(string name, int age, string address)
    {
        List<object> myList = new List<object>();

        myList.Add(name);
        myList.Add(age);
        myList.Add(address);

        StringBuilder queryInsert = new StringBuilder();
        queryInsert.Append("insert into Customers(name, address) values ({0}, {1}, {2})");

        this.myDataContext.ExecuteCommand(queryInsert.ToString(), myList.ToArray());
    }

But, when a parameter is null (address, for instance), I get the following error: "A query parameter cannot be of type 'System.Object'."

The error doesn't occur if no parameters are null. I know the design in my example is a little poor, I just created a simplified example to focus on the problem. Any suggestions?

Answer 1

This is a known bug and Microsoft does not intend to fix it...

https://connect.microsoft.com/VisualStudio/feedback/ViewFeedback.aspx?FeedbackID=305114&wa=wsignin1.0

The work around is to either:

1. Drop into ADO.NET and execute the SQL Command directly
2. Format the string you're executing yourself and call ExecuteCommand with an empty object array (new object[0])

The second isn't a good idea as it opens you up to SQL inject attacks, but its a quick hack.

Answer 2

Kevin is right.

an example of his work around #1 in LinqPad. Need this (Object)s??DBNull.Value

string s = null; 

//ExecuteCommand("insert into T(C1) values({0})", s); //Exception

SqlCommand cmd= new SqlCommand(){
    CommandText = "insert into T(C1) values(@P0)",
    Connection = new SqlConnection(this.Connection.ConnectionString),
};
//cmd.Parameters.AddWithValue("@P0", s); //SqlException
cmd.Parameters.AddWithValue("@P0", (Object)s??DBNull.Value);
cmd.Connection.Open();
cmd.ExecuteNonQuery();
cmd.Connection.Close();

Ts.OrderByDescending(t=>t.ID).Take(1).Dump();