Lookup values between data tables

Question

I would like some help, preferably sample code and a walkthrough on how I can lookup values between two data tables and create a new datatable with the merged values. I was advised that I need to use LINQ but I couldn't understand the examples that I saw online for using SQL to LINQ.

My requirement is to load the contents of two excel files and create a new report containing the missing values. I can do this in excel using vlookup. What I have achieved so far is to load the two excel sheet data into 2 data tables. I am aware we can use datasets as well but I used this method.

So my sample dt1 would look like the one below:

dt2 as below:

I want to get the respective designations from dt2 into dt1 under the designation column, therefore final output to look like below:

EDIT 1:

private void MergeDatatable(string excelFilepath1, string excelFilepath2)
{
    string excelConString = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source='" +
                                    excelFilepath1 + "'; Extended Properties='Excel 12.0 Xml;HDR=Yes;IMEX=1;'";
    string excelSql = "select * from [Sheet1$]";

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

    using (OleDbDataAdapter adap= new OleDbDataAdapter(excelSql, excelConString))
    {
         adap.Fill(dt1);
    }

    string excelConString = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source='" +
                             excelFilepath2 + "'; Extended Properties='Excel 12.0 Xml;HDR=Yes;IMEX=1;'";
    string excelSql = "SELECT * FROM [Sheet1$]";

    using (OleDbDataAdapter adap = new OleDbDataAdapter(excelSql, excelConString))
    {
         adap.Fill(dt2);
    }
            
    var joineddt = from tp in ds.Tables
    join mp in ds.Tables(dt2) on tp.emp_id equals mp.emp_id
    select new
    {
        //my fields here
    };
}

Answer 1

//ds = your DataSet
var joinedTableResult = from emp1 in ds.dt1
join emp2 in ds.dt2 on emp1.Employee_ID equals emp2.Employee_ID
select new
{
    Employee_ID = emp1.Employee_ID,
    Department = emp1.Department,
    Tenure = emp1.Tenure,
    Designation = emp2.Designation
};