How to get all unmatched records from table2 uisng LINQ

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

Answer 1

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

Answer 2

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..

Answer 3

User-1024101449 posted

any update.

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

Answer 4

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

Answer 5

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..

Answer 6

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