Entity Framework Core Triggers In Action (Unofficial Package)

This article shows you how to work with database triggers in  Entity Framework Core using a third party library.

Recently, I have had this problem of updating a field of an entity that is dependent on another field of another entity. Confused already? Let me give you an example:  I have these Inventory and Item entities where there exists a many-to-many relation between them. So, of course, there should also be a relation table between them, i.e., InventoryItem. The problem I was having actually is I have had this one field called CurrentQuantity on the Item entity and other called IssuedQuantity on the relation table (InventoryItem). Thus, as you might have already guessed I’ve had to somehow update the CurrentQuantity field of the Item entity based on the IssuedQuantity field of the InventoryItem entity.

And now for the solution: my path crossed with this beautiful package named EntityFrameworkCore. Triggers created by Nick Strupat. Using this package, you can enable support for Triggers in your project using Entity Framework Core or Entity Framework 6+. For the demo, I have used Entity Framework Core with ASP.NET Core. 

Below is how I used the package to easily solve my problem. Install the package via Nuget with the following command,

Install-Package EntityFrameworkCore.Triggers

Here are the entities used for the demo.

public class Inventory
{
    public int Id { get; set; }
    public string Moniker { get; set; }

    public List<InventoryItem> InventoryItems { get; set; }
}

public class Item
{
    public int Id { get; set; }
    public decimal CostPerUnit { get; set; }
    public int CurrentQuantity { get; set; } = 0;
    public List<InventoryItem> InventoryItems { get; set; }
}

public class InventoryItem
{
    public int Id { get; set; }
    public int IssuedQuantity { get; set; }

    public int ItemId { get; set; }
    public Item Item { get; set; }

    public int InventoryId { get; set; }
    public Inventory Inventory { get; set; }
}

I have used those entities to declare DbSet<T> in the ApplicationDbContext file.

public class ApplicationDbContext : DbContext
{
    public DbSet<Item> Items { get; set; }
    public DbSet<Inventory> Inventories { get; set; }
    public DbSet<InventoryItem> InventoryItemRelation { get; set; }
}

I used MVC scaffolding for generating Controllers and their respective Views for these entities. I won’t show the code for them here. Please download the repository to have a better look at those.

The next thing to do is to create Insert and Delete triggers for updating the value of CurrentQuantityfield respective to the value of IssuedQuantity field.

Here go the triggers in the overridden SaveChangesAsync method of ApplicationDbContext.

public override Task<int> SaveChangesAsync(bool acceptAllChangesOnSuccess,
         CancellationToken cancellationToken = default(CancellationToken))
{
    Triggers<InventoryItem>.Inserting +=
             entry => entry.Entity.Item.CurrentQuantity += entry.Entity.IssuedQuantity;
    Triggers<InventoryItem>.Deleting += entry =>
             entry.Entity.Item.CurrentQuantity -= entry.Entity.IssuedQuantity;

    return this.SaveChangesWithTriggersAsync(base.SaveChangesAsync,
             acceptAllChangesOnSuccess: true, cancellationToken: cancellationToken);
}

Everything will work smoothly now. If you update the value of the IssuedQuantity field while managing relation between Item and Inventory, it will also update the CurrentQuantity field. Here, I’m only listening for the Insert and Delete triggers for the sake of simplicity but you have the Update trigger also for use if you want.

Don’t forget calling the SaveChangesWithTriggersAsync in your SaveChangesAsync method. Otherwise, the triggers won’t get registered.

By the way, you have to modify the Create and Delete action generated by the default scaffolding engine for InventoryItem entity like the following 

[HttpPost]
[ValidateAntiForgeryToken]
public async Task<IActionResult>
    Create([Bind("Id,IssuedQuantity,ItemId,InventoryId")] InventoryItem inventoryItem)
{
    if (ModelState.IsValid)
    {
        var item = _context.Items.Find(inventoryItem.ItemId);
        var inventory = _context.Inventories.Find(inventoryItem.InventoryId);

        inventoryItem.Item = item;
        inventoryItem.Inventory = inventory;

        _context.Add(inventoryItem);
        await _context.SaveChangesAsync();
        return RedirectToAction("Index");
    }
    ViewData["InventoryId"] =
       new SelectList(_context.Inventories, "Id", "Id", inventoryItem.InventoryId);
    ViewData["ItemId"] = new SelectList(_context.Items, "Id", "Id", inventoryItem.ItemId);
    return View(inventoryItem);
}

// POST: InventoryItems/Delete/5
[HttpPost, ActionName("Delete")]
[ValidateAntiForgeryToken]
public async Task<IActionResult> DeleteConfirmed(int id)
{
    var inventoryItem = await _context.InventoryItemRelation.SingleOrDefaultAsync(m => m.Id == id);

    var item = _context.Items.Find(inventoryItem.ItemId);
    var inventory = _context.Inventories.Find(inventoryItem.InventoryId);

    inventoryItem.Item = item;
    inventoryItem.Inventory = inventory;

    _context.InventoryItemRelation.Remove(inventoryItem);
    await _context.SaveChangesAsync();
    return RedirectToAction("Index");
}

Nothing fancy here; just got the appropriate Item and Inventory references using the InventoryId and ItemId fields available inside the inventoryItem parameter. Then, attach the references back to the inventoryItem parameter again so that we don’t get any null reference errors.

I’ve covered the very basics here. To know more about the package and other available configurations, refer to this GitHub repository. It is open source, yay!

• https://github.com/NickStrupat/EntityFramework.Triggers