How to Do, filtering operation manually on Complex DataTable

Question

I have a DataTable in Home Controller as follows:

public DataTable GetTable()
{
    DataTable table = new DataTable();
    table.Columns.Add("Dosage", typeof(int));
    table.Columns.Add("Drug", typeof(string));
    table.Columns.Add("Patient", typeof(Info));
    table.Columns.Add("Date", typeof(DateTime));

    table.Rows.Add(25, "Indocin", new Info("India"), DateTime.Now);
    table.Rows.Add(50, "Enebrel", new Info("UK"), DateTime.Now);
    table.Rows.Add(10, "Hydralazine", new Info("Bhutan"), DateTime.Now);
    table.Rows.Add(21, "Combivent", new Info("India"), DateTime.Now);
    table.Rows.Add(100, "Dilantin", new Info("GreenLand"), DateTime.Now);
    return table;
}

Info class as follows

public class Info
{
    public string Address { get; set; }
    public Info(string Add) {
        this.Address = Add;
    }
}

Now, i want to do the filtering operation based on Address Field, i.e Patient.Address

Here, Patient is object of Info class

I fetched the information regarding each columns as follows I have defined the functionalities in the method "DataTableExtensions.DataTableSerialize(data)"

DataTableExtensions.DataTableSerialize(data)
Count = 4
    [0]: {[Dosage, System.Nullable`1[System.Int32]]}
    [1]: {[Drug, System.String]}
    [2]: {[Patient, MvcApplication66.Controllers.HomeController+Info]}
    [3]: {[Date, System.Nullable`1[System.DateTime]]}

But, i need to filter the DataTable based on Patient.Address

I got the error in the following line.

DataTableExtensions.DataTableSerialize(data)[ColumnName]

An unhandled exception of type 'System.Collections.Generic.KeyNotFoundException' occurred in mscorlib.dll

Additional information: The given key was not present in the dictionary.

Where i commit the mistake.

Is it possible to do filtering operation in Complex Data Table.

Answer 1

You can filter the DataTable using Linq

//fill the datatable
DataTable dt = GetTable();

//filter the datatable with Linq
var filteredRows = dt.AsEnumerable().Where(x => x.Field<Info>("Patient").Address == "India");

//check if there are any rows before calling CopyToDataTable,
//otherwise you'll get a "The source contains no DataRows" error
if (filteredRows.Any())
{
    DataTable dtFiltered = filteredRows.CopyToDataTable();
}

If you are 100% sure that the filtered table will contain rows, you can call CopyToDataTable() directly.

DataTable dtFiltered = dt.AsEnumerable().Where(x => x.Field<Info>("Patient").Address == "Bhutan").CopyToDataTable();

But I recommend you swich from DataTable To Lists, this will be easier in the future when Info gets more complex.

List<Drugs> patients = new List<Drugs>();

public class Drugs
{
    public int Dosage { get; set; }
    public string Drug { get; set; }
    public Info Patient { get; set; }
    public DateTime Date { get; set; }
}

Answer 2

Complex Data Table can be filtered and i have formed the predicate for this requirements.

var memberNames = memberPath.Split('.');
Expression value = Expression.Call(
    typeof(DataRowExtensions), "Field", new Type[] { columnType },
    source, Expression.Constant(memberNames[0]));
for (int i = 1; i < memberNames.Length; i++)
    value = Expression.PropertyOrField(value, memberNames[i]);
return value;

Finally predicate formed as bExp = {(DataRow.Field("Patient").Address.ToLower() == "india")}

so, query string will be as data = {System.Data.EnumerableRowCollection`1[System.Data.DataRow].Where(DataRow => (DataRow.Field("Patient").Address.ToLower() == "india"))}

Now, i can able to fetch the data.

For more details :Please refer to the below link

How to create (predicate) for fetching data from DataTable using Express.call method