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();