It is easy to insert, update, and delete entities using EF Core.
At the bottom of the Main method, after the foreach statement, add the following code to insert a new product and relist all products:
var newProduct = new Product
{
CategoryID = 6, // Meat & Poultry
ProductName = "Bob's Burger",
UnitPrice = 500M
};
// mark product as added in change tracking
db.Products.Add(newProduct);
// save tracked changes to database
db.SaveChanges();
foreach (var item in db.Products)
{
WriteLine($"{item.ProductID}: {item.ProductName} costs
{item.UnitPrice:$#,##0.00}");
}
Rerun the application and enter 50. You will see that the product has been inserted:
78: Bob's Burger costs $500.00
Add the following code to increase the price of the first product with a name that begins with "Bob" by $20 and then relist the products:
Product updateProduct = db.Products.First(
p => p.ProductName.StartsWith("Bob"));
updateProduct.UnitPrice += 20M;
db.SaveChanges();
foreach (var item in db.Products)
{
WriteLine($"{item.ProductID}: {item.ProductName} costs
{item.UnitPrice:$#,##0.00}");
}
Rerun the application and notice that the existing entity for Bob's Burgers has increased in price by $20:
78: Bob's Burger costs $520.00
Add the following code to delete the first product with a name that begins with "Bob" and then relist the products:
Product deleteProduct = db.Products.First(
p => p.ProductName.StartsWith("Bob"));
db.Products.Remove(deleteProduct);
db.SaveChanges();
foreach (var item in db.Products)
{
WriteLine($"{item.ProductID}: {item.ProductName} costs
{item.UnitPrice:$#,##0.00}");
}
Every time you call the SaveChanges method, an implicit transaction is started so that if something goes wrong, it would automatically rollback all the changes. If every operation succeeds, then the transaction is committed.
Transactions maintain the integrity of your database by applying locks to prevent reads and writes while a sequence of operations is occurring.
Transactions are ACID, which is explained here:
- A is for atomic. Either all the operations in the transaction commit or none of them do.
- C is for consistent. The state of the database before and after a transaction is consistent. This is dependent on your code logic.
- I is for isolated. During a transaction, changes are hidden from other processes. There are multiple isolation levels that you can pick from (see the following table). The stronger the level, the better the integrity of the data. However, more locks must be applied, which will negatively affect other processes. Snapshot is a special case because it creates multiple copies of rows to avoid locks, but this will increase the size of your database while transactions occur.
- D is for durable. If a failure occurs during a transaction, it can be recovered. The opposite of durable is volatile.
|
Isolation level |
Lock(s) |
Integrity problems allowed |
|
|
None |
Dirty reads, non-repeatable reads, and phantom data |
|
|
When editing, it applies read lock(s) to block other users from reading the record(s) until the transaction ends |
Non-repeatable reads and phantom data |
|
|
When reading, it applies edit lock(s) to block other users from editing the record(s) until the transaction ends |
Phantom data |
|
|
Applies key-range locks to prevent any action that would affect the results, including inserts and deletes |
None |
|
|
None |
None |
You can control explicit transactions using the Database property of the database context.
Import the following namespace to use the IDbContextTransaction interface:
using Microsoft.EntityFrameworkCore.Storage;
After the instantiation of the db variable, add the following statements to start an explicit transaction and output its isolation level:
using(var db = new Northwind())
{
using(IDbContextTransaction t =
db.Database.BeginTransaction())
{
WriteLine($"Transaction started with this isolation level:
{t.GetDbTransaction().IsolationLevel}");
At the bottom of the Main method, commit the transaction, and close the brace, as shown in the following code:
t.Commit();
}
When using Microsoft SQL Server, you will see the following isolation level:
Transaction started with this isolation level: ReadCommitted
When using SQLite, you will see the following isolation level:
Transaction started with this isolation level: Serializable