Full outer join Linq

Question

I do left join and right join then do union for full outer join here is my code

var brndT = (from a in db.TSA_TARGET_DETAIL 
                         where a.OUTLET_ID == id && a.CAMPAIGN_ID == campaignId && a.EMPLOYEE_ID == empId 
                         select new 
                         {
                             ID=a.BRAND_ID,
                             Target=a.STICK_QTY
                         }).ToList();

            var brndS = (from p in db.SR_TRN_DETAILS
                      where
                          (from ppt in db.SR_TRN_MAIN
                           where
                           ppt.MEMO_DATE >= d2 && ppt.MEMO_DATE <= d1 && ppt.OUTLET_ID==id 
                           select ppt.ORDER_ID).Contains(p.ORDER_ID)
                      group p by p.BRAND_ID into g
                      select new
                      {
                          ID = g.Key,
                          Qty = g.Select(x => x.QUANTITY).Sum()
                      }).ToList();
            var left = (from T in brndT
                       join S in brndS
                       on T.ID equals S.ID
                       into temp
                       from S in temp.DefaultIfEmpty()
                       select new BrandSalesTarget
                       {
                           ID=T.ID,
                           Target = T.Target==null?0:(int)T.Target,
                           Sales = S != null ? (int)S.Qty : 0,
                       }).ToList();
            var right = (from S in brndS
                         join T in brndT
                         on S.ID equals T.ID
                         into temp
                         from T in temp.DefaultIfEmpty()
                         select new BrandSalesTarget
                         {
                             ID=S.ID,
                             Sales = S.Qty==null?0:(int)S.Qty,
                             Target = T != null ? (int)T.Target : 0
                         }).ToList();
            var fullOuter = left.Union(right).ToList();

But union does not work. I get the same id twice. What's wrong with my code? Anyone helps is greatly appreciated. Thanks in advance.

All the things seems ok,how about if you remove `.ToList` from last line like this: `var fullOuter = left.Union(right);` — Hamidreza, Jan 27 '14 at 10:33
I don't have time to investigate, but one of my firt guess is that the class 'BrandSalesTarget' doesn't know how to compare itself. Or you need a comparer as in: http://stackoverflow.com/questions/5969505/using-iequalitycomparer-for-union — TTT, Jan 27 '14 at 10:33

score 0 · Answer 1 · edited May 23 '17 at 10:31

Try using a IEqualityComparer so the union knows which elements are the same.

Code borrowed from. union in two linq statements and remove the duplicate.

class FirstElementComparer : IEqualityComparer<string[]>
{
    //TODO error checking
    public bool Equals(string[] a, string[] b)
    {       
        return a[0].Equals(b[0]);
    }

    public Int32 GetHashCode(string[] obj)
    {
        return obj[0].GetHashCode();
    }
}

and use it like this:

void Main()
{
    string[][] query1 = {new [] {"this is a test","Yes", "This is a remark"},
                         new [] {"this is a test2","No", "This is the second remark"}};

    string[][] query2 = {new [] {"this is a test","",""},
                         new [] {"this is a test2","",""},
                         new [] {"this is a test3","",""},
                         new [] {"this is a test4","",""}};

    query1.Union(query2, new FirstElementComparer()).Dump();                         
}

The EqualityComparer is used by Union to compare the elements in query1 with the elements in query2. It does so by just comparing the first item in each array.

I future please simplify your code the the minimum number of lines to show the actual issue, ideally in the form of a complete short program which can be run instead of copying and pasting a chunk of production code which any helpers will have trouble using to reproduce the problem.

Full outer join Linq

1 Answers1