CHAPTER 22
Exploring Entity Framework Core
The previous chapter covered the components of EF Core. This chapter will dive into the capabilities of EF Core, starting with create, read, update, and delete (CRUD) operations. After covering the CRUD operations, specific EF Core features are explored, including global query filters, mixing SQL queries with LINQ, projections, and more.
■ Note This chapter’s code picks up where the previous chapter left off, so you can continue to use the code from Chapter 21 if you have been coding along with the text. If you are starting with this chapter and want to code along with the text, start with the previous chapter’s code from the repo.
To get started, clear out the Program.cs file and add the following Console.WriteLine(): Console.WriteLine(" More Fun with Entity Framework Core ");
Creating Records
Records are added to the database by creating them in code, adding them to their DbSet
As a reminder from Chapter 21, 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. The handling of server-generated values is covered in depth later in this chapter.
All of the SQL statements shown in this section were collected using SQL Server Profiler.
■ Note Records can also be added using the derived DbContext. These examples will all use the DbSet
© 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_22
921
Entity State
When an entity is created through code but not yet added to a DbSet
The following code shows a newly created Make record and its EntityState:
static void AddRecords()
{
//The factory is not meant to be used like this, but it’s demo code 🙂 var context = new ApplicationDbContextFactory().CreateDbContext(null); var newMake = new Make
{
Name = "BMW"
};
Console.WriteLine($"State of the {newMake.Name} is {context.Entry(newMake).State}");
}
After calling the AddRecords() method from the top-level statements, you should see the
following output:
More Fun with Entity Framework Core State of the BMW is Detached
Add a Single Record Using Add
To add a new Make record to the database, create a new entity instance and call the Add() method of the appropriate DbSet
static void AddRecords()
{
…
context.Makes.Add(newMake);
Console.WriteLine($"State of the {newMake.Name} is {context.Entry(newMake).State}"); context.SaveChanges();
Console.WriteLine($"State of the {newMake.Name} is {context.Entry(newMake).State}");
}
Running the program again, you will see the following output in the console window. After the entity
was added into the Change Tracker (using the Add() method), the state was changed to Added. The message about saving changes comes from the SavingChanges event handler, and the message “Saved 1 entities” came from the SavedChanges event handler. After SaveChanges() is called on the context, the entity’s state is changed to Unchanged:
More Fun with Entity Framework Core State of the BMW is Detached
State of the BMW is Added
Saving changes for server=.,5433;Database=AutoLotSamples;User Id=sa;Password=P@ssw0rd; Saved 1 entities
State of the BMW is Unchanged
The executed SQL statement for the insert is shown here. The format of the query is due to the batching process used by EF Core to improve the performance of database operations. Batching is covered later in this chapter. All of the values passed into the SQL statement are parameterized to help reduce the threat of scripting attacks. Also notice that the recently added entity is queried for the database-generated properties. EF Core’s handling of server-managed values is also covered later in this chapter.
exec sp_executesql N’SET NOCOUNT ON; INSERT INTO [Makes] ([Name]) VALUES (@p0);
SELECT [Id], [TimeStamp] FROM [Makes]
WHERE @@ROWCOUNT = 1 AND [Id] = scope_identity(); ‘,N’@p0 nvarchar(50)’,@p0=N’BMW’
Add a Single Record Using Attach
When an entity’s primary key is mapped to an identity column in SQL Server, EF Core will treat that entity instance as Added when added to the ChangeTracker if the primary key property’s value is zero. The following code adds the new Car record using the Attach() method instead of the Add() method. Note that SaveChanges() must still be called for the data to be persisted.
static void AddRecords()
{
…
var newCar = new Car()
{
Color = "Blue",
DateBuilt = new DateTime(2016, 12, 01), IsDrivable = true,
PetName = "Bluesmobile", MakeId = newMake.Id
};
Console.WriteLine($"State of the {newCar.PetName} is {context.Entry(newCar).State}"); context.Cars.Attach(newCar);
Console.WriteLine($"State of the {newCar.PetName} is {context.Entry(newCar).State}"); context.SaveChanges();
Console.WriteLine($"State of the {newCar.PetName} is {context.Entry(newCar).State}");
}
Running the program again, you will see the following same progression of states as you saw with the
Make entity:
More Fun with Entity Framework Core
…
State of the Bluesmobile is Detached State of the Bluesmobile is Added
Saving changes for server=.,5433;Database=AutoLotSamples;User Id=sa;Password=P@ssw0rd; Saved 1 entities
State of the Bluesmobile is Unchanged
The executed SQL statement for the insert is shown here:
exec sp_executesql N’SET NOCOUNT ON;
INSERT INTO [dbo].[Inventory] ([Color], [DateBuilt], [IsDrivable], [MakeId], [PetName]) VALUES (@p0, @p1, @p2, @p3, @p4);
SELECT [Id], [Display], [TimeStamp] FROM [dbo].[Inventory]
WHERE @@ROWCOUNT = 1 AND [Id] = scope_identity();
‘,N’@p0 nvarchar(50),@p1 datetime2(7),@p2 bit,@p3 int,@p4 nvarchar(50)’,@p0=N’Blue’, @p1=’2016-12-01 00:00:00′,@p2=1,@p3=1,@p4=N’Bluesmobile’
Add Multiple Records at Once
To insert multiple records in a single transaction, use the AddRange() method of a DbSet
static void AddRecords()
{
…
var cars = new List
{
new() { Color = "Yellow", MakeId = newMake.Id, PetName = "Herbie" }, new() { Color = "White", MakeId = newMake.Id, PetName = "Mach 5" }, new() { Color = "Pink", MakeId = newMake.Id, PetName = "Avon" }, new() { Color = "Blue", MakeId = newMake.Id, PetName = "Blueberry" },
};
context.Cars.AddRange(cars); context.SaveChanges();
}
Even though four records were added, EF Core generated only one SQL statement for the inserts. The SQL statement for the inserts 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, 0),
(@p3, @p4, @p5, 1),
(@p6, @p7, @p8, 2),
(@p9, @p10, @p11, 3)) AS i ([Color], [MakeId], [PetName], _Position) ON 1=0 WHEN NOT MATCHED THEN
INSERT ([Color], [MakeId], [PetName]) VALUES (i.[Color], i.[MakeId], i.[PetName]) 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 int,@p5 nvarchar(50), @p6 nvarchar(50),@p7 int,@p8 nvarchar(50),@p9 nvarchar(50),@p10 int,@p11 nvarchar(50)’, @p0=N’Yellow’,@p1=1,@p2=N’Herbie’,@p3=N’White’,@p4=1,@p5=N’Mach 5′,@p6=N’Pink’,@p7=1, @p8=N’Avon’,@p9=N’Blue’,@p10=1,@p11=N’Blueberry’
Identity Column Considerations When Adding Records
When an entity has a numeric property that is defined as the primary key, that property (by default) gets mapped to an Identity column in SQL Server. EF Core considers any entity with the default value (zero) for the key property to be new, and any entity with a nondefault value to already exist in the database. If you create a new entity and set the primary key property to a nonzero number and attempt to add it to the database, EF Core will fail to add the record because identity insert is not enabled.
For SQL Server, identity insert is enabled by issuing the SET IDENTITY_INSERT command within an explicit transaction. This command requires the database schema and table name, not the C# namespace and entity name. To get the database information for an entity, use the FindEntityType() method of
the Model property for the derived DbContext. Once you have the EntityType, use the GetSchema() and
GetTableName() methods:
static void AddRecords()
{
…
IEntityType metadata = context.Model.FindEntityType(typeof(Car).FullName); var schema = metadata.GetSchema();
var tableName = metadata.GetTableName();
}
Recall from the previous chapter that when using an ExecutionStrategy, explicit transactions must execute within the scope of that strategy. For reference, here is the following sample from the previous chapter:
var strategy = context.Database.CreateExecutionStrategy(); strategy.Execute(() =>
{
using var trans = context.Database.BeginTransaction(); try
{
//actionToExecute(); trans.Commit();
Console.WriteLine($"Insert succeeded");
}
catch (Exception ex)
{
}
});
trans.Rollback();
Console.WriteLine($"Insert failed: {ex.Message}");
When adding a record using identity insert, the actionToExecute() placeholder in the previous code block is replaced by code to turn identity insert on, add the record(s), and then save the changes. If that all succeeds, the transaction is committed. If any part of it fails, the transaction is rolled back. In the finally block, identity insert is turned back off.
EF Core provides two methods to execute commands directly against the database. The ExecuteSqlRaw() method executes the string exactly as it is written, while ExecuteSqlInterpolated() uses C# string interpolation to create a parameterized query. For this example, use the ExecuteSqlRaw() version. Here is the updated code, with the new lines in bold:
var strategy = context.Database.CreateExecutionStrategy(); strategy.Execute(() =>
{
using var trans = context.Database.BeginTransaction(); try
{
context.Database.ExecuteSqlRaw(
$"SET IDENTITY_INSERT {schema}.{tableName} ON"); var anotherNewCar = new Car()
{
Id = 27,
Color = "Blue",
DateBuilt = new DateTime(2016, 12, 01), IsDrivable = true,
PetName = "Bluesmobile", MakeId = newMake.Id
};
context.Cars.Add(anotherNewCar); context.SaveChanges(); trans.Commit();
Console.WriteLine($"Insert succeeded");
}
catch (Exception ex)
{
trans.Rollback();
Console.WriteLine($"Insert failed: {ex.Message}");
}
finally
{
}
});
context.Database.ExecuteSqlRaw(
$"SET IDENTITY_INSERT {schema}.{tableName} OFF");
■Note when using known values, as in this example, the ExecuteSqlRaw() method is safe to use. however, if you are collecting inputs from users, you should use the ExecuteSqlInterpolated() version for added protection.
The previous code executed the following commands against the database:
SET IDENTITY_INSERT dbo.Inventory ON SAVE TRANSACTION [ EFSavePoint];
exec sp_executesql N’SET NOCOUNT ON;
INSERT INTO [dbo].[Inventory] ([Id], [Color], [DateBuilt], [IsDrivable], [MakeId], [PetName])
VALUES (@p0, @p1, @p2, @p3, @p4, @p5); SELECT [Display], [TimeStamp]
FROM [dbo].[Inventory]
WHERE @@ROWCOUNT = 1 AND [Id] = @p0;
‘,N’@p0 int,@p1 nvarchar(50),@p2 datetime2(7),@p3 bit,@p4 int,@p5 nvarchar(50)’,@p0=27, @p1=N’Blue’,@p2=’2016-12-01 00:00:00′,@p3=1,@p4=1,@p5=N’Bluesmobile’
SET IDENTITY_INSERT dbo.Inventory OFF
Adding an Object Graph
When adding an entity to the database, child records can be added in the same call without specifically adding them into their own DbSet
static void AddRecords()
{
…
var anotherMake = new Make {Name = "Honda"};
var car = new Car { Color = "Yellow", PetName = "Herbie" };
//Cast the Cars property to List
context.SaveChanges();
}
The executed SQL statements are shown here:
exec sp_executesql N’SET NOCOUNT ON; INSERT INTO [Makes] ([Name])
VALUES (@p0);
SELECT [Id], [TimeStamp] FROM [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]) VALUES (@p1, @p2, @p3);
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)’,@p1=N’Yellow’,@p2=2,@p3=N’Herbie’
Notice how EF Core retrieved the Id for the new Make record and automatically included that in the insert statement for the Car record.
Add Many-to-Many Records
With the new EF Core support for many-to-many tables, records can be added directly from one entity to the other without going through the pivot table. Now you can write the following code to add Driver records directly to the Car records:
//M2M
var drivers = new List
{
new() { PersonInfo = new Person { FirstName = "Fred", LastName = "Flinstone" } }, new() { PersonInfo = new Person { FirstName = "Wilma", LastName = "Flinstone" } }, new() { PersonInfo = new Person { FirstName = "BamBam", LastName = "Flinstone" } }, new() { PersonInfo = new Person { FirstName = "Barney", LastName = "Rubble" } }, new() { PersonInfo = new Person { FirstName = "Betty", LastName = "Rubble" } }, new() { PersonInfo = new Person { FirstName = "Pebbles", LastName = "Rubble" } },
};
var carsForM2M = context.Cars.Take(2).ToList();
//Cast the IEnumerable to a List to access the Add method
//Range support works with LINQ to Objects, but is not translatable to SQL calls ((List
When the SaveChanges() method is executed, two insert statements are executed. The first inserts the six Driver records into the Drivers table, and then the second inserts the six records into the InventoryDriver table (the pivot table). Here is the insert statement for the pivot table:
exec sp_executesql N’SET NOCOUNT ON;
DECLARE @inserted0 TABLE ([InventoryId] int, [DriverId] int, [_Position] [int]); MERGE [dbo].[InventoryToDrivers] USING (
VALUES (@p12, @p13, 0), (@p14, @p15, 1),
(@p16, @p17, 2),
(@p18, @p19, 3),
(@p20, @p21, 4),
(@p22, @p23, 5)) AS i ([InventoryId], [DriverId], _Position) ON 1=0 WHEN NOT MATCHED THEN
INSERT ([InventoryId], [DriverId]) VALUES (i.[InventoryId], i.[DriverId])
OUTPUT INSERTED.[InventoryId], INSERTED.[DriverId], i._Position INTO @inserted0;
SELECT [t].[Id], [t].[TimeStamp] FROM [dbo].[InventoryToDrivers] t
INNER JOIN @inserted0 i ON ([t].[InventoryId] = [i].[InventoryId]) AND ([t].[DriverId] = [i].[DriverId])
ORDER BY [i].[_Position];
‘,N’@p12 int,@p13 int,@p14 int,@p15 int,@p16 int,@p17 int,@p18 int,@p19 int,@p20 int,@p21 int,@p22 int,@p23 int’,@p12=1,@p13=1,@p14=1,@p15=2,@p16=1,@p17=3,@p18=2,@p19=4,@p20=2, @p21=5,@p22=2,@p23=6
This is a much better experience than past versions of EF Core when using many-to-many relationships, where you had to manage the pivot table yourself.
Add Sample Records
The final step is to add a series of Make and Car records for the query examples covered in the next section. The method creates several Make and Car entities and adds them to the database.
Start by creating the Make entities and add them to the Makes DbSet
static void LoadMakeAndCarData()
{
//The factory is not meant to be used like this, but it’s demo code 🙂 var context = new ApplicationDbContextFactory().CreateDbContext(null); List
{
new() { Name = "VW" },
new() { Name = "Ford" },
new() { Name = "Saab" },
new() { Name = "Yugo" },
new() { Name = "BMW" },
new() { Name = "Pinto" },
};
context.Makes.AddRange(makes); context.SaveChanges(); List
{
new() { MakeId = 1, Color = "Black", PetName = "Zippy" }, new() { MakeId = 2, Color = "Rust", PetName = "Rusty" }, new() { MakeId = 3, Color = "Black", PetName = "Mel" }, new() { MakeId = 4, Color = "Yellow", PetName = "Clunker" }, new() { MakeId = 5, Color = "Black", PetName = "Bimmer" }, new() { MakeId = 5, Color = "Green", PetName = "Hank" }, new() { MakeId = 5, Color = "Pink", PetName = "Pinky" },
new() { MakeId = 6, Color = "Black", PetName = "Pete" }, new() { MakeId = 4, Color = "Brown", PetName = "Brownie" },
new() { MakeId = 1, Color = "Rust", PetName = "Lemon", IsDrivable = false },
};
context.Cars.AddRange(inventory); context.SaveChanges();
}
Clear the Sample Data
Later in this chapter, deleting records will be covered in depth. For now, we will create a method that clears out the sample data so when the examples are run multiple times, the previous executions don’t interfere with the examples.
Create a new method called ClearSampleData(). The method uses the FindEntityType() method on the Model property of the ApplicationDbContext to get the table and schema name and then deletes the records. After the records are deleted, the code uses the DBCC CHECKIDENT command to reset the identity for each table.
static void ClearSampleData()
{
//The factory is not meant to be used like this, but it’s demo code 🙂 var context = new ApplicationDbContextFactory().CreateDbContext(null); var entities = new[]
{
typeof(Driver).FullName, typeof(Car).FullName, typeof(Make).FullName,
};
foreach (var entityName in entities)
{
var entity = context.Model.FindEntityType(entityName); var tableName = entity.GetTableName();
var schemaName = entity.GetSchema(); context.Database.ExecuteSqlRaw($"DELETE FROM {schemaName}.{tableName}");
context.Database.ExecuteSqlRaw($"DBCC CHECKIDENT (\"{schemaName}.{tableName}\", RESEED, 0);");
}
}
Add a call to this method at the beginning of the top-level statements to reset the database each time the program is executed. Also add a call after the AddRecords() method to clean up the examples that add individual records:
Console.WriteLine(" More Fun with Entity Framework Core "); ClearSampleData();
AddRecords();
ClearSampleData();
LoadMakeAndCarData();
Querying Data
Querying data using EF Core is typically accomplished using LINQ queries. As a reminder, when using LINQ to query the database for a list of entities, the query isn’t executed until the query is iterated over, converted to a List
■Note This book is not a complete lInQ reference but shows just a few examples. For more examples of lInQ queries, microsoft has published 101 lInQ samples at https://code.msdn.microsoft.com/101– LINQ-Samples-3fb9811b.
New in EF Core 5, you can call the ToQueryString() method in most LINQ queries to examine the query that gets executed against the database. The main exception is any immediate execution queries, such as First()/FirstOrDefault(). For split queries, the ToQueryString() method returns only the first query that will be executed.
Get All Records
To get all the records for a table, simply use the DbSet
static void QueryData()
{
//The factory is not meant to be used like this, but it’s demo code 🙂 var context = new ApplicationDbContextFactory().CreateDbContext(null);
//Return all of the cars IQueryable
{
Console.WriteLine($"{c.PetName} is {c.Color}");
}
//Clean up the context context.ChangeTracker.Clear();
List
{
Console.WriteLine($"{c.PetName} is {c.Color}");
}
}
Notice that the type returned is an IQueryable
List
Filter Records
The Where() method is used to filter records from the DbSet
static void FilterData()
{
//The factory is not meant to be used like this, but it’s demo code 🙂 var context = new ApplicationDbContextFactory().CreateDbContext(null);
//Return all yellow cars
IQueryable
Console.WriteLine("Yellow cars"); foreach (Car c in cars)
{
Console.WriteLine($"{c.PetName} is {c.Color}");
}
context.ChangeTracker.Clear();
//Return all yellow cars with a petname of Clunker
IQueryable
Console.WriteLine("Yellow cars and Clunkers"); foreach (Car c in cars2)
{
Console.WriteLine($"{c.PetName} is {c.Color}");
}
context.ChangeTracker.Clear();
//Return all yellow cars with a petname of Clunker
IQueryable
Console.WriteLine("Yellow cars and Clunkers"); foreach (Car c in cars3)
{
Console.WriteLine($"{c.PetName} is {c.Color}");
}
context.ChangeTracker.Clear();
//Return all yellow cars or cars with PetName of Clunker
IQueryable
Console.WriteLine("Yellow cars or Clunkers"); foreach (Car c in cars4)
{
Console.WriteLine($"{c.PetName} is {c.Color}");
}
context.ChangeTracker.Clear();
}
Notice that the type returned is also an IQueryable
One improvement in EF Core 6 is the handling of converting string.IsNullOrWhiteSpace() into SQL. Examine the added code to the end of the FilterData() method:
static void FilterData()
{
…
IQueryable
foreach (Car c in cars5)
{
Console.WriteLine($"{c.PetName} is {c.Color}");
}
context.ChangeTracker.Clear();
}
Prior to EF Core 6, the resulting query was a mix of LTRIM/RTRIM commands. With the improvements in EF Core 6, the executed query is much cleaner:
SELECT [i].[Id], [i].[Color], [i].[DateBuilt], [i].[Display], [i].[IsDrivable], [i]. [MakeId], [i].[PetName], [i].[TimeStamp]
FROM [dbo].[Inventory] AS [i] WHERE [i].[Color] <> N”
Sort Records
The OrderBy() and OrderByDescending() methods set the sort(s) for the query in either ascending or descending order, respectively. If subsequent sorts are required, use the ThenBy() and/or the ThenByDescending() methods.
static void SortData()
{
//The factory is not meant to be used like this, but it’s demo code 🙂 var context = new ApplicationDbContextFactory().CreateDbContext(null);
//Return all cars ordered by color
IOrderedQueryable
foreach (Car c in cars)
{
Console.WriteLine($"{c.PetName} is {c.Color}");
}
context.ChangeTracker.Clear();
//Return all cars ordered by color then Petname
IOrderedQueryable
foreach (Car c in cars1)
{
Console.WriteLine($"{c.PetName} is {c.Color}");
}
context.ChangeTracker.Clear();
//Return all cars ordered by color Descending
IOrderedQueryable
Console.WriteLine("Cars ordered by Color descending"); foreach (Car c in cars2)
{
Console.WriteLine($"{c.PetName} is {c.Color}");
}
context.ChangeTracker.Clear();
}
Notice that the datatype returned from a LINQ query with an OrderBy()/OrderByDescending() is
IOrderedQueryable
Ordering by ascending and descending can be intermixed, as is shown here:
static void SortData()
{
…
//Return all cars ordered by Color then by PetName descending
IOrderedQueryable
Console.WriteLine("Cars ordered by Color then by PetName descending"); foreach (Car c in cars3)
{
Console.WriteLine($"{c.PetName} is {c.Color}");
}
context.ChangeTracker.Clear();
}
Reverse Sort Records
The Reverse() method reverses the entire sort order, as shown here:
static void SortData()
{
…
//Return all cars ordered by Color then Petname in reverse
IQueryable
foreach (Car c in cars1)
{
Console.WriteLine($"{c.PetName} is {c.Color}");
}
context.ChangeTracker.Clear();
}
Notice that the datatype returned from a LINQ query with a Reverse() clause is IQueryable
The preceding LINQ query gets translated into the following:
SELECT [i].[Id], [i].[Color], [i].[DateBuilt], [i].[Display],
[i].[IsDrivable], [i].[MakeId], [i].[PetName], [i].[TimeStamp] FROM [dbo].[Inventory] AS [i]
ORDER BY [i].[Color] DESC, [i].[PetName] DESC
Paging
EF Core provides paging capabilities using Skip() and Take(). Skip() skips the specified number of records while Take() retrieves the specified number of records.
Using the Skip() method with SQL Server executes a query with an OFFSET command. The OFFSET command is the SQL Server version of skipping records that would normally be returned from the query. Add the following method to the Program.cs file:
static void Paging()
{
//The factory is not meant to be used like this, but it’s demo code 🙂 var context = new ApplicationDbContextFactory().CreateDbContext(null); Console.WriteLine("Paging");
//Skip the first two records
var cars = context.Cars.Skip(2).ToList();
}
■Note The SQl Server OFFSET command has decreasing performance the more records are skipped. most applications probably won’t be using eF Core (or any ORm) with massive amounts of data, but make sure you performance test any calls that use Skip(). If there is a performance issue, it might be better to drop down to a FromSqlRaw()/FromSqlInterpolated() to optimize the query.
The example code skips the first two records and returns the rest. The slightly edited (for readability) query is shown here:
SELECT [i].[Id], [i].[Color], [i].[DateBuilt], [i].[Display],
[i].[IsDrivable], [i].[MakeId], [i].[PetName], [i].[TimeStamp] FROM [dbo].[Inventory] AS [i]
ORDER BY (SELECT 1) OFFSET 2 ROWS
Notice that the generated query adds an ORDER BY clause, even though the LINQ statement did not have any ordering. This is because the SQL Server OFFSET command cannot be used without an ORDER BY clause.
The Take() method generates a SQL Server query that uses the TOP command. The following addition to the Paging() method uses the Clear() method on the ChangeTracker to reset the ApplicationDbContext and then uses the Take() method to return two records.
static void Paging()
{
…
context.ChangeTracker.Clear();
//Take the first two records
cars = context.Cars.Take(2).ToList();
}
The executed query is shown here:
SELECT TOP(2) [i].[Id], [i].[Color], [i].[DateBuilt], [i].[Display],
[i].[IsDrivable], [i].[MakeId], [i].[PetName], [i].[TimeStamp] FROM [dbo].[Inventory] AS [i]
■ Note Recall from Chapter 13 that with .neT 6/C#10, the Take() method can take a range. This capability is not supported in eF Core.
Combing the Skip() and Take() method enables paging of the data. For example, if you have a page size of two (due to our small database size) and you need to get the second page, execute the following LINQ query:
static void Paging()
{
…
//Skip the first two records and take the next two records cars = context.Cars.Skip(2).Take(2).ToList();
}
When combining Skip() and Take(), SQL Server doesn’t use the TOP command, but another version of the OFFSET command, as shown here:
SELECT [i].[Id], [i].[Color], [i].[DateBuilt], [i].[Display], [i].[IsDrivable], [i].[MakeId], [i].[PetName], [i].[TimeStamp]
FROM [dbo].[Inventory] AS [i] ORDER BY (SELECT 1)
OFFSET 2 ROWS FETCH NEXT 2 ROWS ONLY
Retrieve a Single Record
There are three main methods (with OrDefault variants) for returning a single record with a query: First()/FirstOrDefault(), Last()/LastOrDefault(), and Single()/SingleOrDefault(). While all three return a single record, their approaches all differ. The three methods and their variants are detailed here:
•First() returns the first record that matches the query condition and any ordering clauses. If no ordering is specified, then the record returned is based on database order. If no record is returned, an exception is thrown.
•The FirstOrDefault() behavior matches First() except that if no records match the query, the method returns the default value for the type (null).
•Single() returns the first record that matches the query condition and any ordering clauses. If no ordering is specified, then the record returned is based on database order. If no records or more than one record matches the query, then an exception is thrown.
•The SingleOrDefault() behavior matches Single() except that if no records match the query, the method returns the default value for the type (null).
•Last() returns the last record that matches the query condition and any ordering clauses. If no ordering is specified, an exception is thrown. If no record is returned, an exception is thrown.
•The LastOrDefault() behavior matches Last() except that if no records match the query, the method returns the default value for the type (null).
All the methods can also take an Expression<Func<T, bool>> to filter the result set. This means you can place the Where() expression inside the call for the First()/Single() methods as long as there is only a single Where() clause. The following statements are equivalent:
Context.Cars.Where(c=>c.Id < 5).First(); Context.Cars.First(c=>c.Id < 5);
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 example gets the first record based on database order:
static void SingleRecordQueries()
{
//The factory is not meant to be used like this, but it’s demo code 🙂 var context = new ApplicationDbContextFactory().CreateDbContext(null); Console.WriteLine("Single Record with database Sort");
var firstCar = context.Cars.First(); Console.WriteLine($"{firstCar.PetName} is {firstCar.Color}"); context.ChangeTracker.Clear();
}
The preceding LINQ query gets translated into the following:
SELECT TOP(1) [i].[Id], [i].[Color], [i].[DateBuilt], [i].[Display],
[i].[IsDrivable], [i].[MakeId], [i].[PetName], [i].[TimeStamp] FROM [dbo].[Inventory] AS [i]
The following code gets the first record based on Color order:
static void SingleRecordQueries()
{
…
Console.WriteLine("Single Record with OrderBy sort");
var firstCarByColor = context.Cars.OrderBy(c => c.Color).First(); Console.WriteLine($"{firstCarByColor.PetName} is {firstCarByColor.Color}"); context.ChangeTracker.Clear();
}
The preceding LINQ query gets translated into the following:
SELECT TOP(1) [i].[Id], [i].[Color], [i].[DateBuilt], [i].[Display],
[i].[IsDrivable], [i].[MakeId], [i].[PetName], [i].[TimeStamp]
FROM [dbo].[Inventory] AS [i] ORDER BY [i].[Color]
The following code shows First() being used with a Where() clause and then using First() as the
Where() clause:
static void SingleRecordQueries()
{
…
Console.WriteLine("Single Record with Where clause");
var firstCarIdThree = context.Cars.Where(c => c.Id == 3).First(); Console.WriteLine($"{firstCarIdThree.PetName} is {firstCarIdThree.Color}"); context.ChangeTracker.Clear();
Console.WriteLine("Single Record Using First as Where clause"); var firstCarIdThree1 = context.Cars.First(c => c.Id == 3);
Console.WriteLine($"{firstCarIdThree1.PetName} is {firstCarIdThree1.Color}"); context.ChangeTracker.Clear();
}
Both preceding statements get translated into the following SQL:
SELECT TOP(1) [i].[Id], [i].[Color], [i].[DateBuilt], [i].[Display],
[i].[IsDrivable], [i].[MakeId], [i].[PetName], [i].[TimeStamp] FROM [dbo].[Inventory] AS [i]
WHERE [i].[Id] = 3
The following example shows that an exception is thrown if there isn’t a match when using First(): static void SingleRecordQueries()
{
…
Console.WriteLine("Exception when no record is found"); try
{
var firstCarNotFound = context.Cars.First(c => c.Id == 27);
}
catch (InvalidOperationException ex)
{
Console.WriteLine(ex.Message);
}
context.ChangeTracker.Clear();
}
The preceding LINQ query gets translated into the following:
SELECT TOP(1) [i].[Id], [i].[Color], [i].[DateBuilt], [i].[Display],
[i].[IsDrivable], [i].[MakeId], [i].[PetName], [i].[TimeStamp] FROM [dbo].[Inventory] AS [i]
WHERE [i].[Id] = 27
When using FirstOrDefault(), instead of an exception, the result is a null when no data is returned.
static void SingleRecordQueries()
{
…
Console.WriteLine("Return Default (null) when no record is found"); var firstCarNotFound = context.Cars.FirstOrDefault(c => c.Id == 27); Console.WriteLine(firstCarNotFound == true); context.ChangeTracker.Clear();
}
The preceding LINQ query gets translated into the same SQL as the previous example.
■ Note also recall from Chapter 13 that with .neT 6/C#10, the OrDefault() methods can specify a default value when nothing is returned from the query. Unfortunately, this capability is also not supported in eF Core.
Using Last
When using the parameterless form of Last() and LastOrDefault(), the last record (based on any preceding ordering clauses) will be returned. When using Last(), the LINQ query must have an OrderBy()/ OrderByDescending() clause or an InvalidOperationException will be thrown:
static void SingleRecordQueries()
{
…
Console.WriteLine("Exception with Last and no order by"); try
{
context.Cars.Last();
}
catch (InvalidOperationException ex)
{
Console.WriteLine(ex.Message);
}
}
The following test gets the last record based on Color order:
static void SingleRecordQueries()
{
…
Console.WriteLine("Get last record sorted by Color"); var lastCar = context.Cars.OrderBy(c=>c.Color).Last(); Console.WriteLine(firstCarNotFoundDefault == null); context.ChangeTracker.Clear();
}
EF Core reverses the ORDER BY statements and then takes the top(1) to get the result. Here is the executed query:
SELECT TOP(1) [i].[Id], [i].[Color], [i].[DateBuilt], [i].[Display],
[i].[IsDrivable], [i].[MakeId], [i].[PetName], [i].[TimeStamp] FROM [dbo].[Inventory] AS [i]
ORDER BY [i].[Color] DESC
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 tests retrieves the single record where Id == 1:
static void SingleRecordQueries()
{
…
Console.WriteLine("Get single record");
var singleCar = context.Cars.Single(c=>c.Id == 3); context.ChangeTracker.Clear();
}
The preceding LINQ query gets translated into the following:
SELECT TOP(2) [i].[Id], [i].[Color], [i].[DateBuilt], [i].[Display],
[i].[IsDrivable], [i].[MakeId], [i].[PetName], [i].[TimeStamp] FROM [dbo].[Inventory] AS [i]
WHERE [i].[Id] = 3
Single() throws an exception if no records are returned or more than one record is returned:
static void SingleRecordQueries()
{
…
Console.WriteLine("Exception when more than one record is found"); try
{
context.Cars.Single(c => c.Id > 1);
}
catch (InvalidOperationException ex)
{
Console.WriteLine(ex.Message);
}
context.ChangeTracker.Clear();
Console.WriteLine("Exception when no records are found"); try
{
context.Cars.Single(c => c.Id == 27);
}
catch (InvalidOperationException ex)
{
Console.WriteLine(ex.Message);
}
context.ChangeTracker.Clear();}
}
When using SingleOrDefault(), an exception is also thrown if more than one record is returned:
static void SingleRecordQueries()
{
…
Console.WriteLine("Exception when more than one record is found"); try
{
context.Cars.SingleOrDefault(c => c.Id > 1);
}
catch (InvalidOperationException ex)
{
Console.WriteLine(ex.Message);
}
context.ChangeTracker.Clear();
}
When using SingleOrDefault(), instead of an exception, the result is null when no data is returned.
static void SingleRecordQueries()
{
…
var defaultWhenSingleNotFoundCar = context.Cars.SingleOrDefault(c => c.Id == 27); context.ChangeTracker.Clear();
}
Using Find
The Find() method also returns a single record but behaves a little differently than the other single record methods. The Find() method’s parameter(s) represent the primary key(s) of the entity. It then looks in the ChangeTracker for an instance of the entity with the matching primary key and returns it if it’s found. If not, it will then make a call to the database to retrieve the record.
static void SingleRecordQueries()
{
…
var foundCar = context.Cars.Find(27); context.ChangeTracker.Clear();
}
If the entity has a compound primary key, then pass in the values representing the compound key:
var item = context.MyClassWithCompoundKey.Find(27,3);
Aggregation Methods
EF Core also supports server-side aggregate methods (Max(), Min(), Count(), and Average()). All aggregate methods can be used in conjunction with Where() methods and return a single value. The aggregation queries execute on the server side. Global query filters affect aggregate methods as well and can be disabled with IgnoreQueryFilters(). Global query filters are covered later in this chapter.
Note that each of the aggregate methods is a terminating function. In other words, they end the LINQ statement when executed since each method returns a single numeric value. Query execution also happens immediately, like the single record methods discussed earlier.
All of the SQL statements shown in this section were collected using SQL Server Profiler as the
ToQueryString() method doesn’t work with aggregation.
This first example counts all the Car records in the database.
static void Aggregation()
{
//The factory is not meant to be used like this, but it’s demo code 🙂 var context = new ApplicationDbContextFactory().CreateDbContext(null); var count = context.Cars.Count();
}
The executed SQL is shown here:
SELECT COUNT(*)
FROM [dbo].[Inventory] AS [i]
The Count() method can contain the filter expression, just like First() and Single(). The following examples demonstrate the Count() method with a where condition. The first adds the expression directly into the Count() method, and the second adds the Count() method to the end of the LINQ statement after the Where() method.
static void Aggregation()
{
…
var countByMake = context.Cars.Count(x=>x.MakeId == 1); Console.WriteLine($"Count: {countByMake}");
var countByMake2 = context.Cars.Where(x=>x.MakeId == 1).Count(); Console.WriteLine($"Count: {countByMake2}");
}
Both lines of code create the same SQL calls to the server, as shown here:
SELECT COUNT(*)
FROM [dbo].[Inventory] AS [i] WHERE [i].[MakeId] = 1
The next examples show Min(), Max(), and Average(). Each method takes an expression to specify the property that is being operated on:
static void Aggregation()
{
…
var max = context.Cars.Max(x => x.Id);
var min = context.Cars.Min(x => x.Id);
var avg = context.Cars.Average(x => x.Id); Console.WriteLine($"Max ID: {max} Min ID: {min} Ave ID: {avg}");}
–Generated SQL
SELECT MAX([i].[Id]) FROM [dbo].[Inventory] AS [i]
SELECT MIN([i].[Id]) FROM [dbo].[Inventory] AS [i]
SELECT AVG(CAST([i].[Id] AS float)) FROM [dbo].[Inventory] AS [i]
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()). Just like the aggregation methods, the Any() method (but not the All() method) can be added to the end of a LINQ query with Where() methods, or the filter expression can be contained in the method itself. Any() and All() methods execute on the server side, and a Boolean is returned from the query. Both are terminating functions. Global query filters affect Any() and All() methods functions as well and can be disabled with IgnoreQueryFilters().
The ToQueryString() method doesn’t work with the Any()/All() functions either, so all of the SQL statements shown in this section were collected using SQL Server Profiler.
This first sample checks if any car records have a MakeId of 1.
static void AnyAndAll()
{
//The factory is not meant to be used like this, but it’s demo code 🙂 var context = new ApplicationDbContextFactory().CreateDbContext(null); var resultAny = context.Cars.Any(x => x.MakeId == 1);
//This executes the same query as the preceding line
var resultAnyWithWhere = context.Cars.IgnoreQueryFilters().Where(x => x.MakeId == 1).Any();
Console.WriteLine($"Exist? {resultAny}"); Console.WriteLine($"Exist? {resultAnyWithWhere}");
}
The executed SQL for the first example is shown here:
SELECT CASE WHEN EXISTS (
SELECT 1
FROM [dbo].[Inventory] AS [i]
WHERE [i].[MakeId] = 1) THEN CAST(1 AS bit) ELSE CAST(0 AS bit)
END
This second example checks if all car records have a specific MakeId. static void AnyAndAll()
{
…
var resultAll = context.Cars.All(x => x.MakeId == 1); Console.WriteLine($"All? {resultAll}");
}
The executed SQL for the first theory test is shown here:
SELECT CASE
WHEN NOT EXISTS ( SELECT 1
FROM [dbo].[Inventory] AS [i]
WHERE [i].[MakeId] <> 1) THEN CAST(1 AS bit) ELSE CAST(0 AS bit)
END
Getting Data from Stored Procedures
The final data retrieval pattern to examine is getting data from stored procedures. While there are some gaps in EF Core in relation to stored procedures (compared to EF 6), remember that EF Core is built on top of ADO.NET. We just need to drop down a layer and remember how we called stored procedures pre-ORM.
The first step is to create the stored procedure in our database:
CREATE PROCEDURE [dbo].[GetPetName] @carID int,
@petName nvarchar(50) output
AS
SELECT @petName = PetName from dbo.Inventory where Id = @carID
The following method creates the required parameters (input and output), leverages the
ApplicationDbContext Database property, and calls ExecuteSqlRaw():
static string GetPetName(ApplicationDbContext context, int id)
{
var parameterId = new SqlParameter
{
ParameterName = "@carId",
SqlDbType = System.Data.SqlDbType.Int, Value = id,
};
var parameterName = new SqlParameter
{
ParameterName = "@petName",
SqlDbType = System.Data.SqlDbType.NVarChar, Size = 50,
Direction = ParameterDirection.Output
};
var result = context.Database
.ExecuteSqlRaw("EXEC [dbo].[GetPetName] @carId, @petName OUTPUT",parameterId, parameterName);
return (string)parameterName.Value;
}
The next step is to get the Car records (to get the Id values for each of the Car records), loop though them, and use the stored procedure to get their PetName:
static void GetDataFromStoredProc()
{
//The factory is not meant to be used like this, but it’s demo code 🙂 var context = new ApplicationDbContextFactory().CreateDbContext(null); var cars = context.Cars.IgnoreQueryFilters().ToList();
foreach (var c in cars)
{
Console.WriteLine($"PetName: {GetPetName(context,c.Id)}");
}
}
■ Note This example is somewhat contrived, as getting all of the Car records also gets the PetName properties for the records. getting all of the records is a convenient way to demonstrate calling the stored procedure multiple times.
When the code is run, EF Core executes the following SQL for each Car in the list (only one is shown here):
declare @p4 nvarchar(50) set @p4=N’Hank’
exec sp_executesql N’EXEC [dbo].[GetPetName] @carId, @petName OUTPUT’,
N’@carId int,@petName nvarchar(50) output’,@carId=1,@petName=@p4 output select @p4
Querying Related Data
Entity navigation properties are used to load an entity’s related data. The related data can be loaded eagerly (one LINQ statement, one SQL query), eagerly with split queries (one LINQ statement, multiple SQL queries), explicitly (multiple LINQ calls, multiple SQL queries), or lazily (one LINQ statement, multiple on- demand SQL queries).
In addition to the ability to load related data using the navigation properties, EF Core will automatically fix up entities as they are loaded into the Change Tracker. For example, assume all the Make records are loaded into the DbSet
Eager Loading
Eager loading is the term for loading related records from multiple tables in one database call. This is analogous to creating a query in T-SQL linking two or more tables with joins. When entities have navigation properties and those properties are used in the LINQ queries, the translation engine uses joins to get data from the related tables and loads the corresponding entities. This is usually much more efficient than executing one query to get the data from one table and then running additional queries for each of the related tables. For those times when it is less efficient to use one query, EF Core 5 introduced query splitting, covered next.
The Include() and ThenInclude() (for subsequent navigation properties) methods are used to traverse the navigation properties in LINQ queries. If the relationship is required, the LINQ translation engine will create an inner join. If the relationship is optional, the translation engine will create a left join.
For example, to load all the Car records with their related Make information, execute the following LINQ query:
static void RelatedData()
{
//The factory is not meant to be used like this, but it’s demo code 🙂 var context = new ApplicationDbContextFactory().CreateDbContext(null); var carsWithMakes = context.Cars.Include(c => c.MakeNavigation).ToList(); context.ChangeTracker.Clear();
}
The previous LINQ executes the following query against the database:
SELECT [i].[Id], [i].[Color], [i].[DateBuilt], [i].[Display], [i].[IsDrivable], [i].[MakeId], [i].[PetName], [i].[TimeStamp],
[m].[Id], [m].[Name], [m].[TimeStamp] FROM [Dbo].[Inventory] AS [i]
INNER JOIN [dbo].[Makes] AS [m] ON [i].[MakeId] = [m].[Id]
■ Note The SELECT statement is returning all the fields for the Inventory and Makes tables. eF Core then wires up the data correctly, returning the correct object graph.
The MakeNavigation property is a required relationship since the MakeId property of the Car entity is non-nullable. Because it’s required, the Make table is joined to the Inventory table with an INNER JOIN. If the navigation property was optional (MakeId was defined with a nullable int), the join would be an OUTER JOIN. The next example demonstrates optional relationships in the queries generated.
Multiple Include() statements can be used in the same query to join more than one entity to the original. To work down the navigation property tree, use ThenInclude() after an Include(). For example, to get all the Make records with their related Car records and the Driver records for the Cars, use the following statement:
var makesWithCarsAndDrivers = context.Makes.Include(c=>c.Cars).ThenInclude(d=>d.Drivers). ToList();
■ Note The call to Clear() on the ChangeTracker is added to make sure that previous code examples don’t interfere with the results of the code being discussed.
The previous LINQ executes the following query against the database:
SELECT [m].[Id], [m].[Name], [m].[TimeStamp], [t0].[Id], [t0].[Color], [t0].[DateBuilt],
[t0].[Display], [t0].[IsDrivable], [t0].[MakeId], [t0].[PetName], [t0].[TimeStamp], [t0].[CarId],
[t0].[DriverId], [t0].[Id0], [t0].[FirstName], [t0].[LastName], [t0].[TimeStamp0] FROM [Makes] AS [m]
LEFT JOIN (
SELECT [i].[Id], [i].[Color], [i].[DateBuilt], [i].[Display], [i].[IsDrivable], [i]. [MakeId],
[i].[PetName], [i].[TimeStamp], [t].[CarId], [t].[DriverId], [t].[Id] AS [Id0], [t].[FirstName],
[t].[LastName], [t].[TimeStamp] AS [TimeStamp0] FROM [dbo].[Inventory] AS [i]
LEFT JOIN (
SELECT .[CarId], .[DriverId], [d].[Id], [d].[FirstName], [d].[LastName], [d]. [TimeStamp]
FROM [CarDriver] AS
INNER JOIN [Drivers] AS [d] ON .[DriverId] = [d].[Id]
) AS [t] ON [i].[Id] = [t].[CarId]
) AS [t0] ON [m].[Id] = [t0].[MakeId]
ORDER BY [m].[Id], [t0].[Id], [t0].[CarId], [t0].[DriverId], [t0].[Id0]
One thing that might seem odd is the ORDER BY clause, since the LINQ query did not include any ordering. When using chained includes (with the Include()/ThenInclude() statements), the LINQ translation engine will add an ORDER BY clause based on the order of the tables included and their primary and foreign keys. This is in addition to any ordering you specified in the LINQ query.
Take the following updated example:
var orderedMakes = context.Makes.Include(c => c.Cars).ThenInclude(d => d.Drivers). OrderBy(d=>d.Name).ToList();
The SQL produced will be sorted by any (and all) ordering clauses in the LINQ query, suffixed with the autogenerated ORDER BY clauses:
SELECT [m].[Id], [m].[Name], [m].[TimeStamp], [t0].[Id], [t0].[Color], [t0].[DateBuilt],
[t0].[Display], [t0].[IsDrivable], [t0].[MakeId], [t0].[PetName], [t0].[TimeStamp], [t0].[CarId],
[t0].[DriverId], [t0].[Id0], [t0].[FirstName], [t0].[LastName], [t0].[TimeStamp0] FROM [Makes] AS [m]
LEFT JOIN (
SELECT [i].[Id], [i].[Color], [i].[DateBuilt], [i].[Display], [i].[IsDrivable], [i]. [MakeId],
[i].[PetName], [i].[TimeStamp], [t].[CarId], [t].[DriverId], [t].[Id] AS [Id0], [t].[FirstName],
[t].[LastName], [t].[TimeStamp] AS [TimeStamp0] FROM [dbo].[Inventory] AS [i]
LEFT JOIN (
SELECT .[CarId], .[DriverId], [d].[Id], [d].[FirstName], [d].[LastName], [d]. [TimeStamp]
FROM [CarDriver] AS
INNER JOIN [Drivers] AS [d] ON .[DriverId] = [d].[Id]
) AS [t] ON [i].[Id] = [t].[CarId]
) AS [t0] ON [m].[Id] = [t0].[MakeId]
ORDER BY [m].[Name], [m].[Id], [t0].[Id], [t0].[CarId], [t0].[DriverId], [t0].[Id0]
Filtered Include
Introduced in EF Core 5, the included data can be filtered and sorted. The allowable operations on the collection navigation are Where(), OrderBy(), OrderByDescending(), ThenBy(), ThenByDescending(), Skip(), and Take(). For example, if you want to get all Make records, but only the related Car records where the color is yellow, you filter the navigation property in the lambda expression, like this:
//Add to keep the demos clean context.ChangeTracker.Clear();
var makesWithYellowCars = context.Makes
.Include(x => x.Cars.Where(x=>x.Color == "Yellow"))
.ToList();
The query that is executed 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].[TimeStamp] FROM [Makes] AS [m]
LEFT JOIN (
SELECT [i].[Id], [i].[Color], [i].[DateBuilt], [i].[Display], [i].[IsDrivable], [i].[MakeId], [i].[PetName], [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]
Eager Loading with Split Queries
When a LINQ query contains a lot of includes, there can be a negative performance impact. To resolve this situation, EF Core 5 introduced split queries. Instead of executing a single query, EF Core will split the LINQ query into multiple SQL queries and then wire up all the related data. For example, the previous query can be expected as multiple SQL queries by adding AsSplitQuery() into the LINQ query, like this:
//Add to keep the demos clean context.ChangeTracker.Clear();
var splitMakes = context.Makes.AsSplitQuery()
.Include(x => x.Cars.Where(x=>x.Color == "Yellow")).ToList();
The queries that are executed are shown here:
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].[TimeStamp], [m].[Id] FROM [Makes] AS [m]
INNER JOIN (
SELECT [i].[Id], [i].[Color], [i].[DateBuilt], [i].[Display], [i].[IsDrivable], [i]. [MakeId],
[i].[PetName], [i].[TimeStamp] FROM [dbo].[Inventory] AS [i]
WHERE [i].[Color] = N’Yellow’
) AS [t] ON [m].[Id] = [t].[MakeId] ORDER BY [m].[Id]
There is a downside to using split queries: if the data changes between executing the queries, then the data returned will be inconsistent.
Many-to-Many Queries
The new EF Core support for designing many-to-many tables carries over to querying data with LINQ. Prior to the many-to-many support, queries would have to go through the pivot table. Now you can write the following LINQ statement to get the Car and related Driver records:
var carsAndDrivers = context.Cars.Include(x => x.Drivers).Where(x=>x.Drivers.Any());
As you can see from the generated SQL select statement, EF Core takes care of working through the pivot table to get the Car and Driver records matched up correctly:
SELECT [i].[Id], [i].[Color], [i].[DateBuilt], [i].[Display], [i].[IsDeleted], [i].
[IsDrivable], [i].[MakeId], [i].[PetName], [i].[Price], [i].[TimeStamp], [i].[ValidFrom],
[i].[ValidTo], [t].[InventoryId], [t].[DriverId], [t].[Id], [t].[TimeStamp], [t].[Id0], [t]. [TimeStamp0], [t].[FirstName], [t].[LastName]
FROM [dbo].[Inventory] AS [i] LEFT JOIN (
SELECT [i1].[InventoryId], [i1].[DriverId], [i1].[Id], [i1].[TimeStamp], [d0].[Id] AS
[Id0], [d0].[TimeStamp] AS [TimeStamp0], [d0].[FirstName], [d0].[LastName] FROM [dbo].[InventoryToDrivers] AS [i1]
INNER JOIN [dbo].[Drivers] AS [d0] ON [i1].[DriverId] = [d0].[Id]
) AS [t] ON [i].[Id] = [t].[InventoryId]
WHERE ([i].[IsDrivable] = CAST(1 AS bit)) AND EXISTS ( SELECT 1
FROM [dbo].[InventoryToDrivers] AS [i0]
INNER JOIN [dbo].[Drivers] AS [d] ON [i0].[DriverId] = [d].[Id] WHERE [i].[Id] = [i0].[InventoryId])
ORDER BY [i].[Id], [t].[InventoryId], [t].[DriverId]
Explicit Loading
Explicit loading is loading data along a navigation property after the core object is already loaded. This process involves executing an additional database call to get the related data. This can be useful if your application selectively needs to get the related records instead of always pulling them.
The process starts with an entity that is already loaded and uses the Entry() method on the derived DbContext. When querying against a reference navigation property (e.g., getting the Make information for a car), use the Reference() method. When querying against a collection navigation property, use the
Collection() method. The query is deferred until Load(), ToList(), or an aggregate function (e.g., Count(), Max()) is executed.
The following examples show how to get the related Make data as well as any Drivers for a Car record:
//Get the Car record
var car = Context.Cars.First(x => x.Id == 1);
//Get the Make information
context.Entry(car).Reference(c => c.MakeNavigation).Load();
//Get any Drivers the Car is related to context.Entry(car).Collection(c => c.Drivers).Query().Load();
The previous statements generate the following queries:
//Get the Car record
SELECT TOP(1) [i].[Id], [i].[Color], [i].[DateBuilt], [i].[Display], [i].[IsDrivable], [i].[MakeId], [i].[PetName], [i].[TimeStamp]
FROM [dbo].[Inventory] AS [i] WHERE [i].[Id] = 1
//Get the Make information
SELECT [m].[Id], [m].[Name], [m].[TimeStamp] FROM [Makes] AS [m]
WHERE [m].[Id] = 5
//Get any Drivers the Car is related to
SELECT [t].[Id], [t].[FirstName], [t].[LastName], [t].[TimeStamp], [i].[Id], [t].[CarId], [t].[DriverId],
[t0].[CarId], [t0].[DriverId], [t0].[Id], [t0].[Color], [t0].[DateBuilt], [t0].[Display], [t0].[IsDrivable], [t0].[MakeId], [t0].[PetName], [t0].[TimeStamp]
FROM [dbo].[Inventory] AS [i] INNER JOIN (
SELECT [d].[Id], [d].[FirstName], [d].[LastName], [d].[TimeStamp], .[CarId], . [DriverId]
FROM [CarDriver] AS
INNER JOIN [Drivers] AS [d] ON .[DriverId] = [d].[Id]
) AS [t] ON [i].[Id] = [t].[CarId] LEFT JOIN (
SELECT [c0].[CarId], [c0].[DriverId], [t1].[Id], [t1].[Color], [t1].[DateBuilt], [t1]. [Display],
[t1].[IsDrivable], [t1].[MakeId], [t1].[PetName], [t1].[TimeStamp] FROM [CarDriver] AS [c0]
INNER JOIN (
SELECT [i0].[Id], [i0].[Color], [i0].[DateBuilt], [i0].[Display], [i0].[IsDrivable], [i0].[MakeId], [i0].[PetName], [i0].[TimeStamp]
FROM [dbo].[Inventory] AS [i0]
WHERE [i0].[IsDrivable] = CAST(1 AS bit)
) AS [t1] ON [c0].[CarId] = [t1].[Id] WHERE [t1].[Id] = 1
) AS [t0] ON [t].[Id] = [t0].[DriverId] WHERE [i].[Id] = 1
ORDER BY [i].[Id], [t].[CarId], [t].[DriverId], [t].[Id], [t0].[CarId], [t0].[DriverId], [t0].[Id]
As you can see, that third and final query is doing a lot of work to simply get the Driver records for the selected Car record. This shows us two important facts: 1) If you can write it all on one query using eager loading, it is usually better to do so, which negates the need to go back to the database to get the related
records, and 2) EF Core doesn’t always create the best queries. I’ve already shown you how to use eager loading in the previous section. Later in this chapter, I will show you how to use SQL statements with or without additional LINQ statements to retrieve data from the database. This is useful when EF Core creates suboptimal queries.
Lazy Loading
Lazy loading is loading a record on-demand when a navigation property is used to access a related record that is not yet loaded into memory. Lazy loading is a feature from EF 6 that was added back into EF Core with version 2.1. While it might sound like a good idea to turn this on, enabling lazy loading can cause performance problems in your application by making potentially unnecessary round-trips to your database.
Lazy loading can be useful in smart client (WPF, WinForms) applications but is recommended to not be used in web or service applications. For this reason, lazy loading is off by default in EF Core (it was enabled by default in EF 6).
To use lazy loading, the navigation properties to be lazily loaded must be marked as virtual. This is because the navigation properties are wrapped with a proxy. This proxy will then have EF Core make a call to the database if the navigation property has not been loaded when it is referenced in your application.
To use lazy loading with proxies, the derived DbContext must be correctly configured. Start by adding the Microsoft.EntityFrameworkCore.Proxies package to your project. You must then opt in to using
the lazy loading proxies in the derived DbContext options. While this would normally be set in your application code when configuring your derived DbContext, we are going to opt in to the proxies using the ApplicationDbContextFactory class that we built earlier. Remember that this class is meant for design- time use and shouldn’t be used in your application code. However, for learning and exploring, it will work just fine.
Open the ApplicationDbContextFactory.cs file and navigate to the CreateDbContext() method. We will take advantage of the args parameter to indicate that we want the method to return a derived DbContext configured to use the lazy loading proxies. Update the CreateDbContext() method to the following:
public ApplicationDbContext CreateDbContext(string[] args)
{
var optionsBuilder = new DbContextOptionsBuilder
var connectionString = @"server=.,5433;Database=AutoLotSamples;User Id=sa;Password=P @ssw0rd;";
if (args != null && args.Length == 1 && args[0].Equals("lazy", StringComparison. OrdinalIgnoreCase))
{
optionsBuilder = optionsBuilder.UseLazyLoadingProxies();
}
optionsBuilder = optionsBuilder.UseSqlServer(connectionString); Console.WriteLine(connectionString);
return new ApplicationDbContext(optionsBuilder.Options);
}
Next, update the Car class to the following:
[Table("Inventory", Schema = "dbo")] [Index(nameof(MakeId), Name = "IX_Inventory_MakeId")] [EntityTypeConfiguration(typeof(CarConfiguration))] public class Car : BaseEntity
{
private string _color;
[Required, StringLength(50)] public string Color
{
get => _color;
set => _color = value;
}
private bool? _isDrivable;
public bool IsDrivable
{
get => _isDrivable ?? true; set => _isDrivable = value;
}
[DatabaseGenerated(DatabaseGeneratedOption.Computed)] public string Display { get; set; }
public DateTime? DateBuilt { get; set; } [Required, StringLength(50)]
public string PetName { get; set; } public int MakeId { get; set; } [ForeignKey(nameof(MakeId))]
public virtual Make MakeNavigation { get; set; } public virtual Radio RadioNavigation { get; set; }
[InverseProperty(nameof(Driver.Cars))]
public virtual IEnumerable
[InverseProperty(nameof(CarDriver.CarNavigation))]
public virtual IEnumerable
}
Now that the properties are marked virtual, they can be used with lazy loading. Add the following method to your Program.cs file (notice that we are not using the args parameter of the CreateDbContext() method yet) and call the method from your top-level statements:
static void LazyLoadCar()
{
//The factory is not meant to be used like this, but it’s demo code 🙂 var context = new ApplicationDbContextFactory().CreateDbContext(null);
var query = context.Cars.AsQueryable(); var cars = query.ToList();
var make = cars[0].MakeNavigation; Console.WriteLine(make.Name);
}
When you run this sample, you will receive a null reference exception when trying to access the Name property of the Make instance. This is because the Make record wasn’t loaded, and we are not using the proxy-enabled version of the derived DbContext(). Update the method to pass in the “lazy” argument to CreateDbContext(), which enables the lazy loading proxy support:
var context = new ApplicationDbContextFactory().CreateDbContext(new string[] {"lazy"});
When you run the code again, you may be surprised to receive an InvalidOperationException. When using lazy proxies, all navigation properties on the models must be marked as virtual, even ones not directly involved in the executing code block. So far, we have updated only the Car entity. Update the rest of the models to the following (updates in bold):
//CarDriver
[Table("InventoryToDrivers", Schema = "dbo")] public class CarDriver : BaseEntity
{
public int DriverId {get;set;} [ForeignKey(nameof(DriverId))]
public virtual Driver DriverNavigation {get;set;}
[Column("InventoryId")] public int CarId {get;set;} [ForeignKey(nameof(CarId))]
public virtual Car CarNavigation {get;set;}
}
//Driver.cs
[Table("Drivers", Schema = "dbo")] [EntityTypeConfiguration(typeof(DriverConfiguration))] public class Driver : BaseEntity
{
public Person PersonInfo { get; set; } = new Person(); [InverseProperty(nameof(Car.Drivers))]
public virtual IEnumerable
public virtual IEnumerable
}
//Make.cs
[Table("Makes", Schema = "dbo")] public class Make : BaseEntity
{
…
[InverseProperty(nameof(Car.MakeNavigation))]
public virtual IEnumerable
}
//Radio.cs
[Table("Radios", Schema = "dbo")] [EntityTypeConfiguration(typeof(RadioConfiguration))] public class Radio : BaseEntity
{
…
[ForeignKey(nameof(CarId))]
public virtual Car CarNavigation { get; set; }
}
■ Note even though the Owned Person class represents a relationship, it is not a navigation property, and properties that are an Owned type do not need to be marked virtual.
Now, when you run the program again, the Make of the car will be printed to the console. When watching the SQL Server activity in profiler, you can clearly see that there were two queries executed:
–Get initial Inventory/Car records
SELECT [i].[Id], [i].[Color], [i].[DateBuilt], [i].[Display], [i].[IsDrivable], [i].[MakeId], [i].[PetName], [i].[TimeStamp]
FROM [dbo].[Inventory] AS [i]
–Get the Make record for the first Inventory/Car record
–Parameters removed for readability
SELECT [m].[Id], [m].[Name], [m].[TimeStamp] FROM [Makes] AS [m]
WHERE [m].[Id] = 5
If you want to learn more about lazy loading and how to use it with EF Core, consult the documentation here: https://docs.microsoft.com/en-us/ef/core/querying/related-data/lazy.
Updating Records
Records are updated by loading them into DbSet
Entity State
When a tracked entity is edited, EntityState is set to Modified. After the changes are successfully saved, the state is returned to Unchanged.
Update Tracked Entities
Updating a single record is much like adding a single record, except that the initial record is retrieved from the database and not created through code. Load the record from the database into a tracked entity, make some changes, and call SaveChanges(). Note that you do not have to call the Update()/UpdateRange() methods on the DbSet
static void UpdateRecords()
{
//The factory is not meant to be used like this, but it’s demo code 🙂 var context = new ApplicationDbContextFactory().CreateDbContext(null); var car = context.Cars.First();
car.Color = "Green"; context.SaveChanges();
}
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 [Display], [TimeStamp]
FROM [dbo].[Inventory]
WHERE @@ROWCOUNT = 1 AND [Id] = @p1;
‘,N’@p1 int,@p0 nvarchar(50),@p2 varbinary(8)’,@p1=2,@p0=N’Green’,@p2=0x0000000000000867
■ Note The previous where clause checked not only the Id column but also the TimeStamp column. This is eF Core using concurrency checking, covered later in this chapter.
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. There are two ways to notify EF Core that this entity needs to be processed as an update. The first is to call the Update() method on the DbSet
context.Cars.Update(updatedCar);
The second is to use the context instance and the Entry() method to set the state to Modified, like this:
context.Entry(updatedCar).State = EntityState.Modified;
Either way, SaveChanges() must still be called for the values to persist.
■ Note you might be wondering when you might update an entity that is not tracked. Think of an aSp.neT Core post call that sends the values for an entity over hTTp. The updated data needs to be persisted, and using this technique negates the need for another call to the database to get the entity into the ChangeTracker.
The following example reads a record in as nontracked (simulating a postback in ASP.NET Core) and changes one property (Color). Then it sets the state as Modified by calling the Update() method on DbSet
static void UpdateRecords()
{
…
var carToUpdate = context.Cars.AsNoTracking().First(x => x.Id == 1); carToUpdate.Color = "Orange";
context.Cars.Update(carToUpdate); context.SaveChanges();
}
Since the entity is not tracked, EF Core updates all property values in the generated SQL:
exec sp_executesql N’SET NOCOUNT ON;
UPDATE [dbo].[Inventory] SET [Color] = @p0, [DateBuilt] = @p1, [IsDrivable] = @p2, [MakeId] = @p3, [PetName] = @p4
WHERE [Id] = @p5 AND [TimeStamp] = @p6; SELECT [Display], [TimeStamp]
FROM [dbo].[Inventory]
WHERE @@ROWCOUNT = 1 AND [Id] = @p5;
‘,N’@p5 int,@p0 nvarchar(50),@p1 datetime2(7),@p2 bit,@p3 int,@p4 nvarchar(50),
@p6 varbinary(8)’,@p5=1,@p0=N’Orange’,@p1=’2021-06-21 01:12:46.5800000′,@p2=1,@p3=5, @p4=N’Hank’,@p6=0x000000000000088F
The next example follows the same logic, but instead of calling the Update() method, the code manually changes the EntityState to Modified and then calls SaveChanges(). The Clear() method is called on the ChangeTracker to make sure there isn’t any crossover from the different execution paths. The generated SQL is the same as the previous example.
static void UpdateRecords()
{
context.ChangeTracker.Clear();
var carToUpdate2 = context.Cars.AsNoTracking().First(x => x.Id == 1); carToUpdate2.Color = "Orange";
context.Entry(carToUpdate2).State = EntityState.Modified; context.SaveChanges();
}
Deleting Records
One or more entities are marked for deletion by calling Remove() (for a single entity) or RemoveRange() (for a list of entities) on the appropriate DbSet
Entity State
When the Remove() method is called on an entity that is being tracked, its EntityState is set to Deleted. After the SaveChanges() method is successfully executed, the entity is removed from the ChangeTracker, and its state is changed to Detached. Note that the entity still exists in your application unless it has gone out of scope and been garbage collected.
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.
static void DeleteRecords()
{
//The factory is not meant to be used like this, but it’s demo code 🙂 var context = new ApplicationDbContextFactory().CreateDbContext(null); ClearSampleData();
LoadMakeAndCarData();
var car = context.Cars.First(x=>x.Color != "Green"); context.Cars.Remove(car);
context.SaveChanges();
}
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=0x00000000000008EB
As a point of emphasis, 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.
static void DeleteRecords()
{
…
Console.WriteLine($"{car.PetName}’s state is {context.Entry(car).State}");
}
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 follows the same pattern for updating nontracked entities. It reads a record in as nontracked and then uses the Remove() method on DbSet
static void DeleteRecords()
{
…
context.ChangeTracker.Clear();
var carToDelete = context.Cars.AsNoTracking().First(x=>x.Color != "Green"); context.Cars.Remove(carToDelete);
context.SaveChanges();
context.ChangeTracker.Clear();
var carToDelete2 = context.Cars.AsNoTracking().First(x=>x.Color != "Green"); context.Entry(carToDelete2).State = EntityState.Deleted; context.SaveChanges();
}
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:
static void DeleteRecords()
{
…
context.ChangeTracker.Clear(); var make = context.Makes.First(); context.Makes.Remove(make);
try
{
context.SaveChanges();
}
catch (DbUpdateException ex)
{
Console.WriteLine(ex.Message);
}
}
This completes the content on Create, Read, Update, and Delete (CRUD) operations using EF Core. The next section covers notable EF Core features that provide benefit to data access code and developer productivity.
Notable EF Core Features
Many features from EF 6 have been replicated in EF Core, with more being added in every release. Many of those features have been greatly improved in their EF Core implementation, both in functionality and
performance. In addition to bringing forward features from EF 6, EF Core has added many new features. The following are some of the more notable features in EF Core (in no particular order).
Global Query Filters
Global query filters enable a where clause to be added into all LINQ queries for a particular entity. For example, a common database design pattern is to use soft deletes instead of hard deletes. A field is added to the table to indicate the deleted status of the record. If the record is “deleted,” the value is set to true (or 1), but not removed from the database. This is called a soft delete. To filter out the soft-deleted records from normal operations, every where clause must check the value of this field. Remembering to include this filter in every query can be time-consuming, if not problematic.
EF Core enables adding a global query filter to an entity that is then applied to every query involving that entity. For the soft delete example described earlier, you set a filter on the entity class to exclude the soft- deleted records. No longer do you have to remember to include the where clause to filter out the soft-deleted records in every query you write.
Presume that all Car records that are not drivable should be filtered out of the normal queries. Open the
CarConfiguration class and add the following line to the Configure() method:
public void Configure(EntityTypeBuilder
{
…
builder.HasQueryFilter(c=>c.IsDrivable == true);
…
}
With the global query filter in place, queries involving the Car entity will automatically filter out the nondrivable cars. Take the following LINQ query that retrieves all cars except those excluded by the query filter:
static void QueryFilters()
{
//The factory is not meant to be used like this, but it’s demo code 🙂 var context = new ApplicationDbContextFactory().CreateDbContext(null); var cars = context.Cars.ToList();
Console.WriteLine($"Total number of drivable cars: {cars.Count}");
}
The generated SQL is as follows:
SELECT [i].[Id], [i].[Color], [i].[DateBuilt], [i].[Display], [i].[IsDrivable], [i].[MakeId], [i].[PetName], [i].[TimeStamp]
FROM [dbo].[Inventory] AS [i]
WHERE [i].[IsDrivable] = CAST(1 AS bit)
■ Note Query filters are not additive. The last one in wins. If you were to add another query filter for the Car
entity, it would replace the existing query filter.
If you need to retrieve all records, including those filtered with the global query filter, add the
IgnoreQueryFilters() method into the LINQ query:
static void QueryFilters()
{
…
var allCars = context.Cars.IgnoreQueryFilters().ToList(); Console.WriteLine($"Total number of cars: {allCars.Count}"); var radios = context.Radios.ToList();
var allRadios = context.Radios.IgnoreQueryFilters().ToList();
}
With the IgnoreQueryFilters() added to the LINQ query, the generated SQL statement no longer has
the where clause excluding the soft-deleted records:
SELECT [i].[Id], [i].[Color], [i].[DateBuilt], [i].[Display], [i].[IsDrivable], [i].[MakeId], [i].[PetName], [i].[TimeStamp]
FROM [dbo].[Inventory] AS [i]
It is important to note that calling IgnoreQueryFilters() removes the query filter for every entity in the LINQ query, including any that are involved in Include() or ThenInclude() statements.
■ Note global query filters are entirely an eF Core construct. no changes are made to the database. even with the filter to exclude nondrivable cars in place, if you open up SSmS/azure Data Studio and run a query to select all Inventory (remember the Car entity is mapped to the Inventory table) records, you would see all records, even the nondrivable records.
Global Query Filters on Navigation Properties
Global query filters can also be set on navigation properties. Suppose you want to filter out any radios that are in a Car that is not drivable. The query filter is created on the Radio entity’s CarNavigation navigation property, like this (using the RadioConfiguration class):
public void Configure(EntityTypeBuilder
{
…
builder.HasQueryFilter(e=>e.CarNavigation.IsDrivable);
…
}
When executing a standard LINQ query, any orders that contain a nondrivable car will be excluded from the result. Here is the LINQ statement and the generated SQL statement:
//C# Code
static void QueryFilters()
{
…
var radios = context.Radios.ToList();
}
/ Generated SQL query /
SELECT [r].[Id], [r].[InventoryId], [r].[HasSubWoofers],
[r].[HasTweeters], [r].[RadioId], [r].[TimeStamp] FROM [Radios] AS [r]
INNER JOIN (
SELECT [i].[Id], [i].[IsDrivable] FROM [dbo].[Inventory] AS [i]
WHERE [i].[IsDrivable] = CAST(1 AS bit)
) AS [t] ON [r].[InventoryId] = [t].[Id] WHERE [t].[IsDrivable] = CAST(1 AS bit)
To remove the query filter, use IgnoreQueryFilters(). The following is the updated LINQ statements and the subsequent generated SQL:
//C# Code
static void QueryFilters()
{
…
var allRadios = context.Radios.IgnoreQueryFilters().ToList();
}
/ Generated SQL query /
SELECT [r].[Id], [r].[InventoryId], [r].[HasSubWoofers], [r].[HasTweeters], [r].[RadioId], [r].[TimeStamp]
FROM [Radios] AS [r]
A word of caution here: EF Core does not detect cyclic global query filters, so use care when adding query filters to navigation properties.
Explicit Loading with Global Query Filters
Global query filters are also in effect when loading related data explicitly. For example, if you wanted to load the Car records for a Make, the IsDrivable filter will prevent nondrivable cars from being loaded into memory. Take the following code snippet as an example:
static void RelatedDataWithQueryFilters()
{
//The factory is not meant to be used like this, but it’s demo code 🙂 var context = new ApplicationDbContextFactory().CreateDbContext(null); var make = context.Makes.First(x => x.Id == 1);
//Get the Cars collection context.Entry(make).Collection(c => c.Cars).Load(); context.ChangeTracker.Clear();
}
By now it should be no surprise that the generated SQL query includes the filter for nondrivable cars.
SELECT [i].[Id], [i].[Color], [i].[DateBuilt], [i].[Display],
[i].[IsDrivable], [i].[MakeId], [i].[PetName], [i].[TimeStamp] FROM [dbo].[Inventory] AS [i]
WHERE ([i].[IsDrivable] = CAST(1 AS bit)) AND ([i].[MakeId] = 1)
There is a slight catch to ignoring query filters when explicitly loading data. The type returned by the Collection() method is CollectionEntry<Make,Car> and does not explicitly implement the IQueryable
static void RelatedDataWithQueryFilters()
{
…
//Get the Cars collection
context.Entry(make).Collection(c => c.Cars).Query().IgnoreQueryFilters().Load();
}
It should come as no surprise now that the SQL generated by EF Core does not include the filter for nondrivable cars:
SELECT [i].[Id], [i].[Color], [i].[DateBuilt], [i].[Display], [i].[IsDrivable], [i].[MakeId], [i].[PetName], [i].[TimeStamp]
FROM [dbo].[Inventory] AS [i] WHERE [i].[MakeId] = 1
The same process applies when using the Reference() method to retrieve data from a reference navigation property. First call Query() and then call IgnoreQueryFilters().
Raw SQL Queries with LINQ
Sometimes getting the correct LINQ statement for a complicated query can be harder than just writing the SQL directly. Or the generated SQL from your LINQ query is suboptimal. Fortunately, EF Core
has a mechanism to allow raw SQL statements to be executed on a DbSet
If the raw SQL statement is nonterminating (e.g., neither a stored procedure, user-defined function, a statement that uses a common table expression, nor ends with a semicolon), then additional LINQ statements can be added to the query. The additional LINQ statements, such as Include(), OrderBy(), or
Where() clauses, will be combined with the original raw SQL call and any global query filters, and the entire
query is executed on the server side.
When using one of the FromSql variants, the query must be written using the data store schema and table name, and not the entity names. FromSqlRaw() will send the string in just as it is written. FromSqlInterpolated() uses C# string interpolation, and each interpolated string is translated in the SQL parameter. You should use the interpolated version whenever you are using variables for the added protection inherent in parameterized queries.
To get the database schema and table name, use the Model property on the derived DbContext. The Model exposes a method called FindEntityType() that returns an IEntityType, which in turn has methods to get the schema and table name. This was used earlier in the chapter to set identity insert for SQL Server. The following code displays the schema and table name:
static void UsingFromSql()
{
//The factory is not meant to be used like this, but it’s demo code 🙂 var context = new ApplicationDbContextFactory().CreateDbContext(null); IEntityType metadata = context.Model.FindEntityType(typeof(Car).FullName); Console.WriteLine(metadata.GetSchema()); Console.WriteLine(metadata.GetTableName());
}
Presuming the global query filter from the previous section is set on the Car entity, the following LINQ statement will get the first inventory record where the Id is one, include the related Make data, and filter out nondrivable cars:
int carId = 1;
var car = context.Cars
.FromSqlInterpolated($"Select * from dbo.Inventory where Id = {carId}")
.Include(x => x.MakeNavigation)
.First();
■ Note Unfortunately, the table and schema name cannot be added to the query using C# string interpolation, as SQl Server doesn’t support the parameterization of those items.
The LINQ to SQL translation engine combines the raw SQL statement with the rest of the LINQ statements and executes the following query:
SELECT TOP(1) .[Id], .[Color], .[DateBuilt], .[Display],
.[IsDrivable], .[MakeId], .[PetName], .[TimeStamp],
[m].[Id], [m].[Name], [m].[TimeStamp] FROM (
Select * from dbo.Inventory where Id = @p0
) AS
INNER JOIN [Makes] AS [m] ON .[MakeId] = [m].[Id]
WHERE .[IsDrivable] = CAST(1 AS bit)’,N’@p0 int’,@p0=1
Know that there are a few rules that must be observed when using raw SQL with LINQ.
• The SQL query must return data for all properties of the entity type.
• The column names must match the properties they are mapped to (an improvement over EF 6 where mappings were ignored).
• The SQL query can’t contain related data.
Projections
In addition to using raw SQL queries with LINQ, view models can be populated with projections. A projection is where another object type is composed at the end of a LINQ query, projecting the data into another datatype. A projection can be a subset of the original data (e.g., getting all the Id values of Car entities that match a where clause) or a custom type, like the CarMakeViewModel.
To get the list of all the primary keys of the Car records, add the following method to your top-level statements:
static void Projections()
{
//The factory is not meant to be used like this, but it’s demo code 🙂 var context = new ApplicationDbContextFactory().CreateDbContext(null); List
}
The SQL generated for this statement is shown here. Notice the global query filter is honored in the projection:
SELECT [i].[Id]
FROM [dbo].[Inventory] AS [i]
WHERE [i].[IsDrivable] = CAST(1 AS bit)
To populate a custom type, use the new keyword in the Select() method. The values of the new type are populated using object initialization, and the LINQ to SQL translation engine takes care of ensuring the navigation properties used for the new type are retrieved from the database. This is done using eager loading, which is covered in detail later in this chapter. Update the Projections() method with the
following code to create a list of CarMakeViewModel entities (note the additional using statement that must be added to the top of your file):
static void Projections()
{
…
var vms = context.Cars.Select(x => new CarMakeViewModel
{
CarId = x.Id, Color = x.Color,
DateBuilt = x.DateBuilt.GetValueOrDefault(new DateTime(2020, 01, 01)), Display = x.Display,
IsDrivable = x.IsDrivable, Make = x.MakeNavigation.Name, MakeId = x.MakeId,
PetName = x.PetName
});
foreach (CarMakeViewModel c in vms)
{
Console.WriteLine($"{c.PetName} is a {c.Make}");
}
}
The SQL generated for this statement is shown next. Notice the inner join to retrieve the Make Name
property and that the global query filter is once again honored:
SELECT [i].[Id], [i].[Color], [i].[DateBuilt], [i].[Display],
[i].[IsDrivable], [m].[Name], [i].[MakeId], [i].[PetName] FROM [dbo].[Inventory] AS [i]
INNER JOIN [Makes] AS [m] ON [i].[MakeId] = [m].[Id]
WHERE [i].[IsDrivable] = CAST(1 AS bit)
Handling Database-Generated Values
In addition to change tracking and the generation of SQL queries from LINQ, a significant advantage to using EF Core over raw ADO.NET is the seamless handling of database-generated values. After adding or updating an entity, EF Core queries for any database-generated data and automatically updates the entity with the correct values. In raw ADO.NET, you would need to do this yourself.
For example, the Inventory table has an integer primary key that is defined in SQL Server as an Identity column. Identity columns are populated by SQL Server with a unique number (from a sequence) when a record is added, and this primary key is not allowed to be updated during normal updates of the record (excluding the special case of having identity insert enabled). Additionally, the Inventory table has a Timestamp column used for concurrency checking. Concurrency checking is covered next, but for now just know that the Timestamp column is maintained by SQL Server and updated on any add or edit action. We also added two columns with default values to the table, DateBuilt and IsDrivable, and one computed column, Display.
The following code adds a new Car to the Inventory table, similar to the code used earlier in the chapter:
static void AddACar()
{
//The factory is not meant to be used like this, but it’s demo code 🙂
var context = new ApplicationDbContextFactory().CreateDbContext(null); var car = new Car
{
Color = "Yellow", MakeId = 1, PetName = "Herbie"
};
context.Cars.Add(car); context.SaveChanges();
}
When SaveChanges() is executed, there are two queries run against the database. The first, shown here, inserts the new record into the table:
–Process the insert
INSERT INTO [Dbo].[Inventory] ([Color], [MakeId], [PetName]) VALUES (N’Yellow’, 1, N’Herbie’);
The second returns the values for the primary key and all other server-generated data. In this case, the query is returning the Id, DateBuilt, Display, IsDrivable, and Timestamp values. EF Core then updates the entity with the server-generated values.
–Return the server maintained values to EF Core
SELECT [Id], [DateBuilt], [Display], [IsDrivable], [TimeStamp] FROM [dbo].[Inventory]
WHERE @@ROWCOUNT = 1 AND [Id] = scope_identity();
■ Note eF Core actually executes parameterized queries, but I have simplified the SQl examples for readability.
When adding a record that assigns values to the properties with defaults, you will see that EF Core does not query for those properties, as the entity already has the correct values. Take the following example code:
static void AddACarWithDefaultsSet()
{
//The factory is not meant to be used like this, but it’s demo code 🙂 var context = new ApplicationDbContextFactory().CreateDbContext(null); var car = new Car
{
Color = "Yellow", MakeId = 1,
PetName = "Herbie", IsDrivable = true,
DateBuilt = new DateTime(2021,01,01)
};
context.Cars.Add(car); context.SaveChanges();
}
When SaveChanges() is executed, the following two SQL statements are run. Notice that only the Id
(primary key), Display, and TimeStamp values are retrieved:
–Insert the values
INSERT INTO [dbo].[Inventory] ([Color], [DateBuilt], [IsDrivable], [MakeId], [PetName]) VALUES (N’Yellow’,’2021-01-01 00:00:00′,1,1, N’Herbie’);
–Get the database managed values SELECT [Id], [Display], [TimeStamp] FROM [dbo].[Inventory]
WHERE @@ROWCOUNT = 1 AND [Id] = scope_identity();
When updating records, the primary key values are already known, so only the nonprimary key fields that are database controlled are returned. Using the previous Car example, only the updated Timestamp value is queried and returned when the record is updated. The following code retrieves a Car record from the database, changes the color, and then saves the updated Car:
static void UpdateACar()
{
//The factory is not meant to be used like this, but it’s demo code 🙂 var context = new ApplicationDbContextFactory().CreateDbContext(null); var car = context.Cars.First(c => c.Id == 1);
car.Color = "White"; context.SaveChanges();
}
The SaveChanges() command executes the following SQL, which first saves the updates and then returns the new Display and TimeStamp values. Once again the SQL is simplified from the parameterized version. Don’t worry about the TimeStamp value in the where clause; that will be explained in the next section.
//Update the Car Record
UPDATE [dbo].[Inventory] SET [Color] = N’White’ WHERE [Id] = 1 AND [TimeStamp] = 0x00000000000007E1;
//Return the updated Display and TimeStamp values to EF Core SELECT [Display], [TimeStamp]
FROM [dbo].[Inventory]
WHERE @@ROWCOUNT = 1 AND [Id] = 1;
This process also works when adding and/or updating multiple items into the database. EF Core knows how to wire up the values retrieved from the database to the correct entities in your collection.
Concurrency Checking
Concurrency issues arise when two separate processes (users or systems) attempt to update the same record at roughly the same time. For example, User 1 and User 2 both get the data for Customer A. User 1 updates the address and saves the change. User 2 updates the credit rating and attempts to save the same record.
If the save for User 2 works, the changes from User 1 will be reverted, since the address was changed after User 2 retrieved the record. Another option is to fail the save for User 2, in which case User 1’s changes are persisted, but User 2’s changes are not.
How this situation is handled depends on the requirements for the application. Solutions range from doing nothing (second update overwrites the first) to using optimistic concurrency (the second update fails) to more complicated solutions such as checking individual fields. Except for the choice of doing nothing (universally considered a bad programming idea), developers need to know when concurrency issues arise so they can be handled appropriately.
Fortunately, many modern databases have tooling to help the development team handle concurrency issues. SQL Server has a built-in data type called timestamp, a synonym for rowversion. If a column is defined with a data type of timestamp, when a record is added to the database, the value for the column
is created by SQL Server, and when a record is updated, the value for the column is updated as well. The value is virtually guaranteed to be unique and is entirely controlled by SQL Server, so you don’t have to do anything but “opt in.”
EF Core can leverage the SQL Server timestamp data type by implementing a Timestamp property on an entity (represented as byte[] in C#). Entity properties defined with the Timestamp attribute or Fluent API designation are added to the where clause when updating or deleting records. Instead of just using the primary key value(s), the generated SQL adds the value of the timestamp property to the where clause, as you saw in the previous example. This limits the results to those records where the primary key and the timestamp values match. If another user (or the system) has updated the record, the timestamp values will not match, and the update or delete statement will not update the record. Here is the previous update example highlighting the query using the Timestamp column:
UPDATE [dbo].[Inventory] SET [Color] = N’White’ WHERE [Id] = 1 AND [TimeStamp] = 0x00000000000007E1;
Databases (such as SQL Server) report the number of records affected when adding, updating, or deleting records. If the database reports a number of records affected that is different than the number of records the ChangeTracker expected to be changed, EF Core throws a DbUpdateConcurrencyException and rolls the entire transaction back. The DbUpdateConcurrencyException contains information for all the records that did not persist, including the original values (when the entity was loaded from the database) and the current values (as the user/system updated them). There is also a method to get the current database values (this requires another call to the server). With this wealth of information, the developer can then handle the concurrency error as the application requires. The following code shows this in action:
static void ThrowConcurrencyException()
{
//The factory is not meant to be used like this, but it’s demo code 🙂 var context = new ApplicationDbContextFactory().CreateDbContext(null); try
{
//Get a car record (doesn’t matter which one) 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}");
//update the car record in the change tracker and then try and save changes car.Color = "Yellow";
context.SaveChanges();
}
catch (DbUpdateConcurrencyException ex)
{
//Get the entity that failed to update var entry = ex.Entries[0];
//Get the original values (when the entity was loaded) PropertyValues originalProps = entry.OriginalValues;
//Get the current values (updated by this code path) PropertyValues currentProps = entry.CurrentValues;
//get the current values from the data store –
//Note: This needs another database call
PropertyValues databaseProps = entry.GetDatabaseValues();
}
}
Connection Resiliency
Transient errors are difficult to debug and more difficult to replicate. Fortunately, many database providers have a built-in retry mechanism for glitches in the database system (tempdb issues, user limits, etc.) that can be leveraged by EF Core. For SQL Server, SqlServerRetryingExecutionStrategy catches errors
that are transient (as defined by the SQL Server team), and if enabled on the derived DbContext through
DbContextOptions, EF Core automatically retries the operation until the maximum retry limit is reached.
For SQL Server, there is a shortcut method that can be used to enable SqlServerRetryingExecutionStrategy with all the defaults. The method used with SqlServerOptions is EnableRetryOnFailure() and is demonstrated here:
public ApplicationDbContext CreateDbContext(string[] args)
{
var optionsBuilder = new DbContextOptionsBuilder
var connectionString = @"server=.,5433;Database=AutoLot50;User Id=sa;Password=P@ssw0rd;"; optionsBuilder.UseSqlServer(connectionString, options => options.EnableRetryOnFailure()); return new ApplicationDbContext(optionsBuilder.Options);
}
The maximum number of retries and the time limit between retries can be configured per the application’s requirements. If the retry limit is reached without the operation completing, EF Core will notify the application of the connection problems by throwing a RetryLimitExceededException. This
exception, when handled by the developer, can relay the pertinent information to the user, providing a better experience.
try
{
Context.SaveChanges();
}
catch (RetryLimitExceededException ex)
{
//A retry limit error occurred
//Should handle intelligently
Console.WriteLine($"Retry limit exceeded! {ex.Message}");
}
When using an execution strategy, explicit transactions must be created within the context of the execution strategy:
static void TransactionWithExecutionStrategies()
{
//The factory is not meant to be used like this, but it’s demo code 🙂 var context = new ApplicationDbContextFactory().CreateDbContext(null); var strategy = context.Database.CreateExecutionStrategy(); strategy.Execute(() =>
{
using var trans = context.Database.BeginTransaction(); try
{
//actionToExecute(); trans.Commit();
}
catch (Exception ex)
{
trans.Rollback();
}
});
}
For database providers that don’t provide a built-in execution strategy, custom execution strategies can also be created. For more information, refer to the EF Core documentation: https://docs.microsoft.com/ en-us/ef/core/miscellaneous/connection-resiliency.
Database Function Mapping
SQL Server functions can be mapped to C# methods and be included in LINQ statements. The C# method is merely a placeholder as the server function gets folded into the generated SQL for the query. Support for
table-valued function mapping has been added in EF Core to the already existing support for scalar function mapping.
EF Core already supports many built-in SQL Server functions. The C# null coalescing operator (??) translates to the SQL Server coalesce function. String.IsNullOrEmpty() translates to a null check and uses the SQL Server len function to check for an empty string.
To see mapping a user-defined function in action, create a user-defined function that returns the number of Car records based on MakeId:
CREATE FUNCTION udf_CountOfMakes ( @makeid int ) RETURNS int
AS BEGIN
DECLARE @Result int
SELECT @Result = COUNT(makeid) FROM dbo.Inventory WHERE makeid = @makeid RETURN @Result
END GO
To use this in C#, create a new function in the derived DbContext class. The C# body of this function is never executed; it’s merely a placeholder that is mapped to the SQL Server function. Note that this method can be placed anywhere, but it is usually placed in the derived DbContext class for discoverability:
[DbFunction("udf_CountOfMakes", Schema = "dbo")] public static int InventoryCountFor(int makeId)
=> throw new NotSupportedException();
This function can now be used in LINQ queries and becomes part of the generated SQL. To see this in action, add the following code to your top-level statements:
static void UsingMappedFunctions()
{
//The factory is not meant to be used like this, but it’s demo code 🙂 var context = new ApplicationDbContextFactory().CreateDbContext(null); var makes = context.Makes
.Where(x=>ApplicationDbContext.InventoryCountFor(x.Id)>1).ToList();
}
When this code is executed, the following SQL is executed:
SELECT [m].[Id], [m].[Name], [m].[TimeStamp] FROM [Makes] AS [m]
WHERE [dbo].udf_CountOfMakes > 1
EF Core also supports mapping table-valued functions. Add the following function to your database:
CREATE FUNCTION udtf_GetCarsForMake ( @makeId int ) RETURNS TABLE
AS RETURN (
— Add the SELECT statement with parameter references here
SELECT Id, IsDrivable, DateBuilt, Color, PetName, MakeId, TimeStamp, Display FROM Inventory WHERE MakeId = @makeId
) GO
Add the following code to your ApplicationDbContext class:
[DbFunction("udtf_GetCarsForMake", Schema = "dbo")] public IQueryable
=> FromExpression(()=>GetCarsFor(makeId));
The FromExpression() call allows the function to be called directly on the derived DbContext instead of using a regular DbSet
static void UsingMappedFunctions()
{
//The factory is not meant to be used like this, but it’s demo code 🙂 var context = new ApplicationDbContextFactory().CreateDbContext(null);
var makes = context.Makes.Where(x=>ApplicationDbContext.InventoryCountFor(x.Id)>1). ToList();
var cars = context.GetCarsFor(1).ToList();
}
The following SQL gets executed (notice that the global query filter is honored when using the database function):
exec sp_executesql N’SELECT [u].[Id], [u].[Color], [u].[DateBuilt],
[u].[Display], [u].[IsDrivable], [u].[MakeId], [u].[PetName], [u].[TimeStamp]
FROM [dbo].[udtf_GetCarsForMake](@ makeId_1) AS [u] WHERE [u].[IsDrivable] = CAST(1 AS bit)’,
N’@ makeId_1 int’,@ makeId_1=1
For more information on database function mapping, consult the documentation: https://docs. microsoft.com/en-us/ef/core/querying/user-defined-function-mapping.
EF.Functions
The static EF class was created as a placeholder for CLR methods that get translated to database functions, using the same mechanism as database function mapping covered in the previous section. The main difference is that all the implementation details are handled by the database providers. Table 22-1 lists the functions available.
Table 22-1. Functions Available Through EF.Functions
Function Meaning in Life
Like() An implementation of the SQL LIKE operation. Case sensitivity and syntax are dependent on the database. For SQL Server, the comparison is not case sensitive, and the wildcard operator (%) must be provided.
Random() Introduced in EF Core 6, this returns a pseudorandom number between 0 and 1, inclusive. Maps to SQL Server’s RAND function.
Collate
Contains() Maps to the SQL Server CONTAINS function. Table must be full-text indexed to use Contains().
FreeText() Maps to the server FREETEXT store function. Table must be full-text indexed to use FreeText().
DataLength() Returns number of bytes used to represent an expression.
(continued)
Table 22-1. (continued)
Function Meaning in Life
DateDiffYear() DateDiffMonth() DateDiffWeek() DateDiffDay() DateDiffHour() DateDiffMinute() DateDiffSecond() DateDiffMillisecond() DateDiffMicrosecond() DateDiffNansecond() Counts the number time interval of boundaries crossed between start date and end date. Maps to SQL Server’s DATEDIFF function.
DateFromParts() Initializes a new instance of the DateTime structure for specified year, month, day. Maps to SQL Server’s DATEFROMPARTS function.
DateTime2FromParts() Initializes a new instance of the DateTime structure for specified year, month, day, hour, minute, second, fractions, and precision. Maps to SQL Server’s DATETIME2FROMPARTS function.
DateTimeFromParts() Initializes a new instance of the DateTime structure for specified year, month, day, hour, minute, second,
millisecond. Maps to SQL Server’s DATETIMEFROMPARTS
function.
DateTimeOffsetFromParts() Initializes a new instance of the DateTimeOffset structure for specified year, month, day, hour, minute, second, fractions, hourOffset, minuteOffset, and precision. Maps to SQL Server’s DATETIMEOFFSETFROMPARTS function.
SmallDateTimeFromParts() Initializes a new instance of the DateTime structure to the specified year, month, day, hour, and minute. Corresponds to the SQL Server’s SMALLDATETIMEFROMPARTS function.
TimeFromParts() Initializes a new instance of the TimeSpan structure for the specified hour, minute, second, fractions, and
precision. Maps to SQL Server’s TIMEFROMPARTS function.
IsDate() Validate if a given string is a date. Maps to SQL Server’s
ISDATE().
To demonstrate using the EF.Functions.Like method, add the following local function into your top- level statements:
static void UseEFFunctions()
{
Console.WriteLine("Using Like");
//The factory is not meant to be used like this, but it’s demo code 🙂 var context = new ApplicationDbContextFactory().CreateDbContext(null);
//Same as contains
var cars = context.Cars.IgnoreQueryFilters().Where(x=>EF.Functions.Like(x. PetName,"%Clunk%")).ToList();
foreach (var c in cars)
{
Console.WriteLine($"{c.PetName} was found");
}
//Same as Starts with
cars = context.Cars.IgnoreQueryFilters().Where(x=>EF.Functions.Like(x.PetName,"Clun%")). ToList();
foreach (var c in cars)
{
Console.WriteLine($"{c.PetName} was found");
}
//Same as Ends with
cars = context.Cars.IgnoreQueryFilters().Where(x=>EF.Functions.Like(x.PetName,"%er")). ToList();
foreach (var c in cars)
{
Console.WriteLine($"{c.PetName} was found");
}
}
Notice that the SQL Server wildcard character (%) is used just like in a T-SQL query.
Batching of Statements
EF Core has significantly improved the performance when saving changes to the database by executing the statements in one or more batches. This decreases trips between the application and the database, increasing performance and potentially reducing cost (e.g., for cloud databases where customers are charged by the transaction).
EF Core batches the create, update, and delete statements using table-valued parameters. The number of statements that EF batches depends on the database provider. For example, for SQL Server, batching
is inefficient below 4 statements and above 40, so EF Core will max out the number of statements at 42. Regardless of the number of batches, all statements still execute in a transaction. The batch size can also be configured through DbContextOptions, but the recommendation is to let EF Core calculate the batch size for most (if not all) situations.
If you were to insert four cars in one transaction like this:
static void Batching()
{
//The factory is not meant to be used like this, but it’s demo code 🙂 var context = new ApplicationDbContextFactory().CreateDbContext(null); var cars = new List
{
new Car { Color = "Yellow", MakeId = 1, PetName = "Herbie" }, new Car { Color = "White", MakeId = 2, PetName = "Mach 5" }, new Car { Color = "Pink", MakeId = 3, PetName = "Avon" },
new Car { Color = "Blue", MakeId = 4, PetName = "Blueberry" },
};
context.Cars.AddRange(cars); context.SaveChanges();
}
EF Core would batch up the statements into a single call. The query generated 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, 0),
(@p3, @p4, @p5, 1),
(@p6, @p7, @p8, 2),
(@p9, @p10, @p11, 3)) AS i ([Color], [MakeId], [PetName], _Position) ON 1=0 WHEN NOT MATCHED THEN
INSERT ([Color], [MakeId], [PetName]) VALUES (i.[Color], i.[MakeId], i.[PetName]) 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 int,@p5 nvarchar(50), @p6 nvarchar(50),@p7 int,@p8 nvarchar(50),@p9 nvarchar(50),@p10 int,@p11 nvarchar(50)’, @p0=N’Yellow’,@p1=1,@p2=N’Herbie’,@p3=N’White’,@p4=2,@p5=N’Mach 5′,@p6=N’Pink’,@p7=3, @p8=N’Avon’,@p9=N’Blue’,@p10=4,@p11=N’Blueberry’
Value Converters
Value converters are used to automatically convert data when retrieved and saved to the database. EF Core ships with a long list of built-in value converters (you can see the complete list here: https://docs. microsoft.com/en-us/dotnet/api/microsoft.entityframeworkcore.storage.valueconversion). In
addition to the built-in value converters, you can also create your own. For example, you can store the price of a Car in the database as a numeric value but display the price as a currency string.
Table 22-2 lists many of the built-in value converters available in EF Core. In addition to the converters that ship with EF Core, you can create your own.
Table 22-2. Some of the EF Core Value Converters
Value Converter Meaning in Life
BoolToStringConverter Converts Boolean values to and from two string values.
BytesToStringConverter Converts arrays of bytes to and from strings.
CharToStringConverter Converts a Char to and from a single-character String.
DateTimeOffsetToBinaryConverter Converts DateTime to and from binary representation into a long. The DateTime is truncated beyond 0.1 millisecond precision.
DateTimeOffsetToBytesConverter Converts DateTime to and from arrays of bytes.
DateTimeOffsetToStringConverter Converts DateTimeOffset to and from strings.
DateTimeToBinaryConverter Converts DateTime using ToBinary(). This will preserve the
DateTimeKind.
DateTimeToStringConverter Converts DateTime to and from strings.
DateTimeToTicksConverter Converts DateTime to and from Ticks.
EnumToNumberConverter<TEnum,TNumber> Converts enum values to and from their underlying numeric representation.
EnumToStringConverter
GuidToStringConverter Converts a Guid to and from a String using the standard “8-4- 4-4-12” format.
NumberToStringConverter
StringToBoolConverter Converts strings to and from Boolean values.
StringToBytesConverter Converts strings to and from arrays of bytes.
StringToCharConverter Converts strings to and from Char values.
StringToDateTimeConverter Converts strings to and from DateTime values.
StringToDateTimeOffsetConverter Converts strings to and from DateTimeOffset values.
StringToEnumConverter
StringToGuidConverter Converts strings to and from a Guid using the standard “8-4-4- 4-12” format.
StringToNumberConverter
StringToTimeSpanConverter Converts strings to and from TimeSpan values.
StringToUriConverter Converts strings to and from Uri values.
TimeSpanToStringConverter Converts TimeSpan to and from strings.
TimeSpanToTicksConverter Converts TimeSpan to and from Ticks.
UriToStringConverter Converts a Uri to and from a String.
ValueConverter Defines conversions from an object of one type in a model to an object of the same or different type in the store.
Many of the built-in value converters don’t require any configuration. For many situations, EF Core will automatically convert from a C# bool to a SQL Server bit data type without any intervention on your part.
■ Note Value converters are set on the model. while they allow mapping disparate data types between your model and the database, the converters will be used only when using eF Core to query the database. Querying the database directly will return the database type, and not the converted type.
For example, to return a string value from the database instead of a decimal, start by updating the Car
class to have a string Price property:
public class Car : BaseEntity
{
…
public string Price { get; set; }
…
}
If you are following along, make sure to update the database by creating and applying a migration:
dotnet ef migrations add Price -o Migrations -c AutoLot.Samples.ApplicationDbContext dotnet ef database update Price -c AutoLot.Samples.ApplicationDbContext
Add the following global using statements into the GlobalUsings.cs file:
global using Microsoft.EntityFrameworkCore.Storage.ValueConversion; global using System.Globalization;
Next, add code into the CarConfiguration class’s Configure() method to use the built-in
StringToNumberConverter, like this:
public void Configure(EntityTypeBuilder
{
…
builder.Property(p=>p.Price).HasConversion(new StringToNumberConverter
…
}
If you want more control over the values, for example to format the price as currency, you can create a custom converter. Update the value conversion in the CarConfiguration class to the following:
public void Configure(EntityTypeBuilder
{
…
CultureInfo provider = new CultureInfo("en-us");
NumberStyles style = NumberStyles.Number | NumberStyles.AllowCurrencySymbol; builder.Property(p => p.Price)
.HasConversion(
v => decimal.Parse(v, style, provider),
v => v.ToString("C2"));
…
}
The first parameter is converting the values going into the database, and the second parameter is formatting the data coming out of the database.
Adding the Price column breaks the table-valued function from earlier in this chapter. To correct this, update the function to include the new column:
ALTER FUNCTION udtf_GetCarsForMake ( @makeId int ) RETURNS TABLE
AS
RETURN (
— Add the SELECT statement with parameter references here
SELECT Id, IsDrivable, DateBuilt, Color, PetName, MakeId, TimeStamp, Display, Price
FROM Inventory WHERE MakeId = @makeId
)
For more information on value conversion, please read the documentation at https://docs. microsoft.com/en-us/ef/core/modeling/value-conversions.
Shadow Properties
Shadow properties are properties that aren’t explicitly defined on your model but exist due to EF Core. The value and state of these properties are maintained entirely by the Change Tracker. One example of the use of shadow properties is to represent foreign keys for navigation properties if the foreign key isn’t defined as part of the entity. Another example is with temporal tables, discussed next.
Shadow properties that aren’t added to an entity by EF Core can be defined only through the Fluent API using the Property() method. If the name of the property passed into the Property() method matches an existing property for the entity (either a previously defined shadow property or an explicit property), the Fluent API code configures the existing property. Otherwise, a shadow property is created for the entity. To add a shadow property of type bool? named IsDeleted to the Car entity with the default value of true, add the following code to the Configure() method of the CarConfiguration class:
public void Configure(EntityTypeBuilder
{
builder.Property<bool?>("IsDeleted").IsRequired(false).HasDefaultValue(true);
//omitted for brevity
}
Shadow properties can be accessed only through the Change Tracker, so they must be loaded from the database as tracked entities. For example, the following code will not compile:
static void ShadowProperties()
{
Console.WriteLine("Shadow Properties");
//The factory is not meant to be used like this, but it’s demo code 🙂 var context = new ApplicationDbContextFactory().CreateDbContext(null); var newCar = new Car
{
Color = "Blue",
PetName = "TestRecord",
MakeId = context.Makes.First().Id,
//Can’t do this (compile error):
//IsDeleted = false
};
}
Once the record is added to the Change Tracker, though, the IsDeleted property can be accessed:
static void ShadowProperties()
{
Console.WriteLine("Shadow Properties");
//omitted for brevity context.Cars.Add(newCar);
context.Entry(newCar).Property("IsDeleted").CurrentValue = true;
}
Shadow properties can also be accessed in LINQ queries. The following code first loads all the Car records into the Change Tracker and then sets IsDeleted = false for every other record. After updating the database, the next LINQ query retrieves all of the deleted cars:
static void ShadowProperties()
{
Console.WriteLine("Shadow Properties");
//The factory is not meant to be used like this, but it’s demo code 🙂 var context = new ApplicationDbContextFactory().CreateDbContext(null);
//omitted for brevity
var cars = context.Cars.ToList();
foreach (var c in cars.Where(c=>c.Id % 2 == 0))
{
context.Entry(c).Property("IsDeleted").CurrentValue = false;
}
context.SaveChanges();
var nonDeletedCars = context.Cars.Where(c=> !EF.Property
{
Console.WriteLine($"{c.PetName} is deleted? {context.Entry(c).Property("IsDeleted"). CurrentValue}");
}
}
Adding the IsDeleted column also breaks the table-valued function from earlier in this chapter. Update the function to include the new column:
ALTER FUNCTION udtf_GetCarsForMake ( @makeId int ) RETURNS TABLE
AS
RETURN (
— Add the SELECT statement with parameter references here
SELECT Id, IsDrivable, DateBuilt, Color, PetName, MakeId, TimeStamp, Display, Price, IsDeleted
FROM Inventory WHERE MakeId = @makeId
)
SQL Server Temporal Table Support
SQL Server temporal tables automatically keep track of all data ever stored in the table. This is accomplished using a history table into which a timestamped copy of the data is stored whenever a change or deletion is made to the main table. Historical data is then available for querying, auditing, or restoring. EF Core 6 has added support for creating temporal tables, converting normal tables to temporal tables, querying historical data, and restoring data from a point in time.
Configure Temporal Tables
To add default temporal table support, use the ToTable() Fluent API method. This method can also be used to specify the table name and schema, but in our example, it’s not needed due to the Table attribute on the Car entity. Update the CarConfiguration class’s Configure() method to add a ToTable() call:
public void Configure(EntityTypeBuilder
{
//specify table name and schema – not needed because of the Table attribute
//builder.ToTable("Inventory", "dbo", b=> b.IsTemporal()); builder.ToTable(b=> b.IsTemporal());
//omitted for brevity
}
■ Note Split tables are not supported for use as temporal tables. This includes any tables generated from entities using Owned entities.
After creating a new EF Core migration and updating the database, the Inventory table is converted to a system-versioned temporal table with two additional datetime2 columns, PeriodEnd and PeriodStart. This also creates a history table named
The names for the additional columns and the table (as well as the schema for the history table) can be controlled in the IsTemporal() method. The following example uses the names ValidFrom and ValidTo for PeriodStart and PeriodEnd, respectively; names the table InventoryAudit; and places the table in the audits schema:
builder.ToTable(b => b.IsTemporal(t =>
{
t.HasPeriodEnd("ValidTo"); t.HasPeriodStart("ValidFrom"); t.UseHistoryTable("InventoryAudit", "audits");
}));
■ Note at the time of this writing, neither eF Core nor SQl Server will create the schema for the history table if the schema doesn’t exist. make sure you are using an existing schema or update the migration to ensure the schema exists by adding the following:migrationBuilder.EnsureSchema("audits");
After this change is migrated to the database, when you examine the tables using Azure Data Studio (or SSMS), you will see the Inventory table is labeled as System-Versioned, and when you expand the node, you will see the audits.InventoryAudit table. The InventoryAudit table is marked as History, as shown in Figure 22-1.
Figure 22-1. The Inventory and InventoryAudit tables
If you expand the Columns node, you will see the two new fields added to the table, and expanding the
InventoryAudit table, you will see that the table is a clone of the Inventory table, as shown in Figure 22-2.
Figure 22-2. The Inventory and InventoryAudit columns
An important note regarding temporal tables: regular queries will not return the timestamp fields as EF Core configures them as HIDDEN. If you execute the following query, you will see only the regular fields returned:
SELECT * FROM dbo.Inventory
To return the new fields, they must be explicitly specified, like this:
SELECT *,ValidFrom, ValidTo FROM dbo.Inventory
When using LINQ to query the table, EF Core includes the timestamp properties in the query. For example, the following query works as expected without any modification to the Car class:
var c = context.Cars.First();
Notice in the generated SQL the inclusion of the two timestamp columns:
SELECT TOP(1) [i].[Id], [i].[Color], [i].[DateBuilt], [i].[Display], [i].[IsDeleted],
[i].[IsDrivable], [i].[MakeId], [i].[PetName], [i].[Price], [i].[TimeStamp],
[i].[ValidFrom], [i].[ValidTo]
FROM [dbo].[Inventory] AS [i]
WHERE [i].[IsDrivable] = CAST(1 AS bit)
EF Core adds the additional columns onto your entity as shadow properties. As demonstrated in the previous section, shadow properties exist as columns in the database table, but are not explicitly defined on the entity.
The problem happens when you are instantiating classes using FromSqlRaw()/FromSqlInterpolated(), stored procedures, or user-defined functions. As you have learned when populating entities by any means other than LINQ, the query must return every property that is expected. Even though the ValidFrom and ValidTo properties are not defined on the Car entity, EF Core still expects them to be returned.
This breaks some previous code in this chapter. To fix it up, first go back to the UsingFromSql() local function and update the FromSqlInterpolated() call to the following:
var car = context.Cars
.FromSqlInterpolated($"Select *,ValidFrom,ValidTo from dbo.Inventory where Id = {carId}")
.Include(x => x.MakeNavigation)
.First();
Next, update the table-valued function udtf_GetCarsForMake() to the following:
ALTER FUNCTION udtf_GetCarsForMake ( @makeId int ) RETURNS TABLE
AS RETURN (
— Add the SELECT statement with parameter references here SELECT Id, IsDrivable, DateBuilt, Color, PetName, MakeId,
TimeStamp, Display, Price, IsDeleted, ValidFrom, ValidTo
FROM Inventory WHERE MakeId = @makeId
)
Main Table and History Table Interactions
Because the PeriodStart and PeriodEnd column values are maintained by SQL Server, you can continue to use the Car entity as you did prior to adding in the temporal table support. Updates and deletions always
target the main table, and queries that don’t reference the shadow properties retrieve the current state of the table. While there doesn’t appear to be any differences between a nontemporal and a temporal table when used with normal LINQ-based CRUD operations, behind the scenes there is constant interaction with the history table.
To see this in action, the following local function adds, updates, and then deletes a record from the
Inventory table using normal EF Core interactions:
static void TemporalTables()
{
Console.WriteLine("Temporal Tables");
//The factory is not meant to be used like this, but it’s demo code 🙂 var context = new ApplicationDbContextFactory().CreateDbContext(null);
//ensure there is at least one Make record var make = new Make { Name = "Honda" }; context.Makes.Add(make); context.SaveChanges();
//create a Car record to work with var car = new Car
{
Color = "LightBlue",
MakeId = context.Makes.First().Id, PetName = "Sky",
IsDrivable = true,
DateBuilt = new DateTime(2021, 01, 01)
};
context.Cars.Add(car); context.SaveChanges(); car.Color = "Cloudy"; context.Cars.Update(car); context.SaveChanges(); context.Cars.Remove(car); context.SaveChanges();
}
Each insert, edit, or deletion is tracked in the history table. When a record is inserted into the table, the ValidFrom value is set to the time the insertion transaction started, and the ValidTo is set to the max date for a datetime2, which is 12/31/9999 23:59:59.9999999. Nothing exists in the history table.
■ Note all times are stored in UTC time and are recorded at the start of the transaction. Therefore, all rows in a transaction will have the same time recorded in their appropriate tracking fields.
If you step through the code and pause right after the SaveChanges() call that inserted the new Car entity, you can execute the following queries in Azure Data Studio (or SSMS) to verify that there is a record in the Inventory table and nothing in the InventoryAudit table, as well as examine the ValidFrom and ValidTo values:
SELECT ,ValidFrom,ValidTo FROM dbo.Inventory SELECT FROM audits.InventoryAudit
■ Note The history table does not hide the timestamp fields, so using the SELECT * syntax will retrieve all the columns. It’s only the main table that hides the timestamp fields.
When the record is updated, a copy of the record to be updated is added into the history table (prior to the update statement), and the ValidTo value is set to the start of the transaction. In the main table, the ValidFrom value is set to the time of the update transaction started, and the ValidTo is set to the max date. If you query the tables after the SaveChanges() call that updates the Car record, you can see the two records, one in each table.
When a record is deleted, a copy of the record to be deleted is added into the history table (prior to the delete statement), and the ValidTo value is set to the start of the transaction. In the main table, the record is simply deleted. Now when you query the two tables, you will see there isn’t any data in the Inventory table and two records in the InventoryAudit table.
Querying Temporal Tables
The previous example showed querying both the Inventory and InventoryAudit tables. While this showed the interactions between the two tables, there isn’t any need to query the history table. SQL Server added the FOR SYSTEM_TIME clause that uses the main table and the history table to reconstruct the state of the data at the time(s) specified. There are five subclauses that can be used with the FOR SYSTEM_TIME clause, and they are listed in Table 22-3.
Table 22-3. For System Time Subclauses When Querying Temporal Tables (T-SQL)
Sub Clause Qualifying Rows Meaning in Life
AS OF
FROM
BETWEEN
CONTAINED IN (<start datetime>, <end datetime>) PeriodFrom >= start date_time and PeriodTo
= end_date_time Returns all rows that were active only within the specified time range. Note that the boundaries are inclusive.
ALL Returns all records.
The FOR SYSTEM_TIME clause filters out records that have a period of validity that is zero (PeriodFrom = PeriodTo). To make our example a little more meaningful, add some Thread.Sleep() calls after each of the SaveChanges() calls:
static void TemporalTables()
{
..//omitted for brevity context.Cars.Add(car); context.SaveChanges(); Thread.Sleep(5000); car.Color = "Cloudy"; context.Cars.Update(car); context.SaveChanges(); Thread.Sleep(5000); context.Cars.Remove(car); context.SaveChanges();
}
EF Core 6 has added query operators that are translated by the SQL Server provider into FOR SYSTEM_ TIME and the subclauses listed in Table 22-3. Table 22-4 shows the new operators.
Table 22-4. EF Core Support for the System Time Subclauses When Querying Temporal Tables
Sub Clause Translated To Meaning in Life
TemporalAsOf() AS OF
TemporalFromTo() FROM
TemporalBetween() BETWEEN
TemporalContainedIn() CONTAINED IN (
TemporalAll() ALL Returns all records.
Remember that PeriodStart and PeriodEnd (ValidFrom and ValidTo in our example) are shadow properties, and not defined in the Car entity. Therefore, the following query won’t work:
//This doesn’t work with shadow properties
var cars = context.Cars.TemporalAll().OrderBy(e => e.ValidFrom);
Instead, you need to use the EF.Property<>() method to access the shadow properties:
var cars = context.Cars.TemporalAll().OrderBy(e => EF.Property
It is important to note that if you want the historical from and to dates, you must retrieve those explicitly in your query using the EF.Property<>() method. The following LINQ statement that returns all the current and historical data, using a projection to capture each row and its time values:
var cars = context.Cars
.TemporalAll()
.OrderBy(e => EF.Property
.Select(
e => new
{
Car = e,
ValidFrom = EF.Property
});
foreach (var c in cars)
{
Console.WriteLine(
$"{c.Car.PetName} was painted {c.Car.Color} was active from {c.ValidFrom} to {c.ValidTo}");
}
When you examine the generated SQL, you can see that the call uses the FOR SYSTEM_TIME clause with the ALL subclause:
SELECT [i].[Id], [i].[Color], [i].[DateBuilt], [i].[Display], [i].[IsDeleted], [i].[IsDrivable],
[i].[MakeId], [i].[PetName], [i].[Price], [i].[TimeStamp], [i].[ValidFrom], [i].[ValidTo] FROM [dbo].[Inventory] FOR SYSTEM_TIME ALL AS [i]
WHERE [i].[IsDrivable] = CAST(1 AS bit) ORDER BY [i].[ValidFrom]
As a final note on querying temporal tables, all queries using one of the temporal operators are nontracking queries. If you wanted to restore a record that was deleted, for example, you would use one of the temporal operators to get the historical record and call Add() on the DbSet
Clearing Temporal Tables
At this point, you might be wondering how you completely clear temporal tables. The short answer is that you can’t, not without removing the versioning, clearing the historical data, and then adding the versioning back in. When versioning is turned off, the history table and the main table become disassociated. Then you can delete all records from the main table (which no longer records the history) and the history table;
then you can turn versioning back on, and the tables will be re-associated. To do this in Azure Data Studio or SSMS, enter the following commands:
ALTER TABLE [dbo].[Inventory]
SET (SYSTEM_VERSIONING = OFF)
DELETE FROM [dbo].[Inventory]
DELETE FROM [audits].[InventoryAudit] ALTER TABLE [dbo].[Inventory]
SET (SYSTEM_VERSIONING = ON (HISTORY_TABLE=audits.InventoryAudit))
If the history table has a custom name (like audits.InventoryAudit), it must be specified when turning versioning back on, or a new history table will be created.
To clear the history table using EF Core, the same statements are executed in an explicit transaction using the ExecuteSqlRaw() command on the context’s database façade:
var strategy = context.Database.CreateExecutionStrategy(); strategy.Execute(() =>
{
using var trans = context.Database.BeginTransaction(); context.Database.ExecuteSqlRaw($"ALTER TABLE dbo.Inventory SET (SYSTEM_VERSIONING = OFF)"); context.Database.ExecuteSqlRaw($"DELETE FROM audits.InventoryHistory"); context.Database.ExecuteSqlRaw($"ALTER TABLE dbo.Inventory SET (SYSTEM_VERSIONING = ON (HISTORY_TABLE={historySchema}.{historyTable}))");
trans.Commit();
});
Making this more generic is a bit more complicated. There is a method (IsTemporal()) on the entity that checks if a table is temporal, and two methods to get the history table name (GetHistoryTableName()) and schema (GetHistoryTableSchema()). While IsTemporal() works at runtime, the methods to get the table name and schema do not work against the runtime model. The runtime model contains just what is needed for EF Core (and your code) to execute, while the design-time model contains everything. To use these methods, you have to get an instance of the design-time model at runtime.
To enable access to the design time model at runtime, the project file must be updated. The Microsoft.
EntityFrameworkCore.Design package is a DevelopmentDependency package. This means that the dependency won’t flow into other projects, and you can’t, by default, reference its types. To reference its types in your code, update the package metadata in the project file by removing the
To get the design-time model, create a new ServiceCollection and add the DbContextDesignTimeServices.
After building the service provider, you can then get an instance of the design-time model:
var serviceCollection = new ServiceCollection(); serviceCollection.AddDbContextDesignTimeServices(context);
var serviceProvider = serviceCollection.BuildServiceProvider(); var designTimeModel = serviceProvider.GetService
■ Note If you are not familiar with the ServiceCollection, it is used by .neT Core for dependency injection. Dependency injection will be covered in depth in the aSp.neT Core chapters, later in this book.
With this in place, you can update the calls to clear the historical data as follows:
var strategy = context.Database.CreateExecutionStrategy(); strategy.Execute(() =>
{
using var trans = context.Database.BeginTransaction();
var designTimeEntity = designTimeModel.FindEntityType(entityName); var historySchema = designTimeEntity.GetHistoryTableSchema();
var historyTable = designTimeEntity.GetHistoryTableName(); context.Database.ExecuteSqlRaw(
$"ALTER TABLE {schemaName}.{tableName} SET (SYSTEM_VERSIONING = OFF)");
context.Database.ExecuteSqlRaw($"DELETE FROM {historySchema}.{historyTable}"); context.Database.ExecuteSqlRaw(
$"ALTER TABLE {schemaName}.{tableName} SET (SYSTEMVERSIONING = ON (HISTORY
TABLE={historySchema}.{historyTable}))");
trans.Commit();
});
Here is the updated ClearSampleData() method in its entirety:
static void ClearSampleData()
{
//The factory is not meant to be used like this, but it’s demo code 🙂 var context = new ApplicationDbContextFactory().CreateDbContext(null); var entities = new[]
{
typeof(Car).FullName, typeof(Make).FullName,
};
var serviceCollection = new ServiceCollection(); serviceCollection.AddDbContextDesignTimeServices(context);
var serviceProvider = serviceCollection.BuildServiceProvider(); var designTimeModel = serviceProvider.GetService
foreach (var entityName in entities)
{
var entity = context.Model.FindEntityType(entityName); var tableName = entity.GetTableName();
var schemaName = entity.GetSchema(); context.Database.ExecuteSqlRaw($"DELETE FROM {schemaName}.{tableName}");
context.Database.ExecuteSqlRaw($"DBCC CHECKIDENT (\"{schemaName}.{tableName}\", RESEED, 0);");
if (entity.IsTemporal())
{
var strategy = context.Database.CreateExecutionStrategy(); strategy.Execute(() =>
{
using var trans = context.Database.BeginTransaction();
var designTimeEntity = designTimeModel.FindEntityType(entityName); var historySchema = designTimeEntity.GetHistoryTableSchema();
var historyTable = designTimeEntity.GetHistoryTableName(); context.Database.ExecuteSqlRaw(
$"ALTER TABLE {schemaName}.{tableName} SET (SYSTEM_VERSIONING = OFF)");
context.Database.ExecuteSqlRaw($"DELETE FROM {historySchema}.{historyTable}");
context.Database.ExecuteSqlRaw($"ALTER TABLE {schemaName}.{tableName} SET (SYSTEM_ VERSIONING = ON (HISTORY_TABLE={historySchema}.{historyTable}))");
trans.Commit();
});
}
}
}
Summary
This chapter began with a long look at create, read, update, and delete (CRUD) operations using EF Core and then explored several EF Core features to help with developer productivity.
Now that you have a solid foundation for how EF Core works, the next chapter will build the AutoLot
data access layer.