.Net Tips : Execute or Perform LINQ query on DataTable Or DataSet

You can execute LINQ query on DataTable Or Specific table on DataSet with the help of the AsEnumerable.

Here is example for this.
In this example we create one datatable and dataset , You can also fill dataset directly from DataAdapter, and we run LINQ query on this datatable to filter results.
The first query is for get records which ID is less than or equal to 2.
The second query is for get records which name starts with "Key" and "Mo".
You can also perform all LINQ queries on datatable and dataset.

C# Examples :

        DataSet ds = new DataSet();
        DataTable dt = new DataTable();
        DataColumn dc;
        DataRow dr;
        ds.DataSetName = "products";
        dt.TableName = "product";

        dc = new DataColumn("product_id",long.MaxValue.GetType());
        dt.Columns.Add(dc);

        dc = new DataColumn("product_name");
        dt.Columns.Add(dc);

        dr = dt.NewRow();
        dr["product_id"] = 1;
        dr["product_name"] = "Monitor";
        dt.Rows.Add(dr);

        dr = dt.NewRow();
        dr["product_id"] = 2;
        dr["product_name"] = "Mouse";
        dt.Rows.Add(dr);

        dr = dt.NewRow();
        dr["product_id"] = 3;
        dr["product_name"] = "KeyBoard";
        dt.Rows.Add(dr);

        dr = dt.NewRow();
        dr["product_id"] = 4;
        dr["product_name"] = "LCD";
        dt.Rows.Add(dr);

        ds.Tables.Add(dt);

        IEnumerable<DataRow> objResult1 = from tbl in dt.AsEnumerable()
                                       where tbl.Field<long>(0) <= 2
                                       select tbl;

        Response.Write("<b>Query Results 1</b>");
        foreach (DataRow row in objResult1)
        {
            Response.Write(string.Format("<br/>Product ID: {0} ,  Product Name: {1}", row.Field<long>(0), row.Field<string>(1)));
        }

        IEnumerable<DataRow> objResult2 = from tbl in ds.Tables[0].AsEnumerable()
                                       let product_name = tbl.Field<string>(1)
                                       where product_name.StartsWith("Key")
                                       || product_name.StartsWith("Mo")
                                       select tbl;

        Response.Write("<br/><br/><b>Query Results 2</b>");
        foreach (DataRow row in objResult2)
        {
            Response.Write(string.Format("<br/>Product ID: {0} ,  Product Name: {1}", row.Field<long>(0), row.Field<string>(1)));
        }

VB.net Examples :

        Dim ds As New DataSet()
        Dim dt As New DataTable()
        Dim dc As DataColumn
        Dim dr As DataRow
        ds.DataSetName = "products"
        dt.TableName = "product"

        dc = New DataColumn("product_id", Long.MaxValue.GetType())
        dt.Columns.Add(dc)

        dc = New DataColumn("product_name")
        dt.Columns.Add(dc)

        dr = dt.NewRow()
        dr("product_id") = 1
        dr("product_name") = "Monitor"
        dt.Rows.Add(dr)

        dr = dt.NewRow()
        dr("product_id") = 2
        dr("product_name") = "Mouse"
        dt.Rows.Add(dr)

        dr = dt.NewRow()
        dr("product_id") = 3
        dr("product_name") = "KeyBoard"
        dt.Rows.Add(dr)

        dr = dt.NewRow()
        dr("product_id") = 4
        dr("product_name") = "LCD"
        dt.Rows.Add(dr)

        ds.Tables.Add(dt)

        Dim objResult1 As IEnumerable(Of DataRow) = From tbl In dt.AsEnumerable()
                                      Where tbl.Field(Of Long)(0) <= 2
                                      Select tbl

        Response.Write("<b>Query Results 1</b>")
        For Each row As DataRow In objResult1
            Response.Write(String.Format("<br/>Product ID: {0} ,  Product Name: {1}", row.Field(Of Long)(0), row.Field(Of String)(1)))
        Next

        Dim objResult2 As IEnumerable(Of DataRow) = From tbl In ds.Tables(0).AsEnumerable()
                                    Let product_name = tbl.Field(Of String)(1)
                                    Where product_name.StartsWith("Key") Or product_name.StartsWith("Mo")
                                    Select tbl

        Response.Write("<br/><br/><b>Query Results 2</b>")
        For Each row As DataRow In objResult2
            Response.Write(String.Format("<br/>Product ID: {0} ,  Product Name: {1}", row.Field(Of Long)(0), row.Field(Of String)(1)))
        Next

Output :

 

This type of C# Tips is very useful in day to day programming life.

Note : Give Us your valuable feedback in comments. Give your suggestions in this article so we can update our articles accordingly that.