0

I am trying to implement the following SQL to Linq, but I am not able to.

select Isnull(a.Code,b.Code), Isnull(a.Description,b.Description) from table1 b
FULL OUTER JOIN table2 b On a.Code = b.Code AND b.Id = xx

I try to do two separate left join and union statements but I am not able to handle the IsNull check.

Here is the code I tried:

 var leftData = (from a in entities.tbl1
                            join b in entities.tbl2 on a.Code equals b.Code into temp
                            orderby a.Code
                            from b1 in temp.DefaultIfEmpty()
                            where b1.Id == xxx && a.Code.StartsWith(prefixText)
                            select new DCodes()
                            {
                                ID = a.ID,
                                CodeName = a.Code
                            }).ToList();
            var rightData = (from b in entities.tbl2
                             join a in entities.tbl1 on b.Code equals a.Code into temp
                             orderby b.Code
                             where b.Id == xxx && b.Code.StartsWith(prefixText)
                             from b1 in temp.DefaultIfEmpty()
                             select new DCodes()
                             {
                                 ID = b.ID,
                                 CodeName = b.Code
                             }).ToList();

            var a = leftData.Union(rightData).Distinct().ToList();
Saravanan
  • 283
  • 2
  • 17

0 Answers0