C# Programming : Left Outer Join Using LINQ in LINQ To Objects

You can also do left outer join using LINQ.

Here are sample example of this.
In this example there is book class which has PublisherId field , this fields contains only numeric values.
If we need to display Publisher Name we have to join this book class object to Publishers class object which has Publisher Name of each Publisher Id key.
There are also some records in Book class which does not have PublisherId and still we want that records and display some special text like "(No Publisher)" in this records.
In this example you can see that "HTML" book does not have publisher id so we can display "(No Publisher)" in Publisher Name column of this record.
In this example we are using special method like DefaultIfEmpty() of LINQ.

ASPX Code :

<form id="form1" runat="server">
    <div>
        <asp:GridView ID="gvBooks" runat="server">
        </asp:GridView>
    </div>
</form>

C# Example :

    public class Books
    {
        public string Title { get; set; }
        public string ISBN { get; set; }
        public DateTime ReleaseDate { get; set; }
        public int Pages { get; set; }
        public int PublisherId { get; set; }
    }

    protected void Page_Load(object sender, EventArgs e)
    {

        var bookslist = GetBooksList();
        var publisherslist=GetPublishers();
        var query = from bl in bookslist
                    join p in publisherslist on bl.PublisherId equals p.PublisherId into joinData
                    from blp in joinData.DefaultIfEmpty()
                    select new { Title = bl.Title, PublisherName = blp == null ? "(No Publisher)" : blp.PublisherName };
        this.gvBooks.DataSource = query;
        this.gvBooks.DataBind();
    }
    public List<Books> GetBooksList()
    {
        return new List<Books> {
                        new Books { Title="ASP.NET",ISBN="asp1",ReleaseDate= DateTime.Parse( "11/11/2010") ,Pages=200,PublisherId=1},
                        new Books { Title="C#.NET",ISBN="c#2",ReleaseDate= DateTime.Parse( "10/11/2010") ,Pages=500,PublisherId=1},
                        new Books { Title="VB.NET",ISBN="vb3",ReleaseDate= DateTime.Parse( "5/5/2009") ,Pages=400,PublisherId=1},
                        new Books { Title="SQL Server",ISBN="sql4",ReleaseDate= DateTime.Parse( "6/9/2010"),Pages=300,PublisherId=2 },
                        new Books { Title="JAVA",ISBN="java5",ReleaseDate= DateTime.Parse( "8/5/2011"),Pages=400,PublisherId=3 },
                        new Books { Title="HTML",ISBN="html6",ReleaseDate= DateTime.Parse( "9/5/2011"),Pages=400 }
        
        };

    }

    public class Publisher
    {
        public int PublisherId { get; set; }
        public string PublisherName { get; set; }
    }

    public List<Publisher> GetPublishers()
    {
        return new List<Publisher> {
                        new Publisher { PublisherId=1, PublisherName="Microsoft" } ,
                        new Publisher { PublisherId=2, PublisherName="Wrox" } ,
                        new Publisher { PublisherId=3, PublisherName="Sun Publications" }
        };
    }

VB.net Example :

    Public Class Books
        Public Property Title() As String
        Public Property ISBN As String
        Public Property ReleaseDate As Date
        Public Property Pages As Integer
        Public Property PublisherId As Integer
    End Class

    Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
        Dim arrayNumbers As Integer() = {1, 2, 5, 9, 10, 1, 0, 9, 5, 6, 4, 3, 2}
        Dim selectedNumber As Integer() = (From an In arrayNumbers Where an > 5 Select an).ToArray()
        Dim selectedNumberLambda As Integer() = arrayNumbers.Where(Function(o) o > 5).ToArray()



        Dim bookslist = GetBooksList()
        Dim publisherslist = GetPublishers()
        Dim query = From bl In bookslist _
                    Group Join p In publisherslist On bl.PublisherId Equals p.PublisherId Into Group _
                    From blp In Group.DefaultIfEmpty() _
                    Select bl.Title, PublisherName = If(blp Is Nothing, "(No Publisher)", blp.PublisherName)

        gvBooks.DataSource = query
        gvBooks.DataBind()
    End Sub
    Public Function GetBooksList() As List(Of Books)
        Dim lstBooks As New List(Of Books) From { _
                                New Books With {.Title = "ASP.NET", .ISBN = "asp1", .ReleaseDate = DateTime.Parse("11/11/2010"), .Pages = 200, .PublisherId = 1}, _
                                New Books With {.Title = "C#.NET", .ISBN = "c#2", .ReleaseDate = DateTime.Parse("10/11/2010"), .Pages = 500, .PublisherId = 1}, _
                                New Books With {.Title = "VB.NET", .ISBN = "vb3", .ReleaseDate = DateTime.Parse("5/5/2009"), .Pages = 400, .PublisherId = 1}, _
                                New Books With {.Title = "SQL Server", .ISBN = "sql4", .ReleaseDate = DateTime.Parse("6/9/2010"), .Pages = 300, .PublisherId = 2}, _
                                New Books With {.Title = "JAVA", .ISBN = "java5", .ReleaseDate = DateTime.Parse("8/5/2011"), .Pages = 400, .PublisherId = 3}, _
                                New Books With {.Title = "HTML", .ISBN = "html6", .ReleaseDate = DateTime.Parse("9/5/2011"), .Pages = 400}}

        Return lstBooks
    End Function
    Public Class Publisher
        Public Property PublisherId() As Integer
        Public Property PublisherName() As String
    End Class
    Public Function GetPublishers() As List(Of Publisher)
        Dim publishers As Publisher() = { _
        New Publisher With {.PublisherId = 1, .PublisherName = "Microsoft"}, _
        New Publisher With {.PublisherId = 2, .PublisherName = "Wrox"}, _
        New Publisher With {.PublisherId = 3, .PublisherName = "Sun Publications"} _
        }
        Return New List(Of Publisher)(publishers)
    End Function

Output :