Left outer join to perform DataTable Comparison using Linq

Question

Well I am trying to compare two DataTables using LINQ statements

The structure of my tables is something like this

EmpId -- FirstName -- LastName -- DOB

with Emp being the unique key (integer ofcourse) and DOB is Date, rest are strings.

There are two DataTables coming in

SourceTable and TargetTable

The TargetTable might have same number of records or less. Some of the values for FirstName, LastName or DOB might be different.

This is the query without the left outer join which works but without fetching the rest of the Source records.

    var matched = from dtSource in sourceTable.AsEnumerable()
                  join dtTarget in targetTable.AsEnumerable() on dtSource.Field<int>("empid") equals dtTarget.Field<int>("empid")

                  where dtSource.Field<string>("firstname") != dtTarget.Field<string>("firstname")
                  || dtSource.Field<string>("lastname") != dtTarget.Field<string>("lastname")
                  || dtSource.Field<DateTime>("dob") != dtTarget.Field<DateTime>("dob")
                  select dtSource;

I am trying to do a Left Outer Join so that I get the rest of the records not present in the Target DataTable.

This is what I was trying

    var join = from dtSource in source
               join dtTarget in target
                   on dtSource.Field<int>("EmpId") equals dtTarget.Field<int>("EmpId")
               into outer
               where
               dtSource.Field<string>("firstname") != dtTarget.Field<string>("firstname")
                  || dtSource.Field<string>("lastname") != dtTarget.Field<string>("lastname")
                  || dtSource.Field<DateTime>("dob") != dtTarget.Field<DateTime>("dob")

               from dtEmpSal in outer.DefaultIfEmpty()

               select new
               {
                   FirstName = dtSource.Field<string>("FirstName") == null ? "" : dtSource.Field<string>("FirstName"),
                   LastName = dtSource.Field<string>("LastName") == null ? "" : dtSource.Field<string>("LastName"),
                   DOB = dtSource.Field<DateTime>("dob") == null ? DateTime.Today : dtSource.Field<DateTime>("dob")
               };

Ofcourse I am doing it wrong as I cant get this to compile as I cant access dtTarget in the where clause. I am doing something wrong and cant figure out what.

EDIT:

This is how my source DataTable Looks like

Source

and this is how my Target looks like

Target

In my case the result set should include emp records with empIds 1,6,7 and so on. Note 1 is different (last name) and 2,3,4,5 are contained in the target DataTable.

Screencast to show my troubles:

https://www.dropbox.com/s/145ba6oa59fstdc/screencap.wmv

I am finding it hard to debug this :(

score 0 · Answer 1 · answered Apr 15 '14 at 18:43

This is what helped me

public static dynamic GetAllDifferences (DataTable sourceTable,DataTable targetTable)
        {
            var source = sourceTable.AsEnumerable();
            var target = targetTable.AsEnumerable();

            var noMatchInSource = from dtSource in source
                join dtTarget in target on dtSource.Field<int>("empid") equals dtTarget.Field<int>("empid")
                where dtSource.Field<string>("firstname").Equals(dtTarget.Field<string>("firstname"))
                      && dtSource.Field<string>("lastname").Equals(dtTarget.Field<string>("lastname"))
                      && dtSource.Field<DateTime>("dob").Equals(dtTarget.Field<DateTime>("dob"))
                select dtSource;

            var result =
                        from sourceDataRow in sourceTable.AsEnumerable()
                        where !(from noMatchDataRow in noMatchInSource
                                select noMatchDataRow.Field<int>("empid"))
                               .Contains(sourceDataRow.Field<int>("empid"))
                        select sourceDataRow;
                                return result;
        }

Left outer join to perform DataTable Comparison using Linq

1 Answers1