CHAPTER 24<\/p>\n
Test-Driving AutoLot<\/p>\n
Now that you have the finished AutoLot data access layer, it\u2019s time to take it for a test-drive. Integration testing in an integral part of software development and is a great way to make sure your data access code behaves as expected. In this chapter, we will be using xUnit, a testing framework for .NET Core.
\nAfter creating the solution and test project, the AutoLot.Dal and AutoLot.Models projects are added to the solution. Next, xUnit will be explored to show how to run automated tests. Then the rest of the chapter is dedicated to creating and executing integration tests.<\/p>\n
Setting Up the Test-Drives
\nInstead of creating a client application to test-drive the completed AutoLot data access layer, we are going to use automated integration tests. The tests will demonstrate create, read, update, and delete calls to the database. This allows us to examine the code without the overhead of creating another application. Each of the tests in this section will execute a query (either create, read, update, or delete) and then have one or more Assert statements to validate that the result is what was expected.<\/p>\n
Create the Project
\nTo get started, we are going to set up an integration test platform using xUnit, a .NET Core\u2013compatible testing framework. Start by adding a new xUnit test project named AutoLot.Dal.Tests. In Visual Studio, this project type is named xUnit Test Project.<\/p>\n
\u25a0Note Unit tests are designed to test a single unit of code. What we will be doing throughout this chapter is technically creating integration tests, since we are testing the C# code and EF Core all the way to the database and back.<\/p>\n
From the command-line interface, execute the following commands using the command-line interface (CLI):<\/p>\n
dotnet new sln -n Chapter24_AllProjects
\ndotnet new xunit -lang c# -n AutoLot.Dal.Tests -o .\\AutoLot.Dal.Tests -f net6.0 dotnet sln .\\Chapter23_AllProjects.sln add AutoLot.Dal.Tests<\/p>\n
\u00a9 Andrew Troelsen, Phil Japikse 2022 1051<\/p>\n Add the Microsoft.EntityFrameworkCore, Microsoft.EntityFrameworkCore.Design, Microsoft. EntityFrameworkCore.SqlServer, and Microsoft.Extensions.Configuration.Json NuGet packages to the AutoLot.Dal.Tests project. If using the CLI, use the following commands:<\/p>\n dotnet add AutoLot.Dal.Tests package Microsoft.EntityFrameworkCore The tests will use the data initializer code that clears the temporal data, so the same adjustment to the project file must be made regarding the Microsoft.EntityFrameworkCore.Design package. Update the package to remove (or comment out) the IncludeAssets tag:<\/p>\n The versions of the Microsoft.NET.Test.Sdk and coverlet.collector packages that ship with the xUnit project template typically lag behind the currently available versions. To update them, either use the NuGet Package Manager in Visual Studio to update all NuGet packages or use the CLI. To update them with the CLI, then add them again since adding packages from the command line will always retrieve the latest non-prerelease version. Here are the commands:<\/p>\n dotnet add AutoLot.Dal.Tests package Microsoft.NET.Test.Sdk dotnet add AutoLot.Dal.Tests package coverlet.collector<\/p>\n Next, add project references to AutoLot.Models and AutoLot.Dal. To do this from the command line, execute the following (update the path and directory separator to your projects from Chapter 23):<\/p>\n dotnet add AutoLot.Dal.Tests reference ..\\Chapter_23\\AutoLot.Dal dotnet add AutoLot.Dal.Tests reference ..\\Chapter_23\\AutoLot.Models<\/p>\n Finally, turn off nullable reference types and enable global implicit using statements in the project file:<\/p>\n \u25a0Note At the time of this writing, the xUnit project template does add the node for ImplicitUsings into the project file.<\/p>\n Make the AutoLot.Dal Internals Visible to AutoLot.Dal.Tests Add the GlobalUsings File global using System.Data; global using AutoLot.Dal.EfStructures; global using AutoLot.Dal.Exceptions; global using AutoLot.Dal.Initialization; global using AutoLot.Dal.Repos; global using Microsoft.EntityFrameworkCore; A First Look at xUnit namespace AutoLot.Dal.Tests;<\/p>\n public class SampleTests Fact Test Methods [Fact] There are a variety of Assert types available. In this example, the test is asserting the actual result (3+2) equals the expected result (5).<\/p>\n Theory Test Methods [Theory] [InlineData(3,2,5)] \u25a0Note there will be many examples of Fact and Theory tests throughout this chapter, as well as additional capabilities for the xUnit testing framework. For more information in the xUnit test framework, consult the documentation located at https:\/\/xunit.net\/<\/a><\/a><\/a>.<\/p>\n Executing Tests Configure the Project and DbContext Instances { Update the project file to have the settings file copied to the output folder on every build. Do that by adding the following ItemGroup to the AutoLot.Dal.Tests.csproj file:<\/p>\n Create the Integration Test Helper Add a public static method to create an instance of the IConfiguration interface using the appsettings. testing.json file. Add the following code to the class:<\/p>\n public static IConfiguration GetConfiguration() => new ConfigurationBuilder() \u25a0Note Configuration of assemblies is covered in Chapter 16.<\/p>\n Add another public static method to create instances of the ApplicationDbContext class using the public static ApplicationDbContext GetContext(IConfiguration configuration) Add another static method that will create a new instance of ApplicationDbContext. This demonstrates creating an instance of the ApplicationDbContext class from an existing instance to share the connection and transaction.public static ApplicationDbContext GetSecondContext(<\/p>\n ApplicationDbContext oldContext, IDbContextTransaction trans) Add the BaseTest Class namespace AutoLot.Dal.Tests.Base;<\/p>\n public abstract class BaseTest : IDisposable public virtual void Dispose() The xUnit test framework provides a mechanism to run code before and after each test is executed. Test classes (called fixtures) that implement the IDisposable interface will execute the code in the class constructor in the inheritance chain before each test is run. This is commonly referred to as the test setup. After each test is executed, the code in the Dispose methods (through the inheritance chain) is executed. This is referred to as test teardown.<\/p>\n Add a protected constructor that creates an instance of IConfiguration and assigns it to the protected class variable. Use the configuration to create an instance of ApplicationDbContext using the TestHelper class and also assign it to the protected class variable.<\/p>\n protected BaseTest() The ITestOutputHelper interface allows for content to be written to the test output window. When using the IDisposable pattern with xUnit test fixtures, the instance for this interface can be injected into the constructor. Add a protected readonly variable to hold the instance and update the constructor to the following:<\/p>\n protected readonly ITestOutputHelper OutputHelper;<\/p>\n protected BaseTest(ITestOutputHelper outputHelper) Add the Transacted Test Execution Helpers protected void ExecuteInATransaction(Action actionToExecute) The ExecuteInASharedTransaction() method allows for multiple ApplicationDbContext instances to share a single transaction. Add the following code into your BaseTest class:<\/p>\n protected void ExecuteInASharedTransaction(Action Add the EnsureAutoLotDatabase Test Fixture Class namespace AutoLot.Dal.Tests.Base;<\/p>\n public sealed class EnsureAutoLotDatabaseTestFixture : IDisposable public void Dispose() The constructor code uses the TestHelpers class to get the instance of IConfiguration and then gets an instance of the ApplicationDbContext. Next, it calls the ClearAndReseedDatabase() method from the SampleDataInitializer. The final line disposes of the context instance. In our examples, the Dispose() method doesn\u2019t have any code but needs to be implemented to satisfy the IDisposable interface. The following listing shows the updated constructor:<\/p>\n public EnsureAutoLotDatabaseTestFixture() Add the Integration Test Classes [Collection("Integration Tests")]<\/p>\n Next, inherit from BaseTest and implement the IClassFixture interface in both classes. Add a constructor to receive the ITestOutputHelper instance and pass it to the base class. Update each class to match the following:<\/p>\n \/\/CarTests.cs [Collection("Integation Tests")] public class CarTests \/\/CustomerOrderViewModelTests.cs [Collection("Integation Tests")] \/\/CustomerTests.cs [Collection("Integation Tests")] public class CustomerTests { \/\/MakeTests.cs [Collection("Integation Tests")] public class MakeTests \/\/OrderTests.cs [Collection("Integation Tests")] public class OrderTests For the CarTests class, update the constructor to create an instance of the CarRepo and assign the instance to a private readonly class-level variable. Override the Dispose() method, and in that method, dispose of the repo.<\/p>\n [Collection("Integration Tests")] public override void Dispose() Repeat the process for the CustomerOrderViewModelTests class, using the [Collection("Integration Tests")] public override void Dispose() The setup for the CustomerTests class is simpler, since it does not use the CustomerRepo:<\/p>\n [Collection("Integration Tests")] The same process is needed for the MakeTests class, using MakeRepo:<\/p>\n [Collection("Integration Tests")] public override void Dispose() The final repository update is in the OrderTests class, using OrderRepo:<\/p>\n [Collection("Integration Tests")] public override void Dispose() Querying the Database LINQ Queries LINQ Execution Get All Records [Fact] The statement gets translated into the following SQL:<\/p>\n SELECT .[Id], .[TimeStamp], .[FirstName], .[FullName], .[LastName] FROM [Dbo].[Customers] AS <\/p>\n The same process is used for Keyless entities, like the CustomerOrderViewModel, which is configured to get its data from the CustomerOrderView. Add the following test to the CustomerOrderViewModelTest.cs class to show getting data from the view:<\/p>\n [Fact] The statement gets translated into the following SQL:<\/p>\n SELECT .[Color], .[DateBuilt], .[Display], .[FirstName], Filter Records [Fact] List The LINQ query gets translated into the following SQL:<\/p>\n SELECT .[Id], .[TimeStamp], .[FirstName], .[FullName], .[LastName] FROM [dbo].[Customers] AS The next test in the CustomerTests class demonstrates chaining Where() methods in a LINQ query to find the customers where the last name starts with a W and the first name starts with an M. Note that since SQL Server is case insensitive, these queries are also case insensitive:<\/p>\n [Fact] This test in the CustomerTests class repeats the same filter using a single Where() method instead of two chained methods:<\/p>\n [Fact] { Both queries get translated into the following SQL:<\/p>\n SELECT .[Id], .[TimeStamp], .[FirstName], .[FullName], .[LastName] FROM [dbo].[Customers] AS The next test in the CustomerTests class demonstrates querying for customers where the last name starts with a W (case insensitive) or the last name starts with an H (case insensitive):<\/p>\n [Fact] This gets translated into the following SQL:<\/p>\n SELECT .[Id], .[TimeStamp], .[FirstName], .[FullName], .[LastName] FROM [dbo].[Customers] AS The following in the CustomerTests class also queries for customers where the last name starts with a W (case insensitive) or the last name starts with an H (case insensitive). This test demonstrates using the EF.Functions.Like() method. Note that you must include the wildcard (%) yourself.<\/p>\n [Fact] .Where(x => EF.Functions.Like(x.PersonInformation.LastName, "W%") || This gets translated into the following SQL (notice it does not check for null):<\/p>\n SELECT .[Id], .[TimeStamp], .[FirstName], .[FullName], .[LastName] FROM [Dbo].[Customers] AS The following test in the CarTests.cs class uses a Theory to query for the number of Car records (drivable or not) in the Inventory table based on a specified MakeId:<\/p>\n [Theory] [InlineData(1, 2)] Each InlineData row becomes a unique test in the test runner. For this example, six tests are processed, and six queries are executed against the database. Here is the SQL from one of the tests (the only difference in the queries from the other tests in the Theory is the value for MakeId):<\/p>\n DECLARE @ makeId_0 int = 1;<\/p>\n SELECT [i].[Id], [i].[Color], [i].[DateBuilt], [i].[Display], [i].[IsDrivable], The next test uses the Car repository to get the number of records for each Make. Since the GetCarsBy() [Theory] [InlineData(1, 1)] [InlineData(4, 2)] When examining the generated query, you can see the query filter that excluded records that aren\u2019t drivable:<\/p>\n DECLARE @ makeId_0 int = 1;<\/p>\n SELECT [i].[Id], [i].[Color], [i].[DateBuilt], [i].[Display], [i].[IsDrivable], Sort Records [Fact] { The preceding LINQ query gets translated into the following:<\/p>\n SELECT .[Id], .[TimeStamp], .[FirstName], .[FullName], .[LastName] FROM [dbo].[Customers] AS Reverse Sort Records [Fact] for (int x = 0; x < customers.Count - 1; x++) The preceding LINQ query gets translated into the following, inverting the original SELECT .[Id], .[TimeStamp], .[FirstName], .[FullName], .[LastName] FROM [dbo].[Customers] AS Single-Record Queries Using First [Fact] The preceding LINQ query gets translated into the following:<\/p>\n SELECT TOP(1) .[Id], .[TimeStamp], .[FirstName], .[FullName], .[LastName] FROM [Dbo].[Customers] AS [Fact] The preceding LINQ query gets translated into the following:<\/p>\n SELECT TOP(1) .[Id], .[TimeStamp], .[FirstName], .[FullName], .[LastName] FROM [Dbo].[Customers] AS \/\/Filters based on Id. Throws due to no match \u25a0Note Assert.Throws() is a special type of assert statement. it is expecting an exception to the thrown by the code in the expression. if an exception doesn\u2019t get thrown, the assertion fails.<\/p>\n When using FirstOrDefault(), instead of an exception, the result is a null record when no data is returned. This test shows creating an expression variable:<\/p>\n [Fact] The preceding LINQ query gets translated into the same as the previous:<\/p>\n SELECT TOP(1) .[Id], .[TimeStamp], .[FirstName], .[FullName], .[LastName] FROM [Dbo].[Customers] AS Using Last [Fact] EF Core reverses the order by statements and then takes top(1) to get the result. Here is the executed query:<\/p>\n SELECT TOP(1) .[Id], .[TimeStamp], .[FirstName], .[FullName], .[LastName] FROM [Dbo].[Customers] AS This test confirms that EF Core throws an exception when Last() is used without an [Fact] Using Single [Fact] The preceding LINQ query gets translated into the following:<\/p>\n SELECT TOP(2) .[Id], .[TimeStamp], .[FirstName], .[FullName], .[LastName] FROM [Dbo].[Customers] AS Single() throws an exception if no records are returned.[Fact] public void SingleShouldThrowExceptionIfNoneMatch() When using Single() or SingleOrDefault() and more than one record is returned, an exception is thrown.<\/p>\n [Fact] When using SingleOrDefault(), instead of an exception, the result is a null record when no data is returned.<\/p>\n [Fact] The preceding LINQ query gets translated into the following: Global Query Filters [Fact] Recall that we create 10 cars in the data initialization process, and one of them is set to be nondrivable. SELECT [i].[Id], [i].[Color], [i].[DateBuilt], [i].[Display], [i].[IsDrivable], Disable the Query Filters [Fact] As one would expect, the where clause eliminating nondrivable cars is no longer on the generated SQL.<\/p>\n SELECT [i].[Id], [i].[Color], [i].[DateBuilt], [i].[Display], [i].[IsDrivable], [i]. [MakeId], [i].[PetName], [i].[Price], [i].[TimeStamp] Query Filters on Navigation Properties [Fact] The generated SQL is listed here: Because the CarNavigation navigation property is a required navigation property, the query translation engine uses an INNER JOIN, eliminating the Order records where the Car is nondrivable.<\/p>\n To return all records, add IgnoreQueryFilters() to your LINQ query.[Fact] public void ShouldGetAllOrders() var orders = query.ToList(); Assert.NotEmpty(orders); Assert.Equal(5, orders.Count); You can see from the generated SQL that the where clause has been removed, and the query has been simplified:<\/p>\n SELECT [o].[Id], [o].[CarId], [o].[CustomerId], [o].[TimeStamp] FROM [dbo].[Orders] AS [o]<\/p>\n Load Related Data Eagerly The query adds the MakeNavigation property to the results, performing an inner join with the following SQL being executed. The query returns all the columns from both tables, and then EF Core created Car and Make instances from the returned data. Notice the global query filter is in effect.<\/p>\n SELECT [i].[Id], [i].[Color], [i].[DateBuilt], [i].[Display], [i].[IsDrivable], The next test demonstrates using two sets of related data. The first is getting the Make information (same as the previous test), while the second is getting the Orders and then the Customers attached to the Orders. The entire test is also filtering out the Car records that don\u2019t have any orders.<\/p>\n [Fact] IIncludableQueryable<Car, Customer?> query = Context.Cars The generated query is rather lengthy. Here is the generated query:<\/p>\n SELECT [i].[Id], [i].[Color], [i].[DateBuilt], [i].[Display], [i].[IsDrivable], If you run the same query without the query filters, the query becomes much simpler. Here is the updated test that removes the query filters:<\/p>\n [Fact] The generated query is rather lengthy. Here is the generated query:<\/p>\n SELECT [i].[Id], [i].[Color], [i].[DateBuilt], [i].[Display], [i].[IsDrivable], [i]. Splitting Queries on Related Data [Fact] .Include(c => c.MakeNavigation) The ToQueryString() method returns only the first query, so the following queries were captured using SQL Server Profiler:<\/p>\n SELECT [i].[Id], [i].[Color], [i].[DateBuilt], [i].[Display], [i].[IsDrivable], [i]. [MakeId], SELECT [t0].[Id], [t0].[CarId], [t0].[CustomerId], [t0].[TimeStamp], [t0].[Id0], WHERE ([i].[IsDrivable] = CAST(1 AS bit)) AND EXISTS ( SELECT 1 Once again, removing the query filters greatly simplifies the queries generated. Here is the updated test:<\/p>\n [Fact] Here are the generated queries:<\/p>\n SELECT [i].[Id], [i].[Color], [i].[DateBuilt], [i].[Display], [i].[IsDrivable], SELECT [t].[Id], [t].[CarId], [t].[CustomerId], [t].[TimeStamp], [t].[Id0], INNER JOIN ( Filtering Related Data [Fact] The generated SQL is as follows:<\/p>\n SELECT [m].[Id], [m].[Name], [m].[TimeStamp], [t].[Id], [t].[Color], Changing the query to a split query yields this SQL (collection from SQL Server Profiler): SELECT [m].[Id], [m].[Name], [m].[TimeStamp] SELECT [t].[Id], [t].[Color], [t].[DateBuilt], [t].[Display], [t].[IsDrivable], Load Related Data Explicitly [Fact] The generated SQL to get the Make information is as follows (the Car record was already queried):<\/p>\n DECLARE @ p_0 int = 1;<\/p>\n SELECT [m].[Id], [m].[Name], [m].[TimeStamp] FROM [dbo].[Makes] AS [m] This test shows how to load a collection navigation property on the Car entity:<\/p>\n [Fact] The generated SQL is as follows:<\/p>\n DECLARE @ p_0 int = 1;<\/p>\n SELECT [o].[Id], [o].[CarId], [o].[CustomerId], [o].[TimeStamp] FROM [dbo].[Orders] AS [o] Load Related Data Explicitly with Query Filters [Theory] [InlineData(1,1)] [InlineData(2,1)] [InlineData(3,1)] [InlineData(4,2)] [InlineData(5,3)] [InlineData(6,1)] This test is similar to ShouldGetTheCarsByMake() from the \u201cFilter Records\u201d section. However, instead of just getting the Car records that have a certain MakeId, the test first gets a Make record and then explicitly loads the Car records for the already retrieved Make record. The generated query is shown here:<\/p>\n DECLARE @ p_0 int = 1;<\/p>\n SELECT [i].[Id], [i].[Color], [i].[DateBuilt], [i].[Display], [i].[IsDrivable], Notice that the query filter is still being used, even though the principal entity in the query is the Make record. To turn off query filters when explicitly loading records, call IgnoreQueryFilters() in conjunction with the Query() method. Here is the test that turns off query filters (again, in the MakeTests.cs class):<\/p>\n [Theory] [InlineData(1, 2)] Temporal Queries [Fact] var list = _repo.GetAllHistory().Where(x => x.Entity.Id == make.Id).ToList(); Assert.Equal(2, list.Count); The test creates a new Make record and adds it to the database. After pausing operation for two seconds, the Name is updated, and the change saved. After another two-second pause, the record is deleted. The test then uses the MakeRepo to get all the history for the Make record, confirms that there are two records in the history, makes sure the records are retrieved in ValidFrom order, and makes sure that the ValidTo of the first record exactly matches the ValidFrom of the second record.<\/p>\n SQL Queries with LINQ [Fact] When using raw SQL queries, the query gets wrapped into a larger query by EF Core to support the query filter. If the statement was terminated with a semicolon, the query would not be executable on SQL Server.<\/p>\n SELECT [a].[Id], [a].[Color], [a].[DateBuilt], [a].[Display], [a].[IsDrivable], [a]. When the query filter is removed, the generated SQL becomes the same SQL as the string passed into the FromSqlRaw() method:<\/p>\n Select *,ValidFrom,ValidTo from dbo.Inventory<\/p>\n The following test demonstrates using FromSqlInterpolated() with additional LINQ statements (including the MakeNavigation):<\/p>\n [Fact] Here is the generated SQL:<\/p>\n DECLARE p0 int = 1;<\/p>\n SELECT [a].[Id], [a].[Color], [a].[DateBuilt], [a].[Display], [a].[IsDrivable], [a].[MakeId], Aggregate Methods [Fact] The executed SQL is shown here:<\/p>\n SELECT COUNT(*) By adding IgnoreQueryFilters(), the Count() method returns 10, and the where clause is removed from the SQL query.<\/p>\n [Fact] --Generated SQL The following tests (also in CarTests.cs) demonstrate the Count() method with a where condition. The first test adds the expression directly into the Count() method, and the second adds the Count() method to the end of the LINQ statement.<\/p>\n [Theory] [InlineData(1, 1)] [Theory] [InlineData(1, 1)] Both tests create the same SQL calls to the server, as shown here (the MakeId changes with each test based on the InlineData):<\/p>\n exec sp_executesql N'SELECT COUNT(*) FROM [dbo].[Inventory] AS [i] Any() and All() [Theory] [InlineData(1, true)] The executed SQL for the first theory test is shown here:<\/p>\n exec sp_executesql N'SELECT CASE WHEN EXISTS ( The executed SQL for the first theory test is shown here: Getting Data from Stored Procedures [Theory] Creating Records \u25a0Note records can also be added using the derived DbContext as well. these examples will all use the DbSet Add a Single Record [Fact] void RunTheTest() The executed SQL statement is shown here. Notice that the recently added entity is queried for the database-generated properties (Id and TimeStamp). When the results of the query come to EF Core, the entity is updated with the server-side values.<\/p>\n exec sp_executesql N'SET NOCOUNT ON; Add a Single Record Using Attach [Fact] void RunTheTest() var car = new Car Add Multiple Records at Once [Fact] void RunTheTest() The add statements are batched into a single call to the database, and all the generated columns are queried. When the results of the query come to EF Core, the entities are updated with the server-side values. The executed SQL statement is shown here:<\/p>\n exec sp_executesql N'SET NOCOUNT ON; VALUES (@p0, @p1, @p2, @p3, 0), (@p4, @p5, @p6, @p7, 1), SELECT [t].[Id], [t].[DateBuilt], [t].[Display], [t].[IsDrivable], [t].[TimeStamp] FROM [dbo].[Inventory] t Adding an Object Graph [Fact] void RunTheTest() var carCount = Context.Cars.Count(); var makeCount = Context.Makes.Count(); Context.SaveChanges(); The executed SQL statements (one for each table) are shown here:<\/p>\n exec sp_executesql N'SET NOCOUNT ON; INSERT INTO [dbo].[Makes] ([Name]) VALUES (@p0); exec sp_executesql N'SET NOCOUNT ON; ',N'@p1 nvarchar(50),@p2 int,@p3 nvarchar(50),@p4 nvarchar(50)',@p1=N'Yellow',@p2=7, @p3=N'Herbie',@p4=NULL<\/p>\n exec sp_executesql N'SET NOCOUNT ON; Updating Records Update Tracked Entities [Fact] void RunTheTest(IDbContextTransaction trans) The prior code uses a shared transaction across two instances of ApplicationDbContext. This is to provide isolation between the context executing the test and the context checking the result of the test.<\/p>\n The executed SQL statement is listed here:<\/p>\n exec sp_executesql N'SET NOCOUNT ON; UPDATE [dbo].[Inventory] SET [Color] = @p0 WHERE [Id] = @p1 AND [TimeStamp] = @p2; SELECT [TimeStamp] ',N'@p1 int,@p0 nvarchar(50),@p2 varbinary(8)',@p1=1,@p0=N'White',@p2=0x000000000000862D<\/p>\n Update Nontracked Entities [Fact] void RunTheTest(IDbContextTransaction trans) Concurrency Checking When Updating Records UPDATE [dbo].[Inventory] SET [PetName] = @p0 WHERE [Id] = @p1 AND [TimeStamp] = @p2;<\/p>\n The following test shows an example of creating a concurrency exception, catching it, and using the [Fact] void RunTheTest() var car = Context.Cars.First(); Deleting Records Delete Tracked Records [Fact] void RunTheTest() After SaveChanges() is called, the entity instance still exists, but is no longer in the ChangeTracker. The executed SQL call for the delete is listed here:<\/p>\n exec sp_executesql N'SET NOCOUNT ON; DELETE FROM [dbo].[Inventory] Delete Nontracked Entities [Fact] void RunTheTest(IDbContextTransaction trans) Catch Cascade Delete Failures [Fact] void RunTheTest() Concurrency Checking When Deleting Records Summary CHAPTER 24 Test-Driving AutoLot Now that you have the finished AutoLot data access layer, it\u2019s time to take it for a test-drive. Integration testing in an integral part of software development and is a great way to make sure your data access code behaves as expected. In this chapter, we will be using xUnit, a […]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[3],"tags":[22],"class_list":["post-346","post","type-post","status-publish","format-standard","hentry","category-csharp","tag-pro-csharp10-with-net6"],"_links":{"self":[{"href":"https:\/\/diji.net\/index.php?rest_route=\/wp\/v2\/posts\/346","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/diji.net\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/diji.net\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/diji.net\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/diji.net\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=346"}],"version-history":[{"count":0,"href":"https:\/\/diji.net\/index.php?rest_route=\/wp\/v2\/posts\/346\/revisions"}],"wp:attachment":[{"href":"https:\/\/diji.net\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=346"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/diji.net\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=346"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/diji.net\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=346"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}
\nA. Troelsen and P. Japikse, Pro C# 10 with .NET 6, https:\/\/doi.org\/10.1007\/978-1-4842-7869-7_24<\/a><\/a><\/a><\/p>\n
\ndotnet add AutoLot.Dal.Tests package Microsoft.EntityFrameworkCore.Design dotnet add AutoLot.Dal.Tests package Microsoft.EntityFrameworkCore.SqlServer dotnet add AutoLot.Dal.Tests package Microsoft.Extensions.Configuration.Json<\/p>\n
\n
\n
\n<\/PackageReference><\/p>\n
\n
\n
\n
\n
\n<\/PropertyGroup><\/p>\n
\nTo test (or use) the methods and classes in the AutoLot.Dal project that are marked internal, the internals need to be made visible to the AutoLot.Dal.Tests project. Open the AutoLot.Dal.csproj file and add the following:<\/p>\n
\n
\n<_Parameter1>AutoLot.Dal.Tests<\/_Parameter1>
\n<\/AssemblyAttribute>
\n<\/ItemGroup><\/p>\n
\nCreate a new file named GlobalUsings.cs in the root of the AutoLot.Dal.Tests project. This will be the central location for all the using statements needed in this project. Update the code to match the following:<\/p>\n
\nglobal using System.Linq.Expressions;<\/p>\n
\nglobal using AutoLot.Dal.Repos.Interfaces;
\nglobal using AutoLot.Dal.Tests.Base; global using AutoLot.Models.Entities;
\nglobal using AutoLot.Models.Entities.Owned;<\/p>\n
\nglobal using Microsoft.EntityFrameworkCore.ChangeTracking; global using Microsoft.EntityFrameworkCore.Storage;
\nglobal using Microsoft.EntityFrameworkCore.Query; global using Microsoft.Extensions.Configuration;
\nglobal using Xunit;
\nglobal using Xunit.Abstractions;<\/p>\n
\nThere are two types of tests that will be used in this chapter. Parameterless test methods are referred to as facts (and use the Fact attribute). Tests that take parameters are referred to as theories (and use the Theory attribute). Theory tests run multiple iterations of the test method passing in different values for each run. To demonstrate these test types, create a new class named SampleTests.cs in the AutoLot.Dal.Tests project and update the code to the following:<\/p>\n
\n{
\n\/\/tests go here
\n}<\/p>\n
\nThe first test to create is a Fact test. With Fact tests, all values are contained in the test method. The following (trivial) example tests 3+2=5:<\/p>\n
\npublic void SimpleFactTest()
\n{
\nAssert.Equal(5,3+2);
\n}<\/p>\n
\nWhen using Theory type tests, the values for the tests are passed into the test method. The previous test tested only one case, 3+2. Theories permit testing multiple use cases without having to repeat the test code multiple times. The values can come from the InlineData attribute, methods, or classes. For our purpose, we will only use the InlineData attribute. Create the following test that provided different addends and expected results to the test:<\/p>\n
\n[InlineData(1,-1,0)]
\npublic void SimpleTheoryTest(int addend1, int addend2, int expectedResult)
\n{
\nAssert.Equal(expectedResult,addend1+addend2);
\n}<\/p>\n
\nWhile the xUnit tests can be executed from the command line (using dotnet test), it is a better developer experience (in my opinion) to use Visual Studio to execute the tests. Launch the Test Explorer from the Test menu to have access to running and debugging all or selected tests.<\/p>\n
\nTo retrieve the connection string at runtime, we are going to use the .NET Core configuration capabilities using a JSON file. Add a JSON file, named appsettings.testing.json, to the project and add your connection string information into the file in the following format (update your connection string from what is listed here as necessary):<\/p>\n
\n"ConnectionStrings": {
\n"AutoLot": "server=.,5433;Database=AutoLot;User Id=sa;Password=P@ssw0rd;"
\n}
\n}<\/p>\n
\n
\n
\n<\/None>
\n<\/ItemGroup><\/p>\n
\nThe TestHelpers class will handle the application configuration as well as create new instances of
\nApplicationDbContext. Add a new public static class named TestHelpers.cs in the root of the project:
\nnamespace AutoLot.Dal.Tests; public static class TestHelpers
\n{
\n\/\/helper code goes here
\n}<\/p>\n
\n.SetBasePath(Directory.GetCurrentDirectory())
\n.AddJsonFile("appsettings.testing.json", true, true)
\n.Build();<\/p>\n
\nIConfiguration instance. Add the following code to the class:<\/p>\n
\n{
\nvar optionsBuilder = new DbContextOptionsBuilder
\nreturn new ApplicationDbContext(optionsBuilder.Options);
\n}<\/p>\n
\n{
\nvar optionsBuilder = new DbContextOptionsBuilder
\nvar context = new ApplicationDbContext(optionsBuilder.Options); context.Database.UseTransaction(trans.GetDbTransaction()); return context;
\n}<\/p>\n
\nThe BaseTest class handles the infrastructure for the tests in this chapter. Add a new folder named Base to the test project and add a new class file named BaseTest.cs to that folder. Make the class abstract and implement IDisposable. Add two protected readonly properties to hold the IConfiguration and ApplicationDbContext instances and dispose of the ApplicationDbContext instance in the virtual Dispose() method.<\/p>\n
\n{
\nprotected readonly IConfiguration Configuration; protected readonly ApplicationDbContext Context;<\/p>\n
\n{
\nContext.Dispose();
\n}
\n}<\/p>\n
\n{
\nConfiguration = TestHelpers.GetConfiguration(); Context = TestHelpers.GetContext(Configuration);
\n}<\/p>\n
\n{
\nConfiguration = TestHelpers.GetConfiguration(); Context = TestHelpers.GetContext(Configuration); OutputHelper = outputHelper;
\n}<\/p>\n
\nThe final two methods in the BaseTest class enable running test methods in a transaction. The methods will take an Action delegate as a single parameter, create an explicit transaction (or enlist an existing transaction), execute the Action delegate, and then roll back the transaction. We do this so any create\/ update\/delete tests leave the database in the state it was in before the test was run. The transactions are executed inside an execution strategy in case the ApplicationDbContext is configured to enable retry on transient errors.
\nThe ExecuteInATransaction() method executes the action delegate using a single instance of the
\nApplicationDbContext. Add the following code into your BaseTest class:<\/p>\n
\n{
\nvar strategy = Context.Database.CreateExecutionStrategy(); strategy.Execute(() =>
\n{
\nusing var trans = Context.Database.BeginTransaction(); actionToExecute();
\ntrans.Rollback();
\n});
\n}<\/p>\n
\n{
\nvar strategy = Context.Database.CreateExecutionStrategy(); strategy.Execute(() =>
\n{
\nusing IDbContextTransaction trans = Context.Database.BeginTransaction(IsolationLevel.ReadUncommitted);
\nactionToExecute(trans); trans.Rollback();
\n});
\n}<\/p>\n
\nThe xUnit test framework provides a mechanism for running code before any of the tests are run (referred to as fixture setup) and after all the tests are run (referred to as fixture teardown). This practice is generally not recommended, but in our case, we want to ensure that the database is created and loaded with data before any tests are run instead of before each test is run. Test classes that implement IClassFixture
\nTestFixtureClass will have the constructor code of T (the TestFixtureClass) executed before any tests are run, and the Dispose() code will run after all tests are completed.
\nAdd a new class named EnsureAutoLotDatabaseTestFixture.cs to the Base directory and implement
\nIDisposable. Make the class public and sealed, and add the following using statements:<\/p>\n
\n{
\n\/\/add implementation code here<\/p>\n
\n{
\n}
\n}<\/p>\n
\n{
\nvar configuration = TestHelpers.GetConfiguration(); var context = TestHelpers.GetContext(configuration); SampleDataInitializer.ClearAndReseedDatabase(context); context.Dispose();
\n}<\/p>\n
\nThe next step is to add the classes that will hold the automated tests. These classes are referred to as test fixtures. Add a new folder named IntegrationTests in the AutoLot.Dal.Tests folder and add five files named CarTests.cs, CustomerOrderViewModelTests.cs, CustomerTests.cs, MakeTests.cs, and OrderTests.cs to this folder.
\nDepending on the capabilities of the test runner, xUnit tests are run in serial within a test fixture (class), but in parallel across test fixtures. This can be problematic when executing integration tests that interact with a database. Parallel database tests using a single database instance can cause blocks, produce erroneous results, and are generally problematic.
\nxUnit test execution can be changed to serial across test fixtures by adding them into the same test collection. Test collections are defined by name using the Collection attribute on the class. Add the following Collection attribute to the top of all four classes:<\/p>\n
\nnamespace AutoLot.Dal.Tests.IntegrationTests;<\/p>\n
\n: BaseTest, IClassFixture
\n{
\npublic CarTests(ITestOutputHelper outputHelper) : base(outputHelper)
\n{
\n}
\n}<\/p>\n
\nnamespace AutoLot.Dal.Tests.IntegrationTests;<\/p>\n
\npublic class CustomerOrderViewModelTests
\n: BaseTest, IClassFixture
\n{
\npublic CustomerOrderViewModelTests(ITestOutputHelper outputHelper)
\n: base(outputHelper)
\n{
\n}
\n}<\/p>\n
\nnamespace AutoLot.Dal.Tests.IntegrationTests;<\/p>\n
\n: BaseTest, IClassFixture
\npublic CustomerTests(ITestOutputHelper outputHelper) : base(outputHelper)
\n{
\n}
\n}<\/p>\n
\nnamespace AutoLot.Dal.Tests.IntegrationTests;<\/p>\n
\n: BaseTest, IClassFixture
\n{
\npublic MakeTests(ITestOutputHelper outputHelper) : base(outputHelper)
\n{
\n}
\n}<\/p>\n
\nnamespace AutoLot.Dal.Tests.IntegrationTests;<\/p>\n
\n: BaseTest, IClassFixture
\n{
\npublic OrderTests(ITestOutputHelper outputHelper):base(outputHelper)
\n{
\n}
\n}<\/p>\n
\npublic class CarTests : BaseTest, IClassFixture
\n{
\nprivate readonly ICarRepo _carRepo;
\npublic CarTests(ITestOutputHelper outputHelper) : base(outputHelper)
\n{
\n_carRepo = new CarRepo(Context);
\n}<\/p>\n
\n{
\n_carRepo.Dispose(); base.Dispose();
\n}
\n}<\/p>\n
\nCustomerOrderViewModelRepo instead:<\/p>\n
\npublic class CustomerOrderViewModelTests : BaseTest, IClassFixture
\n{
\nprivate readonly ICustomerOrderViewModelRepo _repo;
\npublic CustomerOrderViewModelTests(ITestOutputHelper outputHelper) : base(outputHelper)
\n{
\n_repo = new CustomerOrderViewModelRepo(Context);
\n}<\/p>\n
\n{
\n_repo.Dispose(); base.Dispose();
\n}
\n}<\/p>\n
\npublic class CustomerTests : BaseTest, IClassFixture
\n{
\npublic CustomerOrderViewModelTests(ITestOutputHelper outputHelper) : base(outputHelper)
\n{
\n}
\n}<\/p>\n
\npublic class MakeTests : BaseTest, IClassFixture
\n{
\nprivate readonly IMakeRepo _repo;
\npublic MakeTests(ITestOutputHelper outputHelper) : base(outputHelper)
\n{
\n_repo = new MakeRepo(Context);
\n}<\/p>\n
\n{
\n_repo.Dispose(); base.Dispose();
\n}
\n}<\/p>\n
\npublic class OrderTests : BaseTest, IClassFixture
\n{
\nprivate readonly IOrderRepo _repo;
\npublic OrderTests(ITestOutputHelper outputHelper) : base(outputHelper)
\n{
\n_repo = new OrderRepo(Context);
\n}<\/p>\n
\n{
\n_repo.Dispose(); base.Dispose();
\n}
\n}<\/p>\n
\nRecall that creating entity instances from a database data involves executing a LINQ statement or a SQL statement (using FromSqlRaw()\/FromSqlInterpolated()) against the DbSet
\nFromSqlInterpolated() method using raw SQL strings and, optionally, additional LINQ statements. Entities loaded into the DbSet
\nIf related entities are already loaded into the DbSet
\nMany of the methods demonstrated here have async versions available. The syntax of the LINQ queries are structurally the same, so I will only demonstrate the nonasync version.<\/p>\n
\nThe DbSet
\nWhere ToQueryString() is available, the tests in this next section set a variable (qs) to this value and are output to the test results using the ITestOutputHelper so you can examine the query while running the tests.<\/p>\n
\nTo get all the records for a table, simply use the DbSet
\npublic void ShouldGetAllOfTheCustomers()
\n{
\nvar qs = Context.Customers.ToQueryString(); var customers = Context.Customers.ToList(); Assert.Equal(5, customers.Count);
\n}<\/p>\n
\npublic void ShouldGetAllViewModels()
\n{
\nvar qs = Context.CustomerOrderViewModels.ToQueryString(); OutputHelper.WriteLine($"Query: {qs}"); List
\nContext.CustomerOrderViewModels.ToList(); Assert.NotEmpty(list);
\nAssert.Equal(5, list.Count);
\n}<\/p>\n
\n.[IsDrivable], .[LastName], .[Make], .[PetName], .[Price] FROM [CustomerOrderView] AS <\/p>\n
\nThe following test in the CustomerTests class shows querying for customers where the last name starts with a W (case insensitive):<\/p>\n
\npublic void ShouldGetCustomersWithLastNameW()
\n{
\nIQueryable
\n.Where(x => x.PersonInformation.LastName.StartsWith("W")); var qs = query.ToQueryString(); OutputHelper.WriteLine($"Query: {qs}");<\/p>\n
\nforeach (var customer in customers)
\n{
\nvar pi = customer.PersonInformation;
\nAssert.StartsWith("W", pi.LastName, StringComparison.OrdinalIgnoreCase);
\n}
\n}<\/p>\n
\nWHERE .[LastName] IS NOT NULL AND (.[LastName] LIKE N'W%')<\/p>\n
\npublic void ShouldGetCustomersWithLastNameWAndFirstNameM()
\n{
\nIQueryable
\n.Where(x => x.PersonInformation.LastName.StartsWith("W"))
\n.Where(x => x.PersonInformation.FirstName.StartsWith("M")); var qs = query.ToQueryString(); OutputHelper.WriteLine($"Query: {qs}");
\nList
\nforeach (var customer in customers)
\n{
\nvar pi = customer.PersonInformation;
\nAssert.StartsWith("W", pi.LastName, StringComparison.OrdinalIgnoreCase); Assert.StartsWith("M", pi.FirstName, StringComparison.OrdinalIgnoreCase);
\n}
\n}<\/p>\n
\npublic void ShouldGetCustomersWithLastNameWAndFirstNameM()
\n{
\nIQueryable
\n.Where(x => x.PersonInformation.LastName.StartsWith("W") &&
\nx.PersonInformation.FirstName.StartsWith("M"));
\nvar qs = query.ToQueryString(); OutputHelper.WriteLine($"Query: {qs}"); List
\nforeach (var customer in customers)<\/p>\n
\nvar pi = customer.PersonInformation;
\nAssert.StartsWith("W", pi.LastName, StringComparison.OrdinalIgnoreCase); Assert.StartsWith("M", pi.FirstName, StringComparison.OrdinalIgnoreCase);
\n}
\n}<\/p>\n
\nWHERE (.[LastName] IS NOT NULL AND (.[LastName] LIKE N'W%')) AND (.[FirstName] IS NOT NULL AND (.[FirstName] LIKE N'M%'))<\/p>\n
\npublic void ShouldGetCustomersWithLastNameWOrH()
\n{
\nIQueryable
\n.Where(x => x.PersonInformation.LastName.StartsWith("W") ||
\nx.PersonInformation.LastName.StartsWith("H"));
\nvar qs = query.ToQueryString(); OutputHelper.WriteLine($"Query: {qs}"); List
\nforeach (var customer in customers)
\n{
\nvar pi = customer.PersonInformation; Assert.True(
\npi.LastName.StartsWith("W",StringComparison.OrdinalIgnoreCase) || pi.LastName.StartsWith("H",StringComparison.OrdinalIgnoreCase));
\n}
\n}<\/p>\n
\nWHERE (.[LastName] IS NOT NULL AND (.[LastName] LIKE N'W%')) OR (.[LastName] IS NOT NULL AND (.[LastName] LIKE N'H%'))<\/p>\n
\npublic void ShouldGetCustomersWithLastNameWOrH()
\n{
\nIQueryable
\nEF.Functions.Like(x.PersonInformation.LastName, "H%"));
\nvar qs = query.ToQueryString(); OutputHelper.WriteLine($"Query: {qs}"); List
\n}<\/p>\n
\nWHERE (.[LastName] LIKE N'W%') OR (.[LastName] LIKE N'H%')<\/p>\n
\n[InlineData(2, 1)]
\n[InlineData(3, 1)]
\n[InlineData(4, 2)]
\n[InlineData(5, 3)]
\n[InlineData(6, 1)]
\npublic void ShouldGetTheCarsByMake(int makeId, int expectedCount)
\n{
\nIQueryable
\nContext.Cars.IgnoreQueryFilters().Where(x => x.MakeId == makeId);
\nvar qs = query.ToQueryString(); OutputHelper.WriteLine($"Query: {qs}"); var cars = query.ToList(); Assert.Equal(expectedCount, cars.Count);
\n}<\/p>\n
\n[i].[MakeId], [i].[PetName], [i].[Price], [i].[TimeStamp] FROM [dbo].[Inventory] AS [i]
\nWHERE [i].[MakeId] = @ makeId_0<\/p>\n
\nmethod leaves the query filters in place, there is one less record when the MakeId is one.<\/p>\n
\n[InlineData(2, 1)]
\n[InlineData(3, 1)]<\/p>\n
\n[InlineData(5, 3)]
\n[InlineData(6, 1)]
\npublic void ShouldGetTheCarsByMakeUsingTheRepo(int makeId, int expectedCount)
\n{
\nvar qs = _carRepo.GetAllBy(makeId).AsQueryable().ToQueryString(); OutputHelper.WriteLine($"Query: {qs}");
\nvar cars = _carRepo.GetAllBy(makeId).ToList(); Assert.Equal(expectedCount, cars.Count);
\n}<\/p>\n
\n[i].[MakeId], [i].[PetName], [i].[Price], [i].[TimeStamp], [m].[Id], [m].[Name], [m].[TimeStamp]
\nFROM [dbo].[Inventory] AS [i]
\nINNER JOIN [dbo].[Makes] AS [m] ON [i].[MakeId] = [m].[Id]
\nWHERE ([i].[IsDrivable] = CAST(1 AS bit)) AND ([i].[MakeId] = @ makeId_0) ORDER BY [i].[PetName]<\/p>\n
\nRecall that sorting is achieved by using OrderBy()\/OrderByDescending(). If more than one story level is required, add ThenBy()\/ThenByDescending() for each subsequent property. Ascending and descending sorts for different properties can be used together, as shown in the following test in the CustomerTests.cs file:<\/p>\n
\npublic void ShouldSortByLastNameThenFirstName()
\n{
\n\/\/Sort by Last name then first name var query = Context.Customers
\n.OrderBy(x => x.PersonInformation.LastName)
\n.ThenByDescending(x => x.PersonInformation.FirstName); var qs = query.ToQueryString(); OutputHelper.WriteLine($"Query: {qs}");
\nvar customers = query.ToList();
\nfor (int x = 0; x < customers.Count - 1; x++)
\n{
\nCompare(customers[x].PersonInformation, customers[x + 1].PersonInformation);
\n}
\nstatic void Compare(Person p1, Person p2)
\n{
\nvar compareValue = string.Compare(p1.LastName, p2.LastName, StringComparison.CurrentCultureIgnoreCase);
\nAssert.True(compareValue <=0); if (compareValue == 0)<\/p>\n
\n\/\/Descending first name sort Assert.True(string.Compare(p1.FirstName,p2.FirstName,
\nStringComparison.CurrentCultureIgnoreCase) >= 0);
\n}
\n}
\n}<\/p>\n
\nORDER BY .[LastName], .[FirstName] DESC<\/p>\n
\nThe Reverse() method reverses the entire sort order, as demonstrated in the next test:<\/p>\n
\npublic void ShouldSortByFirstNameThenLastNameUsingReverse()
\n{
\n\/\/Sort by Last name then first name descending then reverse the sort var query = Context.Customers
\n.OrderBy(x => x.PersonInformation.LastName)
\n.ThenByDescending(x => x.PersonInformation.FirstName)
\n.Reverse();
\nvar qs = query.ToQueryString(); var customers = query.ToList();
\n\/\/if only one customer, nothing to test if (customers.Count <= 1) { return; }<\/p>\n
\n{
\nvar pi1 = customers[x].PersonInformation;
\nvar pi2 = customers[x + 1].PersonInformation;
\nvar compareLastName = string.Compare(pi1.LastName, pi2.LastName, StringComparison.CurrentCultureIgnoreCase); Assert.True(compareLastName >= 0);
\nif (compareLastName != 0) continue;
\nvar compareFirstName = string.Compare(pi1.FirstName, pi2.FirstName, StringComparison.CurrentCultureIgnoreCase); Assert.True(compareFirstName <= 0);
\n}
\n}<\/p>\n
\nOrderBy()\/ThenByDescending() query:<\/p>\n
\nORDER BY .[LastName] DESC, .[FirstName]<\/p>\n
\nBecause of the immediate execution of the single-record LINQ statements, the ToQueryString() method isn\u2019t available. The listed query translations are provided by using SQL Server Profiler. All of the single- record tests are in the CustomerTests.cs file.<\/p>\n
\nWhen using the parameterless form of First() and FirstOrDefault(), the first record (based on database order or any preceding ordering clauses) will be returned. The following test shows querying for the first record based on database order:<\/p>\n
\npublic void GetFirstMatchingRecordDatabaseOrder()
\n{
\n\/\/Gets the first record, database order var customer = Context.Customers.First(); Assert.Equal(1, customer.Id);
\n}<\/p>\n
\nThe following test demonstrates getting the first record based on \u201clast name, first name\u201d order:<\/p>\n
\npublic void GetFirstMatchingRecordNameOrder()
\n{
\n\/\/Gets the first record, lastname, first name order var customer = Context.Customers
\n.OrderBy(x => x.PersonInformation.LastName)
\n.ThenBy(x => x.PersonInformation.FirstName)
\n.First();
\nAssert.Equal(1, customer.Id);
\n}<\/p>\n
\nORDER BY .[LastName], .[FirstName]
\nThe following test asserts that an exception is thrown if there isn\u2019t a match when using First(): [Fact]
\npublic void FirstShouldThrowExceptionIfNoneMatch()
\n{<\/p>\n
\nAssert.Throws
\n}<\/p>\n
\npublic void FirstOrDefaultShouldReturnDefaultIfNoneMatch()
\n{
\n\/\/Expression<Func
\n\/\/Returns null when nothing is found
\nvar customer = Context.Customers.FirstOrDefault(expression);
\nAssert.Null(customer);
\n}<\/p>\n
\nWHERE .[Id] = 10<\/p>\n
\nWhen using the parameterless form of Last() and LastOrDefault(), the last record (based on any preceding ordering clauses) will be returned. As a reminder, EF Core will throw an exception if no sort is specified. The following test gets the last record based on \u201clast name, first name\u201d order:<\/p>\n
\npublic void GetLastMatchingRecordNameOrder()
\n{
\n\/\/Gets the last record, lastname desc, first name desc order var customer = Context.Customers
\n.OrderBy(x => x.PersonInformation.LastName)
\n.ThenBy(x => x.PersonInformation.FirstName)
\n.Last();
\nAssert.Equal(4, customer.Id);
\n}<\/p>\n
\nORDER BY .[LastName] DESC, .[FirstName] DESC<\/p>\n
\nOrderBy()\/OrderByDescending():<\/p>\n
\npublic void LastShouldThrowIfNoSortSpecified()
\n{
\nAssert.Throws
\n}<\/p>\n
\nConceptually, Single()\/SingleOrDefault() works the same as First()\/FirstOrDefault(). The main difference is that Single()\/SingleOrDefault() returns Top(2) instead of Top(1) and throws an exception if two records are returned from the database. The following test retrieves the single record where Id == 1:<\/p>\n
\npublic void GetOneMatchingRecordWithSingle()
\n{
\n\/\/Gets the first record, database order
\nvar customer = Context.Customers.Single(x => x.Id == 1);
\nAssert.Equal(1, customer.Id);
\n}<\/p>\n
\nWHERE .[Id] = 1<\/p>\n
\n{
\n\/\/Filters based on Id. Throws due to no match
\nAssert.Throws
\n}<\/p>\n
\npublic void SingleShouldThrowExceptionIfMoreThenOneMatch()
\n{
\n\/\/ Throws due to more than one match
\nAssert.Throws
\n}
\n[Fact]
\npublic void SingleOrDefaultShouldThrowExceptionIfMoreThenOneMatch()
\n{
\n\/\/ Throws due to more than one match
\nAssert.Throws
\n}<\/p>\n
\npublic void SingleOrDefaultShouldReturnDefaultIfNoneMatch()
\n{
\n\/\/Expression<Func
\n\/\/Returns null when nothing is found
\nvar customer = Context.Customers.SingleOrDefault(expression);
\nAssert.Null(customer);
\n}<\/p>\n
\nSELECT TOP(2) .[Id], .[TimeStamp], .[FirstName], .[FullName], .[LastName] FROM [Dbo].[Customers] AS
\nWHERE .[Id] = 10<\/p>\n
\nRecall that there is a global query filter on the Car entity to filter out any cars where IsDrivable is false. Open the CarTests.cs class and add the following test that gets all the records that pass the query filter:<\/p>\n
\npublic void ShouldReturnDrivableCarsWithQueryFilterSet()
\n{
\nIQueryable
\nAssert.Equal(9, cars.Count);
\n}<\/p>\n
\nWhen the query is executed, the global query filter is applied, and the following SQL is executed:<\/p>\n
\n[i].[MakeId], [i].[PetName], [i].[Price], [i].[TimeStamp] FROM [dbo].[Inventory] AS [i]
\nWHERE [i].[IsDrivable] = CAST(1 AS bit)<\/p>\n
\nTo disable global query filters for the entities in a query, add the IgnoreQueryFilters() method to the LINQ query. If there is more than one entity with a global query filter and some of the entities\u2019 filters are required, they must be added to the LINQ statement\u2019s Where() methods. Add the following test to the CarTests.cs class, which disables the query filter and returns all records:<\/p>\n
\npublic void ShouldGetAllOfTheCars()
\n{
\nIQueryable
\nvar cars = query.ToList(); Assert.Equal(10, cars.Count);
\n}<\/p>\n
\nFROM [dbo].[Inventory] AS [i]<\/p>\n
\nIn addition to the global query filter on the Car entity, we added a query filter to the CarNavigation property of the Order entity. To see this in action, add the following test to the OrderTests.cs class:<\/p>\n
\npublic void ShouldGetAllOrdersExceptFiltered()
\n{
\nvar query = Context.Orders.AsQueryable(); var qs = query.ToQueryString(); OutputHelper.WriteLine($"Query: {qs}"); var orders = query.ToList(); Assert.NotEmpty(orders); Assert.Equal(4,orders.Count);
\n}<\/p>\n
\nSELECT [o].[Id], [o].[CarId], [o].[CustomerId], [o].[TimeStamp] FROM [dbo].[Orders] AS [o]
\nINNER JOIN (
\nSELECT [i].[Id], [i].[IsDrivable] FROM [dbo].[Inventory] AS [i]
\nWHERE [i].[IsDrivable] = CAST(1 AS bit)
\n) AS [t] ON [o].[CarId] = [t].[Id]
\nWHERE [t].[IsDrivable] = CAST(1 AS bit)<\/p>\n
\n{
\nvar query = Context.Orders.IgnoreQueryFilters(); var qs = query.ToQueryString(); OutputHelper.WriteLine($"Query: {qs}");<\/p>\n
\n}<\/p>\n
\nEntities that are linked through navigation properties can be instantiated in one query using eager loading. The Include() method indicates a join to the related entity, and the ThenInclude() method is used for subsequent joins to other entities. Both of these methods will be demonstrated in these tests. When the Include()\/ThenInclude() methods are translated into SQL, required relationships use an inner join, and optional relationships use a left join.
\nAdd the following test to the CarTests.cs class to show a single Include(): [Fact]
\npublic void ShouldGetAllOfTheCarsWithMakes()
\n{
\nIIncludableQueryable<Car, Make> query = Context.Cars.Include(c => c.MakeNavigation); var qs = query.ToQueryString(); OutputHelper.WriteLine($"Query: {qs}");
\nvar cars = query.ToList(); Assert.Equal(9, cars.Count);
\n}<\/p>\n
\n[i].[MakeId], [i].[PetName], [i].[Price], [i].[TimeStamp], [m].[Id], [m].[Name], [m].[TimeStamp]
\nFROM [dbo].[Inventory] AS [i]
\nINNER JOIN [dbo].[Makes] AS [m] ON [i].[MakeId] = [m].[Id] WHERE [i].[IsDrivable] = CAST(1 AS bit)<\/p>\n
\npublic void ShouldGetCarsOnOrderWithRelatedProperties()
\n{<\/p>\n
\n.Where(c => c.Orders.Any())
\n.Include(c => c.MakeNavigation)
\n.Include(c => c.Orders).ThenInclude(o => o.CustomerNavigation);
\nvar qs = query.ToQueryString(); OutputHelper.WriteLine($"Query: {qs}"); var cars = query.ToList(); Assert.Equal(4, cars.Count); cars.ForEach(c =>
\n{
\nAssert.NotNull(c.MakeNavigation); Assert.NotNull(c.Orders.ToList()[0].CustomerNavigation);
\n});
\n}<\/p>\n
\n[i].[MakeId], [i].[PetName], [i].[Price], [i].[TimeStamp], [m].[Id],
\n[m].[Name], [m].[TimeStamp], [t0].[Id], [t0].[CarId], [t0].[CustomerId],
\n[t0].[TimeStamp], [t0].[Id0], [t0].[TimeStamp0], [t0].[FirstName],
\n[t0].[FullName], [t0].[LastName], [t0].[Id1] FROM [dbo].[Inventory] AS [i]
\nINNER JOIN [dbo].[Makes] AS [m] ON [i].[MakeId] = [m].[Id] LEFT JOIN (
\nSELECT [o0].[Id], [o0].[CarId], [o0].[CustomerId], [o0].[TimeStamp],
\n.[Id] AS [Id0], .[TimeStamp] AS [TimeStamp0], .[FirstName],
\n.[FullName], .[LastName], [t1].[Id] AS [Id1] FROM [dbo].[Orders] AS [o0]
\nINNER JOIN (
\nSELECT [i1].[Id], [i1].[IsDrivable] FROM [dbo].[Inventory] AS [i1]
\nWHERE [i1].[IsDrivable] = CAST(1 AS bit)
\n) AS [t1] ON [o0].[CarId] = [t1].[Id]
\nINNER JOIN [dbo].[Customers] AS ON [o0].[CustomerId] = .[Id] WHERE [t1].[IsDrivable] = CAST(1 AS bit)
\n) AS [t0] ON [i].[Id] = [t0].[CarId]
\nWHERE ([i].[IsDrivable] = CAST(1 AS bit)) AND EXISTS ( SELECT 1
\nFROM [dbo].[Orders] AS [o] INNER JOIN (
\nSELECT [i0].[Id], [i0].[Color], [i0].[DateBuilt], [i0].[Display],
\n[i0].[IsDrivable], [i0].[MakeId], [i0].[PetName], [i0].[Price], [i0].[TimeStamp] FROM [dbo].[Inventory] AS [i0]
\nWHERE [i0].[IsDrivable] = CAST(1 AS bit)
\n) AS [t] ON [o].[CarId] = [t].[Id]
\nWHERE ([t].[IsDrivable] = CAST(1 AS bit)) AND ([i].[Id] = [o].[CarId]))
\nORDER BY [i].[Id], [m].[Id], [t0].[Id], [t0].[Id1], [t0].[Id0]<\/p>\n
\npublic void ShouldGetCarsOnOrderWithRelatedPropertiesIgnoreFilters()
\n{
\nIIncludableQueryable<Car, Customer> query = Context.Cars.IgnoreQueryFilters().Where(c => c.Orders.Any())
\n.Include(c => c.MakeNavigation)
\n.Include(c => c.Orders).ThenInclude(o => o.CustomerNavigati on) ;
\nvar qs = query.ToQueryString(); OutputHelper.WriteLine($"Query: {qs}"); var cars = query.ToList(); Assert.Equal(5, cars.Count); cars.ForEach(c =>
\n{
\nAssert.NotNull(c.MakeNavigation); Assert.NotNull(c.Orders.ToList()[0].CustomerNavigation);
\n});
\n}<\/p>\n
\n[MakeId], [i].[PetName], [i].[Price], [i].[TimeStamp], [m].[Id], [m].[Name], [m].
\n[TimeStamp], [t].[Id], [t].[CarId], [t].[CustomerId], [t].[TimeStamp], [t].[Id0], [t]. [TimeStamp0], [t].[FirstName], [t].[FullName], [t].[LastName]
\nFROM [dbo].[Inventory] AS [i]
\nINNER JOIN [dbo].[Makes] AS [m] ON [i].[MakeId] = [m].[Id] LEFT JOIN (
\nSELECT [o0].[Id], [o0].[CarId], [o0].[CustomerId], [o0].[TimeStamp], .[Id] AS
\n[Id0], .[TimeStamp] AS [TimeStamp0], .[FirstName], .[FullName], .[LastName] FROM [dbo].[Orders] AS [o0]
\nINNER JOIN [dbo].[Customers] AS ON [o0].[CustomerId] = .[Id]
\n) AS [t] ON [i].[Id] = [t].[CarId] WHERE EXISTS (
\nSELECT 1
\nFROM [dbo].[Orders] AS [o] WHERE [i].[Id] = [o].[CarId])
\nORDER BY [i].[Id], [m].[Id], [t].[Id], [t].[Id0]<\/p>\n
\nThe more joins added into a LINQ query, the more complex the resulting query becomes. As the previous examples demonstrated, query filters can make the queries even more complex. EF Core 5 introduced the ability to run complicated joins as split queries by adding the AsSplitQuery() method into the LINQ query. As discussed in the previous chapters, this can gain efficiency at the risk of data inconsistency. The following test demonstrates the same query just exercised, but as a split query:<\/p>\n
\npublic void ShouldGetCarsOnOrderWithRelatedPropertiesAsSplitQuery()
\n{
\nIQueryable
\n.Include(c => c.Orders).ThenInclude(o => o.CustomerNavigation)
\n.AsSplitQuery();
\nvar qs = query.ToQueryString(); OutputHelper.WriteLine($"Query: {qs}"); var cars = query.ToList(); Assert.Equal(4, cars.Count); cars.ForEach(c =>
\n{
\nAssert.NotNull(c.MakeNavigation); Assert.NotNull(c.Orders.ToList()[0].CustomerNavigation);
\n});
\n}<\/p>\n
\n[i].[PetName], [i].[Price], [i].[TimeStamp], [m].[Id], [m].[Name], [m].[TimeStamp] FROM [dbo].[Inventory] AS [i]
\nINNER JOIN [dbo].[Makes] AS [m] ON [i].[MakeId] = [m].[Id] WHERE ([i].[IsDrivable] = CAST(1 AS bit)) AND EXISTS (
\nSELECT 1
\nFROM [dbo].[Orders] AS [o] INNER JOIN (
\nSELECT [i0].[Id], [i0].[Color], [i0].[DateBuilt], [i0].[Display], [i0].[IsDrivable],
\n[i0].[MakeId], [i0].[PetName], [i0].[Price], [i0].[TimeStamp] FROM [dbo].[Inventory] AS [i0]
\nWHERE [i0].[IsDrivable] = CAST(1 AS bit)
\n) AS [t] ON [o].[CarId] = [t].[Id]
\nWHERE ([t].[IsDrivable] = CAST(1 AS bit)) AND ([i].[Id] = [o].[CarId])) ORDER BY [i].[Id], [m].[Id]<\/p>\n
\n[t0].[TimeStamp0], [t0].[FirstName], [t0].[FullName], [t0].[LastName], [i].[Id], [m].[Id]
\nFROM [dbo].[Inventory] AS [i]
\nINNER JOIN [dbo].[Makes] AS [m] ON [i].[MakeId] = [m].[Id] INNER JOIN (
\nSELECT [o0].[Id], [o0].[CarId], [o0].[CustomerId], [o0].[TimeStamp],
\n.[Id] AS [Id0], .[TimeStamp] AS [TimeStamp0], .[FirstName], .[FullName], .[LastName]
\nFROM [dbo].[Orders] AS [o0] INNER JOIN (
\nSELECT [i1].[Id], [i1].[IsDrivable] FROM [dbo].[Inventory] AS [i1]
\nWHERE [i1].[IsDrivable] = CAST(1 AS bit)
\n) AS [t1] ON [o0].[CarId] = [t1].[Id]
\nINNER JOIN [dbo].[Customers] AS ON [o0].[CustomerId] = .[Id] WHERE [t1].[IsDrivable] = CAST(1 AS bit)
\n) AS [t0] ON [i].[Id] = [t0].[CarId]<\/p>\n
\nFROM [dbo].[Orders] AS [o] INNER JOIN (
\nSELECT [i0].[Id], [i0].[Color], [i0].[DateBuilt], [i0].[Display], [i0].[IsDrivable],
\n[i0].[MakeId], [i0].[PetName], [i0].[Price], [i0].[TimeStamp] FROM [dbo].[Inventory] AS [i0]
\nWHERE [i0].[IsDrivable] = CAST(1 AS bit)
\n) AS [t] ON [o].[CarId] = [t].[Id]
\nWHERE ([t].[IsDrivable] = CAST(1 AS bit)) AND ([i].[Id] = [o].[CarId])) ORDER BY [i].[Id], [m].[Id]<\/p>\n
\npublic void ShouldGetCarsOnOrderWithRelatedPropertiesAsSplitQueryIgnoreQueryFilters()
\n{
\nIQueryable
\n.Where(c => c.Orders.Any())
\n.Include(c => c.MakeNavigation)
\n.Include(c => c.Orders).ThenInclude(o => o.CustomerNavigation)
\n.AsSplitQuery(); var qs = query.ToQueryString();
\nOutputHelper.WriteLine($"Query: {qs}"); var cars = query.ToList(); Assert.Equal(5, cars.Count); cars.ForEach(c =>
\n{
\nAssert.NotNull(c.MakeNavigation); Assert.NotNull(c.Orders.ToList()[0].CustomerNavigation);
\n});
\n}<\/p>\n
\n[i].[MakeId], [i].[PetName], [i].[Price], [i].[TimeStamp], [m].[Id], [m].[Name], [m]. [TimeStamp]
\nFROM [dbo].[Inventory] AS [i]
\nINNER JOIN [dbo].[Makes] AS [m] ON [i].[MakeId] = [m].[Id] WHERE EXISTS (
\nSELECT 1
\nFROM [dbo].[Orders] AS [o] WHERE [i].[Id] = [o].[CarId])
\nORDER BY [i].[Id], [m].[Id]<\/p>\n
\n[t].[TimeStamp0], [t].[FirstName], [t].[FullName], [t].[LastName], [i].[Id], [m].[Id] FROM [dbo].[Inventory] AS [i]
\nINNER JOIN [dbo].[Makes] AS [m] ON [i].[MakeId] = [m].[Id]<\/p>\n
\nSELECT [o0].[Id], [o0].[CarId], [o0].[CustomerId], [o0].[TimeStamp],
\n.[Id] AS [Id0], .[TimeStamp] AS [TimeStamp0], .[FirstName], .[FullName], .[LastName]
\nFROM [dbo].[Orders] AS [o0]
\nINNER JOIN [dbo].[Customers] AS ON [o0].[CustomerId] = .[Id]
\n) AS [t] ON [i].[Id] = [t].[CarId] WHERE EXISTS (
\nSELECT 1
\nFROM [dbo].[Orders] AS [o] WHERE [i].[Id] = [o].[CarId])
\nORDER BY [i].[Id], [m].[Id]<\/p>\n
\nEF Core 5 introduces the ability to filter when including collection properties. Prior to EF Core 5, the only way to get a filtered list for a collection navigation property was to use explicit loading. Add the following test into the MakeTests.cs class, which demonstrates getting all the Make records and those cars that are yellow:<\/p>\n
\npublic void ShouldGetAllMakesAndCarsThatAreYellow()
\n{
\nvar query = Context.Makes.IgnoreQueryFilters()
\n.Include(x => x.Cars.Where(x => x.Color == "Yellow")); var qs = query.ToQueryString(); OutputHelper.WriteLine($"Query: {qs}");
\nvar makes = query.ToList(); Assert.NotNull(makes); Assert.NotEmpty(makes);
\nAssert.NotEmpty(makes.Where(x => x.Cars.Any())); Assert.Empty(makes.First(m => m.Id == 1).Cars); Assert.Empty(makes.First(m => m.Id == 2).Cars); Assert.Empty(makes.First(m => m.Id == 3).Cars); Assert.Single(makes.First(m => m.Id == 4).Cars); Assert.Empty(makes.First(m => m.Id == 5).Cars);
\n}<\/p>\n
\n[t].[DateBuilt], [t].[Display], [t].[IsDrivable], [t].[MakeId],
\n[t].[PetName], [t].[Price], [t].[TimeStamp] FROM [dbo].[Makes] AS [m]
\nLEFT JOIN (
\nSELECT [i].[Id], [i].[Color], [i].[DateBuilt], [i].[Display], [i].[IsDrivable],
\n[i].[MakeId], [i].[PetName], [i].[Price], [i].[TimeStamp] FROM [dbo].[Inventory] AS [i]
\nWHERE [i].[Color] = N'Yellow'
\n) AS [t] ON [m].[Id] = [t].[MakeId]
\nORDER BY [m].[Id], [t].[Id]<\/p>\n
\nFROM [dbo].[Makes] AS [m]
\nORDER BY [m].[Id]<\/p>\n
\n[t].[MakeId], [t].[PetName], [t].[Price], [t].[TimeStamp], [m].[Id] FROM [dbo].[Makes] AS [m]
\nINNER JOIN (
\nSELECT [i].[Id], [i].[Color], [i].[DateBuilt], [i].[Display], [i].[IsDrivable],
\n[i].[MakeId], [i].[PetName], [i].[Price], [i].[TimeStamp] FROM [dbo].[Inventory] AS [i]
\nWHERE [i].[Color] = N'Yellow'
\n) AS [t] ON [m].[Id] = [t].[MakeId] ORDER BY [m].[Id]<\/p>\n
\nIf the related data needs to be loaded after the principal entity was queried into memory, the related entities can be retrieved from the database with subsequent database calls. This is triggered using the Entry() method on the derived DbContext. When loading entities on the many end of a one-to-many relationship, use the Collection() method on the Entry result. To load entities on the one end of a one-to-many (or
\nin a one-to-one relationship), use the Reference() method. Calling Query() on the Collection() or Reference() method returns an IQueryable
\nThe following test (back in the CarTests.cs class) shows how to load a reference navigation property on the Car entity:<\/p>\n
\npublic void ShouldGetReferenceRelatedInformationExplicitly()
\n{
\nvar car = Context.Cars.First(x => x.Id == 1); Assert.Null(car.MakeNavigation);
\nvar query = Context.Entry(car).Reference(c => c.MakeNavigation).Query(); var qs = query.ToQueryString();
\nOutputHelper.WriteLine($"Query: {qs}"); query.Load(); Assert.NotNull(car.MakeNavigation);
\n}<\/p>\n
\nWHERE [m].[Id] = @ p_0<\/p>\n
\npublic void ShouldGetCollectionRelatedInformationExplicitly()
\n{
\nvar car = Context.Cars.First(x => x.Id == 1); Assert.Empty(car.Orders);
\nvar query = Context.Entry(car).Collection(c => c.Orders).Query(); var qs = query.ToQueryString();
\nOutputHelper.WriteLine($"Query: {qs}"); query.Load(); Assert.Single(car.Orders);
\n}<\/p>\n
\nINNER JOIN (
\nSELECT [i].[Id], [i].[IsDrivable] FROM [dbo].[Inventory] AS [i]
\nWHERE [i].[IsDrivable] = CAST(1 AS bit)
\n) AS [t] ON [o].[CarId] = [t].[Id]
\nWHERE ([t].[IsDrivable] = CAST(1 AS bit)) AND ([o].[CarId] = @ p_0)<\/p>\n
\nIn addition to shaping queries generated when eagerly loading related data, global query filters are active when explicitly loading related data. Take the following test (in the MakeTests.cs class):<\/p>\n
\npublic void ShouldGetAllCarsForAMakeExplicitlyWithQueryFilters(int makeId, int carCount)
\n{
\nvar make = Context.Makes.First(x => x.Id == makeId);
\nIQueryable
\nOutputHelper.WriteLine($"Query: {qs}"); query.Load(); Assert.Equal(carCount,make.Cars.Count());
\n}<\/p>\n
\n[i].[MakeId], [i].[PetName], [i].[Price], [i].[TimeStamp] FROM [dbo].[Inventory] AS [i]
\nWHERE ([i].[IsDrivable] = CAST(1 AS bit)) AND ([i].[MakeId] = @ p_0)<\/p>\n
\n[InlineData(2, 1)]
\n[InlineData(3, 1)]
\n[InlineData(4, 2)]
\n[InlineData(5, 3)]
\n[InlineData(6, 1)]
\npublic void ShouldGetAllCarsForAMakeExplicitly(int makeId, int carCount)
\n{
\nvar make = Context.Makes.First(x => x.Id == makeId); IQueryable
\nContext.Entry(make).Collection(c => c.Cars).Query().IgnoreQueryFilters(); var qs = query.IgnoreQueryFilters().ToQueryString(); OutputHelper.WriteLine($"Query: {qs}");
\nquery.Load();
\nAssert.Equal(carCount, make.Cars.Count());
\n}<\/p>\n
\nThis section exercises EF Core\u2019s ability to retrieve historical data from temporal tables. As a reminder, the repos that derive from the TemporalTableBaseRepo contain the methods to query temporal tables using one of the five temporal query operators. To demonstrate this, open the MakeTests class, and add the following test at the bottom of the file:<\/p>\n
\npublic void ShouldGetAllHistoryRows()
\n{
\nvar make = new Make { Name = "TestMake" };
\n_repo.Add(make); Thread.Sleep(2000); make.Name = "Updated Name";
\n_repo.Update(make);
\nThread.Sleep(2000);
\n_repo.Delete(make);<\/p>\n
\nAssert.Equal("TestMake", list[0].Entity.Name); Assert.Equal("Updated Name", list[1].Entity.Name); Assert.Equal(list[0].ValidTo, list[1].ValidFrom);
\n}<\/p>\n
\nThis section exercises EF Core\u2019s ability to retrieve data using raw SQL queries using the DbSet
\npublic void ShouldNotGetTheLemonsUsingFromSql()
\n{
\nvar entity = Context.Model.FindEntityType($"{typeof(Car).FullName}"); var tableName = entity.GetTableName();
\nvar schemaName = entity.GetSchema(); var query = Context.Cars
\n.FromSqlRaw($"Select *,ValidFrom,ValidTo from {schemaName}.{tableName}"); var qs = query.ToQueryString();
\nOutputHelper.WriteLine($"Query: {qs}"); var cars = query.ToList(); Assert.Equal(9, cars.Count);
\n}<\/p>\n
\n[MakeId], [a].[PetName], [a].[Price], [a].[TimeStamp], [a].[ValidFrom], [a].[ValidTo] FROM (
\nSelect *,ValidFrom,ValidTo from dbo.Inventory
\n) AS [a]
\nWHERE [a].[IsDrivable] = CAST(1 AS bit)<\/p>\n
\npublic void ShouldGetOneCarUsingInterpolation()
\n{
\nvar carId = 1;
\nvar query = Context.Cars
\n.FromSqlInterpolated($"Select *,ValidFrom,ValidTo from dbo.Inventory where Id =
\n{carId}")
\n.Include(x => x.MakeNavigation);
\nvar qs = query.ToQueryString(); OutputHelper.WriteLine($"Query: {qs}"); var car = query.First(); Assert.Equal("Black", car.Color);
\nAssert.Equal("VW", car.MakeNavigation.Name);
\n}<\/p>\n
\n[a].[PetName], [a].[Price], [a].[TimeStamp], [a].[ValidFrom], [a].[ValidTo],
\n[m].[Id], [m].[Name], [m].[TimeStamp], [m].[ValidFrom], [m].[ValidTo] FROM (
\nSelect *,ValidFrom,ValidTo from dbo.Inventory where Id = @p0
\n) AS [a]
\nINNER JOIN [dbo].[Makes] AS [m] ON [a].[MakeId] = [m].[Id] WHERE [a].[IsDrivable] = CAST(1 AS bit)<\/p>\n
\nThe next set of tests demonstrate the server-side aggregate methods (Max(), Min(), Count(), Average(), etc.). Aggregate methods can be added to the end of a LINQ query with Where() methods, or the filter expression can be contained in the aggregate method itself (just like First() and Single()). The aggregation executes on the server side, and the single value is returned from the query. Global query filters affect aggregate methods as well and can be disabled with IgnoreQueryFilters().
\nAll the SQL statements shown in this section were collected using SQL Server Profiler.
\nThis first test (in CarTests.cs) simply counts all the Car records in the database. Since the query filter is still active, the count returns nine cars.<\/p>\n
\npublic void ShouldGetTheCountOfCars()
\n{
\nvar count = Context.Cars.Count(); Assert.Equal(9, count);
\n}<\/p>\n
\nFROM [dbo].[Inventory] AS [i]
\nWHERE [i].[IsDrivable] = CAST(1 AS bit)<\/p>\n
\npublic void ShouldGetTheCountOfCarsIgnoreQueryFilters()
\n{
\nvar count = Context.Cars.IgnoreQueryFilters().Count(); Assert.Equal(10, count);
\n}<\/p>\n
\nSELECT COUNT(*) FROM [dbo].[Inventory] AS [i]<\/p>\n
\n[InlineData(2, 1)]
\n[InlineData(3, 1)]
\n[InlineData(4, 2)]
\n[InlineData(5, 3)]
\n[InlineData(6, 1)]
\npublic void ShouldGetTheCountOfCarsByMakeP1(int makeId, int expectedCount)
\n{
\nvar count = Context.Cars.Count(x=>x.MakeId == makeId); Assert.Equal(expectedCount, count);
\n}<\/p>\n
\n[InlineData(2, 1)]
\n[InlineData(3, 1)]
\n[InlineData(4, 2)]
\n[InlineData(5, 3)]
\n[InlineData(6, 1)]
\npublic void ShouldGetTheCountOfCarsByMakeP2(int makeId, int expectedCount)
\n{
\nvar count = Context.Cars.Where(x => x.MakeId == makeId).Count(); Assert.Equal(expectedCount, count);
\n}<\/p>\n
\nWHERE ([i].[IsDrivable] = CAST(1 AS bit)) AND ([i].[MakeId] = @ makeId_0)'
\n,N'@ makeId_0 int',@ makeId_0=6<\/p>\n
\nThe Any() and All() methods check a set of records to see whether any records match the criteria (Any()) or whether all records match the criteria (All()). Global query filters affect Any() and All() methods functions as well and can be disabled with IgnoreQueryFilters(). All the SQL statements shown in this section were collected using SQL Server Profiler.
\nThis first test (in CarTests.cs) checks if any car records have a specific MakeId:<\/p>\n
\n[InlineData(11, false)]
\npublic void ShouldCheckForAnyCarsWithMake(int makeId, bool expectedResult)
\n{
\nvar result = Context.Cars.Any(x => x.MakeId == makeId); Assert.Equal(expectedResult, result);
\n}<\/p>\n
\nSELECT 1
\nFROM [dbo].[Inventory] AS [i]
\nWHERE ([i].[IsDrivable] = CAST(1 AS bit)) AND ([i].[MakeId] = @ makeId_0)) THEN CAST(1 AS bit)
\nELSE CAST(0 AS bit)
\nEND',N'@ makeId_0 int',@ makeId_0=1
\nThis second test checks if all car records have a specific MakeId: [Theory]
\n[InlineData(1, false)] [InlineData(11, false)]
\npublic void ShouldCheckForAllCarsWithMake(int makeId, bool expectedResult)
\n{
\nvar result = Context.Cars.All(x => x.MakeId == makeId); Assert.Equal(expectedResult, result);
\n}<\/p>\n
\nexec sp_executesql N'SELECT CASE WHEN NOT EXISTS (
\nSELECT 1
\nFROM [dbo].[Inventory] AS [i]
\nWHERE ([i].[IsDrivable] = CAST(1 AS bit)) AND ([i].[MakeId] <> @ makeId_0)) THEN CAST(1 AS bit)
\nELSE CAST(0 AS bit)
\nEND',N'@ makeId_0 int',@ makeId_0=1<\/p>\n
\nThe final test is to make sure that the CarRepo can get the PetName from the stored procedure. With this code in place, the test becomes trivial. Add the following test to the CarTests.cs class:<\/p>\n
\n[InlineData(1, "Zippy")]
\n[InlineData(2, "Rusty")]
\n[InlineData(3, "Mel")] [InlineData(4, "Clunker")] [InlineData(5, "Bimmer")] [InlineData(6, "Hank")]
\n[InlineData(7, "Pinky")]
\n[InlineData(8, "Pete")] [InlineData(9, "Brownie")]
\npublic void ShouldGetValueFromStoredProc(int id, string expectedName)
\n{
\nAssert.Equal(expectedName, _carRepo.GetPetName(id));
\n}<\/p>\n
\nRecords are added to the database by creating them in code, adding them to their DbSet
\nAs a reminder, SaveChanges() executes in an implicit transaction, unless an explicit transaction is used.
\nIf the save was successful, the server-generated values are then queried to set the values on the entities. These tests will all use an explicit transaction so the changes can be rolled back, leaving the database in the same state as when the test execution began.
\nAll the SQL statements shown in this section were collected using SQL Server Profiler.<\/p>\n
\nThe following test demonstrates how to add a single record to the Inventory table:<\/p>\n
\npublic void ShouldAddACar()
\n{
\nExecuteInATransaction(RunTheTest);<\/p>\n
\n{
\nvar car = new Car
\n{
\nColor = "Yellow", MakeId = 1, PetName = "Herbie"
\n};
\nvar carCount = Context.Cars.Count(); Context.Cars.Add(car); Context.SaveChanges();
\nvar newCarCount = Context.Cars.Count(); Assert.Equal(carCount+1,newCarCount);
\n}
\n}<\/p>\n
\nINSERT INTO [dbo].[Inventory] ([Color], [MakeId], [PetName], [Price]) VALUES (@p0, @p1, @p2, @p3);
\nSELECT [Id], [DateBuilt], [Display], [IsDrivable], [TimeStamp] FROM [dbo].[Inventory]
\nWHERE @@ROWCOUNT = 1 AND [Id] = scope_identity(); ',
\nN'@p0 nvarchar(50),@p1 int,@p2 nvarchar(50),@p3 nvarchar(50)',@p0=N'Yellow',@p1=1, @p2=N'Herbie',@p3=NULL<\/p>\n
\nThe following test creates a new Car entity with the Id left at the default value of zero. When the entity is attached to the ChangeTracker, the state is set to Added, and calling SaveChanges() adds the entity to the database.<\/p>\n
\npublic void ShouldAddACarWithAttach()
\n{
\nExecuteInATransaction(RunTheTest);<\/p>\n
\n{<\/p>\n
\n{
\nColor = "Yellow", MakeId = 1, PetName = "Herbie"
\n};
\nvar carCount = Context.Cars.Count(); Context.Cars.Attach(car);
\nAssert.Equal(EntityState.Added, Context.Entry(car).State); Context.SaveChanges();
\nvar newCarCount = Context.Cars.Count(); Assert.Equal(carCount + 1, newCarCount);
\n}
\n}<\/p>\n
\nTo insert multiple records in a single transaction, use the AddRange() method of DbSet
\npublic void ShouldAddMultipleCars()
\n{
\nExecuteInATransaction(RunTheTest);<\/p>\n
\n{
\n\/\/Have to add 4 to activate batching var cars = new List
\n{
\nnew() { Color = "Yellow", MakeId = 1, PetName = "Herbie" }, new() { Color = "White", MakeId = 2, PetName = "Mach 5" }, new() { Color = "Pink", MakeId = 3, PetName = "Avon" }, new() { Color = "Blue", MakeId = 4, PetName = "Blueberry" },
\n};
\nvar carCount = Context.Cars.Count(); Context.Cars.AddRange(cars); Context.SaveChanges();
\nvar newCarCount = Context.Cars.Count(); Assert.Equal(carCount + 4, newCarCount);
\n}
\n}<\/p>\n
\nDECLARE @inserted0 TABLE ([Id] int, [_Position] [int]); MERGE [dbo].[Inventory] USING (<\/p>\n
\n(@p8, @p9, @p10, @p11, 2), (@p12, @p13, @p14, @p15, 3))
\nAS i ([Color], [MakeId], [PetName], [Price], _Position) ON 1=0 WHEN NOT MATCHED THEN
\nINSERT ([Color], [MakeId], [PetName], [Price])
\nVALUES (i.[Color], i.[MakeId], i.[PetName], i.[Price]) OUTPUT INSERTED.[Id], i._Position
\nINTO @inserted0;<\/p>\n
\nINNER JOIN @inserted0 i ON ([t].[Id] = [i].[Id]) ORDER BY [i].[_Position];
\n',
\nN'@p0 nvarchar(50),@p1 int,@p2 nvarchar(50),@p3 nvarchar(50), @p4 nvarchar(50),@p5 int,@p6 nvarchar(50),@p7 nvarchar(50), @p8 nvarchar(50),@p9 int,@p10 nvarchar(50),@p11 nvarchar(50),
\n@p12 nvarchar(50),@p13 int,@p14 nvarchar(50),@p15 nvarchar(50)', @p0=N'Yellow',@p1=1,@p2=N'Herbie',@p3=NULL,@p4=N'White',@p5=2,
\n@p6=N'Mach 5',@p7=NULL,@p8=N'Pink',@p9=3,@p10=N'Avon',@p11=NULL,@p12=N'Blue', @p13=4,@p14=N'Blueberry',@p15=NULL<\/p>\n
\nThe following test demonstrates adding an object graph (related Make, Car, and Radio records):<\/p>\n
\npublic void ShouldAddAnObjectGraph()
\n{
\nExecuteInATransaction(RunTheTest);<\/p>\n
\n{
\nvar make = new Make {Name = "Honda"}; var car = new Car
\n{
\nColor = "Yellow", MakeId = 1,
\nPetName = "Herbie", RadioNavigation = new Radio
\n{
\nHasSubWoofers = true, HasTweeters = true, RadioId = "Bose 1234"
\n}
\n};
\n\/\/Cast the Cars property to List
\nContext.Makes.Add(make);<\/p>\n
\nvar newCarCount = Context.Cars. Count(); var newMakeCount = Context.Makes. Count(); Assert.Equal(carCount+1,newCarCount); Assert.Equal(makeCount+1,newMakeCount);
\n}
\n}<\/p>\n
\nSELECT [Id], [TimeStamp] FROM [dbo].[Makes]
\nWHERE @@ROWCOUNT = 1 AND [Id] = scope_identity(); ',
\nN'@p0 nvarchar(50)',@p0=N'Honda'<\/p>\n
\nINSERT INTO [dbo].[Inventory] ([Color], [MakeId], [PetName], [Price]) VALUES (@p1, @p2, @p3, @p4);
\nSELECT [Id], [DateBuilt], [Display], [IsDrivable], [TimeStamp] FROM [dbo].[Inventory]
\nWHERE @@ROWCOUNT = 1 AND [Id] = scope_identity();<\/p>\n
\nINSERT INTO [dbo].[Radios] ([InventoryId], [HasSubWoofers], [HasTweeters], [RadioId])
\nVALUES (@p5, @p6, @p7, @p8); SELECT [Id], [TimeStamp] FROM [dbo].[Radios]
\nWHERE @@ROWCOUNT = 1 AND [Id] = scope_identity(); ',
\nN'@p5 int,@p6 bit,@p7 bit,@p8 nvarchar(50)',@p5=11, @p6=1,@p7=1,@p8=N'Bose 1234'<\/p>\n
\nRecords are updated by loading them into DbSet
\nThe following test updates a single record, but the process is the same if multiple tracked entities are updated and saved.<\/p>\n
\npublic void ShouldUpdateACar()
\n{
\nExecuteInASharedTransaction(RunTheTest);<\/p>\n
\n{
\nvar car = Context.Cars.First(c => c.Id == 1); Assert.Equal("Black",car.Color);
\ncar.Color = "White";
\n\/\/Calling update is not needed because the entity is tracked
\n\/\/Context.Cars.Update(car); Context.SaveChanges(); Assert.Equal("White", car.Color);
\nvar context2 = TestHelpers.GetSecondContext(Context, trans); var car2 = context2.Cars.First(c => c.Id == 1); Assert.Equal("White", car2.Color);
\n}
\n}<\/p>\n
\nFROM [dbo].[Inventory]
\nWHERE @@ROWCOUNT = 1 AND [Id] = @p1;<\/p>\n
\nUntracked entities can also be used to update database records. The process is similar to updating tracked entities except that the entity is created in code (and not queried), and EF Core must be notified that the entity should already exist in the database and needs to be updated.
\nThe following example reads a record in as nontracked, creates a new instance of the Car class from this record, and changes one property (Color). Then it either sets the state or uses the Update() method on
\nDbSet
\npublic void ShouldUpdateACarUsingState()
\n{
\nExecuteInASharedTransaction(RunTheTest);<\/p>\n
\n{
\nvar car = Context.Cars.AsNoTracking().First(c => c.Id == 1); Assert.Equal("Black", car.Color);
\nvar updatedCar = new Car
\n{
\nColor = "White", \/\/Original is Black Id = car.Id,
\nMakeId = car.MakeId, PetName = car.PetName, TimeStamp = car.TimeStamp, IsDrivable = car.IsDrivable
\n};
\nvar context2 = TestHelpers.GetSecondContext(Context, trans);
\n\/\/Either call Update or modify the state context2.Entry(updatedCar).State = EntityState.Modified;
\n\/\/context2.Cars.Update(updatedCar); context2.SaveChanges();
\nvar context3 = TestHelpers.GetSecondContext(Context, trans);
\nvar car2 = context3.Cars.First(c => c.Id == 1); Assert.Equal("White", car2.Color);
\n}
\n}<\/p>\n
\nThe previous chapters covered concurrency checking in great detail. As a reminder, when an entity has a Timestamp property defined, the value of that property is used in the where clause when changes (updates or deletes) are being persisted to the database. Instead of just searching for the primary key, the TimeStamp value is added to the query, like this example:<\/p>\n
\nEntries to get the original values, current values, and values that are currently stored in the database. Getting the current values requires another database call.<\/p>\n
\npublic void ShouldThrowConcurrencyException()
\n{
\nExecuteInATransaction(RunTheTest);<\/p>\n
\n{<\/p>\n
\n\/\/Update the database outside of the context Context.Database.ExecuteSqlInterpolated(
\n$"Update dbo.Inventory set Color='Pink' where Id = {car.Id}"); car.Color = "Yellow";
\nvar ex = Assert.Throws
\nvar entry = ((DbUpdateConcurrencyException) ex.InnerException)?.Entries[0]; PropertyValues originalProps = entry.OriginalValues;
\nPropertyValues currentProps = entry.CurrentValues;
\n\/\/This needs another database call
\nPropertyValues databaseProps = entry.GetDatabaseValues();
\n}
\n}<\/p>\n
\nA single entity is marked for deletion by calling Remove() on DbSet
\ncause cascade effects on navigation properties based on the rules configured in the Fluent API (or by EF Core conventions). If deletion is prevented due to cascade policy, an exception is thrown.<\/p>\n
\nThe delete process mirrors the update process. Once an entity is tracked, call Remove() on that instance, and then call SaveChanges() to remove the record from the database.<\/p>\n
\npublic void ShouldRemoveACar()
\n{
\nExecuteInATransaction(RunTheTest);<\/p>\n
\n{
\nvar carCount = Context.Cars. Count();
\nvar car = Context.Cars.First(c => c.Id == 9); Context.Cars.Remove(car); Context.SaveChanges();
\nvar newCarCount = Context.Cars.Count(); Assert.Equal(carCount - 1, newCarCount); Assert.Equal(
\nEntityState.Detached, Context.Entry(car).State);
\n}
\n}<\/p>\n
\nWhen checking the EntityState, the state will be Detached.<\/p>\n
\nWHERE [Id] = @p0 AND [TimeStamp] = @p1; SELECT @@ROWCOUNT;'
\n,N'@p0 int,@p1 varbinary(8)',@p0=2,@p1=0x0000000000008680<\/p>\n
\nUntracked entities can delete records the same way untracked entities can update records. The difference is that the entity is tracked by calling Remove()\/RemoveRange() or setting the state to Deleted and then calling SaveChanges().
\nThe following example reads a record in as nontracked, creates a new instance of the Car class from this record, and changes one property (Color). Then it either sets the state or uses the Remove() method on DbSet
\npublic void ShouldRemoveACarUsingState()
\n{
\nExecuteInASharedTransaction(RunTheTest);<\/p>\n
\n{
\nvar carCount = Context.Cars.Count();
\nvar car = Context.Cars.AsNoTracking().First(c => c.Id == 1); var context2 = TestHelpers.GetSecondContext(Context, trans);
\n\/\/Either call Remove or modify the state context2.Entry(car).State = EntityState.Deleted;
\n\/\/context2.Cars.Remove(car); context2.SaveChanges();
\nvar newCarCount = Context.Cars.Count(); Assert.Equal(carCount - 1, newCarCount); Assert.Equal(
\nEntityState.Detached, Context.Entry(car).State);
\n}
\n}<\/p>\n
\nEF Core will throw a DbUpdateException when an attempt to delete a record fails due to the cascade rules. The following test shows this in action:<\/p>\n
\npublic void ShouldFailToRemoveACar()
\n{
\nExecuteInATransaction(RunTheTest);<\/p>\n
\n{
\nvar car = Context.Cars.First(c => c.Id == 1); Context.Cars.Remove(car); Assert.Throws
\n()=>Context.SaveChanges());
\n}
\n}<\/p>\n
\nDelete also uses concurrency checking if the entity has a TimeStamp property. See the section \u201cConcurrency Checking\u201d in the \u201cUpdating Records\u201d section for more information.<\/p>\n
\nThis chapter used the knowledge gained in the previous chapter to complete the data access layer for the AutoLot database. You used the EF Core command-line tools to scaffold an existing database, updated the model to its final version, and then created migrations and applied them. Repositories were added for the encapsulation of the data access, and database initialization code with sample data can drop and create the database in a repeatable, reliable manner. The rest of the chapter focused on test-driving the data access layer. This completes our journey through data access and Entity Framework Core.<\/p>\n","protected":false},"excerpt":{"rendered":"