static void Main(string[] args)
{
var context = new BookShopDBContext();
context.Database.Initialize(true);
var bookcount = context.Books.Count();
Console.WriteLine(bookcount);
var books = context.Books.Take(3).ToList();
//Ruun the following lines ONLY once! Or it will print the books 2 times.
//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);
}
Console.WriteLine();
}
}
private void SeedCategories(BookShopDBContext context)
{
int booksCount = context.Books.Local.Count;
string[] categories = File.ReadAllLines("../../../Data/Imports/categories.csv");
for (int i = 1; i < categories.Length; i++)
{
string[] data = categories[i]
.Split(',')
.Select(arg => arg.Replace("\"", string.Empty))
.ToArray();
string categoryName = data[0];
Category category = new Category()
{
Name = categoryName
};
int bookIndex = (i * 30) % booksCount;
for (int j = 0; j < bookIndex; j++)
{
Book book = context.Books.Local[j];
category.Books.Add(book);
}
context.Categories.AddOrUpdate(c => c.Name, category);
}
}
public DemoService(
IOptionsSnapshot <DemoSettings> settings,
BookShopDBContext dbContext)
{
_settings = settings.Value;
_dbContext = dbContext;
}
public BasicAuthenticationHandler(
IOptionsMonitor <AuthenticationSchemeOptions> options,
ILoggerFactory logger,
UrlEncoder encoder,
ISystemClock clock,
BookShopDBContext context)
: base(options, logger, encoder, clock)
{
_context = context;
}
static void Main(string[] args)
{
var ctx = new BookShopDBContext();
Console.WriteLine("enter number:");
int n = int.Parse(Console.ReadLine());
var books = ctx.Books
.Where(b => b.Title.Length > n)
.Count();
Console.WriteLine(books);
}
public void ListAll()
{
using (var db = new BookShopDBContext())
{
foreach (var book in db.Books.AsNoTracking().Include(book => book.Author))
{
var webURL = book.Author.WebUrl == null
? "- no web URL given -" : book.Author.WebUrl;
Console.WriteLine($"{book.Title} by {book.Author.Name}");
}
}
}
static void Main()
{
var context = new BookShopDBContext();
var books = context.Books
.Where(b => b.Price <5m || b.Price> 40m)
.OrderBy(b => b.Id);
foreach (var book in books)
{
Console.WriteLine($"{book.Title} - ${book.Price}");
}
}
static void Main()
{
var ctx = new BookShopDBContext();
Console.WriteLine("Enter book categories:");
var categories = Console.ReadLine().ToLower().Split(new[] { ' ' }, StringSplitOptions.RemoveEmptyEntries).ToList();
var books = ctx.Books.AsNoTracking().Where(b => (b.Categories.Select(c => c.Name.ToLower()).Intersect(categories)).Count() > 0);
foreach (var book in books)
{
Console.WriteLine(book.Title);
}
}
static void Main(string[] args)
{
var ctx = new BookShopDBContext();
var tran = ctx.Database.BeginTransaction();
var books = ctx.Books
.Where(b => b.Copies < 4200)
.ToList();
ctx.Books.RemoveRange(books);
ctx.SaveChanges();
Console.WriteLine($"{books.Count} books were deleted");
tran.Rollback();
}
static void Main()
{
var context = new BookShopDBContext();
Console.WriteLine("Enter a book release year that will be excluded:");
int year = int.Parse(Console.ReadLine());
var books = context.Books
.Where(b => b.ReleaseDate.Year != year)
.OrderBy(b => b.Id);
foreach (var book in books)
{
Console.WriteLine(book.Title);
}
}
static void Main(string[] args)
{
Console.WriteLine("Enter release date");
string input = Console.ReadLine();
var releaseDate = DateTime.ParseExact(input, "d-M-yyyy", CultureInfo.InvariantCulture);
var ctx = new BookShopDBContext();
var books = ctx.Books.Where(b => b.ReleaseDate < releaseDate)
.Select(b => new { b.Title, b.EditionType, b.Price })
.ToList();
foreach (var book in books)
{
Console.WriteLine($"{book.Title} - {(EditionType)book.EditionType} - {book.Price}");
}
}
static void Main(string[] args)
{
var ctx = new BookShopDBContext();
var authors = ctx.Books
.GroupBy(b => new { b.Author.FirstName, b.Author.LastName })
.Select(b => new
{ name = b.Key,
total = b.Sum(c => c.Copies), }
)
.OrderByDescending(b => b.total);
foreach (var a in authors)
{
Console.WriteLine($"{a.name.FirstName} {a.name.LastName} - {a.total}");
}
}
static void Main(string[] args)
{
var ctx = new BookShopDBContext();
Console.WriteLine("enter string:");
string s = Console.ReadLine().ToLower();
var books = ctx.Books
.AsNoTracking()
.Where(b => b.Title.ToLower().Contains(s))
.Select(b => b.Title);
foreach (var t in books)
{
Console.WriteLine(t);
}
}
static void Main(string[] args)
{
var ctx = new BookShopDBContext();
Console.WriteLine("enter string:");
string s = Console.ReadLine().ToLower();
var books = ctx.Books
.Where(b => b.Author.LastName.Substring(0, s.Length).ToLower() == s)
.OrderBy(b => b.Id)
.Select(b => new { b.Title, b.Author.FirstName, b.Author.LastName });
foreach (var b in books)
{
Console.WriteLine($"{b.Title} ({b.FirstName} {b.LastName})");
}
}
static void Main()
{
Console.WriteLine("Golden Edition Books:");
Console.WriteLine("----------------------");
var context = new BookShopDBContext();
var goldenBooksTitles = context.Books
.Where(b => b.EditionType == (int)EditionType.Gold && b.Copies < 5000)
.OrderBy(b => b.Id)
.Select(b => b.Title)
.ToList();
foreach (var title in goldenBooksTitles)
{
Console.WriteLine(title);
}
}
static void Main()
{
Console.WriteLine("Enter age category:");
string input = Console.ReadLine().Trim();
AgeRestriction ageRestriction = (AgeRestriction)Enum.Parse(typeof(AgeRestriction), input, true);
var context = new BookShopDBContext();
var bookTitles = context.Books
.Where(b => b.AgeRestriction == (int)ageRestriction)
.Select(b => b.Title)
.ToList();
foreach (var title in bookTitles)
{
Console.WriteLine(title);
}
}
public void ChangeWebURL()
{
Console.Write("New Quantum Networking WebUrl > ");
string newWebUrl = Console.ReadLine();
using (var db = new BookShopDBContext())
{
Book singleBook = db.Books
.Include(book => book.Author)
.Single(book => book.Title == "Quantum Networking");
singleBook.Author.WebUrl = newWebUrl;
db.SaveChanges();
Console.WriteLine("changes had been saved ...");
}
ListAll();
}
static void Main(string[] args)
{
var ctx = new BookShopDBContext();
var categories = ctx.Categories
.GroupBy(ca => ca.Name)
.Select(b => new
{
name = b.Key,
profit = b.Sum(c => c.Books.Sum(bb => bb.Copies * bb.Price))
})
.OrderByDescending(b => b.profit)
.ThenBy(c => c.name);;
foreach (var c in categories)
{
Console.WriteLine($"{c.name} - ${c.profit}");
}
}
static void Main(string[] args)
{
var ctx = new BookShopDBContext();
var tran = ctx.Database.BeginTransaction();
var date = DateTime.ParseExact("06 Jun 2013", "d MMM yyyy", CultureInfo.InvariantCulture);
var books = ctx.Books
.Where(b => b.ReleaseDate > date)
.ToList();
int addedBookCopies = 0;
foreach (var b in books)
{
b.Copies += 44;
addedBookCopies += 44;
}
Console.WriteLine(addedBookCopies);
tran.Rollback();
}
static void Main(string[] args)
{
var ctx = new BookShopDBContext();
Console.WriteLine("enter string:");
string s = Console.ReadLine();
var authors = ctx.Authors
.Where(a => a.FirstName.Substring
(
a.FirstName.Length - s.Length,
s.Length
) == s)
.Select(a => new { a.FirstName, a.LastName });
foreach (var a in authors)
{
Console.WriteLine($"{a.FirstName} {a.LastName}");
}
}
static void Main(string[] args)
{
var ctx = new BookShopDBContext();
var categories = ctx.Categories
.OrderByDescending(c => c.Books.Count);
foreach (var cat in categories)
{
Console.WriteLine($"--{cat.Name}: {cat.Books.Count}");
var books = cat.Books
.OrderByDescending(b => b.ReleaseDate)
.Take(3);
foreach (var b in books)
{
Console.WriteLine($"{b.Title} ({b.ReleaseDate.Year})");
}
}
}
private void SeedAuthors(BookShopDBContext context)
{
string[] authors = File.ReadAllLines("../../../Data/Imports/authors.csv");
// Foreach all authors and add them to database.
// Skip first row because it is only describing column names.
for (int i = 1; i < authors.Length; i++)
{
string[] data = authors[i].Split(',');
string firstName = data[0].Replace("\"", string.Empty);
string lastName = data[1].Replace("\"", string.Empty);
Author author = new Author()
{
FirstName = firstName,
LastName = lastName
};
// When adding we may want to check if author with same first name and last name is existing.
context.Authors.AddOrUpdate(a => new { a.FirstName, a.LastName }, author);
}
}
static void Main(string[] args)
{
var ctx = new BookShopDBContext();
Console.WriteLine("Enter author name:");
string[] name = Console.ReadLine().Split(new[] { ' ' }, StringSplitOptions.RemoveEmptyEntries);
string fName = name[0];
string lName = name[1];
SqlParameter fNameParameter = new SqlParameter("@fName", SqlDbType.VarChar);
fNameParameter.Value = fName;
SqlParameter lNameParameter = new SqlParameter("@lName", SqlDbType.VarChar);
lNameParameter.Value = lName;
var result = ctx.Database.SqlQuery <int>($"EXEC dbo.TotalBooksByAuthor {@fName}, {@lName}").First();
Console.WriteLine(result);
}
private void SeedBooks(BookShopDBContext context)
{
int authorsCount = context.Authors.Local.Count;
string[] books = File.ReadAllLines("../../../Data/Imports/books.csv");
for (int i = 1; i < books.Length; i++)
{
string[] data = books[i]
.Split(',')
.Select(arg => arg.Replace("\"", string.Empty))
.ToArray();
int authorIndex = i % authorsCount;
Author author = context.Authors.Local[authorIndex];
EditionType edition = (EditionType)int.Parse(data[0]);
DateTime releaseDate = DateTime.ParseExact(data[1], "d/M/yyyy", CultureInfo.InvariantCulture);
int copies = int.Parse(data[2]);
decimal price = decimal.Parse(data[3]);
AgeRestriction ageRestriction = (AgeRestriction)int.Parse(data[4]);
string title = data[5];
Book book = new Book
{
Author = author,
AuthorId = author.Id,
EditionType = edition,
ReleaseDate = releaseDate,
Copies = copies,
Price = price,
AgeRestriction = ageRestriction,
Title = title
};
context.Books.AddOrUpdate(b => new { b.Title, b.AuthorId }, book);
}
}
public BookRepository(BookShopDBContext context)
{
_context = context;
}
public BookShopRepository(BookShopDBContext bookShopDBContext)
{
Context = bookShopDBContext;
}
public PublishersController(BookShopDBContext context)
{
_context = context;
}
public BookController(ILogger <BookController> logger, BookShopDBContext context)
{
_logger = logger;
_context = context;
}
public AuthorsController(BookShopDBContext context)
{
_context = context;
}
public StoresController(BookShopDBContext context)
{
_context = context;
}