} // 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