CODEWITHSUNDEEP
c#linqdatatable
Ullan
Ullan

Reputation: 1017

How to write Outer Join using LINQ

I am using the below Inner Join to retrive the Data between the two tables, but all data is not getting populated. I tried implementing Outer join by connecting using CCY1== CCY1 and PCODE == PCODE, but no luck.

enter image description here

    var q = from g1 in TableCCY1.AsEnumerable()
            join g2 in TableCCY2.AsEnumerable()
            on g1.Field<string>("CCY1") equals g2.Field<string>("CCY1") 
            where  g1.Field<string>("PCODE") == g2.Field<string>("PCODE")
                   select new
                     {
                             g1currency = g1.Field<string>("CCY1"),
                             g2currency = g2.Field<string>("CCY1"),
                             g1code = g1.Field<string>("PCODE"),
                             g2code = g2.Field<string>("PCODE"),
                             g1Amt1 = g1.Field<string>("AMT1"),
                             g2Amt2 = g2.Field<string>("AMT2")
                     };

Thanks for your help.

Upvotes: 0

Views: 126

Answers (2)

nikolai.serdiuk
nikolai.serdiuk

Reputation: 762

For left join you can use this approuch: http://msdn.microsoft.com/en-us/library/vstudio/bb397895.aspx

The code should be:

var q = from g1 in TableCCY1
            join g2 in TableCCY2 on g1.CCY1 equals g2.CCY1 && g1.PCODE equals g2.PCODE into TableCCY3
from g3 in TableCCY3.DefaultIfEmpty()
 select new
                     {
                             g1currency = g1.CCY1,
                             g2currency = (g3 == null ? String.Empty : g3.CCY1),
                             g1code = g1.PCODE,
                             g2code = (g3 == null ? String.Empty : g3.PCODE),
                             g1Amt1 = g1.AMT1,
                             g2Amt2 = (g3 == null ? 0 : g3.AMT2)
                     };

Upvotes: 2

Servy
Servy

Reputation: 203842

It looks like you just want to union/concat the two tables into one and then just group on those two columns. You're not logically joining the two tables. That actually makes it much easier.

var q = from row in TableCCY1.AsEnumerable().Concat(TableCCY2.AsEnumerable())
        group row by new
        {
            CCY1 = row.Field<string>("CCY1"),
            PCode = row.Field<string>("PCODE")
        } into matches
        select new
        {
            CCY1 = matches.Key.CCY1,
            PCODE = matches.Key.PCode,
            Sum = matches.Sum(match => match.Field<decimal?>("AMT2")),
        };

Upvotes: 1

Related Questions