Adding Entity Framework Deletion, including Cascade Delete to the Helpers App.

The Helper App

The previous posts in this series considered a Blazor Server app for helpers to volunteer for duties at an Athletics Track Meet. Much use is made of Entity Framework Core for accessing the app’s SQL Server database. The immediately previous posts covered the auto-generation of the database by Code-First EF Migration, and adding some sample data to the Helpers App, which was generated from a Json string, as well as displaying of lists of the entities in Blazor which were generated from a Json string.

This post covers some further database CRUD including deletion of entries including Cascade Delete.

LINK: This version of the app on GitHub in the CRUD branch


The App Data Structure

Helpers volunteer for Activities. An Activity is for a specific Round in a calendar of Track Meets.

Entities

  • Helpers
  • Rounds
  • Activities

Each Activity has a nullable Helper, a non-null Round and a non-empty Task. Each Helper has a Name. Each Round has a round number.

public class Activity
{
    [Key]
    [Column("Id")]
    public int Id { get; set; }

    [Column("Round")]
    [Required]
    public Round Round { get; set; }

    [Column("Helper")]
    public Helper Helper { get; set; }

    [Column("Task")]
    [Required]
    public string Task { get; set; }
}

The Class Diagram

The SQL Database Digram

FetchData Page Functionality

When the FetchData page is opened, or refreshed, a call is made to the HelpersService to get the Activitys list. If the Activitys is empty, a call is made to generate the data.

In this version of the app, the Activity, Helper and Round tables in the database are cleared through deletion of records, rather than dropping and recreating the tables so that Entity Framework referential integrity is maintained throughout. The table Id seeds are zeroed at this point.

The sample data is generated via a Json string deserialization and saved to the database. The Json string has a compound structure in that its deserialization generates 3 instances of the Activity class as well as the Helpers and Rounds that they refer to, as new instances or Helper and Round classes respectively. The save of the Activity data to the database, also saves the Helper and Round instances without any requirement to explicitly implement that.

The three lists are then returned by the HelperService and separately displayed via razor iteration through the lists.

In this version of the app, there are buttons on the page to delete items.

Of interest is the demonstration of Cascade Delete. The deletion of a Round deletes any activity in that round … Cascade Delete, whereas deletion of a Helper does not delete an activity that the Helper has volunteered for. It nulls the helper property in the activity. The Helper null reaction is because the Helper property in the Activity class is annotated as nullable. An activity can exist without a Helper. It awaits a volunteer. An activity must have a Round though. It is a required property. To fully implement the cascade delete some further subtle coding was required.


The UI

Activitys

Id Round Helper Task
1 1 John Marshall Shot Put
2 2 Sue Burrows Marshalling
3 3 Jimmy Beans Discus

Helpers

Id Name
1 John Marshall
2 Sue Burrows
3 Jimmy Beans

Rounds

Id Round No.
1 1
2 2
3 3

Actions:

Action  
[Delete Round] 1
[Delete Activity] 2
[Delete Helper] 3


Entity Framework and Cascade Delete

In the discussion about this topic on Microsoft Dox at Cascade Delete mention is made of the need for some EF operations to have the coplete set of all entities involved in a transaction in memeory (my wording). There is also a StackOverFlow post on this here.

When deleting a Round any Activities for that round would be orphaned if not deleted at the same time. An alternative is to set the Round property to null in the Activity when a round is deleted. It does not make sense to have orphaned activities looking for a round. On the otherhand, when an Activity is created, that is part of scheduling and awaits a helper to volunteer and so the deletion of a helper does not require deletion of an activities tha they have voiumteerd for; just a nulling of the helper for those activities.

Consider when a simple delete is applied to a round such as:

    public async Task DeleteRound(int Id)
    {
        var roundLst = _context.Rounds.Where(e => e.Id == Id);
        if (roundLst.Count() != 0)
        {
            _context.Rounds.Remove(roundLst.First());
            await _context.SaveChangesAsync();
        }
    }

Deletion By Id is used here with a query for that entity instance so tha that the deletion SHOULD occur.

An error will arise because the round for a an activity is a required property, The scafolding can be handled by EF or in the database itself.

Where do cascading behaviours happen?

Quoting from the MS DOx link above:
**_“Cascading behaviours can be applied to:

  • Entities tracked by the current DbContext
  • Entities in the database that have not been loaded into the context._**”

The first approach is used in this app for cascade deletes.

    // Cascade Deletion
    public async Task DeleteRound(int Id)
    {
        var roundLst = _context.Rounds.Where(e => e.Id == Id).Include(e => e.Activitys);
        if (roundLst.Count() != 0)
        {
            _context.Rounds.Remove(roundLst.First());
            await _context.SaveChangesAsync();
        }
    }

This loads into context the Round and all of its Activities through the Activitys property of the round which is a reverse referencing property of the Round. That readonly property as viewed below was added for this version of the app.

    public class Round
    {
        [Key]
        [Column("Id")]
        public int Id { get; set; }

        [Column("No")]
        [Required]
        public int No { get; set; }

        public IList<Activity> Activitys { get; } = new List<Activity>();
    }

That list is instantiated by the appending of .Include(e => e.Activitys) which gets the list when that line of code gets the specific round… Magic!

Further

In the Migration files this is formally specified in the ModelBuilder.Entity code for ASP Identity by adding (not used though with the Activity-Round-Helper functionality):

    .OnDelete(DeleteBehavior.Cascade)
    .IsRequired();

An alternative is:

    .OnDelete(DeleteBehavior.SetNull)

Also The .include appendage is als0 used with Activity list queries:

    public async Task<List<Activity>> GetActivitys()
    {
        var list = await _context.Activitys.Include(activity => activity.Helper).Include(activity => activity.Round).ToListAsync();
        return list;
    }

In the complete Helper app (the app here is a simplified version of that) it was found that on a refresh of the page some data came up null. . By adding the .includes that issues was resolved. This is discussed on StackOverFlow at here


 TopicSubtopic
   
 This Category Links 
Category:Blazor Index:Blazor
  Next: > Blazor Helpers App
<  Prev:   Blazor Helpers App