Entity Framework code. The first existing database. EntityState.Unchanged for many-to-many.
I have an existing database that I am coding with Entity Framework 6 Code First. I have a many-to-many relationship that works on selections, inserts and deletes. I'm having a problem with EF adding an extra insert to a many-to-many table for an existing relationship.
Scheme:
DbContext:
protected override void OnModelCreating(DbModelBuilder modelBuilder)
{
Database.SetInitializer<MainContext>(null);
modelBuilder.Entity<DocumentType>()
.HasMany(u => u.DocumentStatuses)
.WithMany()
.Map(m =>
{
m.MapLeftKey("DOCUMENT_TYPE_ID");
m.MapRightKey("DOCUMENT_STATUS_ID");
m.ToTable("DOCUMENT_TYPES_DOCUMENT_STATUSES");
});
base.OnModelCreating(modelBuilder);
}
DTOS:
[Table("DOCUMENT_TYPES")]
public class DocumentType
{
[Key]
[Column("DOCUMENT_TYPE_ID")]
public int? Id { get; set; }
[Required]
[Column("TYPE_NAME")]
public string TypeName { get; set; }
[Required]
[Column("IS_ACTIVE")]
public bool IsActive { get; set; }
[Display(Name = "Updated By")]
[Column("LAST_UPDATED_BY")]
public string LastUpdatedBy { get; set; }
[Display(Name = "Updated Date")]
[Column("LAST_UPDATED_DATE")]
public DateTimeOffset? LastUpdatedDate { get; set; }
public virtual List<DocumentStatus> DocumentStatuses { get; set; }
public DocumentType()
{
DocumentStatuses = new List<DocumentStatus>();
}
}
[Table("DOCUMENT_STATUSES")]
public class DocumentStatus
{
[Key]
[Column("DOCUMENT_STATUS_ID")]
public int? Id { get; set; }
[Required]
[Column("STATUS_NAME")]
public string StatusName { get; set; }
[Required]
[Column("IS_COMPLETE")]
public bool IsComplete { get; set; }
[Required]
[Column("IS_ACTIVE")]
public bool IsActive { get; set; }
[Display(Name = "Updated By")]
[Column("LAST_UPDATED_BY")]
public string LastUpdatedBy { get; set; }
[Display(Name = "Updated Date")]
[Column("LAST_UPDATED_DATE")]
public DateTimeOffset? LastUpdatedDate { get; set; }
}
Storage update:
public bool Update(DocumentType entity, string updatedBy)
{
DateTimeOffset updatedDateTime = DateTimeOffset.Now;
entity.LastUpdatedBy = updatedBy;
entity.LastUpdatedDate = updatedDateTime;
using (var db = new MainContext())
{
db.DocumentTypes.Add((DocumentType)entity);
db.Entry(entity).State = EntityState.Modified;
foreach (var item in entity.DocumentStatuses)
{
if (item.Id != null)
db.Entry(item).State = EntityState.Unchanged;
}
db.SaveChanges();
}
return true;
}
A loop containing:
if (item.Id != null)
db.Entry(item).State = EntityState.Unchanged;
Prevents the addition of new records, but EF still tries to insert a many-to-many duplicate of existing records into DOCUMENT_TYPES_DOCUMENT_STATUSES.
Unit Test:
[TestMethod()]
public void UpdateTest()
{
DocumentTypeRepository documentTypeRepository = new DocumentTypeRepository();
DocumentType type = NewDocumentType(true);
DocumentType typefromDb;
string updatedBy = "DocumentTypeRepositoryTests.UpdateTest";
bool actual;
type.IsActive = true;
type.TypeName = RandomValues.RandomString(18);
type.DocumentStatuses.Add(DocumentStatusRepositoryTests.NewDocumentStatus(true));
actual = documentTypeRepository.Update(type, updatedBy);
Assert.AreEqual(true, actual);
typefromDb = documentTypeRepository.GetById((int)type.Id);
Assert.AreEqual(type.DocumentStatuses.Count, typefromDb.DocumentStatuses.Count);
}
How can I set a many-to-many table in EntityState.Unchanged if it already exists?
source to share
Try to replace
db.DocumentTypes.Add((DocumentType)entity); db.Entry(entity).State = EntityState.Modified;
from
db.Entry(entity).State = entity.Id == null
? EntityState.Added
: EntityState.Modified;
When performing CRUD operations on individual objects, it is easier to not use DbSet operations and manipulate only the object state data. At least this leads to fewer errors.
source to share
Two things are important here:
-
When you are an
Add
entity in context, the entire object graph that belongs to the entity is marked asAdded
. -
In any association, this also denotes associations as new. However, although in a 1: n or 1: 1 association, the state of the association changes when the state of one of the ends changes, in many, many associations there is part of a latent association that will always remain
Added
. (Under the hood, a many-to-many association is an association1:n:1
, some aren
not visible in your code).
So this statement ...
db.DocumentTypes.Add(entity);
makes associations get state Added
, and the subsequent ...
db.Entry(entity).State = EntityState.Modified;
does not change anymore.
You have to make sure that for an existing DocumentType
state it never changes to Added
, which you can do by setting its values Id
as you do for DocumentStatus
.
Also, you must change the way you set the state DocumentStatuses
:
foreach (var item in entity.DocumentStatuses)
{
db.Entry(item).State = item.Id != null
? EntityState.Unchanged
: EntityState.Added;
}
source to share