public void TestRightWayToSetupRelationshipsOk()
{
//SETUP
var showlog = false;
var options = SqliteInMemory.CreateOptionsWithLogging <EfCoreContext>(log =>
{
if (showlog)
{
_output.WriteLine(log.Message);
}
});
using (var context = new EfCoreContext(options))
{
context.Database.EnsureCreated();
showlog = true;
//ATTEMPT
var book = new Book //#A
{ //#A
Title = "Test", //#A
Reviews = new List <Review>() //#A
}; //#A
book.Reviews.Add( //#B
new Review {
NumStars = 1
}); //#B
context.Add(book); //#C
context.SaveChanges(); //#D
/*********************************************************
#A This creates a new Book
#B This adds a new Review to the Book's Reviews navigational property
#C The Add method says that the entity instance should be Added to the appropriate row, with any relationships either added or updated
#D The SaveChanges carries out the database update
*********************************************************/
//VERIFY
var bookWithReview = context.Books.Include(x => x.Reviews).Single();
bookWithReview.Reviews.Count.ShouldEqual(1);
}
}
public void TestCreateBookWithExistingAuthorAttached()
{
//SETUP
Author disconnectedAuthor;
var options = SqliteInMemory.CreateOptions <EfCoreContext>();
using (var context = new EfCoreContext(options))
{
context.Database.EnsureCreated();
context.SeedDatabaseFourBooks();
//ATTEMPT
disconnectedAuthor = context.Authors.First();
}
using (var context = new EfCoreContext(options))
{
var book = new Book
{
Title = "Test Book",
PublishedOn = DateTime.Today
};
context.Authors.Attach(disconnectedAuthor);
book.AuthorsLink = new List <BookAuthor>
{
new BookAuthor
{
Book = book,
Author = disconnectedAuthor
}
};
//ATTEMPT
context.Add(book);
context.SaveChanges();
//VERIFY
context.Books.Count().ShouldEqual(5);
context.Authors.Count().ShouldEqual(3);
}
}
public void TestCreateBookWithReview()
{
//SETUP
var options = SqliteInMemory.CreateOptions <EfCoreContext>();
using (var context = new EfCoreContext(options))
{
context.Database.EnsureCreated();
var book = new Book //#A
{ //#A
Title = "Test Book", //#A
PublishedOn = DateTime.Today, //#A
Reviews = new List <Review>() //#B
{
new Review //#C
{ //#C
NumStars = 5, //#C
Comment = "Great test book!", //#C
VoterName = "Mr U Test" //#C
}
}
};
//ATTEMPT
context.Add(book); //#D
context.SaveChanges(); //#E
/******************************************************
#A This creates the book with the title "Test Book"
#B I create a new collection of Reviews
#C I add one review, with its content
#D It uses the .Add method to add the book to the application's DbContext property, Books
#E It calls the SaveChanges() method from the application's DbContext to update the database. It finds a new Book, which has a collection containing a new Review, so it adds both of these to the database
* *****************************************************/
//VERIFY
context.Books.Count().ShouldEqual(1);
context.Set <Review>().Count().ShouldEqual(1);
}
}
public void TestTestCreateBookWithExistingAuthorOk()
{
//SETUP
var options = SqliteInMemory.CreateOptions <EfCoreContext>();
using (var context = new EfCoreContext(options))
{
context.Database.EnsureCreated();
context.SeedDatabaseFourBooks();
}
using (var context = new EfCoreContext(options))
{
//STAGE1 //#A
var author = context.Authors.First(); //#B
var bookAuthor = new BookAuthor {
Author = author
}; //#C
var book = new Book //#D
{ //#D
Title = "Test Book", //#D
AuthorsLink = new List <BookAuthor> {
bookAuthor
} //#D
}; //#D
//STAGE2
context.Add(book); //#E
//STAGE3
context.SaveChanges(); //#F
/*********************************************************
#A Each of the three stages start with a comment
#B This reads in an existing Author for the new book
#C This creates a new BookAuthor linking table ready to link to Book to the Author
#D This creates a Book, and fills in the AuthorsLink navigational property with a single entry, linking it to the existing Author
#E This is where you call the Add method, which tells EF Core that the Book needs to be added to the database.
#F SaveChanges now looks the all the tracked entities and works out how to update the database to achieve what you have asked it to do
******************************************************/
}
}
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 TestBookQueryWithIncludes()
{
//SETUP
var options = SqliteInMemory.CreateOptions <EfCoreContext>();
using var context = new EfCoreContext(options);
context.Database.EnsureCreated();
var book4Reviews2Authors = EfTestData.CreateDummyBooks(5).Last();
context.Add(book4Reviews2Authors);
context.SaveChanges();
//ATTEMPT
var query = context.Books
.Include(x => x.Reviews)
.Include(x => x.AuthorsLink)
.ThenInclude(x => x.Author);
//VERIFY
_output.WriteLine(query.ToQueryString());
}
public void TestDeleteBookNoRelationshipsOk()
{
//SETUP
int bookId;
var options = SqliteInMemory.CreateOptions <EfCoreContext>();
using var context = new EfCoreContext(options);
context.Database.EnsureCreated();
var bookSetup = new Book {
Title = "Test Book"
};
context.Add(bookSetup);
context.SaveChanges();
bookId = bookSetup.BookId;
context.ChangeTracker.Clear();
//ATTEMPT
var book = new Book //#A
{
BookId = bookId //#B
};
context.Remove(book); //#C
context.SaveChanges(); //#D
/*****************************************************
#A This creates the entity class that we want to delete - in this case a Book
#B This sets the primary key of the entity instance
#C The call to Remove tells EF Core you want this entity/row to be deleted
#D Then SaveChanges sends the command to the database to delete that row
****************************************************/
context.ChangeTracker.Clear();
//VERIFY
context.Books.Count().ShouldEqual(0);
}
public void TestIncludeSortSingle()
{
//SETUP
var options = SqliteInMemory.CreateOptions <EfCoreContext>();
using (var context = new EfCoreContext(options))
{
context.Database.EnsureCreated();
var newBook = new Book
{
AuthorsLink = new List <BookAuthor>
{
new BookAuthor {
Author = new Author {
Name = "Author2"
}, Order = 2
},
new BookAuthor {
Author = new Author {
Name = "Author1"
}, Order = 1
},
}
};
context.Add(newBook);
context.SaveChanges();
}
using (var context = new EfCoreContext(options))
{
//ATTEMPT
var query = context.Books
.Include(x => x.AuthorsLink.OrderBy(y => y.Order))
.ThenInclude(x => x.Author);
var books = query.ToList();
//VERIFY
_output.WriteLine(query.ToQueryString());
books.Single().AuthorsLink.Select(x => x.Author.Name).ShouldEqual(new[] { "Author1", "Author2" });
}
}
public void TestCreateBookWithExistingAuthorAttached()
{
//SETUP
var options =
this.NewMethodUniqueDatabaseSeeded4Books();
Author disconnectedAuthor;
using (var context = new EfCoreContext(options))
{
//ATTEMPT
disconnectedAuthor = context.Authors.First();
}
using (var context = new EfCoreContext(options))
{
var book = new Book
{
Title = "Test Book",
PublishedOn = DateTime.Today
};
context.Authors.Attach(disconnectedAuthor);
book.AuthorsLink = new List <BookAuthor>
{
new BookAuthor
{
Book = book,
Author = disconnectedAuthor
}
};
//ATTEMPT
context.Add(book);
context.SaveChanges();
//VERIFY
context.Books.Count().ShouldEqual(5);
context.Authors.Count().ShouldEqual(3);
}
}
public void TestBookListReviewSaveChangesTimeOk(int numReviews)
{
//SETUP
var options = SqliteInMemory.CreateOptions <EfCoreContext>();
using var context = new EfCoreContext(options);
context.Database.EnsureCreated();
var book = CreateBookWithListReviews(numReviews);
using (new TimeThings(_output, $"ADD BookListReview: {numReviews} entries"))
context.Add(book);
context.SaveChanges();
using (new TimeThings(_output, $"1: BookListReview: {numReviews} entries"))
{
context.SaveChanges();
}
using (new TimeThings(_output, $"2: BookListReview: {numReviews} entries"))
{
context.SaveChanges();
}
}
public void TestDeletePriceOfferQuicklyOk()
{
//SETUP
int priceOfferId;
var options = SqliteInMemory.CreateOptions <EfCoreContext>();
using var context = new EfCoreContext(options);
context.Database.EnsureCreated();
var book = new Book
{
Title = "Test Book",
Promotion = new PriceOffer {
NewPrice = 1
}
};
context.Add(book);
context.SaveChanges();
priceOfferId = book.Promotion.PriceOfferId;
context.Books.Count().ShouldEqual(1);
context.PriceOffers.Count().ShouldEqual(1);
context.ChangeTracker.Clear();
//ATTEMPT
var pOfferToDelete = new PriceOffer {
PriceOfferId = priceOfferId
};
context.Remove(pOfferToDelete);
context.SaveChanges();
context.ChangeTracker.Clear();
//VERIFY
context.Books.Count().ShouldEqual(1);
context.PriceOffers.Count().ShouldEqual(0);
}
public void TestIncludeSortReviewsDisconnected()
{
//SETUP
var options = SqliteInMemory.CreateOptions <EfCoreContext>();
using (var context = new EfCoreContext(options))
{
context.Database.EnsureCreated();
var newBook = new Book
{
Reviews = new List <Review>
{
new Review {
NumStars = 2
}, new Review {
NumStars = 1
}, new Review {
NumStars = 3
}
}
};
context.Add(newBook);
context.SaveChanges();
}
using (var context = new EfCoreContext(options))
{
//ATTEMPT
var query = context.Books
.Include(x => x.Reviews.OrderBy(y => y.NumStars));
var books = query.ToList();
//VERIFY
_output.WriteLine(query.ToQueryString());
books.Single().Reviews.Select(x => x.NumStars).ShouldEqual(new[] { 1, 2, 3 });
var hashSet = new HashSet <Review>(context.Set <Review>().ToList());
hashSet.Select(x => x.NumStars).ShouldEqual(new[] { 2, 1, 3 });
}
}
public void TestCreatePriceOffer()
{
//SETUP
var options = SqliteInMemory.CreateOptions <EfCoreContext>();
using (var context = new EfCoreContext(options))
{
context.Database.EnsureCreated();
context.SeedDatabaseFourBooks();
var orgPriceOffers = context.PriceOffers.Count();
var book = context.Books
.First(p => p.Promotion == null); //#A
//ATTEMPT
context.Add(new PriceOffer //#B
{ //#C
BookId = book.BookId, //#C
NewPrice = book.Price / 2, //#C
PromotionalText = "Half price today!" //#C
}); //#C
context.SaveChanges(); //#D
/******************************************************
#A Here I find the book that I want to add the new PriceOffer to. It must NOT have an existing PriceOffer
#B I add the new PriceOffer to the PriceOffers table
#C This defines the PriceOffer. Note that you MUST include the BookId (previously EF Core filled that in)
#D SaveChanges adds the PriceOffer to the PriceOffers table
* *****************************************************/
//VERIFY
var bookAgain = context.Books
.Include(p => p.Promotion)
.Single(p => p.BookId == book.BookId);
bookAgain.Promotion.ShouldNotBeNull();
context.PriceOffers.Count().ShouldEqual(orgPriceOffers + 1);
}
}
public void TestDeletePriceOfferRemoveNullsNavigatinalLinkOk()
{
//SETUP
var options = SqliteInMemory.CreateOptions <EfCoreContext>();
using var context = new EfCoreContext(options);
context.Database.EnsureCreated();
var book = new Book
{
Title = "Test Book",
Promotion = new PriceOffer {
NewPrice = 1
}
};
context.Add(book);
context.SaveChanges();
context.Books.Count().ShouldEqual(1);
context.PriceOffers.Count().ShouldEqual(1);
context.ChangeTracker.Clear();
//ATTEMPT
var bookWithPromotion = context.Books
.Include(x => x.Promotion).Single();
context.Remove(bookWithPromotion.Promotion);
context.SaveChanges();
bookWithPromotion.Promotion.ShouldBeNull();
context.ChangeTracker.Clear();
//VERIFY
context.Books.Count().ShouldEqual(1);
context.PriceOffers.Count().ShouldEqual(0);
}
public async Task CorrectPaymentsSelected()
{
// Arrange
var account = new Account("asdf");
var category = new Category("Test");
var payment1 = new Payment(DateTime.Now, 20, PaymentType.Expense, account);
var payment2 = new Payment(DateTime.Now, 30, PaymentType.Expense, account, category: category);
var payment3 = new Payment(DateTime.Now, 40, PaymentType.Expense, account, category: category);
context.Add(payment1);
context.Add(payment2);
context.Add(payment3);
await context.SaveChangesAsync();
// Act
System.Collections.Generic.List <Payment> result = await new GetPaymentsForCategoryQuery.Handler(contextAdapterMock.Object).Handle(
new GetPaymentsForCategoryQuery(
category.Id,
DateTime.Now.AddDays(-1),
DateTime.Now.AddDays(1)), default);
// Assert
result.Count.Should().Be(2);
}
public void Add(FormDictionary formDictionary)
{
_context.Add <FormDictionary>(formDictionary);
}
public async Task<int> Add(UserTask task)
{
_context.Add(task);
return await _context.SaveChangesAsync();
}
public void TestCopyOrderOk()
{
//SETUP
var sqlOptions = SqliteInMemory.CreateOptions <EfCoreContext>();
using (var context = new EfCoreContext(sqlOptions))
{
context.Database.EnsureCreated();
var books = context.SeedDatabaseFourBooks(); //#A
var order = new Order //#B
{
CustomerId = Guid.Empty, //#C
LineItems = new List <LineItem>
{
new LineItem //#D
{ //#D
LineNum = 1, ChosenBook = books[0], NumBooks = 1 //#D
}, //#D
new LineItem //#E
{ //#E
LineNum = 2, ChosenBook = books[1], NumBooks = 2 //#E
}, //#E
}
};
context.Add(order); //#F
context.SaveChanges(); //#F
/*************************************************************
#A For this test I add four books to use as test data
#B I create an Order with two LinItems that I want to copy
#C I set CustomerId to the default value so that the query filter lets me read the order back
#D I add the first LineNum linked to the first book
#E I add the second LineNum linked to the second book
#F I write this Order out to the database
**********************************************************/
}
using (var context = new EfCoreContext(sqlOptions))
{
//ATTEMPT
var order = context.Orders //#A
.AsNoTracking() //#B
.Include(x => x.LineItems) //#C
//#D
.Single(); //#E
order.OrderId = default; //#F
order.LineItems.First().LineItemId = default; //#F
order.LineItems.Last().LineItemId = default; //#F
context.Add(order); //#G
context.SaveChanges(); //#G
/******************************************************
#A This is going to query the Orders table.
#B The AsNoTracking means the entities read in as not tracked, that means their State will be Detached
#C You include the LineItems as we want to copy those too
#D You do NOT add .ThenInclude(x => x.ChosenBook) to the query. If you did it would copy the Book entities and that not what we want
#E You take the Order that we want to copy
#F You reset the primary keys (Order and LineItem) to their default value. That will tell the database to generate new primary keys
#G Finally you write out the order, which then create a copy.
****************************************************/
}
using (var context = new EfCoreContext(sqlOptions))
{
//VERIFY
var orders = context.Orders
.Include(x => x.LineItems)
.ToList();
orders.Count.ShouldEqual(2);
orders[0].LineItems.Count.ShouldEqual(2);
orders[1].LineItems.Count.ShouldEqual(2);
for (int i = 0; i < 2; i++)
{
orders[i].LineItems.OrderBy(x => x.LineNum).Select(x => x.BookId).ShouldEqual(new [] { 1, 2 });
orders[i].LineItems.OrderBy(x => x.LineNum).Select(x => x.NumBooks).ShouldEqual(new short[] { 1, 2 });
orders[i].LineItems.OrderBy(x => x.LineNum).Select(x => x.LineNum).ShouldEqual(new byte[] { 1, 2 });
}
}
}
public void Add(MDRDocument mDRDocument)
{
_context.Add <MDRDocument>(mDRDocument);
}
public void Add(Activity activity)
{
_context.Add <Activity>(activity);
}
public void TestCreateBookWithExistingAuthor_CheckThreeStagesOk()
{
//SETUP
var options = SqliteInMemory.CreateOptions <EfCoreContext>();
using (var context = new EfCoreContext(options))
{
context.Database.EnsureCreated();
context.SeedDatabaseFourBooks();
}
using (var context = new EfCoreContext(options))
{
//STAGE1
var author = context.Authors.First();
var bookAuthor = new BookAuthor {
Author = author
};
var book = new Book
{
Title = "Test Book",
AuthorsLink = new List <BookAuthor> {
bookAuthor
}
};
//Read the states
context.Entry(author).State.ShouldEqual(EntityState.Unchanged);
context.Entry(bookAuthor).State.ShouldEqual(EntityState.Detached);
context.Entry(book).State.ShouldEqual(EntityState.Detached);
//These navigational links have not been set up
book.AuthorsLink.Single().Book.ShouldBeNull();
author.BooksLink.ShouldBeNull();
//tracked PK/FK values not set
context.Entry(book).Property(nameof(Book.BookId)).CurrentValue.ShouldEqual(0);
context.Entry(bookAuthor).Property(nameof(BookAuthor.BookId)).CurrentValue.ShouldEqual(0);
context.Entry(bookAuthor).Property(nameof(BookAuthor.AuthorId)).CurrentValue.ShouldEqual(0);
//STAGE2
context.Add(book);
//Read the states
context.Entry(author).State.ShouldEqual(EntityState.Unchanged);
context.Entry(bookAuthor).State.ShouldEqual(EntityState.Added);
context.Entry(book).State.ShouldEqual(EntityState.Added);
//Extra Navigational links filled in after call to Add
book.AuthorsLink.Single().Book.ShouldEqual(book);
author.BooksLink.Single().ShouldEqual(book.AuthorsLink.Single());
//Real PKs/FKs
book.BookId.ShouldEqual(0);
book.AuthorsLink.Single().BookId.ShouldEqual(0);
book.AuthorsLink.Single().AuthorId.ShouldEqual(author.AuthorId);
//tracked PK/FK values of new entities should be negative for Added classes, or actual PK if read in/Unchanged
var tempBookId = (int)context.Entry(bookAuthor).Property(nameof(BookAuthor.BookId)).CurrentValue;
(tempBookId < 0).ShouldBeTrue();
context.Entry(book).Property(nameof(Book.BookId)).CurrentValue.ShouldEqual(tempBookId);
((int)context.Entry(bookAuthor).Property(nameof(BookAuthor.AuthorId)).CurrentValue).ShouldEqual(author.AuthorId);
//STAGE3
context.SaveChanges();
context.Entry(author).State.ShouldEqual(EntityState.Unchanged);
context.Entry(bookAuthor).State.ShouldEqual(EntityState.Unchanged);
context.Entry(book).State.ShouldEqual(EntityState.Unchanged);
book.BookId.ShouldEqual(5);
book.AuthorsLink.Single().BookId.ShouldEqual(book.BookId);
book.AuthorsLink.Single().AuthorId.ShouldEqual(author.AuthorId);
}
}
public void TestInit()
{
var optionsBuilder = new DbContextOptionsBuilder <EfCoreContext>();
optionsBuilder.UseSqlServer(ConfigurationManager.ConnectionStrings["DEV26"].ConnectionString);
_dbCoreContext = new EfCoreContext(optionsBuilder.Options);
_dbCoreContext.Database.EnsureCreated();
// clean the DB
_dbCoreContext.Database.ExecuteSqlCommand("DELETE FROM Reviews");
_dbCoreContext.Database.ExecuteSqlCommand("DELETE FROM Books");
// create initial test data
var books = new[]
{
new Book()
{
Author = "Vadim",
Title = "Title 1",
},
new Book()
{
Author = "Dmitry",
Title = "Title 2",
},
new Book()
{
Author = "Igor",
Title = "Title 3",
},
};
var reviews = new[]
{
new Review()
{
Book = books[0],
ReviewText = "Review 1",
},
new Review()
{
Book = books[0],
ReviewText = "Review 2",
},
new Review()
{
Book = books[1],
ReviewText = "Review 1",
},
new Review()
{
Book = books[1],
ReviewText = "Review 2",
},
new Review()
{
Book = books[2],
ReviewText = "Review 1",
},
new Review()
{
Book = books[2],
ReviewText = "Review 2",
},
};
foreach (var book in books)
{
_dbCoreContext.Add(book);
}
foreach (var review in reviews)
{
_dbCoreContext.Add(review);
}
_dbCoreContext.SaveChanges();
// stored proc for reads
try
{
_dbCoreContext.Database.ExecuteSqlCommand("DROP PROCEDURE sp_ReadBooks");
}
catch
{
// intentionally left blank
}
_dbCoreContext.Database.ExecuteSqlCommand(@"
CREATE PROCEDURE sp_ReadBooks
@id int = 0
AS
BEGIN
SET NOCOUNT ON;
if @id = 0
begin
SELECT * from Books;
end
else begin
SELECT * from Books where Id = @id;
end
END
");
}
