How to evaluate a DataColumn expression faster?

Question

I created a class that's responsible to evaluate an expression values for the row when I need it but the problem that when my methods works very slow in big datatables.

public static class RowExpressionEvaluator
{

    public static object EvaluateValue(DataRow row, string expression, Type outputType)
    {
        if (row == null) throw new ArgumentNullException(nameof(row));
        return EvaluateValue(row.Table, row, expression, outputType);
    }


    private static object EvaluateValue(DataTable table, DataRow row, string expression, Type outputType)
    {
        if (table == null) throw new ArgumentNullException(nameof(table));
        if (row == null) throw new ArgumentNullException(nameof(row));
        if (string.IsNullOrEmpty(expression)) throw new ArgumentException("Expression cannot be null or empty.", nameof(expression));

        if (row.RowState.Equals(DataRowState.Detached)) throw new ArgumentException(@"The rowState is detached.");



        object result = null;

        using (var tempColumn = new DataColumn("Exp", outputType))
        {
            table.Columns.Add(tempColumn);

            tempColumn.Expression = expression;

            if (!row[tempColumn.ColumnName].IsDbNull())
                result = row[tempColumn.ColumnName];

            table.Columns.Remove(tempColumn);
        }

        return result;
    }
}

This code works fine but its slow when the datatable contains big data

I tried to improve this code so when I have many expressions to evaluate then when I finish I dispose it it works better but still needs to improve because I think when I add the column with an expression the datatable evaluate it for all the rows but I need to evaluate the value only for the passed row.

like this one :

public sealed class BetterRowExpressionEvaluator :IDisposable
{
    private readonly DataRow _row;

    private readonly DataColumn _expressionColumn;


    public BetterRowExpressionEvaluator(DataRow row)
    {
        _row = row ?? throw new ArgumentNullException(nameof(row));
        if (row.RowState.Equals(DataRowState.Detached)) throw new ArgumentException(@"The rowState is detached.");

        _expressionColumn = new DataColumn("Expression",typeof(object));


        DataTable table = _row.Table;

        table.Columns.Add(_expressionColumn);


    }


    public object Evaluate(string expression)
    {
        if (string.IsNullOrEmpty(expression)) throw new ArgumentException("Value cannot be null or empty.", nameof(expression));

        _expressionColumn.Expression = expression;

        return !_row[_expressionColumn.ColumnName].IsDbNull() ? _row[_expressionColumn.ColumnName] : null;
    }


    public void Dispose()
    {
        if (_expressionColumn == null) return;

        DataTable table = _row.Table;

        table.Columns.Remove(_expressionColumn);

        _expressionColumn?.Dispose();

    }
}

I did something like this to clone the row and it's parent and child relations so when I evaluate the expressions it comes faster and this is what I did :

    private DataSet CloneTableWithRelations(DataRow row)
    {
        var dataset = new DataSet("EvaluationDataSet") {Locale = CultureInfo.InvariantCulture};

        dataset.Tables.Add(row.Table.Clone());
        dataset.Tables[row.Table.TableName].ImportRow(row);

        foreach (DataRelation parentRelation in row.Table.ParentRelations)
        {
            string relationName = parentRelation.RelationName;

            DataTable parentTable = parentRelation.ParentTable;

            // clone the parent table
            dataset.Tables.Add(parentTable.Clone());

            // copy the parent rows related only to the passed row
            DataRow parentRow= row.GetParentRow(relationName);
            dataset.Tables[parentTable.TableName].ImportRow(parentRow);

            DataColumn parentColumn=parentRelation.ParentColumns[0];

            DataColumn childColumn=parentRelation.ChildColumns[0];

            dataset.Relations.Add(relationName, parentColumn, childColumn,false);

        }

        foreach (DataRelation dataRelation in row.Table.ChildRelations)
        {
            DataTable childTable = dataRelation.ChildTable;

            // clone the parent table
            dataset.Tables.Add(childTable.Clone());

            // copy the parent rows related only to the passed row
            foreach (DataRow childRow in row.GetChildRows(dataRelation.RelationName))
            {
                dataset.Tables[childTable.TableName].ImportRow(childRow);
            }

            DataColumn parentColumn=dataRelation.ParentColumns[0];

            DataColumn childColumn=dataRelation.ChildColumns[0];

            dataset.Relations.Add(dataRelation.RelationName, parentColumn, childColumn,false);
        }

        return dataset;
    }

