CHAPTER 24
Test-Driving AutoLot
Now that you have the finished AutoLot data access layer, it’s 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.
After 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.
Setting Up the Test-Drives
Instead 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.
Create the Project
To get started, we are going to set up an integration test platform using xUnit, a .NET Core–compatible 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.
■Note 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.
From the command-line interface, execute the following commands using the command-line interface (CLI):
dotnet new sln -n Chapter24_AllProjects
dotnet new xunit -lang c# -n AutoLot.Dal.Tests -o .\AutoLot.Dal.Tests -f net6.0 dotnet sln .\Chapter23_AllProjects.sln add AutoLot.Dal.Tests
© Andrew Troelsen, Phil Japikse 2022
A. Troelsen and P. Japikse, Pro C# 10 with .NET 6, https://doi.org/10.1007/978-1-4842-7869-7_24
1051
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:
dotnet add AutoLot.Dal.Tests package Microsoft.EntityFrameworkCore
dotnet 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
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:
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:
dotnet add AutoLot.Dal.Tests package Microsoft.NET.Test.Sdk dotnet add AutoLot.Dal.Tests package coverlet.collector
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):
dotnet add AutoLot.Dal.Tests reference ..\Chapter_23\AutoLot.Dal dotnet add AutoLot.Dal.Tests reference ..\Chapter_23\AutoLot.Models
Finally, turn off nullable reference types and enable global implicit using statements in the project file:
■Note At the time of this writing, the xUnit project template does add the node for ImplicitUsings into the project file.
Make the AutoLot.Dal Internals Visible to AutoLot.Dal.Tests
To 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:
<_Parameter1>AutoLot.Dal.Tests
Add the GlobalUsings File
Create 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:
global using System.Data;
global using System.Linq.Expressions;
global using AutoLot.Dal.EfStructures; global using AutoLot.Dal.Exceptions; global using AutoLot.Dal.Initialization; global using AutoLot.Dal.Repos;
global using AutoLot.Dal.Repos.Interfaces;
global using AutoLot.Dal.Tests.Base; global using AutoLot.Models.Entities;
global using AutoLot.Models.Entities.Owned;
global using Microsoft.EntityFrameworkCore;
global using Microsoft.EntityFrameworkCore.ChangeTracking; global using Microsoft.EntityFrameworkCore.Storage;
global using Microsoft.EntityFrameworkCore.Query; global using Microsoft.Extensions.Configuration;
global using Xunit;
global using Xunit.Abstractions;
A First Look at xUnit
There 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:
namespace AutoLot.Dal.Tests;
public class SampleTests
{
//tests go here
}
Fact Test Methods
The 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:
[Fact]
public void SimpleFactTest()
{
Assert.Equal(5,3+2);
}
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).
Theory Test Methods
When 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:
[Theory] [InlineData(3,2,5)]
[InlineData(1,-1,0)]
public void SimpleTheoryTest(int addend1, int addend2, int expectedResult)
{
Assert.Equal(expectedResult,addend1+addend2);
}
■Note 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/.
Executing Tests
While 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.
Configure the Project and DbContext Instances
To 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):
{
"ConnectionStrings": {
"AutoLot": "server=.,5433;Database=AutoLot;User Id=sa;Password=P@ssw0rd;"
}
}
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:
Create the Integration Test Helper
The TestHelpers class will handle the application configuration as well as create new instances of
ApplicationDbContext. Add a new public static class named TestHelpers.cs in the root of the project:
namespace AutoLot.Dal.Tests; public static class TestHelpers
{
//helper code goes here
}
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:
public static IConfiguration GetConfiguration() => new ConfigurationBuilder()
.SetBasePath(Directory.GetCurrentDirectory())
.AddJsonFile("appsettings.testing.json", true, true)
.Build();
■Note Configuration of assemblies is covered in Chapter 16.
Add another public static method to create instances of the ApplicationDbContext class using the
IConfiguration instance. Add the following code to the class:
public static ApplicationDbContext GetContext(IConfiguration configuration)
{
var optionsBuilder = new DbContextOptionsBuilder
return new ApplicationDbContext(optionsBuilder.Options);
}
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(
ApplicationDbContext oldContext, IDbContextTransaction trans)
{
var optionsBuilder = new DbContextOptionsBuilder
var context = new ApplicationDbContext(optionsBuilder.Options); context.Database.UseTransaction(trans.GetDbTransaction()); return context;
}
Add the BaseTest Class
The 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.
namespace AutoLot.Dal.Tests.Base;
public abstract class BaseTest : IDisposable
{
protected readonly IConfiguration Configuration; protected readonly ApplicationDbContext Context;
public virtual void Dispose()
{
Context.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.
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.
protected BaseTest()
{
Configuration = TestHelpers.GetConfiguration(); Context = TestHelpers.GetContext(Configuration);
}
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:
protected readonly ITestOutputHelper OutputHelper;
protected BaseTest(ITestOutputHelper outputHelper)
{
Configuration = TestHelpers.GetConfiguration(); Context = TestHelpers.GetContext(Configuration); OutputHelper = outputHelper;
}
Add the Transacted Test Execution Helpers
The 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.
The ExecuteInATransaction() method executes the action delegate using a single instance of the
ApplicationDbContext. Add the following code into your BaseTest class:
protected void ExecuteInATransaction(Action actionToExecute)
{
var strategy = Context.Database.CreateExecutionStrategy(); strategy.Execute(() =>
{
using var trans = Context.Database.BeginTransaction(); actionToExecute();
trans.Rollback();
});
}
The ExecuteInASharedTransaction() method allows for multiple ApplicationDbContext instances to share a single transaction. Add the following code into your BaseTest class:
protected void ExecuteInASharedTransaction(Action
{
var strategy = Context.Database.CreateExecutionStrategy(); strategy.Execute(() =>
{
using IDbContextTransaction trans = Context.Database.BeginTransaction(IsolationLevel.ReadUncommitted);
actionToExecute(trans); trans.Rollback();
});
}
Add the EnsureAutoLotDatabase Test Fixture Class
The 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
TestFixtureClass 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.
Add a new class named EnsureAutoLotDatabaseTestFixture.cs to the Base directory and implement
IDisposable. Make the class public and sealed, and add the following using statements:
namespace AutoLot.Dal.Tests.Base;
public sealed class EnsureAutoLotDatabaseTestFixture : IDisposable
{
//add implementation code here
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’t have any code but needs to be implemented to satisfy the IDisposable interface. The following listing shows the updated constructor:
public EnsureAutoLotDatabaseTestFixture()
{
var configuration = TestHelpers.GetConfiguration(); var context = TestHelpers.GetContext(configuration); SampleDataInitializer.ClearAndReseedDatabase(context); context.Dispose();
}
Add the Integration Test Classes
The 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.
Depending 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.
xUnit 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:
[Collection("Integration Tests")]
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:
//CarTests.cs
namespace AutoLot.Dal.Tests.IntegrationTests;
[Collection("Integation Tests")] public class CarTests
: BaseTest, IClassFixture
{
public CarTests(ITestOutputHelper outputHelper) : base(outputHelper)
{
}
}
//CustomerOrderViewModelTests.cs
namespace AutoLot.Dal.Tests.IntegrationTests;
[Collection("Integation Tests")]
public class CustomerOrderViewModelTests
: BaseTest, IClassFixture
{
public CustomerOrderViewModelTests(ITestOutputHelper outputHelper)
: base(outputHelper)
{
}
}
//CustomerTests.cs
namespace AutoLot.Dal.Tests.IntegrationTests;
[Collection("Integation Tests")] public class CustomerTests
: BaseTest, IClassFixture
{
public CustomerTests(ITestOutputHelper outputHelper) : base(outputHelper)
{
}
}
//MakeTests.cs
namespace AutoLot.Dal.Tests.IntegrationTests;
[Collection("Integation Tests")] public class MakeTests
: BaseTest, IClassFixture
{
public MakeTests(ITestOutputHelper outputHelper) : base(outputHelper)
{
}
}
//OrderTests.cs
namespace AutoLot.Dal.Tests.IntegrationTests;
[Collection("Integation Tests")] public class OrderTests
: BaseTest, IClassFixture
{
public OrderTests(ITestOutputHelper outputHelper):base(outputHelper)
{
}
}
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.
[Collection("Integration Tests")]
public class CarTests : BaseTest, IClassFixture
{
private readonly ICarRepo _carRepo;
public CarTests(ITestOutputHelper outputHelper) : base(outputHelper)
{
_carRepo = new CarRepo(Context);
}
public override void Dispose()
{
_carRepo.Dispose(); base.Dispose();
}
}
Repeat the process for the CustomerOrderViewModelTests class, using the
CustomerOrderViewModelRepo instead:
[Collection("Integration Tests")]
public class CustomerOrderViewModelTests : BaseTest, IClassFixture
{
private readonly ICustomerOrderViewModelRepo _repo;
public CustomerOrderViewModelTests(ITestOutputHelper outputHelper) : base(outputHelper)
{
_repo = new CustomerOrderViewModelRepo(Context);
}
public override void Dispose()
{
_repo.Dispose(); base.Dispose();
}
}
The setup for the CustomerTests class is simpler, since it does not use the CustomerRepo:
[Collection("Integration Tests")]
public class CustomerTests : BaseTest, IClassFixture
{
public CustomerOrderViewModelTests(ITestOutputHelper outputHelper) : base(outputHelper)
{
}
}
The same process is needed for the MakeTests class, using MakeRepo:
[Collection("Integration Tests")]
public class MakeTests : BaseTest, IClassFixture
{
private readonly IMakeRepo _repo;
public MakeTests(ITestOutputHelper outputHelper) : base(outputHelper)
{
_repo = new MakeRepo(Context);
}
public override void Dispose()
{
_repo.Dispose(); base.Dispose();
}
}
The final repository update is in the OrderTests class, using OrderRepo:
[Collection("Integration Tests")]
public class OrderTests : BaseTest, IClassFixture
{
private readonly IOrderRepo _repo;
public OrderTests(ITestOutputHelper outputHelper) : base(outputHelper)
{
_repo = new OrderRepo(Context);
}
public override void Dispose()
{
_repo.Dispose(); base.Dispose();
}
}
Querying the Database
Recall that creating entity instances from a database data involves executing a LINQ statement or a SQL statement (using FromSqlRaw()/FromSqlInterpolated()) against the DbSet
FromSqlInterpolated() method using raw SQL strings and, optionally, additional LINQ statements. Entities loaded into the DbSet
If related entities are already loaded into the DbSet
Many 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.
LINQ Queries
The DbSet
LINQ Execution
Where 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.
Get All Records
To get all the records for a table, simply use the DbSet
[Fact]
public void ShouldGetAllOfTheCustomers()
{
var qs = Context.Customers.ToQueryString(); var customers = Context.Customers.ToList(); Assert.Equal(5, customers.Count);
}
The statement gets translated into the following SQL:
SELECT .[Id], .[TimeStamp], .[FirstName], .[FullName], .[LastName] FROM [Dbo].[Customers] AS
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:
[Fact]
public void ShouldGetAllViewModels()
{
var qs = Context.CustomerOrderViewModels.ToQueryString(); OutputHelper.WriteLine($"Query: {qs}"); List
Context.CustomerOrderViewModels.ToList(); Assert.NotEmpty(list);
Assert.Equal(5, list.Count);
}
The statement gets translated into the following SQL:
SELECT .[Color], .[DateBuilt], .[Display], .[FirstName],
.[IsDrivable], .[LastName], .[Make], .[PetName], .[Price] FROM [CustomerOrderView] AS
Filter Records
The following test in the CustomerTests class shows querying for customers where the last name starts with a W (case insensitive):
[Fact]
public void ShouldGetCustomersWithLastNameW()
{
IQueryable
.Where(x => x.PersonInformation.LastName.StartsWith("W")); var qs = query.ToQueryString(); OutputHelper.WriteLine($"Query: {qs}");
List
foreach (var customer in customers)
{
var pi = customer.PersonInformation;
Assert.StartsWith("W", pi.LastName, StringComparison.OrdinalIgnoreCase);
}
}
The LINQ query gets translated into the following SQL:
SELECT .[Id], .[TimeStamp], .[FirstName], .[FullName], .[LastName] FROM [dbo].[Customers] AS
WHERE .[LastName] IS NOT NULL AND (.[LastName] LIKE N’W%’)
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:
[Fact]
public void ShouldGetCustomersWithLastNameWAndFirstNameM()
{
IQueryable
.Where(x => x.PersonInformation.LastName.StartsWith("W"))
.Where(x => x.PersonInformation.FirstName.StartsWith("M")); var qs = query.ToQueryString(); OutputHelper.WriteLine($"Query: {qs}");
List
foreach (var customer in customers)
{
var pi = customer.PersonInformation;
Assert.StartsWith("W", pi.LastName, StringComparison.OrdinalIgnoreCase); Assert.StartsWith("M", pi.FirstName, StringComparison.OrdinalIgnoreCase);
}
}
This test in the CustomerTests class repeats the same filter using a single Where() method instead of two chained methods:
[Fact]
public void ShouldGetCustomersWithLastNameWAndFirstNameM()
{
IQueryable
.Where(x => x.PersonInformation.LastName.StartsWith("W") &&
x.PersonInformation.FirstName.StartsWith("M"));
var qs = query.ToQueryString(); OutputHelper.WriteLine($"Query: {qs}"); List
foreach (var customer in customers)
{
var pi = customer.PersonInformation;
Assert.StartsWith("W", pi.LastName, StringComparison.OrdinalIgnoreCase); Assert.StartsWith("M", pi.FirstName, StringComparison.OrdinalIgnoreCase);
}
}
Both queries get translated into the following SQL:
SELECT .[Id], .[TimeStamp], .[FirstName], .[FullName], .[LastName] FROM [dbo].[Customers] AS
WHERE (.[LastName] IS NOT NULL AND (.[LastName] LIKE N’W%’)) AND (.[FirstName] IS NOT NULL AND (.[FirstName] LIKE N’M%’))
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):
[Fact]
public void ShouldGetCustomersWithLastNameWOrH()
{
IQueryable
.Where(x => x.PersonInformation.LastName.StartsWith("W") ||
x.PersonInformation.LastName.StartsWith("H"));
var qs = query.ToQueryString(); OutputHelper.WriteLine($"Query: {qs}"); List
foreach (var customer in customers)
{
var pi = customer.PersonInformation; Assert.True(
pi.LastName.StartsWith("W",StringComparison.OrdinalIgnoreCase) || pi.LastName.StartsWith("H",StringComparison.OrdinalIgnoreCase));
}
}
This gets translated into the following SQL:
SELECT .[Id], .[TimeStamp], .[FirstName], .[FullName], .[LastName] FROM [dbo].[Customers] AS
WHERE (.[LastName] IS NOT NULL AND (.[LastName] LIKE N’W%’)) OR (.[LastName] IS NOT NULL AND (.[LastName] LIKE N’H%’))
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.
[Fact]
public void ShouldGetCustomersWithLastNameWOrH()
{
IQueryable
.Where(x => EF.Functions.Like(x.PersonInformation.LastName, "W%") ||
EF.Functions.Like(x.PersonInformation.LastName, "H%"));
var qs = query.ToQueryString(); OutputHelper.WriteLine($"Query: {qs}"); List
}
This gets translated into the following SQL (notice it does not check for null):
SELECT .[Id], .[TimeStamp], .[FirstName], .[FullName], .[LastName] FROM [Dbo].[Customers] AS
WHERE (.[LastName] LIKE N’W%’) OR (.[LastName] LIKE N’H%’)
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:
[Theory] [InlineData(1, 2)]
[InlineData(2, 1)]
[InlineData(3, 1)]
[InlineData(4, 2)]
[InlineData(5, 3)]
[InlineData(6, 1)]
public void ShouldGetTheCarsByMake(int makeId, int expectedCount)
{
IQueryable
Context.Cars.IgnoreQueryFilters().Where(x => x.MakeId == makeId);
var qs = query.ToQueryString(); OutputHelper.WriteLine($"Query: {qs}"); var cars = query.ToList(); Assert.Equal(expectedCount, cars.Count);
}
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):
DECLARE @ makeId_0 int = 1;
SELECT [i].[Id], [i].[Color], [i].[DateBuilt], [i].[Display], [i].[IsDrivable],
[i].[MakeId], [i].[PetName], [i].[Price], [i].[TimeStamp] FROM [dbo].[Inventory] AS [i]
WHERE [i].[MakeId] = @ makeId_0
The next test uses the Car repository to get the number of records for each Make. Since the GetCarsBy()
method leaves the query filters in place, there is one less record when the MakeId is one.
[Theory] [InlineData(1, 1)]
[InlineData(2, 1)]
[InlineData(3, 1)]
[InlineData(4, 2)]
[InlineData(5, 3)]
[InlineData(6, 1)]
public void ShouldGetTheCarsByMakeUsingTheRepo(int makeId, int expectedCount)
{
var qs = _carRepo.GetAllBy(makeId).AsQueryable().ToQueryString(); OutputHelper.WriteLine($"Query: {qs}");
var cars = _carRepo.GetAllBy(makeId).ToList(); Assert.Equal(expectedCount, cars.Count);
}
When examining the generated query, you can see the query filter that excluded records that aren’t drivable:
DECLARE @ makeId_0 int = 1;
SELECT [i].[Id], [i].[Color], [i].[DateBuilt], [i].[Display], [i].[IsDrivable],
[i].[MakeId], [i].[PetName], [i].[Price], [i].[TimeStamp], [m].[Id], [m].[Name], [m].[TimeStamp]
FROM [dbo].[Inventory] AS [i]
INNER JOIN [dbo].[Makes] AS [m] ON [i].[MakeId] = [m].[Id]
WHERE ([i].[IsDrivable] = CAST(1 AS bit)) AND ([i].[MakeId] = @ makeId_0) ORDER BY [i].[PetName]
Sort Records
Recall 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:
[Fact]
public void ShouldSortByLastNameThenFirstName()
{
//Sort by Last name then first name var query = Context.Customers
.OrderBy(x => x.PersonInformation.LastName)
.ThenByDescending(x => x.PersonInformation.FirstName); var qs = query.ToQueryString(); OutputHelper.WriteLine($"Query: {qs}");
var customers = query.ToList();
for (int x = 0; x < customers.Count – 1; x++)
{
Compare(customers[x].PersonInformation, customers[x + 1].PersonInformation);
}
static void Compare(Person p1, Person p2)
{
var compareValue = string.Compare(p1.LastName, p2.LastName, StringComparison.CurrentCultureIgnoreCase);
Assert.True(compareValue <=0); if (compareValue == 0)
{
//Descending first name sort Assert.True(string.Compare(p1.FirstName,p2.FirstName,
StringComparison.CurrentCultureIgnoreCase) >= 0);
}
}
}
The preceding LINQ query gets translated into the following:
SELECT .[Id], .[TimeStamp], .[FirstName], .[FullName], .[LastName] FROM [dbo].[Customers] AS
ORDER BY .[LastName], .[FirstName] DESC
Reverse Sort Records
The Reverse() method reverses the entire sort order, as demonstrated in the next test:
[Fact]
public void ShouldSortByFirstNameThenLastNameUsingReverse()
{
//Sort by Last name then first name descending then reverse the sort var query = Context.Customers
.OrderBy(x => x.PersonInformation.LastName)
.ThenByDescending(x => x.PersonInformation.FirstName)
.Reverse();
var qs = query.ToQueryString(); var customers = query.ToList();
//if only one customer, nothing to test if (customers.Count <= 1) { return; }
for (int x = 0; x < customers.Count – 1; x++)
{
var pi1 = customers[x].PersonInformation;
var pi2 = customers[x + 1].PersonInformation;
var compareLastName = string.Compare(pi1.LastName, pi2.LastName, StringComparison.CurrentCultureIgnoreCase); Assert.True(compareLastName >= 0);
if (compareLastName != 0) continue;
var compareFirstName = string.Compare(pi1.FirstName, pi2.FirstName, StringComparison.CurrentCultureIgnoreCase); Assert.True(compareFirstName <= 0);
}
}
The preceding LINQ query gets translated into the following, inverting the original
OrderBy()/ThenByDescending() query:
SELECT .[Id], .[TimeStamp], .[FirstName], .[FullName], .[LastName] FROM [dbo].[Customers] AS
ORDER BY .[LastName] DESC, .[FirstName]
Single-Record Queries
Because of the immediate execution of the single-record LINQ statements, the ToQueryString() method isn’t available. The listed query translations are provided by using SQL Server Profiler. All of the single- record tests are in the CustomerTests.cs file.
Using First
When 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:
[Fact]
public void GetFirstMatchingRecordDatabaseOrder()
{
//Gets the first record, database order var customer = Context.Customers.First(); Assert.Equal(1, customer.Id);
}
The preceding LINQ query gets translated into the following:
SELECT TOP(1) .[Id], .[TimeStamp], .[FirstName], .[FullName], .[LastName] FROM [Dbo].[Customers] AS
The following test demonstrates getting the first record based on “last name, first name” order:
[Fact]
public void GetFirstMatchingRecordNameOrder()
{
//Gets the first record, lastname, first name order var customer = Context.Customers
.OrderBy(x => x.PersonInformation.LastName)
.ThenBy(x => x.PersonInformation.FirstName)
.First();
Assert.Equal(1, customer.Id);
}
The preceding LINQ query gets translated into the following:
SELECT TOP(1) .[Id], .[TimeStamp], .[FirstName], .[FullName], .[LastName] FROM [Dbo].[Customers] AS
ORDER BY .[LastName], .[FirstName]
The following test asserts that an exception is thrown if there isn’t a match when using First(): [Fact]
public void FirstShouldThrowExceptionIfNoneMatch()
{
//Filters based on Id. Throws due to no match
Assert.Throws
}
■Note 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’t get thrown, the assertion fails.
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:
[Fact]
public void FirstOrDefaultShouldReturnDefaultIfNoneMatch()
{
//Expression<Func
//Returns null when nothing is found
var customer = Context.Customers.FirstOrDefault(expression);
Assert.Null(customer);
}
The preceding LINQ query gets translated into the same as the previous:
SELECT TOP(1) .[Id], .[TimeStamp], .[FirstName], .[FullName], .[LastName] FROM [Dbo].[Customers] AS
WHERE .[Id] = 10
Using Last
When 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 “last name, first name” order:
[Fact]
public void GetLastMatchingRecordNameOrder()
{
//Gets the last record, lastname desc, first name desc order var customer = Context.Customers
.OrderBy(x => x.PersonInformation.LastName)
.ThenBy(x => x.PersonInformation.FirstName)
.Last();
Assert.Equal(4, customer.Id);
}
EF Core reverses the order by statements and then takes top(1) to get the result. Here is the executed query:
SELECT TOP(1) .[Id], .[TimeStamp], .[FirstName], .[FullName], .[LastName] FROM [Dbo].[Customers] AS
ORDER BY .[LastName] DESC, .[FirstName] DESC
This test confirms that EF Core throws an exception when Last() is used without an
OrderBy()/OrderByDescending():
[Fact]
public void LastShouldThrowIfNoSortSpecified()
{
Assert.Throws
}
Using Single
Conceptually, 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:
[Fact]
public void GetOneMatchingRecordWithSingle()
{
//Gets the first record, database order
var customer = Context.Customers.Single(x => x.Id == 1);
Assert.Equal(1, customer.Id);
}
The preceding LINQ query gets translated into the following:
SELECT TOP(2) .[Id], .[TimeStamp], .[FirstName], .[FullName], .[LastName] FROM [Dbo].[Customers] AS
WHERE .[Id] = 1
Single() throws an exception if no records are returned.[Fact] public void SingleShouldThrowExceptionIfNoneMatch()
{
//Filters based on Id. Throws due to no match
Assert.Throws
}
When using Single() or SingleOrDefault() and more than one record is returned, an exception is thrown.
[Fact]
public void SingleShouldThrowExceptionIfMoreThenOneMatch()
{
// Throws due to more than one match
Assert.Throws
}
[Fact]
public void SingleOrDefaultShouldThrowExceptionIfMoreThenOneMatch()
{
// Throws due to more than one match
Assert.Throws
}
When using SingleOrDefault(), instead of an exception, the result is a null record when no data is returned.
[Fact]
public void SingleOrDefaultShouldReturnDefaultIfNoneMatch()
{
//Expression<Func
//Returns null when nothing is found
var customer = Context.Customers.SingleOrDefault(expression);
Assert.Null(customer);
}
The preceding LINQ query gets translated into the following:
SELECT TOP(2) .[Id], .[TimeStamp], .[FirstName], .[FullName], .[LastName] FROM [Dbo].[Customers] AS
WHERE .[Id] = 10
Global Query Filters
Recall 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:
[Fact]
public void ShouldReturnDrivableCarsWithQueryFilterSet()
{
IQueryable
Assert.Equal(9, cars.Count);
}
Recall that we create 10 cars in the data initialization process, and one of them is set to be nondrivable.
When the query is executed, the global query filter is applied, and the following SQL is executed:
SELECT [i].[Id], [i].[Color], [i].[DateBuilt], [i].[Display], [i].[IsDrivable],
[i].[MakeId], [i].[PetName], [i].[Price], [i].[TimeStamp] FROM [dbo].[Inventory] AS [i]
WHERE [i].[IsDrivable] = CAST(1 AS bit)
Disable the Query Filters
To 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’ filters are required, they must be added to the LINQ statement’s Where() methods. Add the following test to the CarTests.cs class, which disables the query filter and returns all records:
[Fact]
public void ShouldGetAllOfTheCars()
{
IQueryable
var cars = query.ToList(); Assert.Equal(10, cars.Count);
}
As one would expect, the where clause eliminating nondrivable cars is no longer on the generated SQL.
SELECT [i].[Id], [i].[Color], [i].[DateBuilt], [i].[Display], [i].[IsDrivable], [i]. [MakeId], [i].[PetName], [i].[Price], [i].[TimeStamp]
FROM [dbo].[Inventory] AS [i]
Query Filters on Navigation Properties
In 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:
[Fact]
public void ShouldGetAllOrdersExceptFiltered()
{
var query = Context.Orders.AsQueryable(); var qs = query.ToQueryString(); OutputHelper.WriteLine($"Query: {qs}"); var orders = query.ToList(); Assert.NotEmpty(orders); Assert.Equal(4,orders.Count);
}
The generated SQL is listed here:
SELECT [o].[Id], [o].[CarId], [o].[CustomerId], [o].[TimeStamp] FROM [dbo].[Orders] AS [o]
INNER JOIN (
SELECT [i].[Id], [i].[IsDrivable] FROM [dbo].[Inventory] AS [i]
WHERE [i].[IsDrivable] = CAST(1 AS bit)
) AS [t] ON [o].[CarId] = [t].[Id]
WHERE [t].[IsDrivable] = CAST(1 AS bit)
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.
To return all records, add IgnoreQueryFilters() to your LINQ query.[Fact] public void ShouldGetAllOrders()
{
var query = Context.Orders.IgnoreQueryFilters(); var qs = query.ToQueryString(); OutputHelper.WriteLine($"Query: {qs}");
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:
SELECT [o].[Id], [o].[CarId], [o].[CustomerId], [o].[TimeStamp] FROM [dbo].[Orders] AS [o]
Load Related Data Eagerly
Entities 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.
Add the following test to the CarTests.cs class to show a single Include(): [Fact]
public void ShouldGetAllOfTheCarsWithMakes()
{
IIncludableQueryable<Car, Make> query = Context.Cars.Include(c => c.MakeNavigation); var qs = query.ToQueryString(); OutputHelper.WriteLine($"Query: {qs}");
var cars = query.ToList(); Assert.Equal(9, cars.Count);
}
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.
SELECT [i].[Id], [i].[Color], [i].[DateBuilt], [i].[Display], [i].[IsDrivable],
[i].[MakeId], [i].[PetName], [i].[Price], [i].[TimeStamp], [m].[Id], [m].[Name], [m].[TimeStamp]
FROM [dbo].[Inventory] AS [i]
INNER JOIN [dbo].[Makes] AS [m] ON [i].[MakeId] = [m].[Id] WHERE [i].[IsDrivable] = CAST(1 AS bit)
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’t have any orders.
[Fact]
public void ShouldGetCarsOnOrderWithRelatedProperties()
{
IIncludableQueryable<Car, Customer?> query = Context.Cars
.Where(c => c.Orders.Any())
.Include(c => c.MakeNavigation)
.Include(c => c.Orders).ThenInclude(o => o.CustomerNavigation);
var qs = query.ToQueryString(); OutputHelper.WriteLine($"Query: {qs}"); var cars = query.ToList(); Assert.Equal(4, cars.Count); cars.ForEach(c =>
{
Assert.NotNull(c.MakeNavigation); Assert.NotNull(c.Orders.ToList()[0].CustomerNavigation);
});
}
The generated query is rather lengthy. Here is the generated query:
SELECT [i].[Id], [i].[Color], [i].[DateBuilt], [i].[Display], [i].[IsDrivable],
[i].[MakeId], [i].[PetName], [i].[Price], [i].[TimeStamp], [m].[Id],
[m].[Name], [m].[TimeStamp], [t0].[Id], [t0].[CarId], [t0].[CustomerId],
[t0].[TimeStamp], [t0].[Id0], [t0].[TimeStamp0], [t0].[FirstName],
[t0].[FullName], [t0].[LastName], [t0].[Id1] FROM [dbo].[Inventory] AS [i]
INNER JOIN [dbo].[Makes] AS [m] ON [i].[MakeId] = [m].[Id] LEFT JOIN (
SELECT [o0].[Id], [o0].[CarId], [o0].[CustomerId], [o0].[TimeStamp],
.[Id] AS [Id0], .[TimeStamp] AS [TimeStamp0], .[FirstName],
.[FullName], .[LastName], [t1].[Id] AS [Id1] FROM [dbo].[Orders] AS [o0]
INNER JOIN (
SELECT [i1].[Id], [i1].[IsDrivable] FROM [dbo].[Inventory] AS [i1]
WHERE [i1].[IsDrivable] = CAST(1 AS bit)
) AS [t1] ON [o0].[CarId] = [t1].[Id]
INNER JOIN [dbo].[Customers] AS ON [o0].[CustomerId] = .[Id] WHERE [t1].[IsDrivable] = CAST(1 AS bit)
) AS [t0] ON [i].[Id] = [t0].[CarId]
WHERE ([i].[IsDrivable] = CAST(1 AS bit)) AND EXISTS ( SELECT 1
FROM [dbo].[Orders] AS [o] INNER JOIN (
SELECT [i0].[Id], [i0].[Color], [i0].[DateBuilt], [i0].[Display],
[i0].[IsDrivable], [i0].[MakeId], [i0].[PetName], [i0].[Price], [i0].[TimeStamp] FROM [dbo].[Inventory] AS [i0]
WHERE [i0].[IsDrivable] = CAST(1 AS bit)
) AS [t] ON [o].[CarId] = [t].[Id]
WHERE ([t].[IsDrivable] = CAST(1 AS bit)) AND ([i].[Id] = [o].[CarId]))
ORDER BY [i].[Id], [m].[Id], [t0].[Id], [t0].[Id1], [t0].[Id0]
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:
[Fact]
public void ShouldGetCarsOnOrderWithRelatedPropertiesIgnoreFilters()
{
IIncludableQueryable<Car, Customer> query = Context.Cars.IgnoreQueryFilters().Where(c => c.Orders.Any())
.Include(c => c.MakeNavigation)
.Include(c => c.Orders).ThenInclude(o => o.CustomerNavigati on) ;
var qs = query.ToQueryString(); OutputHelper.WriteLine($"Query: {qs}"); var cars = query.ToList(); Assert.Equal(5, cars.Count); cars.ForEach(c =>
{
Assert.NotNull(c.MakeNavigation); Assert.NotNull(c.Orders.ToList()[0].CustomerNavigation);
});
}
The generated query is rather lengthy. Here is the generated query:
SELECT [i].[Id], [i].[Color], [i].[DateBuilt], [i].[Display], [i].[IsDrivable], [i].
[MakeId], [i].[PetName], [i].[Price], [i].[TimeStamp], [m].[Id], [m].[Name], [m].
[TimeStamp], [t].[Id], [t].[CarId], [t].[CustomerId], [t].[TimeStamp], [t].[Id0], [t]. [TimeStamp0], [t].[FirstName], [t].[FullName], [t].[LastName]
FROM [dbo].[Inventory] AS [i]
INNER JOIN [dbo].[Makes] AS [m] ON [i].[MakeId] = [m].[Id] LEFT JOIN (
SELECT [o0].[Id], [o0].[CarId], [o0].[CustomerId], [o0].[TimeStamp], .[Id] AS
[Id0], .[TimeStamp] AS [TimeStamp0], .[FirstName], .[FullName], .[LastName] FROM [dbo].[Orders] AS [o0]
INNER JOIN [dbo].[Customers] AS ON [o0].[CustomerId] = .[Id]
) AS [t] ON [i].[Id] = [t].[CarId] WHERE EXISTS (
SELECT 1
FROM [dbo].[Orders] AS [o] WHERE [i].[Id] = [o].[CarId])
ORDER BY [i].[Id], [m].[Id], [t].[Id], [t].[Id0]
Splitting Queries on Related Data
The 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:
[Fact]
public void ShouldGetCarsOnOrderWithRelatedPropertiesAsSplitQuery()
{
IQueryable
.Include(c => c.MakeNavigation)
.Include(c => c.Orders).ThenInclude(o => o.CustomerNavigation)
.AsSplitQuery();
var qs = query.ToQueryString(); OutputHelper.WriteLine($"Query: {qs}"); var cars = query.ToList(); Assert.Equal(4, cars.Count); cars.ForEach(c =>
{
Assert.NotNull(c.MakeNavigation); Assert.NotNull(c.Orders.ToList()[0].CustomerNavigation);
});
}
The ToQueryString() method returns only the first query, so the following queries were captured using SQL Server Profiler:
SELECT [i].[Id], [i].[Color], [i].[DateBuilt], [i].[Display], [i].[IsDrivable], [i]. [MakeId],
[i].[PetName], [i].[Price], [i].[TimeStamp], [m].[Id], [m].[Name], [m].[TimeStamp] FROM [dbo].[Inventory] AS [i]
INNER JOIN [dbo].[Makes] AS [m] ON [i].[MakeId] = [m].[Id] WHERE ([i].[IsDrivable] = CAST(1 AS bit)) AND EXISTS (
SELECT 1
FROM [dbo].[Orders] AS [o] INNER JOIN (
SELECT [i0].[Id], [i0].[Color], [i0].[DateBuilt], [i0].[Display], [i0].[IsDrivable],
[i0].[MakeId], [i0].[PetName], [i0].[Price], [i0].[TimeStamp] FROM [dbo].[Inventory] AS [i0]
WHERE [i0].[IsDrivable] = CAST(1 AS bit)
) AS [t] ON [o].[CarId] = [t].[Id]
WHERE ([t].[IsDrivable] = CAST(1 AS bit)) AND ([i].[Id] = [o].[CarId])) ORDER BY [i].[Id], [m].[Id]
SELECT [t0].[Id], [t0].[CarId], [t0].[CustomerId], [t0].[TimeStamp], [t0].[Id0],
[t0].[TimeStamp0], [t0].[FirstName], [t0].[FullName], [t0].[LastName], [i].[Id], [m].[Id]
FROM [dbo].[Inventory] AS [i]
INNER JOIN [dbo].[Makes] AS [m] ON [i].[MakeId] = [m].[Id] INNER JOIN (
SELECT [o0].[Id], [o0].[CarId], [o0].[CustomerId], [o0].[TimeStamp],
.[Id] AS [Id0], .[TimeStamp] AS [TimeStamp0], .[FirstName], .[FullName], .[LastName]
FROM [dbo].[Orders] AS [o0] INNER JOIN (
SELECT [i1].[Id], [i1].[IsDrivable] FROM [dbo].[Inventory] AS [i1]
WHERE [i1].[IsDrivable] = CAST(1 AS bit)
) AS [t1] ON [o0].[CarId] = [t1].[Id]
INNER JOIN [dbo].[Customers] AS ON [o0].[CustomerId] = .[Id] WHERE [t1].[IsDrivable] = CAST(1 AS bit)
) AS [t0] ON [i].[Id] = [t0].[CarId]
WHERE ([i].[IsDrivable] = CAST(1 AS bit)) AND EXISTS ( SELECT 1
FROM [dbo].[Orders] AS [o] INNER JOIN (
SELECT [i0].[Id], [i0].[Color], [i0].[DateBuilt], [i0].[Display], [i0].[IsDrivable],
[i0].[MakeId], [i0].[PetName], [i0].[Price], [i0].[TimeStamp] FROM [dbo].[Inventory] AS [i0]
WHERE [i0].[IsDrivable] = CAST(1 AS bit)
) AS [t] ON [o].[CarId] = [t].[Id]
WHERE ([t].[IsDrivable] = CAST(1 AS bit)) AND ([i].[Id] = [o].[CarId])) ORDER BY [i].[Id], [m].[Id]
Once again, removing the query filters greatly simplifies the queries generated. Here is the updated test:
[Fact]
public void ShouldGetCarsOnOrderWithRelatedPropertiesAsSplitQueryIgnoreQueryFilters()
{
IQueryable
.Where(c => c.Orders.Any())
.Include(c => c.MakeNavigation)
.Include(c => c.Orders).ThenInclude(o => o.CustomerNavigation)
.AsSplitQuery(); var qs = query.ToQueryString();
OutputHelper.WriteLine($"Query: {qs}"); var cars = query.ToList(); Assert.Equal(5, cars.Count); cars.ForEach(c =>
{
Assert.NotNull(c.MakeNavigation); Assert.NotNull(c.Orders.ToList()[0].CustomerNavigation);
});
}
Here are the generated queries:
SELECT [i].[Id], [i].[Color], [i].[DateBuilt], [i].[Display], [i].[IsDrivable],
[i].[MakeId], [i].[PetName], [i].[Price], [i].[TimeStamp], [m].[Id], [m].[Name], [m]. [TimeStamp]
FROM [dbo].[Inventory] AS [i]
INNER JOIN [dbo].[Makes] AS [m] ON [i].[MakeId] = [m].[Id] WHERE EXISTS (
SELECT 1
FROM [dbo].[Orders] AS [o] WHERE [i].[Id] = [o].[CarId])
ORDER BY [i].[Id], [m].[Id]
SELECT [t].[Id], [t].[CarId], [t].[CustomerId], [t].[TimeStamp], [t].[Id0],
[t].[TimeStamp0], [t].[FirstName], [t].[FullName], [t].[LastName], [i].[Id], [m].[Id] FROM [dbo].[Inventory] AS [i]
INNER JOIN [dbo].[Makes] AS [m] ON [i].[MakeId] = [m].[Id]
INNER JOIN (
SELECT [o0].[Id], [o0].[CarId], [o0].[CustomerId], [o0].[TimeStamp],
.[Id] AS [Id0], .[TimeStamp] AS [TimeStamp0], .[FirstName], .[FullName], .[LastName]
FROM [dbo].[Orders] AS [o0]
INNER JOIN [dbo].[Customers] AS ON [o0].[CustomerId] = .[Id]
) AS [t] ON [i].[Id] = [t].[CarId] WHERE EXISTS (
SELECT 1
FROM [dbo].[Orders] AS [o] WHERE [i].[Id] = [o].[CarId])
ORDER BY [i].[Id], [m].[Id]
Filtering Related Data
EF 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:
[Fact]
public void ShouldGetAllMakesAndCarsThatAreYellow()
{
var query = Context.Makes.IgnoreQueryFilters()
.Include(x => x.Cars.Where(x => x.Color == "Yellow")); var qs = query.ToQueryString(); OutputHelper.WriteLine($"Query: {qs}");
var makes = query.ToList(); Assert.NotNull(makes); Assert.NotEmpty(makes);
Assert.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);
}
The generated SQL is as follows:
SELECT [m].[Id], [m].[Name], [m].[TimeStamp], [t].[Id], [t].[Color],
[t].[DateBuilt], [t].[Display], [t].[IsDrivable], [t].[MakeId],
[t].[PetName], [t].[Price], [t].[TimeStamp] FROM [dbo].[Makes] AS [m]
LEFT JOIN (
SELECT [i].[Id], [i].[Color], [i].[DateBuilt], [i].[Display], [i].[IsDrivable],
[i].[MakeId], [i].[PetName], [i].[Price], [i].[TimeStamp] FROM [dbo].[Inventory] AS [i]
WHERE [i].[Color] = N’Yellow’
) AS [t] ON [m].[Id] = [t].[MakeId]
ORDER BY [m].[Id], [t].[Id]
Changing the query to a split query yields this SQL (collection from SQL Server Profiler): SELECT [m].[Id], [m].[Name], [m].[TimeStamp]
FROM [dbo].[Makes] AS [m]
ORDER BY [m].[Id]
SELECT [t].[Id], [t].[Color], [t].[DateBuilt], [t].[Display], [t].[IsDrivable],
[t].[MakeId], [t].[PetName], [t].[Price], [t].[TimeStamp], [m].[Id] FROM [dbo].[Makes] AS [m]
INNER JOIN (
SELECT [i].[Id], [i].[Color], [i].[DateBuilt], [i].[Display], [i].[IsDrivable],
[i].[MakeId], [i].[PetName], [i].[Price], [i].[TimeStamp] FROM [dbo].[Inventory] AS [i]
WHERE [i].[Color] = N’Yellow’
) AS [t] ON [m].[Id] = [t].[MakeId] ORDER BY [m].[Id]
Load Related Data Explicitly
If 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
in a one-to-one relationship), use the Reference() method. Calling Query() on the Collection() or Reference() method returns an IQueryable
The following test (back in the CarTests.cs class) shows how to load a reference navigation property on the Car entity:
[Fact]
public void ShouldGetReferenceRelatedInformationExplicitly()
{
var car = Context.Cars.First(x => x.Id == 1); Assert.Null(car.MakeNavigation);
var query = Context.Entry(car).Reference(c => c.MakeNavigation).Query(); var qs = query.ToQueryString();
OutputHelper.WriteLine($"Query: {qs}"); query.Load(); Assert.NotNull(car.MakeNavigation);
}
The generated SQL to get the Make information is as follows (the Car record was already queried):
DECLARE @ p_0 int = 1;
SELECT [m].[Id], [m].[Name], [m].[TimeStamp] FROM [dbo].[Makes] AS [m]
WHERE [m].[Id] = @ p_0
This test shows how to load a collection navigation property on the Car entity:
[Fact]
public void ShouldGetCollectionRelatedInformationExplicitly()
{
var car = Context.Cars.First(x => x.Id == 1); Assert.Empty(car.Orders);
var query = Context.Entry(car).Collection(c => c.Orders).Query(); var qs = query.ToQueryString();
OutputHelper.WriteLine($"Query: {qs}"); query.Load(); Assert.Single(car.Orders);
}
The generated SQL is as follows:
DECLARE @ p_0 int = 1;
SELECT [o].[Id], [o].[CarId], [o].[CustomerId], [o].[TimeStamp] FROM [dbo].[Orders] AS [o]
INNER JOIN (
SELECT [i].[Id], [i].[IsDrivable] FROM [dbo].[Inventory] AS [i]
WHERE [i].[IsDrivable] = CAST(1 AS bit)
) AS [t] ON [o].[CarId] = [t].[Id]
WHERE ([t].[IsDrivable] = CAST(1 AS bit)) AND ([o].[CarId] = @ p_0)
Load Related Data Explicitly with Query Filters
In 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):
[Theory] [InlineData(1,1)] [InlineData(2,1)] [InlineData(3,1)] [InlineData(4,2)] [InlineData(5,3)] [InlineData(6,1)]
public void ShouldGetAllCarsForAMakeExplicitlyWithQueryFilters(int makeId, int carCount)
{
var make = Context.Makes.First(x => x.Id == makeId);
IQueryable
OutputHelper.WriteLine($"Query: {qs}"); query.Load(); Assert.Equal(carCount,make.Cars.Count());
}
This test is similar to ShouldGetTheCarsByMake() from the “Filter Records” 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:
DECLARE @ p_0 int = 1;
SELECT [i].[Id], [i].[Color], [i].[DateBuilt], [i].[Display], [i].[IsDrivable],
[i].[MakeId], [i].[PetName], [i].[Price], [i].[TimeStamp] FROM [dbo].[Inventory] AS [i]
WHERE ([i].[IsDrivable] = CAST(1 AS bit)) AND ([i].[MakeId] = @ p_0)
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):
[Theory] [InlineData(1, 2)]
[InlineData(2, 1)]
[InlineData(3, 1)]
[InlineData(4, 2)]
[InlineData(5, 3)]
[InlineData(6, 1)]
public void ShouldGetAllCarsForAMakeExplicitly(int makeId, int carCount)
{
var make = Context.Makes.First(x => x.Id == makeId); IQueryable
Context.Entry(make).Collection(c => c.Cars).Query().IgnoreQueryFilters(); var qs = query.IgnoreQueryFilters().ToQueryString(); OutputHelper.WriteLine($"Query: {qs}");
query.Load();
Assert.Equal(carCount, make.Cars.Count());
}
Temporal Queries
This section exercises EF Core’s 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:
[Fact]
public void ShouldGetAllHistoryRows()
{
var make = new Make { Name = "TestMake" };
_repo.Add(make); Thread.Sleep(2000); make.Name = "Updated Name";
_repo.Update(make);
Thread.Sleep(2000);
_repo.Delete(make);
var list = _repo.GetAllHistory().Where(x => x.Entity.Id == make.Id).ToList(); Assert.Equal(2, list.Count);
Assert.Equal("TestMake", list[0].Entity.Name); Assert.Equal("Updated Name", list[1].Entity.Name); Assert.Equal(list[0].ValidTo, list[1].ValidFrom);
}
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.
SQL Queries with LINQ
This section exercises EF Core’s ability to retrieve data using raw SQL queries using the DbSet
[Fact]
public void ShouldNotGetTheLemonsUsingFromSql()
{
var entity = Context.Model.FindEntityType($"{typeof(Car).FullName}"); var tableName = entity.GetTableName();
var schemaName = entity.GetSchema(); var query = Context.Cars
.FromSqlRaw($"Select *,ValidFrom,ValidTo from {schemaName}.{tableName}"); var qs = query.ToQueryString();
OutputHelper.WriteLine($"Query: {qs}"); var cars = query.ToList(); Assert.Equal(9, cars.Count);
}
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.
SELECT [a].[Id], [a].[Color], [a].[DateBuilt], [a].[Display], [a].[IsDrivable], [a].
[MakeId], [a].[PetName], [a].[Price], [a].[TimeStamp], [a].[ValidFrom], [a].[ValidTo] FROM (
Select *,ValidFrom,ValidTo from dbo.Inventory
) AS [a]
WHERE [a].[IsDrivable] = CAST(1 AS bit)
When the query filter is removed, the generated SQL becomes the same SQL as the string passed into the FromSqlRaw() method:
Select *,ValidFrom,ValidTo from dbo.Inventory
The following test demonstrates using FromSqlInterpolated() with additional LINQ statements (including the MakeNavigation):
[Fact]
public void ShouldGetOneCarUsingInterpolation()
{
var carId = 1;
var query = Context.Cars
.FromSqlInterpolated($"Select *,ValidFrom,ValidTo from dbo.Inventory where Id =
{carId}")
.Include(x => x.MakeNavigation);
var qs = query.ToQueryString(); OutputHelper.WriteLine($"Query: {qs}"); var car = query.First(); Assert.Equal("Black", car.Color);
Assert.Equal("VW", car.MakeNavigation.Name);
}
Here is the generated SQL:
DECLARE p0 int = 1;
SELECT [a].[Id], [a].[Color], [a].[DateBuilt], [a].[Display], [a].[IsDrivable], [a].[MakeId],
[a].[PetName], [a].[Price], [a].[TimeStamp], [a].[ValidFrom], [a].[ValidTo],
[m].[Id], [m].[Name], [m].[TimeStamp], [m].[ValidFrom], [m].[ValidTo] FROM (
Select *,ValidFrom,ValidTo from dbo.Inventory where Id = @p0
) AS [a]
INNER JOIN [dbo].[Makes] AS [m] ON [a].[MakeId] = [m].[Id] WHERE [a].[IsDrivable] = CAST(1 AS bit)
Aggregate Methods
The 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().
All the SQL statements shown in this section were collected using SQL Server Profiler.
This 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.
[Fact]
public void ShouldGetTheCountOfCars()
{
var count = Context.Cars.Count(); Assert.Equal(9, count);
}
The executed SQL is shown here:
SELECT COUNT(*)
FROM [dbo].[Inventory] AS [i]
WHERE [i].[IsDrivable] = CAST(1 AS bit)
By adding IgnoreQueryFilters(), the Count() method returns 10, and the where clause is removed from the SQL query.
[Fact]
public void ShouldGetTheCountOfCarsIgnoreQueryFilters()
{
var count = Context.Cars.IgnoreQueryFilters().Count(); Assert.Equal(10, count);
}
–Generated SQL
SELECT COUNT(*) FROM [dbo].[Inventory] AS [i]
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.
[Theory] [InlineData(1, 1)]
[InlineData(2, 1)]
[InlineData(3, 1)]
[InlineData(4, 2)]
[InlineData(5, 3)]
[InlineData(6, 1)]
public void ShouldGetTheCountOfCarsByMakeP1(int makeId, int expectedCount)
{
var count = Context.Cars.Count(x=>x.MakeId == makeId); Assert.Equal(expectedCount, count);
}
[Theory] [InlineData(1, 1)]
[InlineData(2, 1)]
[InlineData(3, 1)]
[InlineData(4, 2)]
[InlineData(5, 3)]
[InlineData(6, 1)]
public void ShouldGetTheCountOfCarsByMakeP2(int makeId, int expectedCount)
{
var count = Context.Cars.Where(x => x.MakeId == makeId).Count(); Assert.Equal(expectedCount, count);
}
Both tests create the same SQL calls to the server, as shown here (the MakeId changes with each test based on the InlineData):
exec sp_executesql N’SELECT COUNT(*) FROM [dbo].[Inventory] AS [i]
WHERE ([i].[IsDrivable] = CAST(1 AS bit)) AND ([i].[MakeId] = @ makeId_0)’
,N’@ makeId_0 int’,@ makeId_0=6
Any() and All()
The 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.
This first test (in CarTests.cs) checks if any car records have a specific MakeId:
[Theory] [InlineData(1, true)]
[InlineData(11, false)]
public void ShouldCheckForAnyCarsWithMake(int makeId, bool expectedResult)
{
var result = Context.Cars.Any(x => x.MakeId == makeId); Assert.Equal(expectedResult, result);
}
The executed SQL for the first theory test is shown here:
exec sp_executesql N’SELECT CASE WHEN EXISTS (
SELECT 1
FROM [dbo].[Inventory] AS [i]
WHERE ([i].[IsDrivable] = CAST(1 AS bit)) AND ([i].[MakeId] = @ makeId_0)) THEN CAST(1 AS bit)
ELSE CAST(0 AS bit)
END’,N’@ makeId_0 int’,@ makeId_0=1
This second test checks if all car records have a specific MakeId: [Theory]
[InlineData(1, false)] [InlineData(11, false)]
public void ShouldCheckForAllCarsWithMake(int makeId, bool expectedResult)
{
var result = Context.Cars.All(x => x.MakeId == makeId); Assert.Equal(expectedResult, result);
}
The executed SQL for the first theory test is shown here:
exec sp_executesql N’SELECT CASE WHEN NOT EXISTS (
SELECT 1
FROM [dbo].[Inventory] AS [i]
WHERE ([i].[IsDrivable] = CAST(1 AS bit)) AND ([i].[MakeId] <> @ makeId_0)) THEN CAST(1 AS bit)
ELSE CAST(0 AS bit)
END’,N’@ makeId_0 int’,@ makeId_0=1
Getting Data from Stored Procedures
The 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:
[Theory]
[InlineData(1, "Zippy")]
[InlineData(2, "Rusty")]
[InlineData(3, "Mel")] [InlineData(4, "Clunker")] [InlineData(5, "Bimmer")] [InlineData(6, "Hank")]
[InlineData(7, "Pinky")]
[InlineData(8, "Pete")] [InlineData(9, "Brownie")]
public void ShouldGetValueFromStoredProc(int id, string expectedName)
{
Assert.Equal(expectedName, _carRepo.GetPetName(id));
}
Creating Records
Records are added to the database by creating them in code, adding them to their DbSet
As a reminder, SaveChanges() executes in an implicit transaction, unless an explicit transaction is used.
If 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.
All the SQL statements shown in this section were collected using SQL Server Profiler.
■Note records can also be added using the derived DbContext as well. these examples will all use the DbSet
Add a Single Record
The following test demonstrates how to add a single record to the Inventory table:
[Fact]
public void ShouldAddACar()
{
ExecuteInATransaction(RunTheTest);
void RunTheTest()
{
var car = new Car
{
Color = "Yellow", MakeId = 1, PetName = "Herbie"
};
var carCount = Context.Cars.Count(); Context.Cars.Add(car); Context.SaveChanges();
var newCarCount = Context.Cars.Count(); Assert.Equal(carCount+1,newCarCount);
}
}
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.
exec sp_executesql N’SET NOCOUNT ON;
INSERT INTO [dbo].[Inventory] ([Color], [MakeId], [PetName], [Price]) VALUES (@p0, @p1, @p2, @p3);
SELECT [Id], [DateBuilt], [Display], [IsDrivable], [TimeStamp] FROM [dbo].[Inventory]
WHERE @@ROWCOUNT = 1 AND [Id] = scope_identity(); ‘,
N’@p0 nvarchar(50),@p1 int,@p2 nvarchar(50),@p3 nvarchar(50)’,@p0=N’Yellow’,@p1=1, @p2=N’Herbie’,@p3=NULL
Add a Single Record Using Attach
The 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.
[Fact]
public void ShouldAddACarWithAttach()
{
ExecuteInATransaction(RunTheTest);
void RunTheTest()
{
var car = new Car
{
Color = "Yellow", MakeId = 1, PetName = "Herbie"
};
var carCount = Context.Cars.Count(); Context.Cars.Attach(car);
Assert.Equal(EntityState.Added, Context.Entry(car).State); Context.SaveChanges();
var newCarCount = Context.Cars.Count(); Assert.Equal(carCount + 1, newCarCount);
}
}
Add Multiple Records at Once
To insert multiple records in a single transaction, use the AddRange() method of DbSet
[Fact]
public void ShouldAddMultipleCars()
{
ExecuteInATransaction(RunTheTest);
void RunTheTest()
{
//Have to add 4 to activate batching var cars = new List
{
new() { 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" },
};
var carCount = Context.Cars.Count(); Context.Cars.AddRange(cars); Context.SaveChanges();
var newCarCount = Context.Cars.Count(); Assert.Equal(carCount + 4, newCarCount);
}
}
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:
exec sp_executesql N’SET NOCOUNT ON;
DECLARE @inserted0 TABLE ([Id] int, [_Position] [int]); MERGE [dbo].[Inventory] USING (
VALUES (@p0, @p1, @p2, @p3, 0), (@p4, @p5, @p6, @p7, 1),
(@p8, @p9, @p10, @p11, 2), (@p12, @p13, @p14, @p15, 3))
AS i ([Color], [MakeId], [PetName], [Price], _Position) ON 1=0 WHEN NOT MATCHED THEN
INSERT ([Color], [MakeId], [PetName], [Price])
VALUES (i.[Color], i.[MakeId], i.[PetName], i.[Price]) OUTPUT INSERTED.[Id], i._Position
INTO @inserted0;
SELECT [t].[Id], [t].[DateBuilt], [t].[Display], [t].[IsDrivable], [t].[TimeStamp] FROM [dbo].[Inventory] t
INNER JOIN @inserted0 i ON ([t].[Id] = [i].[Id]) ORDER BY [i].[_Position];
‘,
N’@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),
@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,
@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
Adding an Object Graph
The following test demonstrates adding an object graph (related Make, Car, and Radio records):
[Fact]
public void ShouldAddAnObjectGraph()
{
ExecuteInATransaction(RunTheTest);
void RunTheTest()
{
var make = new Make {Name = "Honda"}; var car = new Car
{
Color = "Yellow", MakeId = 1,
PetName = "Herbie", RadioNavigation = new Radio
{
HasSubWoofers = true, HasTweeters = true, RadioId = "Bose 1234"
}
};
//Cast the Cars property to List
Context.Makes.Add(make);
var carCount = Context.Cars.Count(); var makeCount = Context.Makes.Count(); Context.SaveChanges();
var newCarCount = Context.Cars. Count(); var newMakeCount = Context.Makes. Count(); Assert.Equal(carCount+1,newCarCount); Assert.Equal(makeCount+1,newMakeCount);
}
}
The executed SQL statements (one for each table) are shown here:
exec sp_executesql N’SET NOCOUNT ON; INSERT INTO [dbo].[Makes] ([Name]) VALUES (@p0);
SELECT [Id], [TimeStamp] FROM [dbo].[Makes]
WHERE @@ROWCOUNT = 1 AND [Id] = scope_identity(); ‘,
N’@p0 nvarchar(50)’,@p0=N’Honda’
exec sp_executesql N’SET NOCOUNT ON;
INSERT INTO [dbo].[Inventory] ([Color], [MakeId], [PetName], [Price]) VALUES (@p1, @p2, @p3, @p4);
SELECT [Id], [DateBuilt], [Display], [IsDrivable], [TimeStamp] FROM [dbo].[Inventory]
WHERE @@ROWCOUNT = 1 AND [Id] = scope_identity();
‘,N’@p1 nvarchar(50),@p2 int,@p3 nvarchar(50),@p4 nvarchar(50)’,@p1=N’Yellow’,@p2=7, @p3=N’Herbie’,@p4=NULL
exec sp_executesql N’SET NOCOUNT ON;
INSERT INTO [dbo].[Radios] ([InventoryId], [HasSubWoofers], [HasTweeters], [RadioId])
VALUES (@p5, @p6, @p7, @p8); SELECT [Id], [TimeStamp] FROM [dbo].[Radios]
WHERE @@ROWCOUNT = 1 AND [Id] = scope_identity(); ‘,
N’@p5 int,@p6 bit,@p7 bit,@p8 nvarchar(50)’,@p5=11, @p6=1,@p7=1,@p8=N’Bose 1234′
Updating Records
Records are updated by loading them into DbSet
Update Tracked Entities
The following test updates a single record, but the process is the same if multiple tracked entities are updated and saved.
[Fact]
public void ShouldUpdateACar()
{
ExecuteInASharedTransaction(RunTheTest);
void RunTheTest(IDbContextTransaction trans)
{
var car = Context.Cars.First(c => c.Id == 1); Assert.Equal("Black",car.Color);
car.Color = "White";
//Calling update is not needed because the entity is tracked
//Context.Cars.Update(car); Context.SaveChanges(); Assert.Equal("White", car.Color);
var context2 = TestHelpers.GetSecondContext(Context, trans); var car2 = context2.Cars.First(c => c.Id == 1); Assert.Equal("White", car2.Color);
}
}
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.
The executed SQL statement is listed here:
exec sp_executesql N’SET NOCOUNT ON; UPDATE [dbo].[Inventory] SET [Color] = @p0 WHERE [Id] = @p1 AND [TimeStamp] = @p2; SELECT [TimeStamp]
FROM [dbo].[Inventory]
WHERE @@ROWCOUNT = 1 AND [Id] = @p1;
‘,N’@p1 int,@p0 nvarchar(50),@p2 varbinary(8)’,@p1=1,@p0=N’White’,@p2=0x000000000000862D
Update Nontracked Entities
Untracked 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.
The 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
DbSet
[Fact]
public void ShouldUpdateACarUsingState()
{
ExecuteInASharedTransaction(RunTheTest);
void RunTheTest(IDbContextTransaction trans)
{
var car = Context.Cars.AsNoTracking().First(c => c.Id == 1); Assert.Equal("Black", car.Color);
var updatedCar = new Car
{
Color = "White", //Original is Black Id = car.Id,
MakeId = car.MakeId, PetName = car.PetName, TimeStamp = car.TimeStamp, IsDrivable = car.IsDrivable
};
var context2 = TestHelpers.GetSecondContext(Context, trans);
//Either call Update or modify the state context2.Entry(updatedCar).State = EntityState.Modified;
//context2.Cars.Update(updatedCar); context2.SaveChanges();
var context3 = TestHelpers.GetSecondContext(Context, trans);
var car2 = context3.Cars.First(c => c.Id == 1); Assert.Equal("White", car2.Color);
}
}
Concurrency Checking When Updating Records
The 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:
UPDATE [dbo].[Inventory] SET [PetName] = @p0 WHERE [Id] = @p1 AND [TimeStamp] = @p2;
The following test shows an example of creating a concurrency exception, catching it, and using the
Entries to get the original values, current values, and values that are currently stored in the database. Getting the current values requires another database call.
[Fact]
public void ShouldThrowConcurrencyException()
{
ExecuteInATransaction(RunTheTest);
void RunTheTest()
{
var car = Context.Cars.First();
//Update the database outside of the context Context.Database.ExecuteSqlInterpolated(
$"Update dbo.Inventory set Color=’Pink’ where Id = {car.Id}"); car.Color = "Yellow";
var ex = Assert.Throws
var entry = ((DbUpdateConcurrencyException) ex.InnerException)?.Entries[0]; PropertyValues originalProps = entry.OriginalValues;
PropertyValues currentProps = entry.CurrentValues;
//This needs another database call
PropertyValues databaseProps = entry.GetDatabaseValues();
}
}
Deleting Records
A single entity is marked for deletion by calling Remove() on DbSet
cause 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.
Delete Tracked Records
The 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.
[Fact]
public void ShouldRemoveACar()
{
ExecuteInATransaction(RunTheTest);
void RunTheTest()
{
var carCount = Context.Cars. Count();
var car = Context.Cars.First(c => c.Id == 9); Context.Cars.Remove(car); Context.SaveChanges();
var newCarCount = Context.Cars.Count(); Assert.Equal(carCount – 1, newCarCount); Assert.Equal(
EntityState.Detached, Context.Entry(car).State);
}
}
After SaveChanges() is called, the entity instance still exists, but is no longer in the ChangeTracker.
When checking the EntityState, the state will be Detached.
The executed SQL call for the delete is listed here:
exec sp_executesql N’SET NOCOUNT ON; DELETE FROM [dbo].[Inventory]
WHERE [Id] = @p0 AND [TimeStamp] = @p1; SELECT @@ROWCOUNT;’
,N’@p0 int,@p1 varbinary(8)’,@p0=2,@p1=0x0000000000008680
Delete Nontracked Entities
Untracked 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().
The 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
[Fact]
public void ShouldRemoveACarUsingState()
{
ExecuteInASharedTransaction(RunTheTest);
void RunTheTest(IDbContextTransaction trans)
{
var carCount = Context.Cars.Count();
var car = Context.Cars.AsNoTracking().First(c => c.Id == 1); var context2 = TestHelpers.GetSecondContext(Context, trans);
//Either call Remove or modify the state context2.Entry(car).State = EntityState.Deleted;
//context2.Cars.Remove(car); context2.SaveChanges();
var newCarCount = Context.Cars.Count(); Assert.Equal(carCount – 1, newCarCount); Assert.Equal(
EntityState.Detached, Context.Entry(car).State);
}
}
Catch Cascade Delete Failures
EF 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:
[Fact]
public void ShouldFailToRemoveACar()
{
ExecuteInATransaction(RunTheTest);
void RunTheTest()
{
var car = Context.Cars.First(c => c.Id == 1); Context.Cars.Remove(car); Assert.Throws
()=>Context.SaveChanges());
}
}
Concurrency Checking When Deleting Records
Delete also uses concurrency checking if the entity has a TimeStamp property. See the section “Concurrency Checking” in the “Updating Records” section for more information.
Summary
This 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.