Monday, May 11, 2009

Perform left outer join using LinQ

For my work, I had to perform a left outer join in LinQ. Here is the scenario:
There are 2 tables:

Auto text is the left table. Its customer column holds the Id for Customer table's elements. My task was, if there is a value in the Autotext.Customer field, then I have to show the relevant customer name. And if the value is null then it would show n\a. Only performing a left outer join can solve this. And this is how I solved it:

public IEnumerable GetAutotextsWithCustomerNameLeftJoin(int customerId)
IEnumerable AutotextsWithCustomerName = from AutotextTable in this.Context.AutoTexts
join CustomerTable in this.Context.Customers
on AutotextTable.Customer equals CustomerTable.Id into temporaryTable
from xTable in temporaryTable.DefaultIfEmpty()
select new { AutotextTable.Id, AutotextTable.Key, AutotextTable.Value, CustomerName = xTable.Id==null?"n\a":xTable.Name };
return AutotextsWithCustomerName;
catch (Exception ex)
return null;

Here, temporaryTable is the table that is created after the join operation is performed. xTable is the table that is created after left outer join is performed.

This solved the problem. I took help from different online posts. But, this post helped me the most:

No comments: