public IHttpActionResult RemoveUserRole([FromUri] string username, [FromBody] string roleName)
{
using (var context = new BookShopContext())
{
if (!context.Users.Any(u => u.UserName == username))
{
return this.NotFound();
}
var user = context.Users.First(u => u.UserName == username);
if (!context.Roles.Any(r => r.Name == roleName))
{
return this.NotFound();
}
var role = context.Roles.First(r => r.Name == roleName);
var identityUserRole = role.Users.First(u => u.UserId == user.Id);
role.Users.Remove(identityUserRole);
context.SaveChanges();
return this.Ok();
}
}
public static void TestBookRealtions(BookShopContext context)
{
var books = context.Books
.Take(3)
.ToList();
books[0].RelatedBooks.Add(books[1]);
books[1].RelatedBooks.Add(books[0]);
books[0].RelatedBooks.Add(books[2]);
books[2].RelatedBooks.Add(books[0]);
context.SaveChanges();
var booksFromQuery = context.Books.Take(3);
foreach (var book in booksFromQuery)
{
Console.WriteLine("--{0}", book.Title);
foreach (var relatedBook in book.RelatedBooks)
{
Console.WriteLine(relatedBook.Title);
}
}
}
static void Main()
{
//set and seed database
Database.SetInitializer(new MigrateDatabaseToLatestVersion<BookShopContext, Configuration>());
var context = new BookShopContext();
var bookCount = context.Books.Count();
Console.WriteLine(bookCount);
//LINQ queries
//1.Get all books after the year 2000. Select only their titles.
var booksByYear = context.Books
.Where(b => b.ReleaseDate.Year > 2000)
.Select(b => b.Title);
foreach (var book in booksByYear)
{
Console.WriteLine(book);
}
Console.WriteLine();
//2.Get all authors with at least one book with release date before 1990. Select their first name and last name.
var authorsWithBooks = context.Authors
.Where(a => a.Books.Any(b => b.ReleaseDate.Year < 1990))
.Select(a => new
{
a.FirstName,
a.LastName
});
foreach (var author in authorsWithBooks)
{
Console.WriteLine(author);
}
Console.WriteLine();
//3.Get all authors, ordered by the number of their books (descending). Select their first name, last name and book count.
var authors = context.Authors
.OrderByDescending(a => a.Books.Count)
.Select(a => new
{
a.FirstName,
a.LastName,
NumberOfBooks = a.Books.Count
});
foreach (var author in authors)
{
Console.WriteLine(author);
}
Console.WriteLine();
//4.Get all books from author George Powell, ordered by their release date (descending), then by book title (ascending).
//Select the book's title, release date and copies.
var booksByAuthor = context.Books
.OrderByDescending(b => b.ReleaseDate)
.ThenBy(b => b.Title)
.Where(b => b.Author.FirstName == "George" && b.Author.LastName == "Powell")
.Select(b => new
{
b.Title,
b.ReleaseDate,
b.Copies
});
foreach (var book in booksByAuthor)
{
Console.WriteLine(book);
}
Console.WriteLine();
//5.Get the most recent books by categories. The categories should be ordered by total book count.
//Only take the top 3 most recent books from each category - ordered by date (descending), then by title (ascending).
//Select the category name, total book count and for each book - its title and release date.
var booksByCategories = context.Categories
.OrderByDescending(c => c.Books.Count)
.Select(c => new
{
c.Name,
NumberOfBooks = c.Books.Count,
Books = c.Books.Select(b => new
{
b.Title,
b.ReleaseDate
})
.OrderByDescending(b => b.ReleaseDate)
.ThenBy(b => b.Title)
.Take(3)
});
foreach (var category in booksByCategories)
{
Console.WriteLine("--" + category.Name + ": " + category.NumberOfBooks + " books");
var books = category.Books;
foreach (var book in books)
{
Console.WriteLine(book);
}
}
Console.WriteLine();
//Populate table RelatedBooks
//Query 3 books from the database and set them as related
var selectedBooks = context.Books
.Take(3)
.ToList();
selectedBooks[0].RelatedBooks.Add(selectedBooks[1]);
selectedBooks[1].RelatedBooks.Add(selectedBooks[0]);
selectedBooks[0].RelatedBooks.Add(selectedBooks[2]);
selectedBooks[2].RelatedBooks.Add(selectedBooks[0]);
context.SaveChanges();
//query the first 3 books
var booksFromQuery = context.Books
.Take(3)
.Select(b => new
{
b.Title,
RelatedBooks = b.RelatedBooks.Select(rb => rb.Title)
});
foreach (var book in booksFromQuery)
{
Console.WriteLine("--{0}", book.Title);
foreach (var relatedBook in book.RelatedBooks)
{
Console.WriteLine(relatedBook);
}
}
}
public static void Main()
{
var context = new BookShopContext();
var bookCount = context.Books.Count();
// Problem 06 - 01
var books = context.Books
.Where(b => b.ReleaseDate.Year > 2000)
.Select(b => b.Title);
foreach (var book in books)
{
Console.WriteLine(book);
}
Console.WriteLine();
// Problem 06 - 02
var authors = context.Authors
.Where(a => a.Books.Any(b => b.ReleaseDate.Year < 1990))
.Select(a => new
{
a.FirstName,
a.LastName
});
foreach (var author in authors)
{
Console.WriteLine(author.FirstName + " " + author.LastName);
}
Console.WriteLine();
// Problem 06 - 03
var authorsByBooksCount = context.Authors
.OrderByDescending(a => a.Books.Count)
.Select(a => new
{
a.FirstName,
a.LastName,
a.Books.Count
});
foreach (var author in authorsByBooksCount)
{
Console.WriteLine(author.FirstName + " " + author.LastName + ", " + author.Count);
}
Console.WriteLine();
// Problem 06 - 04
var booksByGivenAuthor =
context.Books.Where(b => b.Author.FirstName == "George" && b.Author.LastName == "Powell")
.OrderByDescending(b => b.ReleaseDate)
.ThenBy(b => b.Title)
.Select(b => new
{
b.Title,
b.ReleaseDate,
b.Copies
});
foreach (var book in booksByGivenAuthor)
{
Console.WriteLine(book.Title + ", " + book.ReleaseDate + ", " + book.Copies);
}
Console.WriteLine();
// Problem 06 - 05
var categoriesRecentBooks =
context.Categories.OrderByDescending(c => c.Books.Count)
.Select(c => new
{
Category = c.Name,
BooksCount = c.Books.Count,
Books = c.Books
.OrderByDescending(b => b.ReleaseDate)
.ThenBy(b => b.Title)
.Take(3)
.Select(b => new
{
b.Title,
b.ReleaseDate.Year
})
});
foreach (var category in categoriesRecentBooks)
{
Console.WriteLine("--{0}: {1} books", category.Category, category.BooksCount);
foreach (var book in category.Books)
{
Console.WriteLine("{0} ({1})", book.Title, book.Year);
}
}
Console.WriteLine();
// Problem 07
var booksToAddRelatedBook = context.Books
.Take(3)
.ToList();
booksToAddRelatedBook[0].RelatedBooks.Add(booksToAddRelatedBook[1]);
booksToAddRelatedBook[1].RelatedBooks.Add(booksToAddRelatedBook[0]);
booksToAddRelatedBook[0].RelatedBooks.Add(booksToAddRelatedBook[2]);
booksToAddRelatedBook[2].RelatedBooks.Add(booksToAddRelatedBook[0]);
context.SaveChanges();
// Query the first three books
// and get their names and their related book names
var booksFromQuery = context.Books
.Take(3)
.Select(b => new
{
b.Title,
RelatedBooks = b.RelatedBooks.Select(r => r.Title)
});
foreach (var book in booksFromQuery)
{
Console.WriteLine("--{0}", book.Title);
foreach (var relatedBook in book.RelatedBooks)
{
Console.WriteLine(relatedBook);
}
}
}
