public Task <IEnumerable <ColumnInfo> > GetColumnInfoAsync(string schemaName, string tableName)
{
object[] parameters =
{
new MySqlParameter
{
DbType = DbType.String,
ParameterName = "p_schema_name",
Value = schemaName
},
new MySqlParameter
{
DbType = DbType.String,
ParameterName = "p_table_name",
Value = tableName
}
};
return(Task.Run(() =>
{
var result = _dbContext.Set <ColumnInfo>()
.FromSqlRaw("call sp_query_column_info(@p_schema_name,@p_table_name)", parameters).AsNoTracking();
return result.AsEnumerable();
}));
}
public void TestReadEachSeparately()
{
//SETUP
int bookId;
var options = SqliteInMemory.CreateOptions <EfCoreContext>();
using (var context = new EfCoreContext(options))
{
context.Database.EnsureCreated();
context.SeedDatabaseFourBooks();
bookId = context.Books.Single(x => x.Reviews.Any()).BookId;
}
using (var context = new EfCoreContext(options))
{
//ATTEMPT
var book = context.Books.ToList();
var reviews = context.Set <Review>().ToList();
var authorsLinks = context.Set <BookAuthor>().ToList();
var authors = context.Authors.ToList();
//VERIFY
book.Last().Reviews.Count.ShouldEqual(2);
book.Last().AuthorsLink.Single().Author.ShouldNotBeNull();
}
}
public void TestBookWithRelatedLinks()
{
//SETUP
var options = SqliteInMemory.CreateOptions <EfCoreContext>();
using (var context = new EfCoreContext(options))
{
context.Database.EnsureCreated();
context.SeedDatabaseFourBooks();
//ATTEMPT
var book = context.Books
.Include(p => p.Promotion) //#A
.Include(p => p.Reviews) //#A
.Include(p => p.AuthorsLink) //#A
.Single(p => p.Title //#B
== "Quantum Networking"); //#B
context.Books.Remove(book); //#C
context.SaveChanges(); //#D
/**********************************************************
#A The three Includes make sure that the three dependent relationships are loaded with the Book
#B This finds the "Quantum Networking" book, which I know has a promotion, 2 reviews and one BookAuthor link
#B I then delete that book
#C The SaveChanges calls DetectChanges which finds a tracked Book entity which is marked as deleted. It then deletes its dependent relationships and then deletes the book
* *******************************************************/
//VERIFY
context.Books.Count().ShouldEqual(3);
context.PriceOffers.Count().ShouldEqual(0); //Quantum Networking is the only book with a priceOffer and reviews
context.Set <Review>().Count().ShouldEqual(0);
context.Set <BookAuthor>().Count().ShouldEqual(3); //three books left, each with a one author
}
}
public void TestBookWithRelatedLinksWithoutIncludes()
{
//SETUP
var options =
this.NewMethodUniqueDatabaseSeeded4Books();
using (var context = new EfCoreContext(options))
{
var logIt = new LogDbContext(context);
//ATTEMPT
var book = context.Books
.Single(p => p.Title
== "Quantum Networking");
context.Books.Remove(book);
context.SaveChanges();
//VERIFY
context.Books.Count().ShouldEqual(3);
context.PriceOffers.Count().ShouldEqual(0); //Quantum Networking is the only book with a priceOffer and reviews
context.Set <Review>().Count().ShouldEqual(0);
context.Set <BookAuthor>().Count().ShouldEqual(3); //three books left, each with a one author
foreach (var log in logIt.Logs)
{
_output.WriteLine(log);
}
}
}
public void TestBookWithRelatedLinksWithoutIncludes()
{
//SETUP
var options = SqliteInMemory.CreateOptions <EfCoreContext>();
using (var context = new EfCoreContext(options))
{
context.Database.EnsureCreated();
context.SeedDatabaseFourBooks();
//ATTEMPT
var book = context.Books
.Single(p => p.Title
== "Quantum Networking");
context.Books.Remove(book);
context.SaveChanges();
//VERIFY
context.Books.Count().ShouldEqual(3);
context.PriceOffers.Count().ShouldEqual(0); //Quantum Networking is the only book with a priceOffer and reviews
context.Set <Review>().Count().ShouldEqual(0);
context.Set <BookAuthor>().Count().ShouldEqual(3); //three books left, each with a one author
}
}
public void TestChangeAllAuthorsViaForeignKeyOk()
{
//SETUP
var options = SqliteInMemory.CreateOptions <EfCoreContext>();
ChangeAuthorDto dto;
using var context = new EfCoreContext(options);
context.Database.EnsureCreated();
context.SeedDatabaseFourBooks();
var book = context.Books
.Include(p => p.AuthorsLink)
.Single(p => p.Title == "Quantum Networking");
var newAuthor = context.Authors
.Single(p => p.Name == "Martin Fowler");
dto = new ChangeAuthorDto
{
BookId = book.BookId,
AuthorId = book.AuthorsLink.First().AuthorId,
NewAuthorId = newAuthor.AuthorId
};
context.ChangeTracker.Clear();
//ATTEMPT
var orgBookAuthor = context //#A
.Find <BookAuthor>(dto.BookId, dto.AuthorId); //#A
context.Set <BookAuthor>().Remove(orgBookAuthor); //#B
context.Set <BookAuthor>().Add(new BookAuthor //#C
{ //#C
BookId = dto.BookId, //#C
AuthorId = dto.NewAuthorId, //#C
Order = 0 //#C
}); //#C
context.SaveChanges(); //#D
/*****************************************************
#A I find the existing BookAuthor link using the BookId and the Authorid of the original author
#B I then delete the original link
#C Now I create a new BookAuthor link to the Author chosen by the user and add it the BookAuthor table
#D Finally I call SaveChanges which find the deleted BookAuthor entry and the new BookAuthor entry and deletes/adds then respectively
* **************************************************/
context.ChangeTracker.Clear();
//VERIFY
var bookAgain = context.Books
.Include(p => p.AuthorsLink).ThenInclude(p => p.Author)
.Single(p => p.BookId == dto.BookId);
bookAgain.AuthorsLink.ShouldNotBeNull();
bookAgain.AuthorsLink.Count.ShouldEqual(1);
bookAgain.AuthorsLink.First().Author.Name.ShouldEqual("Martin Fowler");
}
public void TestRemoveLinkByRemovingFromCollectionToAuthorOk()
{
//SETUP
var options = SqliteInMemory.CreateOptions <EfCoreContext>();
using var context = new EfCoreContext(options);
context.Database.EnsureCreated();
context.SeedDatabaseDummyBooks(1);
var bookId = context.Books.First().BookId;
context.ChangeTracker.Clear();
//ATTEMPT
var existingBook = context.Books
.Include(book => book.AuthorsLink.OrderBy(x => x.Order))
.Single(book => book.BookId == bookId);
var linkToRemove = existingBook.AuthorsLink.Last();
existingBook.AuthorsLink.Remove(linkToRemove);
context.SaveChanges();
context.ChangeTracker.Clear();
//VERIFY
var bookAgain = context.Books
.Include(p => p.AuthorsLink).ThenInclude(p => p.Author)
.Single(p => p.BookId == bookId);
bookAgain.AuthorsLink.ShouldNotBeNull();
bookAgain.AuthorsLink.Count.ShouldEqual(1);
bookAgain.AuthorsLink.First().Author.Name.ShouldEqual("Author0000");
context.Set <BookAuthor>().Count().ShouldEqual(1);
}
public void TestCreateBookUseCtorOk()
{
//SETUP
var options = SqliteInMemory.CreateOptions <EfCoreContext>();
using (var context = new EfCoreContext(options))
{
context.Database.EnsureCreated();
var utData = context.SetupSingleDtoAndEntities <CreatorOfBooksDto>();
var service = new CrudServices(context, utData.Wrapped);
//ATTEMPT
var dto = new CreatorOfBooksDto
{
Title = "Hello",
Price = 50,
PublishedOn = new DateTime(2010, 1, 1),
Authors = new List <Author> {
new Author {
Name = "test"
}
}
};
service.CreateAndSave(dto, "ctor");
//VERIFY
service.IsValid.ShouldBeTrue(service.GetAllErrors());
context.Set <Book>().Count().ShouldEqual(1);
var book = context.Find <Book>(1);
book.ActualPrice.ShouldEqual(dto.Price);
}
}
public void TestAddReviewToBookOk()
{
//SETUP
var options = SqliteInMemory.CreateOptions <EfCoreContext>();
using (var context = new EfCoreContext(options))
{
context.Database.EnsureCreated();
context.SeedDatabaseFourBooks();
}
using (var context = new EfCoreContext(options))
{
var service = new AddReviewService(context);
//ATTEMPT
var dto = service.GetOriginal(1);
dto.NumStars = 2;
service.AddReviewToBook(dto);
context.SaveChanges();
//VERIFY
context.Set <Review>().Count().ShouldEqual(3);
context.Books.Include(x => x.Reviews).Single(x => x.BookId == 1).Reviews.Single().NumStars.ShouldEqual(2);
}
}
public void TestUpdateAddReviewOk()
{
//SETUP
var options = SqliteInMemory.CreateOptions <EfCoreContext>();
using (var context = new EfCoreContext(options))
{
context.Database.EnsureCreated();
context.SeedDatabaseFourBooks();
//ATTEMPT
var utData = context.SetupSingleDtoAndEntities <Tests.Dtos.AddReviewDto>();
var service = new CrudServices(context, utData.Wrapped);
//ATTEMPT
var dto = new Tests.Dtos.AddReviewDto {
BookId = 1, Comment = "comment", NumStars = 3, VoterName = "user"
};
service.UpdateAndSave(dto, nameof(Book.AddReview));
//VERIFY
service.IsValid.ShouldBeTrue(service.GetAllErrors());
service.Message.ShouldEqual("Successfully updated the Book");
context.Set <Review>().Count().ShouldEqual(3);
}
}
public void TestAddReviewToBookOk()
{
//SETUP
var options = SqliteInMemory.CreateOptions <EfCoreContext>();
using (var context = new EfCoreContext(options))
{
context.Database.EnsureCreated();
context.SeedDatabaseFourBooks();
}
using (var context = new EfCoreContext(options))
{
var utData = context.SetupSingleDtoAndEntities <AddReviewDto>();
var service = new CrudServices(context, utData.Wrapped);
//ATTEMPT
var dto = new AddReviewDto {
BookId = 1, NumStars = 2
};
service.UpdateAndSave(dto);
//VERIFY
service.IsValid.ShouldBeTrue(service.GetAllErrors());
context.Set <Review>().Count().ShouldEqual(3);
context.Books.Include(x => x.Reviews).Single(x => x.BookId == 1).Reviews.Single().NumStars.ShouldEqual(2);
}
}
public void TestCreateBookOk()
{
//SETUP
var options = SqliteInMemory.CreateOptions <EfCoreContext>();
using (var context = new EfCoreContext(options))
{
context.Database.EnsureCreated();
context.SeedDatabaseFourBooks();
var utData = context.SetupSingleDtoAndEntities <CreateBookDto>();
var service = new CrudServices(context, utData.Wrapped);
//ATTEMPT
var dto = new CreateBookDto {
Title = "Hello", Price = 50, PublishedOn = new DateTime(2010, 1, 1)
};
dto.BookAuthorIds.Add(1);
dto.BeforeSave(context);
service.CreateAndSave(dto);
//VERIFY
service.IsValid.ShouldBeTrue(service.GetAllErrors());
context.Set <Book>().Count().ShouldEqual(5);
var book = context.Books.Include(x => x.AuthorsLink).ThenInclude(x => x.Author)
.Single(x => x.BookId == dto.BookId);
book.AuthorsLink.Single().Author.Name.ShouldEqual("Martin Fowler");
}
}
public void RunHandCoded()
{
//SETUP
using (var context = new EfCoreContext(_options))
{
var numReviews = context.Set <Review>().Count();
//ATTEMPT
var book = context.Books.Single(x => x.BookId == 1);
book.AddReview(5, "comment", "user", context);
context.SaveChanges();
//VERIFY
context.Set <Review>().Count().ShouldEqual(numReviews + 1);
}
}
public void TestReadBookWithSecondRead()
{
//SETUP
int bookId;
var options = SqliteInMemory.CreateOptions <EfCoreContext>();
using (var context = new EfCoreContext(options))
{
context.Database.EnsureCreated();
context.SeedDatabaseFourBooks();
bookId = context.Books.Single(x => x.Reviews.Any()).BookId;
}
using (var context = new EfCoreContext(options))
{
//ATTEMPT
var bookWithReviews = context.Books
.Single(x => x.BookId == bookId);
bookWithReviews.Reviews.ShouldBeNull();
var reviews = context.Set <Review>()
.Where(x => x.BookId == bookId)
.ToList();
//VERIFY
bookWithReviews.Reviews.Count.ShouldEqual(2);
}
}
public MultipleResult <ExpenseReport> Handle(ExpenseReportSpecificationQuery command)
{
var reports = _context.Set <ExpenseReport>()
.Include(r => r.AuditEntries).ThenInclude(a => a.Employee)
.Include(r => r.Submitter)
.Include(r => r.Approver)
.AsQueryable();
if (command.Approver != null)
{
reports = reports.Where(r => r.Approver == command.Approver);
}
if (command.Submitter != null)
{
reports = reports.Where(r => r.Submitter == command.Submitter);
}
if (command.Status != null)
{
reports = reports.Where(r => r.StatusCode == command.Status.Code);
}
IList <ExpenseReport> list = reports.ToList();
return(new MultipleResult <ExpenseReport> {
Results = new List <ExpenseReport>(list).ToArray()
});
}
public void TestMissingIncludeReplaceListAddsToDatabaseOk()
{
//SETUP
int twoReviewBookId;
var options = SqliteInMemory.CreateOptions <EfCoreContext>();
using (var context = new EfCoreContext(options))
{
context.Database.EnsureCreated();
twoReviewBookId = context.SeedDatabaseFourBooks().Last().BookId;
}
using (var context = new EfCoreContext(options))
{
var book = context.Books
//missing .Include(x => x.Reviews)
.Single(p => p.BookId == twoReviewBookId);
//ATTEMPT
book.Reviews = new List <Review> {
new Review {
NumStars = 1
}
};
context.SaveChanges();
//VERIFY
context.Set <Review>().Count().ShouldEqual(3);
}
}
public void TestCreateOrderAndAddToDbOk()
{
//SETUP
var options = SqliteInMemory.CreateOptions <EfCoreContext>();
using (var context = new EfCoreContext(options))
{
context.Database.EnsureCreated();
context.SeedDatabaseFourBooks();
}
using (var context = new EfCoreContext(options))
{
//ATTEMPT
var book = context.Books.First();
var lineItems = new List <OrderBooksDto> {
new OrderBooksDto(book.BookId, book, 1)
};
context.Add(Order.CreateOrderFactory("user", DateTime.Today.AddDays(3), lineItems).Result);
context.SaveChanges();
//VERIFY
context.Orders.Count().ShouldEqual(1);
context.Set <LineItem>().Count().ShouldEqual(1);
}
}
public async Task <ResourceDto> HandleAsync(GetResource query)
{
var entity = await _context.Set <ResourceEntity>()
.Where(r => r.Id == query.ResourceId)
.SingleOrDefaultAsync();
return(entity?.AsDto());
}
public MultipleResult <Employee> Handle(EmployeeSpecificationQuery request)
{
var employees = _context.Set <Employee>().ToArray();
Array.Sort(employees);
return(new MultipleResult <Employee> {
Results = employees
});
}
public SingleResult <ExpenseReport> Handle(ExpenseReportByNumberQuery request)
{
var report = _context.Set <ExpenseReport>()
.Include(r => r.AuditEntries).ThenInclude(a => a.Employee)
.Include(r => r.Approver)
.Include(r => r.Submitter)
.Single(r => r.Number == request.ExpenseReportNumber);
return(new SingleResult <ExpenseReport>(report));
}
public void RunGenericService()
{
//SETUP
using (var context = new EfCoreContext(_options))
{
var service = new CrudServices <EfCoreContext>(context, _utData.ConfigAndMapper);
var numReviews = context.Set <Review>().Count();
//ATTEMPT
var dto = new AddReviewDto {
BookId = 1, Comment = "comment", NumStars = 3, VoterName = "user"
};
service.UpdateAndSave(dto, nameof(Book.AddReview));
//VERIFY
service.IsValid.ShouldBeTrue(service.GetAllErrors());
context.Set <Review>().Count().ShouldEqual(numReviews + 1);
}
}
public void TestReplaceReviewsLoggedOk()
{
//SETUP
int twoReviewBookId;
var showLog = false;
var options = SqliteInMemory.CreateOptionsWithLogging <EfCoreContext>(log =>
{
if (showLog)
{
_output.WriteLine(log.DecodeMessage());
}
});
using var context = new EfCoreContext(options);
context.Database.EnsureCreated();
context.SeedDatabaseFourBooks();
twoReviewBookId = context.Books.ToList().Last().BookId; //Last has reviews
context.ChangeTracker.Clear();
//ATTEMPT
showLog = true;
var book = context.Books
.Include(p => p.Reviews) //#A
.Single(p => p.BookId == twoReviewBookId); //#B
book.Reviews = new List <Review> //#C
{ //#C
new Review //#C
{ //#C
VoterName = "Unit Test", //#C
NumStars = 5, //#C
} //#C
}; //#C
context.SaveChanges(); //#D
/*******************************************************
#A This include is important; this will create a collection with any existing reviews in it, or an empty collection if there aren't any existing reviews.
#B This book I am loading has two review
#C I completely replace the whole collection
#D SaveChanges, via DetectChanges knows that a) the old collection should be deleted, b) the new collection should be written to the database
* ******************************************************/
//VERIFY
var bookAgain = context.Books
.Include(p => p.Reviews)
.Single(p => p.BookId == book.BookId);
bookAgain.Reviews.ShouldNotBeNull();
bookAgain.Reviews.Count.ShouldEqual(1);
context.Set <Review>().Count().ShouldEqual(1);
}
public void TestDeleteBookWithDependentEntityCascadeDeleteOk()
{
//SETUP
int bookId;
var options = SqliteInMemory.CreateOptions <EfCoreContext>();
using var context = new EfCoreContext(options);
context.Database.EnsureCreated();
var bookSetup = new Book
{
Title = "Test Book",
Reviews = new List <Review> {
new Review()
}
};
context.Add(bookSetup);
context.SaveChanges();
bookId = bookSetup.BookId;
context.Books.Count().ShouldEqual(1);
context.Set <Review>().Count().ShouldEqual(1);
context.ChangeTracker.Clear();
//ATTEMPT
var book = new Book {
BookId = bookId
};
context.Remove(book);
context.SaveChanges();
context.ChangeTracker.Clear();
//VERIFY
context.ChangeTracker.Clear();
context.Books.Count().ShouldEqual(0);
context.Set <Review>().Count().ShouldEqual(0);
}
public void TestRemoveReviewBookNoExistingReviewsOk()
{
//SETUP
var options = SqliteInMemory.CreateOptions <EfCoreContext>();
using (var context = new EfCoreContext(options))
{
context.Database.EnsureCreated();
context.SeedDatabaseFourBooks();
context.ChangeTracker.Clear();
//ATTEMPT
var book = context.Books.Single(x => x.Reviews.Count() == 2);
book.RemoveReview(context.Set <Review>().First(), context);
context.SaveChanges();
//VERIFY
context.Set <Review>().Count().ShouldEqual(1);
}
}
public void TestReplaceReviewsLoggedOk()
{
//SETUP
var options =
this.NewMethodUniqueDatabaseSeeded4Books( );
int twoReviewBookId;
using (var context = new EfCoreContext(options))
{
//ATTEMPT
twoReviewBookId = context.Books.ToList().Last().BookId; //Last has reviews
}
using (var context = new EfCoreContext(options))
{
var logIt = new LogDbContext(context);
var book = context.Books
.Include(p => p.Reviews) //#A
.Single(p => p.BookId == twoReviewBookId); //#B
book.Reviews = new List <Review> //#C
{ //#C
new Review //#C
{ //#C
VoterName = "Unit Test", //#C
NumStars = 5, //#C
} //#C
}; //#C
context.SaveChanges(); //#D
/*******************************************************
#A This include is important, otherwise EF Core won't know about the old reviews
#B This book I am loading has two review
#C I completely replace the whole collection
#D SaveChanges, via DetectChanges knows that a) the old collection should be deleted, b) the new collection should be written to the database
* ******************************************************/
//VERIFY
var bookAgain = context.Books
.Include(p => p.Reviews)
.Single(p => p.BookId == book.BookId);
bookAgain.Reviews.ShouldNotBeNull();
bookAgain.Reviews.Count.ShouldEqual(1);
context.Set <Review>().Count().ShouldEqual(1);
foreach (var log in logIt.Logs)
{
_output.WriteLine(log);
}
}
}
public Task <IEnumerable <TableInfo> > GetTableInfoAsync(string schemaName)
{
var param = new MySqlParameter
{
DbType = DbType.String,
ParameterName = "p_schema_name",
Value = schemaName
};
return(Task.Run(() =>
_dbContext.Set <TableInfo>().FromSqlRaw("call sp_query_table_info(@p_schema_name)", param).AsNoTracking()
.AsEnumerable()));
}
public void TestLoadOnlyBookAndDelete()
{
//SETUP
var options = SqliteInMemory.CreateOptions <EfCoreContext>();
using var context = new EfCoreContext(options);
context.Database.EnsureCreated();
context.SeedDatabaseFourBooks();
context.ChangeTracker.Clear();
//ATTEMPT
var book = context.Books
.Single(p => p.Title == "Quantum Networking");
context.Remove(book);
context.SaveChanges();
//VERIFY
context.Books.Count().ShouldEqual(3);
context.Set <BookAuthor>().Count().ShouldEqual(3);
context.Set <Review>().Count().ShouldEqual(0);
}
public void TestAddAuthorViaForeignKeyOk()
{
//SETUP
var options = SqliteInMemory.CreateOptions <EfCoreContext>();
ChangeAuthorDto dto;
using var context = new EfCoreContext(options);
context.Database.EnsureCreated();
context.SeedDatabaseFourBooks();
var book = context.Books
.Include(p => p.AuthorsLink)
.Single(p => p.Title == "Quantum Networking");
var newAuthor = context.Authors
.Single(p => p.Name == "Martin Fowler");
dto = new ChangeAuthorDto
{
BookId = book.BookId,
NewAuthorId = newAuthor.AuthorId
};
context.ChangeTracker.Clear();
//ATTEMPT
context.Set <BookAuthor>().Add(new BookAuthor
{
BookId = dto.BookId,
AuthorId = dto.NewAuthorId,
Order = 1
});
context.SaveChanges();
context.ChangeTracker.Clear();
//VERIFY
var bookAgain = context.Books
.Include(p => p.AuthorsLink).ThenInclude(p => p.Author)
.Single(p => p.BookId == dto.BookId);
bookAgain.AuthorsLink.ShouldNotBeNull();
bookAgain.AuthorsLink.Count.ShouldEqual(2);
var authorsInOrder = bookAgain.AuthorsLink.OrderBy(p => p.Order).ToList();
authorsInOrder.First().Author.Name.ShouldEqual("Future Person");
authorsInOrder.Last().Author.Name.ShouldEqual("Martin Fowler");
}
public void SoftDeleteOneBookSetT()
{
//SETUP
var options = SqliteInMemory.CreateOptions <EfCoreContext>();
using (var context = new EfCoreContext(options))
{
context.Database.EnsureCreated();
context.SeedDatabaseFourBooks();
//ATTEMPT
context.Books.First().SoftDeleted = true;
context.SaveChanges();
//VERIFY
context.Set <Book>().Count().ShouldEqual(3);
}
}
public void TestChangeReviewViaForeignKeyOk()
{
//SETUP
ChangeReviewDto dto;
var options = SqliteInMemory.CreateOptions <EfCoreContext>();
using var context = new EfCoreContext(options);
context.Database.EnsureCreated();
context.SeedDatabaseFourBooks();
var book = context.Books.First();
var review = context.Set <Review>().First();
dto = new ChangeReviewDto
{
ReviewId = review.ReviewId,
NewBookId = book.BookId
};
context.ChangeTracker.Clear();
//ATTEMPT
var reviewToChange = context //#A
.Find <Review>(dto.ReviewId); //#A
reviewToChange.BookId = dto.NewBookId; //#B
context.SaveChanges(); //#C
/*****************************************************
#A I find the review that I want to move using the primary key returned from the browser
#C I then change the foreign key in the review to point to the book it should be linked to
#D Finally I call SaveChanges which finds the foreign key in the Review changed, so it updates that column in the database
* **************************************************/
//VERIFY
var bookAgain = context.Books
.Include(p => p.Reviews)
.First();
bookAgain.Reviews.ShouldNotBeNull();
bookAgain.Reviews.Count.ShouldEqual(1);
}
