Thursday, 7 June 2012

C# Programming : Group Join Using LINQ in LINQ To Objects

Using a group join you can get group wise data using LINQ.
Here are sample example for this.
In this example there are two classes Books and Publishers. Now we want to display Publisher wise books, One publishers has multiple books. We can do this using LINQ.
Using a group join you can get all the publishers that match a books as a sequence.

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 pl in publisherslist
                    join bl in bookslist on pl.PublisherId equals bl.PublisherId into plbl
                    select new { pl.PublisherName, BooksLst = plbl };
        foreach (var publishers in query)
        {
            Response.Write("<b>"+ publishers.PublisherName + ":</b><br/>" );
            foreach (var b in publishers.BooksLst)
            {
                Response.Write("&nbsp;&nbsp;&nbsp;&nbsp;" + b.Title + "<br/>");
            }
        }
        Response.Write("<br/><br/>");

    }
    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 bookslist = GetBooksList()
        Dim publisherslist = GetPublishers()
        Dim query = From pl In publisherslist _
                    Group Join bl In bookslist On pl.PublisherId Equals bl.PublisherId Into Group _
                    Select New With {.PublisherName = pl.PublisherName, .BooksLst = Group}

        For Each publishers In query
            Response.Write("<b>" + publishers.PublisherName + ":</b><br/>")
            For Each b In publishers.BooksLst
                Response.Write("&nbsp;&nbsp;&nbsp;&nbsp;" + b.Title + "<br/>")
            Next
        Next
        Response.Write("<br/><br/>")

    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 :

 

No comments:

Post a Comment