Saturday, 4 August 2012

.Net Tips , C# Tips : Union or combine Two LINQ query Results with C# Examples and VB.Net Examples

You can union of two LINQ query or Results.
You can also say combine records from two LINQ query or Results.
We are using "Union" method of  LINQ.
In this example we combine or union records from two DataTable using LINQ.

Here is example of this.
In this example we execute two different LINQ Query on DataTable and find two different results object. After that we combine those records and display.

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) >=3
                                       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)));
        }


        IEnumerable<DataRow> objUnionResult = objResult1.Union(objResult2);

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

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) >= 3
                                      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

        Dim objUnionResult As IEnumerable(Of DataRow) = objResult1.Union(objResult2)

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

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.




2 comments:

  1. Your example contains casting result to IEnumerable. This is an error that you would see retrieving result from real database. Product ID:3 would be printed twice in united data set.
    Your bug was hidden because you are reading the same dataset from memory. Linq could to detect the duplication in this case but reading from database will create 2 different objects for Product ID: 3.
    Don’t use the conversion. The Linq will join both queries sending one request to SQL server retrieving for you correct result.
    One comment: I did not test this assertion. It may be that I am lying.

    ReplyDelete
    Replies
    1. Thanks for you valuable comment.

      I do not think any issue occurred in product id : 3 scenario.

      Delete