CODEWITHSUNDEEP
entity-framework-coreef-core-2.0
Charles Okwuagwu
Charles Okwuagwu

Reputation: 10876

Prevent updates to navigation properties in EF-core

Please how can we prevent EF.core from trying to insert/update foreign-key tables when we create a new primary entity?

This Exception is thrown:

SqlException: Cannot insert explicit value for identity column in table 'clients' when IDENTITY_INSERT is set to OFF.
Cannot insert explicit value for identity column in table 'guards' when IDENTITY_INSERT is set to OFF.
Cannot insert explicit value for identity column in table 'penalties' when IDENTITY_INSERT is set to OFF.

My code is as follows:

  public class Offence
  {
    [Key]
    public Int32 offence_id { get; set; }

    public Int32? guard_id { get; set; }
    public Int32? penalty_id { get; set; }
    public DateTime? dt_recorded { get; set; }
    public Int32? salary_id { get; set; }
    public Decimal? amount { get; set; }
    public String status { get; set; }
    public Int32? site_id { get; set; }

    public Guard Guard { get; set; }
    public Salary Salary { get; set; }
    public Site Site { get; set; }
    public Penalty Penalty { get; set; }
  }

Any attempt to create a new Offence gives errors, as EF.core tries to run inserts for the related navigation properties:

public Guard Guard { get; set; }
public Salary Salary { get; set; }
public Site Site { get; set; }
public Penalty Penalty { get; set; }

How can we prevent this?

Edit: Create and update code

[HttpPost]
public async Task<IActionResult> Create([FromBody] Offence o)
{
  if (o == null)
  {
    return BadRequest();
  }

  o.last_modified_by = int.Parse(((ClaimsIdentity)User.Identity).Claims.FirstOrDefault(c => c.Type == ClaimTypes.NameIdentifier)?.Value);
  o.last_modified = DateTime.Now;

  await db.AddAsync(o);
  await db.SaveChangesAsync();

  return CreatedAtRoute("GetOffenceAsync", new { id = o.offence_id }, o);
}

Upvotes: 5

Views: 3463

Answers (3)

Charles Okwuagwu
Charles Okwuagwu

Reputation: 10876

To get this to work, I had to null-out the navigation properties before saving.

However if sending the initial object back with CreatedAtRoute, then you need to cache the nulled-out properties and add them back to before you return:

Actual code:

[HttpPost]
public async Task<IActionResult> Create([FromBody] Offence o)
{
  if (o == null)
  {
    return BadRequest();
  }

  o.last_modified_by = int.Parse(((ClaimsIdentity)User.Identity).Claims.FirstOrDefault(c => c.Type == ClaimTypes.NameIdentifier)?.Value);
  o.last_modified = DateTime.Now;

  var _g = o.Guard;
  var _p = o.Penalty;
  var _s = o.Site;

  o.Guard = null;
  o.Penalty = null;
  o.Site = null;

  await db.AddAsync(o);
  await db.SaveChangesAsync();

  o.Guard = _g;
  o.Penalty = _p;
  o.Site = _s;

  return CreatedAtRoute("GetOffenceAsync", new { id = o.offence_id }, o);
}

Upvotes: 1

H. Herzl
H. Herzl

Reputation: 3248

It seems like your navigation properties have values, please check your navigation property have null reference before to save; EF Core save logic try to save navigation properties if they have value.

Let me know if this is useful

Upvotes: 0

Cristian Szpisjak
Cristian Szpisjak

Reputation: 2479

You need to set those properties as virtual. This way EF knows what's part of the model and what is an navigational property. Also this will enable the LazyLoading mechanism you need.

public virtual Guard Guard { get; set; }
public virtual Salary Salary { get; set; }
public virtual Site Site { get; set; }
public virtual Penalty Penalty { get; set; }

Upvotes: 0

Related Questions