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}");
}
}
}