Left join two datatables with certain parameters

user_3120129 0
Viewed 163

I have two datatables t1 and t2. Imported them to SQL, tested with SQL command LEFT JOIN and it is working. However as amount of data is not so big I am wondering is it possible to perform the same action directly to datatables without exporting them to SQL?

Here I have two tables in SQL LFI = t1 and LSE = t2.

Table LFI in database:

NRO   SNAME  NAMEA   NAMEB   IADDRESS  POSTA   POSTN   POSTADR   COMPANYN   COUNTRY   BID
123   Fiat   Punto   500     J5        K4      O3      P4        O2         JT        S1
133   Opel   Meriva  FTG     J5        K4      O3      P4        O2         JO        T3
153   MB     E200    C25     JN        KI      OP      PY        OR         JD        Y5
183   BMW    E64     SE0     JR        KE      OT      PG        OL         J8        U9 
103   Audi   S6      700     JP        KU      OU      PN        OH         J6        I11

Table LSE in database:

NRO   SNAME  NAMEA   NAMEB   IADDRESS  POSTA   POSTN   POSTADR   COMPANYN   COUNTRY   BID
423   Fiat   Punto   500     J5        K4      O3      P4        O2         JT        S1
463   BMW    E64     SE0     JR        KE      OT      PG        OL         J8        U9
483   KIA    E89     S78     J7        K7      O9      P6        O5         J4        U6

Below code is capable of doing what is expected:

SELECT *
FROM LFI
LEFT JOIN LSE 
ON CAST(LFI.SNAME AS VARCHAR(50)) = CAST(LSE.SNAME AS VARCHAR(50));

How to perform the same directly to datatables so that joined result would go to temporary datatable with LEFT JOIN?

Current code:

                var results = from table1 in t1.AsEnumerable()
                              join table2 in t2.AsEnumerable() 
                              on (string)table1["SNAME"] equals (string)table2["SNAME"]
                              //into temp
                              //from row in temp.DefaultIfEmpty()
                              select new
                              {
                                  NRO = (string)table1["NRO"],
                                  SNAME = (string)table1["SNAME"],
                                  NAMEA = (string)table1["NAMEA"],
                                  NAMEB = (string)table1["NAMEB"],
                                  ADDRESS = (string)table1["ADDRESS"],
                                  POSTA = (string)table1["POSTA"],
                                  POSTN = (string)table1["POSTN"],
                                  POSTITP = (string)table1["POSTITP"],
                                  COMPANYN = (string)table1["COMPANYN"],
                                  COUNTRY = (string)table1["COUNTRY"],
                                  BID = (string)table1["BID"]
                              };

                foreach (var item in results)
                {
                    Console.WriteLine(String.Format("NRO = {0}, SNAME = {1}, NAMEA = {2}, NAMEB = {3}, KAYNTIOS = {4}, ADDRESS = {5}, POSTA = {6}, POSTN = {7}, COMPANYN = {8}, COUNTRY = {9}, BID = {10}", item.NRO, item.SNAME, 
                        item.NAMEA, item.NAMEB, item.KAYNTIOS, item.ADDRESS, item.POSTA, item.POSTN, item.COMPANYN, 
                        item.COUNTRY, item.BID));
                }

My current code is outputting only matching results:

NRO  SNAME  NAMEA   NAMEB   IADDRESS  POSTA   POSTN   POSTADR   COMPANYN   COUNTRY   BID
123  Fiat   Punto   500     J5        K4      O3      P4        O2         JT        S1
183  BMW    E64     SE0     JR        KE      OT      PG        OL         J8        U9

Joined output should look like this (Column order doesn't really matter as long as it is the same all the time):

NRO  NRO1   SNAME  NAMEA   NAMEB   IADDRESS  POSTA   POSTN   POSTADR   COMPANYN   COUNTRY   BID
123  423    Fiat   Punto   500     J5        K4      O3      P4        O2         JT        S1
133         Opel   Meriva  FTG     J5        K4      O3      P4        O2         JO        T3
153         MB     E200    C25     JN        KI      OP      PY        OR         JD        Y5
183  463    BMW    E64     SE0     JR        KE      OT      PG        OL         J8        U9
103         Audi   S6      700     JP        KU      OU      PN        OH         J6        I11
     483    KIA    E89     S78     J7        K7      O9      P6        O5         J4        U6
sql linq c#
1 Answers

What you need here is Full Outer Join in Sql. But because it doesn't have direct equivalent in Linq, you should implement it this way : T1 ⋃ (T2 - T1)

T1 : Is achieved by T1 Left Join T2

(T2 - T1) : Is achieved by T2 Left Join T1 Where T1 is null

This should results what you're looking for:

// T1
var firstPart = from table1 in t1.AsEnumerable()
                join table2 in t2.AsEnumerable()
                    on (string)table1["SNAME"] equals (string)table2["SNAME"] into temp
                from table2 in temp.DefaultIfEmpty()
                select new
                {
                    NRO = (string)table1["NRO"],
                    NRO1 = table2 != null ? (string)table2["NRO"] : string.Empty,
                    SNAME = (string)table1["SNAME"],
                    NAMEA = (string)table1["NAMEA"],
                    NAMEB = (string)table1["NAMEB"],
                    ADDRESS = (string)table1["ADDRESS"],
                    POSTA = (string)table1["POSTA"],
                    POSTN = (string)table1["POSTN"],
                    POSTITP = (string)table1["POSTITP"],
                    COMPANYN = (string)table1["COMPANYN"],
                    COUNTRY = (string)table1["COUNTRY"],
                    BID = (string)table1["BID"]
                };

// (T2 -T1)
var secndPart = from table2 in t2.AsEnumerable()
                join table1 in t1.AsEnumerable()
                    on (string)table2["SNAME"] equals (string)table1["SNAME"] into temp
                from table1 in temp.DefaultIfEmpty()
                where table1 == null
                select new
                {
                    NRO = string.Empty,
                    NRO1 = (string)table2["NRO"],
                    SNAME = (string)table2["SNAME"],
                    NAMEA = (string)table2["NAMEA"],
                    NAMEB = (string)table2["NAMEB"],
                    ADDRESS = (string)table2["ADDRESS"],
                    POSTA = (string)table2["POSTA"],
                    POSTN = (string)table2["POSTN"],
                    POSTITP = (string)table2["POSTITP"],
                    COMPANYN = (string)table2["COMPANYN"],
                    COUNTRY = (string)table2["COUNTRY"],
                    BID = (string)table2["BID"]
                };

// T1 ⋃ (T2 - T1)
var results = firstPart.Concat(secndPart);

foreach (var item in results)
{
    Console.WriteLine(
        $"NRO = {item.NRO}, " +
        $"NRO1 = {item.NRO1}, " +
        $"SNAME = {item.SNAME}, " +
        $"NAMEA = {item.NAMEA}, " +
        $"NAMEB = {item.NAMEB}, " +
        $"ADDRESS = {item.ADDRESS}, " +
        $"POSTA = {item.POSTA}, " +
        $"POSTN = {item.POSTN}, " +
        $"POSTITP = {item.POSTITP}, " +
        $"COMPANYN = {item.COMPANYN}, " +
        $"COUNTRY = {item.COUNTRY}, " +
        $"BID = {item.BID}"
        );
}
user_92120400
Related