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

  • Question

  • User-1024101449 posted

    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:44 AM

Answers

  • User1724605321 posted

    Hi gani7787,

    You can try below code sample :

     Dim Unmatched = (From table2 In dt2.AsEnumerable() Where Not dt1.AsEnumerable().Any(Function(f) f.Field(Of String)("sno") = table2.Field(Of String)("sno") AndAlso f.Field(Of String)("name") = table2.Field(Of String)("name")) Select table2)
            If Unmatched.Count() > 0 Then dtUnMatched = Unmatched.CopyToDataTable()

    Best Regards,

    Nan Yu

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Tuesday, June 26, 2018 5:15 AM

All replies

  • User1724605321 posted

    Hi gani7787,

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

    Do you want to show the Country column also in Table 2 ? If yes :

     var Unmatched = (from table2 in dt2.AsEnumerable()
                            where !dt1.AsEnumerable().Any(f => f.Field<string>("sno") == table2.Field<string>("sno") && f.Field<string>("name") == table2.Field<string>("name"))
                            select table2);
                if (Unmatched.Count() > 0)
                    dtUnMatched = Unmatched.CopyToDataTable();

    If no , use :

     var Unmatched = (from table2 in dt2.AsEnumerable()
                                 where !dt1.AsEnumerable().Any(f => f.Field<string>("sno") == table2.Field<string>("sno") && f.Field<string>("name") == table2.Field<string>("name"))
                                 select new {
                                     sno= table2.Field<string>("sno"),
                                     name = table2.Field<string>("name"),
                                 }).ToList();

    Then loop the list and copy value to datatable .

    Best Regards,

    Nan Yu

    Friday, June 22, 2018 5:55 AM
  • User-1024101449 posted

    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:08 AM
  • User-1024101449 posted

    any update.

    i need query in both matching and unmatching records with vb.net

    Friday, June 22, 2018 1:12 PM
  • User1724605321 posted

    Hi gani7787

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

    What do you mean by same query ?It seems that you have provide the codes to return the matching rows :

    //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();

    You give the c# demo and now you want the vb.net codes ? LinQ in vb is not quite different with c# :

    https://docs.microsoft.com/en-us/dotnet/visual-basic/programming-guide/language-features/linq/introduction-to-linq 

    You can refer to above article and try to translate that .

    Best Regards,

    Nan Yu

    Monday, June 25, 2018 2:19 AM
  • User-1024101449 posted

    i am getting error in "f.Field(Of String)("sno")"

    Error : Expression is a value and therefore cannot be the target of an assignment.

    Dim dtNewRecords = (From table2 In dtSecond.AsEnumerable()
    Where Not dtFirst.AsEnumerable().Any(Sub(f) f.Field(Of String)("sno") = table2.Field(Of String)("sno") AndAlso f.Field(Of String)("name") = table2.Field(Of String)("name"))
    Select table2)

    what is the problem in the sysntax..

    Monday, June 25, 2018 10:18 AM
  • User1724605321 posted

    Hi gani7787,

    You can try below code sample :

     Dim Unmatched = (From table2 In dt2.AsEnumerable() Where Not dt1.AsEnumerable().Any(Function(f) f.Field(Of String)("sno") = table2.Field(Of String)("sno") AndAlso f.Field(Of String)("name") = table2.Field(Of String)("name")) Select table2)
            If Unmatched.Count() > 0 Then dtUnMatched = Unmatched.CopyToDataTable()

    Best Regards,

    Nan Yu

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Tuesday, June 26, 2018 5:15 AM