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 :
