linq - DataTable select method with single quote conflict C#

Question

I recently found when I do a LINQ select under a field that contains an apostrophe, it makes my application to throw an exception.

DataRow[] newDr = this.ds.Tables["TableName"].Select("Name = '" + drData["Name"].ToString() + "'");

If drData["Name"] = "The business's money"

I got an exception of "Syntax error: Missing operand after 'S' operator "

Can anyone tell me how to preserve it and no replace it or remove it, please?

Answer 1

You have to escape the single quote to 2 single quotes, there may be other special characters so you have to take care of them:

DataRow[] newDr = this.ds.Tables["TableName"]
                         .Select(string.Format("Name ='{0}'",drData["Name"].ToString().Replace("'","''")));

More examples on escaping special characters in a filter string (used in Select method): Row filter string examples