Linq Join Two Datatables

LINQ join two DataTables

This will let you default to 0 if the row doesn't exist in table2:

var result = from dataRows1 in table1.AsEnumerable()
             join dataRows2 in table2.AsEnumerable()
             on dataRows1.Field<string>("ID") equals dataRows2.Field<string>("ID") into lj
             from r in lj.DefaultIfEmpty()
             select dtResult.LoadDataRow(new object[]
             {
                dataRows1.Field<string>("ID"),
                dataRows1.Field<string>("name"),
                r == null ? 0 : r.Field<int>("stock")
              }, false);

MSDN source

C# LINQ join two DataTables with a common field

The qry is not executed. You need .CopyToDataTable() to execute it.

qry.CopyToDataTable();

Sample program

How to use Linq to join multiple DataTables on multiple columns containing DBNull values

Linq will match DBNull.Value to another DBNull.Value correctly. I believe that was your question and not joining a second child table as this is trivial

DataTable parent = new DataTable();
parent.Columns.Add("Id1", typeof(string));
parent.Columns.Add("Id2", typeof(string));
DataRow r1 = parent.NewRow();
r1["Id1"] = "1";
r1["Id2"] = DBNull.Value;
parent.Rows.Add(r1);
DataRow r3 = parent.NewRow();
r3["Id1"] = "1";
r3["Id2"] = "2";
parent.Rows.Add(r3);

DataTable child1 = new DataTable();
child1.Columns.Add("Id1", typeof(string));
child1.Columns.Add("Id2", typeof(string));
child1.Columns.Add("BeginDate", typeof(DateTime));
child1.Columns.Add("SomeData", typeof(float));
DataRow r2 = child1.NewRow();
r2["Id1"] = "1";
r2["Id2"] = DBNull.Value;
child1.Rows.Add(r2);
DataRow r4 = child1.NewRow();
r4["Id1"] = "1";
r4["Id2"] = "2";
child1.Rows.Add(r4);

var dataToReturn =
    from
    p in parent.AsEnumerable()
    join c1 in child1.AsEnumerable()
    on new { Id1 = p["Id1"], Id2 = p["Id2"] }
    equals new { Id1 = c1["Id1"], Id2 = c1["Id2"] }
    select new
    {
        Id1 = p["Id1"],
        Id2 = p["Id2"]
    };

foreach(var l in dataToReturn)
{
    Console.WriteLine(l.Id1 + "|" + l.Id2);
}

Console.ReadKey();

Output:

1|
1|2

LINQ two datatables join

(Untested code ahead.....)

var newRows = FirstDatatable.Rows
             .Where(row =>SecondDatatable.Rows
                 .Any(secondRow => row["Por"] >= secondRow["FirstPor"] 
                                && row["Por"] <= secondRow["LastPor"]);

FinalDataTable.Rows.AddRange(newRows);

However, if speed is your real concern, my first suggestion is to dump the datatables, and use a list. I'm gonna gues that SecondDatatable, is largely fixed, and probabaly changes less than once a day. So, less create a nice in-memory structure for that:

class Range
{
     public int FirstPor {get; set;}
     public int LastPor {get; set;}
 }

 var ranges = (from r in SecondDatatable.Rows
               select new Range
                 { 
                     FirstPor = Int32.Parse(r["FirstPor"]),
                     LastPor = Int32.Parse(r["LastPor"])
                  }).ToList();

Then our code becomes:

var newRows = FirstDatatable.Rows
         .Where(row =>ranges
             .Any(range => row["Por"] >= range.FirstPor
                            && row["Por"] <= range.LastPor).ToList();

Which by itself should make this considerably faster.

Now, on a success, it will scan the Ranges up until it finds one that matches. On a failure, it will have to scan the whole list before it gives up. So, the first thing we need to do to speed this up, is to sort the list of ranges. Then we only have to scan up to the point where the low end of the range it higher than the value we are looking for. That should cut the processing time for those rows outside any range in half.

Using LINQ to JOIN two datatables using two key columns

var list1 = (from t1 in dataTable1.AsEnumerable()
                select new
                {
                    Key1 = t1.Field<int>("Key1"),
                    Key2 = t1.Field<int>("Key2"),
                    A = t1.Field<string>("A"),
                    B = t1.Field<string>("B")
                });

var list2 = (from b in dataTable2.AsEnumerable()
                select new
                {
                    Key1 = b.Field<int>("Key1"),
                    Key2 = b.Field<int>("Key2"),
                    X = b.Field<string>("X"),
                    Y = b.Field<string>("Y")
                });

// Now join the 2 collections and get the result you want.

var result = (from x in list1
                join y in list2 on new { x.Key1,x.Key2} equals new { y.Key1,y.Key2 }
                select new { A = x.A, X = y.X }).ToList();

Assuming Key1 and Key2 are int type and A.B,X and Y are of string type.

Inner Join Two Datatable using Linq without having relation with another table

Here

select * from table1 Inner join table2 on table2.id in (1,2,3)

technically speaking, table2.id in (1,2,3) is just a filter (where), and there is no Inner Join, but Cross Join, i.e. no join (relation) between the tables, but Cartesian product of the two.

So it really is something like this

select * from table1, table2 where table2.id in (1,2,3)

hence translating it to LINQ could be like this

var t2Filter = new HasSet<int>(new int[] { 1, 2, 3});
var result = (
    from t1 in table1.AsEnumerable()
    from t2 in table2.AsEnumerable()
    where t2Filter.Contains(t2.Field<int>("id"))
    select new
    {
        ID = t1.Field<int>("ID")
        // ...
    }
).ToList(); 

Use .Select().Join() to Join Two DataTables

I typically accomplish this by building an anonymous object that contains a reference to my source and destination objects through a Join or GroupJoin, then looping over the result of the Join to update my destination object. See the example below.

Take a look at the documentation on Join and GroupJoin. Join is great for a 1-1 match, while GroupJoin is a 0-* match (like a SQL left join). The arguments to Join and GroupJoin allow you to specify a selector function for each IEnumerable followed by a selector function for the output object. Note that t1 and t2 below refer to table1 and table2.

using System;
using System.Data;
using System.Linq;

public class Program
{
    public static void Main()
    {
        var table1 = GetEmptyTable();
        table1.Rows.Add(1, "Old Value", false);
        table1.Rows.Add(2, "Untouched Value", false);

        var table2 = GetEmptyTable();
        table2.Rows.Add(1, "New Value", false);
        table2.Rows.Add(3, "Unused Value", false);

        Console.WriteLine("Before...");
        Console.WriteLine(PrintTable(table1));

        var matched = table1.Select()
            .Join(table2.Select(), t1 => (int)t1["A"], t2 => (int)t2["A"], (t1, t2) 
=> new
            {
                DestinationRow = t1,
                SourceRow = t2
            });
        foreach (var match in matched)
        {
            match.DestinationRow["B"] = match.SourceRow["B"];
            match.DestinationRow["C"] = true;
        }

        Console.WriteLine("After...");
        Console.WriteLine(PrintTable(table1));
    }

    private static DataTable GetEmptyTable()
    {
        var table = new DataTable();
        table.Columns.Add("A", typeof(int));
        table.Columns.Add("B", typeof(string));
        table.Columns.Add("C", typeof(bool));
        return table;
    }

    private static string PrintTable(DataTable table)
    {
        return string.Join(Environment.NewLine, table.Select().Select(x => "[" + 
string.Join(", ", x.ItemArray) + "]"));
    }
}

---

Related Topics