Querying the Data table using LINQ

In this article you will learn Querying the Data table using LINQ.

In normal mode you cannot query the Data table using linq, if you do it means you will get null value.

Solution:

1. Add the System.Data.DataSetExtensions as reference in your project.
   
   
2. Convert the data table as AsEnumerable.
   
   
3. Using anonymous method and try to separate the values as properties or entities.
   
   
4. Now query the above result using LINQ.

Example:

DataRow _dataRow0, _dataRow1, _dataRow2, _dataRow3, _dataRow4, _dataRow5;
    DataTable _datatable = new DataTable();

    _datatable.Columns.Add("Name");
    _datatable.Columns.Add("ID");
    _datatable.Columns.Add("Paid");
    _datatable.Columns.Add("Employed");

    string[] str = { "id", "Name", "A" };

    _dataRow0 = _datatable.NewRow();
    _dataRow0[0] = "Arun";
    _dataRow0[1] = 1;
    _dataRow0[2] = 'Y';
    _dataRow0[3] = 'N';

    _dataRow1 = _datatable.NewRow();
    _dataRow1[0] = "Aruna";
    _dataRow1[1] = 2;
    _dataRow1[2] = 'N';
    _dataRow1[3] = 'N';

    _dataRow2 = _datatable.NewRow();
    _dataRow2[0] = "Arun";
    _dataRow2[1] = 3;
    _dataRow2[2] = 'Y';
    _dataRow2[3] = 'Y';

    _dataRow3 = _datatable.NewRow();
    _dataRow3[0] = "Aswin";
    _dataRow3[1] = 4;
    _dataRow3[2] = 'Y';
    _dataRow3[3] = 'N';

    _dataRow4 = _datatable.NewRow();
    _dataRow4[0] = "Senthil";
    _dataRow4[1] = 5;
    _dataRow4[2] = 'Y';
    _dataRow4[3] = 'N';

    _dataRow5 = _datatable.NewRow();
    _dataRow5[0] = "ArunKUMARI";
    _dataRow5[1] = 5;
    _dataRow5[2] = 'Y';
    _dataRow5[3] = 'N';

    _datatable.Rows.Add(_dataRow0);
    _datatable.Rows.Add(_dataRow1);
    _datatable.Rows.Add(_dataRow2);
    _datatable.Rows.Add(_dataRow3);
    _datatable.Rows.Add(_dataRow4);
    _datatable.Rows.Add(_dataRow5);

    var dt1 = _datatable.AsEnumerable().Select(p => new
    {
        _ID = p.Field<string>("ID"),
        _Name = p.Field<string>("Name"),
        _Paid = p.Field<string>("Paid"),
        _Employed = p.Field<string>("Employed")
    });


    var idr2 = (from xx in dt1
                where xx._Employed == "Y"
                orderby xx._Name
                select xx._Name).Distinct();

    foreach (string a in idr2)
        Console.WriteLine(a);

By using anonymous method secrete the values.

var dt1 = _datatable.AsEnumerable().Select(p => new
{
   _ID = p.Field<string>("ID"),
   _Name = p.Field<string>("Name"),
   _Paid = p.Field<string>("Paid"),
   _Employed = p.Field<string>("Employed")
});

Now using LINQ, query the data as you want.