} // end constructor private void JoiningTableData_Load(object sender, EventArgs e) { // Entity Framework DBContext BooksEntities dbcontext = new BooksEntities(); // ------------------------------------------------------------------------------------------------------------------------------ // A. Get a list of all the titles and the authors who wrote them. Sort the result by title. // // ------------------------------------------------------------------------------------------------------------------------------ var titlesAndAuthors = from book in dbcontext.Titles from author in book.Authors orderby book.Title1 select new { Name = author.FirstName + " " + author.LastName, book.Title1 }; // Display the result by Title outputTextBox.AppendText("\r\n\r\nTitles and Authors:"); foreach (var element in titlesAndAuthors) { outputTextBox.AppendText(String.Format("\r\n" + element.Title1 + " " + element.Name)); } // ------------------------------------------------------------------------------------------------------------------------------ // ------------------------------------------------------------------------------------------------------------------------------ // B. Get a list of all the titles and the authors who wrote them. Sort the result by title. For each title sort the authors // alphabetically by last name, then first name. // // ------------------------------------------------------------------------------------------------------------------------------ var authorsAndTitles = from book in dbcontext.Titles from author in book.Authors orderby book.Title1, author.LastName, author.FirstName select new { book.Title1, author.FirstName, author.LastName }; // Display the Authors and Titles with authors sorted for each title. outputTextBox.AppendText("\r\n\r\nAuthors and titles with authors sorted for each title:"); foreach (var element in authorsAndTitles) { outputTextBox.AppendText(String.Format("\r\n" + element.Title1 + " " + element.FirstName + " " + element.LastName)); } // ---------------------------------------------------------------------------------------------------------------------------- // ---------------------------------------------------------------------------------------------------------------------------- // C. 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 first then first name. // // ---------------------------------------------------------------------------------------------------------------------------- var titlesByAuthor = from book in dbcontext.Titles orderby book.Title1 select new { titleOfBook = book.Title1, Author = from author in book.Authors orderby author.LastName, author.FirstName select new { author.LastName, author.FirstName } }; // Display Titles grouped by author outputTextBox.AppendText("\r\n\r\nTitles grouped by author:"); foreach (var title in titlesByAuthor) { outputTextBox.AppendText("\r\n" + title.titleOfBook + ":"); foreach (var author in title.Author) { outputTextBox.AppendText("\r\n" + author.FirstName + " " + author.LastName); } } // ---------------------------------------------------------------------------------------------------------------------------- } // end method JoiningTableData_Load
} // end constructor private void JoiningTableData_Load(object sender, EventArgs e) { // Entity Framework DBContext BooksEntities dbcontext = new BooksEntities(); // get authors and ISBNs of each book they co-authored var authorsAndISBNs = from author in dbcontext.Authors from book in author.Titles orderby author.LastName, author.FirstName select new { author.FirstName, author.LastName, book.ISBN }; outputTextBox.AppendText("Authors and ISBNs:"); // display authors and ISBNs in tabular format foreach (var element in authorsAndISBNs) { outputTextBox.AppendText( String.Format("\r\n\t{0,-10} {1,-10} {2,-10}", element.FirstName, element.LastName, element.ISBN)); } // end foreach // get authors and titles of each book they co-authored var authorsAndTitles = from book in dbcontext.Titles from author in book.Authors orderby author.LastName, author.FirstName, book.Title1 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 authorsAndTitles) { outputTextBox.AppendText( String.Format("\r\n\t{0,-10} {1,-10} {2}", element.FirstName, element.LastName, element.Title1)); } // end foreach // get authors and titles of each book // they co-authored; group by author var titlesByAuthor = from author in dbcontext.Authors orderby author.LastName, author.FirstName select new { Name = author.FirstName + " " + author.LastName, Titles = from book in author.Titles orderby book.Title1 select book.Title1 }; outputTextBox.AppendText("\r\n\r\nTitles grouped by author:"); // display titles written by each author, grouped by author foreach (var author in titlesByAuthor) { // display author's name outputTextBox.AppendText("\r\n\t" + author.Name + ":"); // display titles written by that author foreach (var title in author.Titles) { outputTextBox.AppendText("\r\n\t\t" + title); } // end inner foreach } // end outer foreach // First var titlesByAuthor2 = from book in dbcontext.Titles orderby book.Title1 select new { Titles = from author in dbcontext.Authors from book1 in author.Titles orderby book1.Title1 select book1.Title1, Name = from author in dbcontext.Authors orderby author.LastName, author.FirstName select author.FirstName + " " + author.LastName }; outputTextBox.AppendText("\r\n\r\nAuthors and titles by title (2):"); foreach (var book in titlesByAuthor2) { outputTextBox.AppendText("\r\n\t" + book + ":"); foreach (var author in book.Name) { outputTextBox.AppendText("\r\n\t\t" + author); } // end inner foreach } // end outer foreach //Second var titlesByAuthor3 = from book in dbcontext.Titles from author in book.Authors orderby book.Title1, author.LastName, author.FirstName select new { author.LastName, author.FirstName, book.Title1 }; outputTextBox.AppendText("\r\n\r\nAuthors and titles by title then author (3):"); foreach (var book in titlesByAuthor3) { outputTextBox.AppendText("\r\n\t" + book.Title1 + ", " + book.FirstName + ", " + book.LastName); } // end outer foreach } // end method JoiningTableData_Load
} // end constructor private void JoiningTableData_Load(object sender, EventArgs e) { // Entity Framework DBContext BooksEntities dbcontext = new BooksEntities(); // get authors and ISBNs of each book they co-authored var authorsAndISBNs = from author in dbcontext.Authors from book in author.Titles orderby author.LastName, author.FirstName select new { author.FirstName, author.LastName, book.ISBN }; outputTextBox.AppendText("Authors and ISBNs:"); // display authors and ISBNs in tabular format foreach (var element in authorsAndISBNs) { outputTextBox.AppendText( String.Format("\r\n\t{0,-10} {1,-10} {2,-10}", element.FirstName, element.LastName, element.ISBN)); } // end foreach // get authors and titles of each book they co-authored var authorsAndTitles = from book in dbcontext.Titles from author in book.Authors orderby author.LastName, author.FirstName, book.Title1 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 authorsAndTitles) { outputTextBox.AppendText( String.Format("\r\n\t{0,-10} {1,-10} {2}", element.FirstName, element.LastName, element.Title1)); } // end foreach // get authors and titles of each book // they co-authored; group by author var titlesByAuthor = from author in dbcontext.Authors orderby author.LastName, author.FirstName select new { Name = author.FirstName + " " + author.LastName, Titles = from book in author.Titles orderby book.Title1 select book.Title1 }; outputTextBox.AppendText("\r\n\r\nTitles grouped by author:"); // display titles written by each author, grouped by author foreach (var author in titlesByAuthor) { // display author's name outputTextBox.AppendText("\r\n\t" + author.Name + ":"); // display titles written by that author foreach (var title in author.Titles) { outputTextBox.AppendText("\r\n\t\t" + title); } // end inner foreach } // end outer foreach /// ****************************** /// * /// * my codes /// * /// ****************************** //Get a list of all the titles and the authors who wrote them. //Sort the result by title. var allTitles = from author in dbcontext.Authors from book in author.Titles orderby book.Title1 select new { author.FirstName, author.LastName, book.Title1 }; outputTextBox.AppendText("\r\n\r\nTitles and Authors:"); // display titles and author in tabular format foreach (var element in allTitles) { outputTextBox.AppendText( String.Format("\r\n\t{0,-50} {1,-10} {2,-10}", element.Title1, element.FirstName, element.LastName)); } // end foreach //Get a list of all the titles and the authors who wrote them. //Sort the result by title. //For each title sort the authors alphabetically by last name, then first name. var allTitlesLF = 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\nAuthors and titles with authors sorted for each title:"); // display titles and author in tabular format foreach (var element in allTitlesLF) { outputTextBox.AppendText( String.Format("\r\n\t{0,-50} {1,-10} {2,-10}", element.Title1, element.LastName, element.FirstName)); } // end foreach //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 first then first name. var authorsByTitle = from author in dbcontext.Authors from book in author.Titles group new { author.FirstName, author.LastName } by book.Title1 into bookGroup orderby bookGroup.Key select bookGroup; outputTextBox.AppendText("\r\n\r\nTitles grouped by author:"); // display titles written by each author, grouped by titles foreach (var title in authorsByTitle) { // display titles's name outputTextBox.AppendText("\r\n\t" + title.Key + ":"); // display authors foreach (var author in title.OrderBy(name => name.LastName + name.FirstName)) { outputTextBox.AppendText("\r\n\t\t" + author.FirstName + " " + author.LastName); } // end inner foreach } // end outer foreach } // end method JoiningTableData_Load
} // end constructor private void JoiningTableData_Load(object sender, EventArgs e) { // Entity Framework DBContext BooksEntities dbcontext = new BooksEntities(); //A . Get a list of all the titles and the authors who wrote them. Sort the result by title. var titleAuthor = from author in dbcontext.Authors from book in author.Titles orderby book.Title1 select new { book.Title1, author.FirstName, author.LastName }; outputTextBox.AppendText("Titles and Authors :"); // display titles and authors foreach (var element in titleAuthor) { outputTextBox.AppendText( String.Format("\r\n\t{0,0} {1,0} {2,0}", element.Title1, element.FirstName, element.LastName)); } // end foreach //B . Get a list of all the titles and the authors who wrote them. Sort the result by title. For each title sort the authors alphabetically by last name, then first name. var allTitleAuthors = from author in dbcontext.Authors from book in author.Titles orderby book.Title1, author.LastName, author.FirstName select new { book.Title1, author.LastName, author.FirstName }; outputTextBox.AppendText("\r\n\r\nAuthors and titles with authors sorted for each title:"); foreach (var element in allTitleAuthors) { outputTextBox.AppendText( String.Format("\r\n\t{0,0} {1,0} {2,0}", element.Title1, element.FirstName, element.LastName)); } // end foreach //C . 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 first then first name var allAuthors = from book in dbcontext.Titles orderby book.Title1 select new { Title = book.Title1, Authors = from author in book.Authors orderby author.LastName, author.FirstName select new { author.FirstName, author.LastName } }; outputTextBox.AppendText("\r\n\r\nTitles grouped by author:"); foreach (var element in allAuthors) { outputTextBox.AppendText( String.Format("\r\n\t{0,0}", element.Title)); foreach (var authors in element.Authors) { outputTextBox.AppendText( String.Format("\r\n\t\t{0,0} {1,0} ", authors.FirstName, authors.LastName)); } } // end foreach } // end method JoiningTableData_Load
} // end constructor private void JoiningTableData_Load(object sender, EventArgs e) { // Entity Framework DBContext BooksEntities dbcontext = new BooksEntities(); // get authors and ISBNs of each book they co-authored var authorsAndISBNs = from author in dbcontext.Authors from book in author.Titles orderby author.LastName, author.FirstName select new { author.FirstName, author.LastName, book.ISBN }; //outputTextBox.AppendText("Authors and ISBNs:"); //// display authors and ISBNs in tabular format //foreach (var element in authorsAndISBNs) //{ // outputTextBox.AppendText( // String.Format("\r\n\t{0,-10} {1,-10} {2,-10}", // element.FirstName, element.LastName, element.ISBN)); //} // end foreach //// get authors and titles of each book they co-authored //var authorsAndTitles = // from book in dbcontext.Titles // from author in book.Authors // orderby author.LastName, author.FirstName, book.Title1 // 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 authorsAndTitles) //{ // outputTextBox.AppendText( // String.Format("\r\n\t{0,-10} {1,-10} {2}", // element.FirstName, element.LastName, element.Title1)); //} // end foreach // get authors and titles of each book they co-authored var titlesAndAuthors = from book in dbcontext.Titles from author in book.Authors orderby book.Title1 select new { author.FirstName, author.LastName, book.Title1 }; outputTextBox.AppendText("\r\n\r\nTitles and Authors:"); // display authors and titles in tabular format foreach (var element in titlesAndAuthors) { outputTextBox.AppendText( String.Format("\r\n\t{0,-10} {1} {2}", element.Title1, element.FirstName, element.LastName)); } // end foreach /////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////// /// var authorsAndTitles = from book in dbcontext.Titles from author in book.Authors orderby book.Title1, author.LastName, author.FirstName select new { author.FirstName, author.LastName, book.Title1 }; outputTextBox.AppendText("\r\n\r\nAuthors and titles with authors sorted for each title:"); // display authors and titles in tabular format foreach (var element in authorsAndTitles) { outputTextBox.AppendText( String.Format("\r\n\t{0,-10} {1} {2}", element.Title1, element.FirstName, element.LastName)); } // end foreach // get authors and titles of each book // they co-authored; group by author //var titlesByAuthor = // from author in dbcontext.Authors // orderby author.LastName, author.FirstName // select new // { // Name = author.FirstName + " " + author.LastName, // Titles = // from book in author.Titles // orderby book.Title1 // select book.Title1 // }; //outputTextBox.AppendText("\r\n\r\nTitles grouped by author:"); //// display titles written by each author, grouped by author //foreach (var author in titlesByAuthor) //{ // // display author's name // outputTextBox.AppendText("\r\n\t" + author.Name + ":"); // // display titles written by that author // foreach (var title in author.Titles) // { // outputTextBox.AppendText("\r\n\t\t" + title); // } // end inner foreach //} // end outer foreach var titlesByAuthor = from book in dbcontext.Titles orderby book.Title1 select new { Titles = book.Title1, Names = from author in book.Authors orderby author.LastName, author.FirstName select author.FirstName + " " + author.LastName }; outputTextBox.AppendText("\r\n\r\nTitles grouped by author:"); // display author of each book, grouped by book foreach (var book in titlesByAuthor) { // display title outputTextBox.AppendText("\r\n\t" + book.Titles + ":"); // display authors that wrote the book foreach (var author in book.Names) { outputTextBox.AppendText("\r\n\t\t" + author); } // end inner foreach } // end outer foreach } // end method JoiningTableData_Load
} // end constructor private void JoiningTableData_Load(object sender, EventArgs e) { // Entity Framework DBContext BooksEntities dbcontext = new BooksEntities(); // get authors and ISBNs of each book they co-authored var authorsAndISBNs = from author in dbcontext.Authors from book in author.Titles orderby book.Title1 select new { book.Title1, author.FirstName, author.LastName }; outputTextBox.AppendText("Titles and Authors"); // display authors and ISBNs in tabular format foreach (var element in authorsAndISBNs) { outputTextBox.AppendText( String.Format("\r\n\t{0,-50} {1,-10} {2}", element.Title1, element.FirstName, element.LastName)); } // end foreach // get authors and titles of each book they co-authored var authorsAndTitles = from book in dbcontext.Titles from author in book.Authors orderby book.Title1, author.LastName, author.FirstName select new { book.Title1, author.FirstName, author.LastName }; outputTextBox.AppendText("\r\n\r\nAuthors and titles with authors sorted for each title:"); // display authors and titles in tabular format foreach (var element in authorsAndTitles) { outputTextBox.AppendText( String.Format("\r\n\t{0,-10} {1,-10} {2}", element.FirstName, element.LastName, element.Title1)); } // end foreach // get authors and titles of each book // they co-authored; group by author var titlesByAuthor = from books in dbcontext.Titles orderby books.Title1 select new { Title = books.Title1, Authors = from authors in books.Authors orderby authors.LastName, authors.FirstName select new { authors.LastName, authors.FirstName } }; outputTextBox.AppendText("\r\n\r\nTitles grouped by author:"); // display titles written by each author, grouped by author foreach (var title in titlesByAuthor) { // display author's name outputTextBox.AppendText("\r\n\t" + title.Title + ":"); // display titles written by that author foreach (var names in title.Authors) { outputTextBox.AppendText("\r\n\t\t" + names.FirstName + " " + names.LastName); } // end inner foreach } // end outer foreach } // end method JoiningTableData_Load