is there a better way to do it and more reliable way ?

Finally It works much better

when I clone the row and its parent and child data

using this class I've created

  public class RowCloneHandler
  {

    private readonly DataRow _row;

    [System.Diagnostics.CodeAnalysis.SuppressMessage("Globalization", "CA1303:Do not pass literals as localized parameters", Justification = "<Pending>")]
    public RowCloneHandler(DataRow row)
    {
        _row = row ?? throw new ArgumentNullException(nameof(row));

        if (row.RowState.Equals(DataRowState.Detached)) throw new ArgumentException("The rowState is detached.");


    }

    public DataSet CloneToDataSet()
    {
        var ClonedDataset = new DataSet { Locale = CultureInfo.InvariantCulture };

        DataTable clonedMainTable = _row.Table.Clone();

        ClonedDataset.Tables.Add(clonedMainTable);
        ClonedDataset.Tables[_row.Table.TableName].ImportRow(_row);


        CloneParentTablesToDataset(ClonedDataset, clonedMainTable);
        CloneChildTablesToDataSet(ClonedDataset, clonedMainTable);

        return ClonedDataset;
    }

    private  void CloneChildTablesToDataSet(DataSet clonedDataset,  DataTable clonedMainTable)
    {
        foreach (DataRelation dataRelation in _row.Table.ChildRelations)
        {
            DataTable childTable = dataRelation.ChildTable;

            // clone the parent table
            DataTable clonedChildTable = childTable.Clone();

            // copy the parent rows related only to the passed row

            foreach (DataRow childRow in _row.GetChildRows(dataRelation.RelationName))
            {
                clonedChildTable.ImportRow(childRow);
            }

            clonedDataset.Tables.Add(clonedChildTable);

            DataColumn parentColumn = clonedMainTable.Columns[dataRelation.ParentColumns[0].ColumnName];

            DataColumn childColumn = clonedChildTable.Columns[dataRelation.ChildColumns[0].ColumnName];

            clonedDataset.Relations.Add(dataRelation.RelationName, parentColumn, childColumn, false);
        }
    }

    private  void CloneParentTablesToDataset(DataSet clonedDataset,  DataTable clonedMainTable)
    {
        foreach (DataRelation parentRelation in _row.Table.ParentRelations)
        {

            DataTable parentTable = parentRelation.ParentTable;

            // clone the parent table
            DataTable clonedParentTable = parentTable.Clone();

            // copy the parent rows related only to the passed row
            DataRow parentRow = _row.GetParentRow(parentRelation.RelationName);

            clonedParentTable.ImportRow(parentRow);

            clonedDataset.Tables.Add(clonedParentTable);

            DataColumn parentColumn = clonedParentTable.Columns[parentRelation.ParentColumns[0].ColumnName];

            DataColumn childColumn = clonedMainTable.Columns[parentRelation.ChildColumns[0].ColumnName];

            clonedDataset.Relations.Add(parentRelation.RelationName, parentColumn, childColumn, false);

        }
    }



}

Theodor Zoulias · Accepted Answer · 2019-07-29T14:13:47.733

You need to create a clone of your DataTable, import the DataRow to the cloned DataTable, and then add the computed column in the cloned DataTable. Here are some extension methods that do exactly that.

Update: I revised the code to take into account the existing relations with other tables. The code became much more complicated because now the cloned table must be placed inside the existing DataSet, and the existing relations must be also cloned, and temporary renamed. The cloned relations are created without constraints, so hopefully the performance will not be negatively affected.

