EntityFramework and SQL Server Remove Related Entity-CodePudding

I am having a weird issue with my understanding of Entity Framework. It's likely something I am doing wrong.

I have the following sample objects recording as data in my SQL Database.

public class Competition 
{
    public long Id { get; set;}
    public string Name { get; set; }
}

public class SeasonCompetition 
{
    public long Id { get; set;}
    public string Name { get; set; }
    public Competition Competition { get; set; }
}

Now, I create a new instance of Competition and save it to the database. I then create a new instance of SeasonCompetition relating to the created Competition.

Competition competition = new Competition();
competition.Name = "Test";
_context.Add(competition);
_context.SaveChanges();

SeasonCompetition seasoncompetition = new SeasonCompetition();
seasoncompetition.Name = "Test Season Comp";
seasoncompetition.Competiton = competition;
_context.Add(seasoncompetition);
_context.SaveChanges();

This successfully writes a record to the SeasonCompetitions table looking a bit like this.

Id	Name	SeasonCompetitionId
1	Test Season Competition	1

Now, I want to remove the relationship so this SeasonCompetition does not relate to the Competition record

seasoncompetition.Competition = null;
_context.Update(seasoncompetition);
_Context.SaveChanges();

If I put a breakpoint in and examine the seasoncompetition object after SaveChanges(), I can see seasoncompetition.Competition = null, but looking at the database, it still shows this.

Id	Name	SeasonCompetitionId
1	Test Season Competition	1

How to I update the SeasonCompetition record to remove the relationship to Competition but still keep the Competition record in place? in the database?

Using Visual Studio 2022 and .NET 5

CodePudding user response：

Make 'Competition' nullable.

public class SeasonCompetition
{
    public long Id { get; set; }
    public string Name { get; set; }
    public Competition? Competition { get; set; }
}

CodePudding user response：

There are two ways to remove a record from SQL using using entity framework, detach or remove.

Competition competition = new Competition();
competition.Name = "Test";
_context.Add(competition);
_context.SaveChanges();

SeasonCompetition seasoncompetition = new SeasonCompetition();
seasoncompetition.Name = "Test Season Comp";
seasoncompetition.Competiton = competition;
_context.Add(seasoncompetition);
_context.SaveChanges();


_context.Entry(competition).State = EntityState.Deleted;
_context.SaveChanges();

Alternatively you could use remove like this:

_context.Competitions.Remove(competition);
_context.SaveChanges();

The difference of detach vs remove in EF:

Remove: mark the entity to be deleted so when you do SaveChanges it's deleted from the db

Detach: remove the entity from the DbContext change tracker so whatever you do with the entity DbContext doesn't notice. So when you do SaveChanges nothing happens with this entity