예제 #1
0
        private void JoiningTableData_Load(object sender, EventArgs e)
        {
            // Entity Framework DbContext
            var dbcontext = new BookExamples.BooksEntities();

            //Store the last title to break the display
            String previousTitle = null;

            // 1) list of all the titles and the authors who wrote them. Sort the results by title.

            var authorsAndISBNs =
               from author in dbcontext.Authors
               from book in author.Titles
               orderby book.Title1
               select new { author.FirstName, author.LastName, book.Title1 };


            outputTextBox.AppendText("1) Titles and Authors:");

            //display authors and titles
            foreach (var element in authorsAndISBNs)
            {
                if (previousTitle == element.Title1)
                {
                    outputTextBox.AppendText($"\r\n\t\t\t " +
                   $"{element.FirstName} \t{element.LastName}");
                }
                else
                {
                    previousTitle = element.Title1;
                    outputTextBox.AppendText($"\r\n\t{element.Title1} " +
                   $"\r\n\t\t\t {element.FirstName} \t{element.LastName}");
                }
            }

            // 2) Get a list of all the titles and the authors who wrote them. Sort the results by title. Each title sort the authors
            //alphabetically by last name, then first name

            var authorsAndTitles2 =
                from author in dbcontext.Authors
                from book in author.Titles
                orderby book.Title1, author.LastName, author.FirstName
                select new { author.FirstName, author.LastName, book.Title1 };

            outputTextBox.AppendText("\r\n\r\n2) Titles and Authors:");      

            // display authors and titles in tabular format
            foreach (var element in authorsAndTitles2)
            {
                if (previousTitle == element.Title1)
                {
                    outputTextBox.AppendText($"\r\n\t\t\t" +
                   $"{element.FirstName} " + $" {element.LastName}");
                } else
                {
                    previousTitle = element.Title1;
                    outputTextBox.AppendText($"\r\n\t{element.Title1} " +
                  $"\r\n\t\t\t{element.FirstName} " + $" {element.LastName}");
                }
               
            }

            // Get a list of all the authors grouped by title, sorted by title; for a given title sort the author names
            //alphabetically by last name then first name

            var authorsGroupedByTitles =
                from book in dbcontext.Titles
                from author in book.Authors
                group author by new { book.Title1, author.LastName, author.FirstName } into r1
                orderby r1.Key, r1.Key.LastName, r1.Key.FirstName
                select new { r1.Key, r1.Key.FirstName, r1.Key.LastName };

            outputTextBox.AppendText($"\r\n 3) Authors Grouped by Titles ");
            previousTitle = null;

            foreach (var author in authorsGroupedByTitles)
            {
                if (previousTitle == author.Key.Title1)
                {
                    outputTextBox.AppendText($"\r\n\t\t\t {author.FirstName}, {author.LastName}");
                }
                else
                {
                    previousTitle = author.Key.Title1;
                    outputTextBox.AppendText($"\r\n\t" + $"{author.Key.Title1}");
                    outputTextBox.AppendText($"\r\n\t\t\t {author.FirstName}, {author.LastName}");
                }
            }
        }
        private void Excercise01_Load(object sender, EventArgs e)
        {
            // Entity Framework Dbcontext
            var dbcontext = new BookExamples.BooksEntities();

            // Ex01 - 1.
            // Get a list of all the titles and the authors who wrote them. Sort the results by title
            // Get authors and titles of each book
            var authorAndTitles =
                from book in dbcontext.Titles
                from author in book.Authors
                select new { author.FirstName, author.LastName, book.Title1 };

            outputTextBox.AppendText("\r\n\r\nAuthors and titles:");

            // Display authors and titles in tabular format
            foreach (var element in authorAndTitles)
            {
                outputTextBox.AppendText($"\r\n\t{element.FirstName,-10} " +
                                         $"{element.LastName,-10} {element.Title1}");
            }


            // Ex01 - 2.
            // Get a list of all the titles and the authors who wrote them. Sort the results by title. Each title sort the authors
            // alphabetically by last name, then first name.
            // Get authors and titles of each books sorted alphabetically by author's last name and then first name
            var sortedAuthorAndTitles =
                from book in dbcontext.Titles
                from author in book.Authors
                orderby author.LastName, author.FirstName
                select new { author.FirstName, author.LastName, book.Title1 };

            outputTextBox.AppendText("\r\n\r\nAuthors and titles sorted alphabetically by author's last name and then first name:");

            // Display sorted results of authors and titles in tabular format
            foreach (var element in sortedAuthorAndTitles)
            {
                outputTextBox.AppendText($"\r\n\t{element.FirstName,-10} " +
                                         $"{element.LastName,-10} {element.Title1}");
            }


            // Ex01 - 3.
            // Get a list of all the authors grouped by title, sorted by title; for a given title sort the author names
            // alphabetically by last name then first name.
            var authorsByTitle =
                from book in dbcontext.Titles
                select new
            {
                Titles = book.Title1,
                Name   =
                    from author in book.Authors
                    orderby author.FirstName, author.LastName
                select new { author.FirstName, author.LastName }
            };

            outputTextBox.AppendText("\r\n\r\nAuthors grouped by title:");

            // Display authors grouped by title
            foreach (var title in authorsByTitle)
            {
                outputTextBox.AppendText($"\r\n\t{title.Titles}");
                foreach (var author in title.Name)
                {
                    outputTextBox.AppendText($"\r\n\t\t{author}");
                }
            }
        }