C#中DataTable使用带CASE的Select语句报错求助

Hey there! Let's break down why your C# DataTable filter is throwing that error, and how to fix it.

The Root Cause #

The DataTable.Select() method uses DataColumn.Expression syntax, which doesn't support the SQL-style CASE WHEN statement you're trying to use. That's exactly why you're seeing the "Missing operand after 'WHEN' operator" error—this syntax just isn't recognized here.

The Solution #

Your logic is trying to:

• Return all rows when MakerBy.Trim() equals "A" (since MAKER_BY = MAKER_BY is always true)
• Return only rows where MAKER_BY matches the trimmed MakerBy value otherwise

We can rewrite this logic without CASE WHEN using a simple conditional check in C# before building the filter expression:

DataTable dt = (DataTable)ViewState["FilteredData"];
string trimmedMakerBy = MakerBy.Trim();

// Handle the two cases directly in C#
string filterExpression;
if (trimmedMakerBy == "A")
{
    // Return all rows (since MAKER_BY always equals itself)
    filterExpression = "MAKER_BY = MAKER_BY";
}
else
{
    // Escape single quotes to avoid syntax errors if the value contains them
    string safeMakerBy = trimmedMakerBy.Replace("'", "''");
    filterExpression = $"MAKER_BY = '{safeMakerBy}'";
}

dt = dt.Select(filterExpression).CopyToDataTable();

Bonus: Avoiding Syntax Errors #

Notice the Replace("'", "''") part—this is important because if your MakerBy value contains a single quote, it will break the filter expression. Escaping single quotes by doubling them ensures the expression stays valid.

Alternative with IIF (If You Prefer Inline Logic) #

If you want to use an inline conditional in the expression (similar to CASE but supported by DataColumn syntax), you can use the IIF function. Here's how that would look:

string trimmedMakerBy = MakerBy.Trim();
string safeMakerBy = trimmedMakerBy.Replace("'", "''");
string filterExpression = $"IIF('{safeMakerBy}' = 'A', MAKER_BY = MAKER_BY, MAKER_BY = '{safeMakerBy}')";

dt = dt.Select(filterExpression).CopyToDataTable();

Either approach will fix your error and implement your desired filtering logic.

内容的提问来源于stack exchange，提问作者Abhishek Jain