How to leave orphaned data?

Question

Using Visual Studio 2017, C#, Entity Framework .Net 6.2.0

I have a setup where a table (LOG) has a foreign key to another table (BAR). I want to delete an entry from BAR while leaving the entry and foreign key untouched in LOG.

public class LOG
{
    [Key]
    public int id { get; set; }
    public string statusLog { get; set; }
    public virtual BAR bar { get; set; }
}

public class BAR
{
    [Key]
    public int id { get; set; }
    public string data { get; set; }
}

I then attempt to delete an entry.

BAR bar1 = DBContext.BARs.Where(b => b.id == enteredID).First();
DBContext.BARs.Remove(bar1);
DB.Context.SaveChanges();

and get this exception

The DELETE statement conflicted with the REFERENCE constraint "FK_dbo.LOG_dbo.BARs_BAR_id". The conflict occurred in database \"*******\", table "dbo.LOG", column 'BAR_id'.

The statement has been terminated

How can I remove an entry from the BAR table while leaving the entries in the LOG table? I want the data to show what happened in my logging table even as entries are added and removed from my system.

A foreign key is specifically designed to prevent orphaned records in the manner you describe. Sounds like you need to remove your foreign key constraint in the database. — BJ Myers, May 14 '18 at 04:07
When you delete Bar. There is no Bar for Log to point at it so you will get an error. after deleting why do you need Bar in your Log? — Farshad, May 14 '18 at 04:20

score 3 · Answer 1 · answered May 14 '18 at 04:20

3

From a purely Entity Framework standpoint, Use a Nullable Foreign Key

Example

public class LOG
{
    [Key]
    public int id { get; set; }
    public string statusLog { get; set; }
    public int? BarId{ get; set; }
    public virtual BAR bar { get; set; }
}

Things to take into consideration :

Although SQL Server supports it, EF is not able to set a cascading rule to nullify the FK when the related object is deleted.

When deleting Bar you will need to have Logs loaded into memory to null them...

See any number of related questions to get your mind around this problem

How to update FK to null when deleting optional related entity

answered May 14 '18 at 04:20

TheGeneral

79,002
9
103
141

"When deleting Bar you will need to have Logs loaded into memory to null them..." This is not what I want. I would like the values in LOG to keep the values of BAR, even though BAR no longer contains the co-responding entry. – Zeckal May 14 '18 at 05:23
@user9786078 the only thing Logs can keep is the ID, thats the idea of the foreign key , is this what you want? – TheGeneral May 14 '18 at 05:25
@user9786078 if you want to keep the ID, then remove the relation and design the column as a ID, So you need to manually do validation and all things which were provided by foreign key.So when you delete the bar it will exist. Infact these value doesn't point to bar anymore – Eldho May 14 '18 at 09:29
@user9786078 yes to the above, and also another options might just to use a deleted flag in bar, ie it still exists just not active – TheGeneral May 14 '18 at 09:31
Just to make sure I am understanding this right. Your suggestion is to remove the Foreign Key Constraint from LOG, and turn 'LOG.bar' into an int value instead of a Virtual BAR value. This way I store the value of the id there but don't have Entity Framework attempting to honor a constraint? – Zeckal May 14 '18 at 19:15

Farshad · Answer 2 · 2018-05-14T04:59:28.500

0

You should add an id into your log class :

public class LOG
{
[Key]
public int id { get; set; }
public string statusLog { get; set; }
public int? BarId{ get; set; }

[ForeignKey("BarId")]
public virtual BAR bar { get; set; }
}

and modify OnModelCreating

protected override void OnModelCreating(ModelBuilder modelBuilder)
{
modelBuilder.Entity<Log>()
            .HasOptional(p => p.bar)
            .WithMany()
            .HasForeignKey(p => p.barID);
 }

So it means relation is optional and when you delete Bar it will update Log.barid equal to null and delete corresponding Bar.

But again first of all you should load them all.

edited May 14 '18 at 04:59

answered May 14 '18 at 04:44

Farshad

325
2
15

I see the value in this, as it would be a good long term solution, but at the moment I can't make a Model change in my environment. Is there any other solution? – Zeckal May 14 '18 at 05:25

How to leave orphaned data?

2 Answers2