none
How to get all unmatched records from table2 uisng LINQ RRS feed

  • Question

  • How to get all unmatched records from table2 uisng LINQ 

    below is my code...

     protected void Button1_Click(object sender, EventArgs e)
        
        {

            DataTable dt1 = new DataTable();
            DataTable dt2 = new DataTable();
            DataTable dtMatched = new DataTable();
            DataTable dtUnMatched = new DataTable();

            dt1.Columns.Add("Sno");
            dt1.Columns.Add("name");

            DataRow workRow = dt1.NewRow();
            workRow["Sno"] = "1";
            workRow["name"] = "name1";
            dt1.Rows.Add(workRow);
            workRow = dt1.NewRow();
            workRow["Sno"] = "2";
            workRow["name"] = "name2";
            dt1.Rows.Add(workRow);
            workRow = dt1.NewRow();
            workRow["Sno"] = "3";
            workRow["name"] = "name3";
            dt1.Rows.Add(workRow);
          

            dt2.Columns.Add("Sno");
            dt2.Columns.Add("name");
            dt2.Columns.Add("Country");
            workRow = dt2.NewRow();
            workRow["Sno"] = "1";
            workRow["name"] = "name1";
            workRow["Country"] = "India1";
            dt2.Rows.Add(workRow);
            workRow = dt2.NewRow();
            workRow["Sno"] = "2";
            workRow["name"] = "name22";
            workRow["Country"] = "India2";
            dt2.Rows.Add(workRow);
            workRow = dt2.NewRow();
            workRow["Sno"] = "3";
            workRow["name"] = "name3";
            workRow["Country"] = "India3";
            dt2.Rows.Add(workRow);
            workRow = dt2.NewRow();
            workRow["Sno"] = "4";
            workRow["name"] = "name4";
            workRow["Country"] = "India4";
            dt2.Rows.Add(workRow);

            //Matching Records
            var matched = from table1 in dt1.AsEnumerable()
                          join table2 in dt2.AsEnumerable() on table1.Field<string>("sno") equals table2.Field<string>("sno")
                          where table1.Field<string>("name") == table2.Field<string>("name")
                          select table2;
            if (matched.Count() > 0)
                dtMatched = matched.CopyToDataTable();

            //UnMatching Records
            var Unmatched = from table1 in dt1.AsEnumerable()
                          join table2 in dt2.AsEnumerable() on table1.Field<string>("sno") equals table2.Field<string>("sno")
                          where table1.Field<string>("name") == table2.Field<string>("name")
                          select table2;
            if (matched.Count() > 0)
                dtUnMatched = matched.CopyToDataTable();
            
        }

    I am getting count only one from table2 (i.e recrod "2").

    But, i have two records 2 & 4 from table2.

    Is there any option to get all unmatched rows from table2..?

    My final unmatched rows should be

    SNo   Name
    -----    ---------
    2        name22
    4        name4
    Thursday, June 21, 2018 7:46 AM

Answers

  • Check this example:

    DataTable dt1 = new DataTable();
    DataTable dt2 = new DataTable();
    DataTable dtUnMatched = new DataTable(); ;
    
    dt1.Columns.Add( "Sno" );
    dt1.Columns.Add( "name" );
    
    dt1.Rows.Add( "1", "name1" );
    dt1.Rows.Add( "2", "name2" );
    dt1.Rows.Add( "3", "name3" );
    
    
    dt2.Columns.Add( "Sno" );
    dt2.Columns.Add( "name" );
    dt2.Columns.Add( "Country" );
    
    dt2.Rows.Add( "1", "name1", "India1" );
    dt2.Rows.Add( "2", "name22", "India2" );
    dt2.Rows.Add( "3", "name3", "India3" );
    dt2.Rows.Add( "4", "name4", "India4" );
    
    var Unmatched = from table2 in dt2.AsEnumerable()
                    join table1 in dt1.AsEnumerable() on table2.Field<string>( "sno" ) equals table1.Field<string>( "sno" )
                    into j
                    from t1 in j.DefaultIfEmpty()
                    where t1 == null || t1.Field<string>( "name" ) != table2.Field<string>( "name" )
                    select table2;
    
    if( Unmatched.Any() )
    {
        dtUnMatched = Unmatched.CopyToDataTable();
    }
    

    • Marked as answer by Gani tpt Thursday, June 28, 2018 8:43 AM
    Thursday, June 21, 2018 9:04 AM
  • Hi Gani,

    It may be due to the duplicate rows in dt1(same "sno" number but various names). You should process variable Unmatched further.

                if (Unmatched.Any())
                {              
                    var UnMatchedFilter = from table in Unmatched.Distinct().AsEnumerable()
                                  select table;
                    if (UnMatchedFilter.Any())
                    {
                        dtUnMatched = UnMatchedFilter.CopyToDataTable();
                    }
                }

    Regards,

    Dolen


    MSDN Community Support Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    • Marked as answer by Gani tpt Thursday, June 28, 2018 8:43 AM
    Monday, June 25, 2018 5:55 AM

