DataColumn.Expression referencing another column

Question

I am importing values from a file to a DataTable and one of the columns is a location column and unfortunately it contains both the latitude and longitude values, which requires me to split them.

Ideally, I would like to have two computed, Latitude and Longitude however I don't know how to write the expression for the DataColumn.Expression property.

private void CalcColumns()
{
    DataTable table = new DataTable ();

    // Create the first column.
    DataColumn locationColumn = new DataColumn();
    locationColumn.ColumnName = "Location (Lat, Long)";
    locationColumn.DefaultValue = "40.00, -60.00";

    // Create the second, calculated, column.
    DataColumn latColumn = new DataColumn();
    latColumn.ColumnName = "Latitude";
    latColumn.Expression = ""; // what expression goes here

    // Create third column.
    DataColumn longColumn = new DataColumn();
    longColumn.ColumnName = "Longitude";
    longColumn.Expression = ""; // what expression goes here

    // Add columns to DataTable.
    table.Columns.Add(locationColumn);
    table.Columns.Add(latColumn);
    table.Columns.Add(longColumn);
    DataRow row = table.NewRow();
    table.Rows.Add(row);
    DataView view = new DataView(table);
    dataGrid1.DataSource = view;
}

I tried Expression = "SUBSTRING(Location (Lat, Long),1,5)" but that gave me an error: System.Data.EvaluateException: 'The expression contains undefined function call Location().'

So, tried Expression = "SUBSTRING('Location (Lat, Long)',1,5)" but that gave me Locat.

https://learn.microsoft.com/en-us/dotnet/api/system.data.datacolumn.expression?view=net-8.0 An example from the Microsoft documentation shows:

private void CalcColumns()
{
    DataTable table = new DataTable ();

    // Create the first column.
    DataColumn priceColumn = new DataColumn();
    priceColumn.DataType = System.Type.GetType("System.Decimal");
    priceColumn.ColumnName = "price";
    priceColumn.DefaultValue = 50;

    // Create the second, calculated, column.
    DataColumn taxColumn = new DataColumn();
    taxColumn.DataType = System.Type.GetType("System.Decimal");
    taxColumn.ColumnName = "tax";
    taxColumn.Expression = "price * 0.0862";

    // Create third column.
    DataColumn totalColumn = new DataColumn();
    totalColumn.DataType = System.Type.GetType("System.Decimal");
    totalColumn.ColumnName = "total";
    totalColumn.Expression = "price + tax";

    // Add columns to DataTable.
    table.Columns.Add(priceColumn);
    table.Columns.Add(taxColumn);
    table.Columns.Add(totalColumn);

    DataRow row = table.NewRow();
    table.Rows.Add(row);
    DataView view = new DataView(table);
    dataGrid1.DataSource = view;
}

So, I should be able to refer to the column name as: Location (Lat, Long) but that threw the error.

How do I write the expression to get the value I'm after?

Answer 1

It's because of the parentheses, need an escape character. IRC `` or [] will work (may depend on .NET version), so you'd want:

"SUBSTRING([Location (Lat, Long)],1,5)" 

or

"SUBSTRING(`Location (Lat, Long)`,1,5)"

If you've got more references to that column though, may consider a more C# friendly name during the script (like LocationLatLong) then just fix at the final output.