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

Does the insert value need to actually be a String "null" instead of a null object?

Answer 2

have you tried assigning a value to those that are null? Meaning (pseudo):

If address is null then address = "" or If age is < 0 then age = 0

then add it to myList

or you could always use a Ternary operator:

name = name.Length < 1 ? "" : name;
age = age < 1 ? Int32.MinValue : age;

then add it to myList

Answer 3

why not use nullable values?

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

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

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

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

Answer 4

Hey Samuel

In .NET, a null/nothing string does not evaluate to an empty string, i.e. "". If you want "", then that has to be the value of the string, or if you want to represent null/nothing in SQL, you have to manually write out "NULL" if your .NET string is in fact null/nothing.

All the execute command does, is execute a SQL query, provide by you, as a String. it doesn't do anything else special in terms of that SQL string.

So, for the Execute Command to work, you have to pass in a valid SQL string, you have to manually construct the string correctly.

Answer 5

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

https://connect.microsoft.com/VisualStudio/feedback/ViewFeedback.aspx?FeedbackID=305114&amp;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.