DataTable - Dynamic Linq OrderBy using Lambda expressions

Question

I'm getting a collection of records in a DataTable and binding it to a grid control. Before binding it I'm sorting the data based on few conditions. For brevity I'm will explain a test scenario.

I've two fields Category and Country. I want to first sort the records based on category and then by country. But the catch here is I want to push all the empty category values to the end and then sort based on the alphabetical order.

For that I'm doing -

var rows = dt.AsEnumerable()
        .OrderBy(r => string.IsNullOrEmpty(Convert.ToString(r["Category"]))) //push empty values to bottom
        .ThenBy(r => Convert.ToString(r["Category"]))
        .ThenBy(r => Convert.ToString(r["Country"]))

But now, the fields based on which I need to sort, is dynamic which I'm having in an array.

How can I use the lambda expressions to order the records dynamically based on the fields? (pushing the empty values to the end)

Answer 1

I assume the array you're talking about is an array of strings.

var columns = new string[] { "Category", "Country" };

var rows = dt.AsEnumerable().OrderBy(x => 0);
foreach(var columnName in columns)
{
    rows = rows.ThenBy(r => string.IsNullOrEmpty(Convert.ToString(r[category])))
               .ThenBy(r => Convert.ToString(r[category]));
}

Because LINQ uses deferred execution, your query will not be evaluated until you actually need results. That's why you can construct it in multiple steps like in the example above.