Search code examples
c#linqmergedatatableienumerable

Matching two data sets with C# and LinQ

I have to import information from a plain text file, matching it to a DB Record using three fields.

DB fields
RowId, DF1, DF2, DF3, 

Text file fields
TF1,TF2,TF3,TF4

Matching
DF1 = TF1
DF2 = TF2
DF3 = TF3

Result
RowID, TF4 (Optional, F1,F2,F3)

Due to my limited experience with C# and LinQ, this is proving troublesome.

I tried loading the DB table and the text file into a DataTable each, and converting them asEnumerable() for use with LinQ, but cannot find a code reference to construct the LinQ properly.

var result = (from db in dtDB.AsEnumerable()
join txt in dtTxt.AsEnumerable() on <<MATCH INSTRUCTION>>
where << WHERE INSTRUCTION >>
select new {
           db.RowID,
//         F1,
//         F2,
//         F3,
           txt.TF4
}).asArray

The problem is how to reference each field in the "Enumerable" and construct the Match and Where instructions. Should I use other structure instead of DataTables? Any pointers on how to solve this would be helpful.

Solution

  • The simplest route is to use the functionality built into a datatable for resolving this. It's a single line of code that does the work, but here are 3 steps because of how the problem is presented in the question:

    1. Set the primary key columns of the table from the db
    2. Name the matching columns in the table from the text file as the same
    3. Call dataTableFromDb.Merge(dataTableFromTextFile);

    Step 1:

    
//for example, imagine your DB has given you this:
var dtDb = new DataTable();
dtDb.Columns.Add("RowId");
dtDb.Columns.Add("DF1");
dtDb.Columns.Add("DF2");
dtDb.Columns.Add("DF3");
dtDb.Rows.Add("Rowid1", "A", "B", "C");
dtDb.Rows.Add("Rowid2", "D", "E", "F");

//and your text file has given you this:
var dtF = new DataTable();
dtF.Columns.Add("TF1");
dtF.Columns.Add("TF2");
dtF.Columns.Add("TF3");
dtF.Columns.Add("TF4");
dtF.Rows.Add("A", "B", "C", "4");

    Step 2: Make sure the column names for the linking columns are the same in each table (rename them earlier in the process to make your life easier, e.g. name them when reading from the text file, or use AS in the SQL when filling dtdb from db)

    dtF.Columns["TF1"].ColumnName = "DF1";
dtF.Columns["TF2"].ColumnName = "DF2";
dtF.Columns["TF3"].ColumnName = "DF3";

    Make sure the destination table has a primary key

    dtDb.PrimaryKey = new[] { dtDb.Columns["DF1"], dtDb.Columns["DF2"], dtDb.Columns["DF3"] };

    Merge the file table into the database table

    dtDb.Merge(dtF);

    Realistically, the process that produces the database table should give it its primary key (so step 1 ought to be unnecessary), and the process that generates the table from file should equate the column names (so step 2 ought to be unnecessary) so literally the only line of code you need to use is from Step 3:

    dtDb.Merge(dtF);

    The dtDb table is the resulting merged table; you can show it on screen and save it back to the DB etcc

    https://dotnetfiddle.net/Ia71IM