All replies

  • Check this example:

    DataTable dt1 = new DataTable();
    DataTable dt2 = new DataTable();
    DataTable dtUnMatched = new DataTable(); ;
    
    dt1.Columns.Add( "Sno" );
    dt1.Columns.Add( "name" );
    
    dt1.Rows.Add( "1", "name1" );
    dt1.Rows.Add( "2", "name2" );
    dt1.Rows.Add( "3", "name3" );
    
    
    dt2.Columns.Add( "Sno" );
    dt2.Columns.Add( "name" );
    dt2.Columns.Add( "Country" );
    
    dt2.Rows.Add( "1", "name1", "India1" );
    dt2.Rows.Add( "2", "name22", "India2" );
    dt2.Rows.Add( "3", "name3", "India3" );
    dt2.Rows.Add( "4", "name4", "India4" );
    
    var Unmatched = from table2 in dt2.AsEnumerable()
                    join table1 in dt1.AsEnumerable() on table2.Field<string>( "sno" ) equals table1.Field<string>( "sno" )
                    into j
                    from t1 in j.DefaultIfEmpty()
                    where t1 == null || t1.Field<string>( "name" ) != table2.Field<string>( "name" )
                    select table2;
    
    if( Unmatched.Any() )
    {
        dtUnMatched = Unmatched.CopyToDataTable();
    }
    

    • Marked as answer by Gani tpt Thursday, June 28, 2018 8:43 AM
    Thursday, June 21, 2018 9:04 AM
  • Thanks...

    Shall i use same for matching record..?

    Also tell us to convert in to vb.net the below code..

    var Unmatched = from table2 in dt2.AsEnumerable()
                    join table1 in dt1.AsEnumerable() on table2.Field<string>( "sno" ) equals table1.Field<string>( "sno" )
                    into j
                    from t1 in j.DefaultIfEmpty()
                    where t1 == null || t1.Field<string>( "name" ) != table2.Field<string>( "name" )
                    select table2

    when i use into j, getting error in vb.net...?

    • Edited by Gani tpt Thursday, June 21, 2018 9:49 AM vb.net code..into j
    Thursday, June 21, 2018 9:20 AM
  • In VB:

    Dim Unmatched = From table2 In dt2.AsEnumerable()
                    Group Join table1 In dt1.AsEnumerable() On table2.Field(Of String)("sno") Equals table1.Field(Of String)("sno")
                    Into j = Group
                    From t1 In j.DefaultIfEmpty()
                    Where t1 Is Nothing OrElse t1.Field(Of String)("name") <> table2.Field(Of String)("name")
                    Select table2
    

    Thursday, June 21, 2018 10:16 AM
  • Thanks dear..

    What about matching records..

    shall i use the below conditions..

     Where t1 Is Nothing OrElse t1.Field(Of String)("name")= table2.Field(Of String)("name")

    I want only matching records (duplicate rows) from both tables..


    Thursday, June 21, 2018 10:37 AM
  • Hi Gani,

    Yes, if you want matching records, can use the conditions.

    Where t1 Is Nothing OrElse t1.Field(Of String)("name")= table2.Field(Of String)("name")

    Alternate, you can also use below conditions to get matching records. Since we don't need to detect the rows whose value is null.

    Dim Unmatched = From table2 In dt2.AsEnumerable()
                    Group Join table1 In dt1.AsEnumerable() On table2.Field(Of String)("sno") Equals table1.Field(Of String)("sno")
                    Into j = Group
                    From t1 In j
                    Where t1.Field(Of String)("name") = table2.Field(Of String)("name")
                    Select table2

    Regards,

    Dolen



    MSDN Community Support Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Friday, June 22, 2018 9:05 AM
  • Any update...

    is it possible to get matching rows (duplicate rows) for the same query.

    I need query in vb.net both in matching (duplicate rows) and unmatching..

    Friday, June 22, 2018 9:09 AM
  • Thanks For your help...

    When i compare my Sql table, unmatching records showing same rows will be repeating multiple times.

    how we can avoid repeating rows multiple times..?

    if this is ok, then mine is over...

    Friday, June 22, 2018 1:10 PM
  • Hi Gani,

    It may be due to the duplicate rows in dt1(same "sno" number but various names). You should process variable Unmatched further.

                if (Unmatched.Any())
                {              
                    var UnMatchedFilter = from table in Unmatched.Distinct().AsEnumerable()
                                  select table;
                    if (UnMatchedFilter.Any())
                    {
                        dtUnMatched = UnMatchedFilter.CopyToDataTable();
                    }
                }

    Regards,

    Dolen


    MSDN Community Support Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    • Marked as answer by Gani tpt Thursday, June 28, 2018 8:43 AM
    Monday, June 25, 2018 5:55 AM
  • Thank you dear..

    It's charming...

    Thursday, June 28, 2018 8:43 AM