public static class DataRowExtensions
{
    public static object Compute(this DataRow dataRow, string expression)
    {
        using (var clonedDT = CloneDataTable(dataRow))
        {
            clonedDT.ImportRow(dataRow);
            var clonedRow = clonedDT.Rows[0];
            var dataColumn = clonedDT.Columns.Add(null, typeof(object), expression);
            return clonedRow[dataColumn];
        }
    }

    public static T Compute<T>(this DataRow dataRow, string expression)
    {
        using (var clonedDT = CloneDataTable(dataRow))
        {
            clonedDT.ImportRow(dataRow);
            var clonedRow = clonedDT.Rows[0];
            var dataColumn = clonedDT.Columns.Add(null, typeof(T), expression);
            return clonedRow.Field<T>(dataColumn);
        }
    }

    public static T? ComputeNullable<T>(this DataRow dataRow, string expression)
        where T : struct
    {
        using (var clonedDT = CloneDataTable(dataRow))
        {
            clonedDT.ImportRow(dataRow);
            var clonedRow = clonedDT.Rows[0];
            var dataColumn = clonedDT.Columns.Add(null, typeof(T), expression);
            return clonedRow.Field<T?>(dataColumn);
        }
    }

    private static DataTable CloneDataTable(DataRow dataRow)
    {
        var dataTable = dataRow.Table;
        var dataSet = dataRow.Table.DataSet;
        if (dataSet == null) return dataTable.Clone();
        var clonedDT = dataSet.Tables.Add();
        foreach (DataColumn column in dataTable.Columns)
        {
            clonedDT.Columns.Add(column.ColumnName, column.DataType);
        }
        var relationsAdded = new List<
            (DataRelation Cloned, DataRelation Original)>();
        foreach (var relation in dataTable.ParentRelations
            .Cast<DataRelation>().ToArray())
        {
            var relationName = relation.RelationName;
            relation.RelationName = Guid.NewGuid().ToString();
            var clonedColumns = relation.ChildColumns
                .Select(c => clonedDT.Columns[c.ColumnName]).ToArray();
            var clonedRelation = dataSet.Relations.Add(relationName,
                relation.ParentColumns, clonedColumns, createConstraints: false);
            relationsAdded.Add((clonedRelation, relation));
        }
        foreach (var relation in dataTable.ChildRelations
            .Cast<DataRelation>().ToArray())
        {
            var relationName = relation.RelationName;
            relation.RelationName = Guid.NewGuid().ToString();
            var clonedColumns = relation.ParentColumns
                .Select(c => clonedDT.Columns[c.ColumnName]).ToArray();
            var clonedRelation = dataSet.Relations.Add(relationName,
                clonedColumns, relation.ChildColumns, createConstraints: false);
            relationsAdded.Add((clonedRelation, relation));
        }
        clonedDT.Disposed += (s, e) => // Cleanup
        {
            clonedDT.Rows.Clear();
            foreach (var entry in relationsAdded)
            {
                dataSet.Relations.Remove(entry.Cloned);
                entry.Original.RelationName = entry.Cloned.RelationName;
            }
            clonedDT.Columns.Clear();
            dataSet.Tables.Remove(clonedDT);
        };
        return clonedDT;
    }
}

Usage example:

var dt = new DataTable();
dt.Columns.Add("Price", typeof(decimal));
dt.Rows.Add(10);
decimal doublePrice = dt.Rows[0].Compute<decimal>("Price * 2");
Console.WriteLine(doublePrice);

Output:

20

the problem that I have some expressions that depends on the relations on the dataset will that work also fine ? I'll test your code — Shehab, Jul 29 '19 at 08:35
It didn't work maybe if I could get the child and parent tables related to this row. — Shehab, Jul 29 '19 at 08:51
Because I have some expressions like this :sum(Child([relationName]).[Net]) — Shehab, Jul 29 '19 at 08:51
I edited my question and I added how I'm trying to clone the row and its relations — Shehab, Jul 29 '19 at 09:20
Now using your extensions it works much better. Thank you. if you have any suggestions please tell me. thanks again — Shehab, Jul 29 '19 at 11:41

How to evaluate a DataColumn expression faster?

1 Answers1