Entity Framework Core update column with foreign key reference

asp.net-core c# entity-framework-core

Question

This is the simplified version of the table structure I have:

[Table("PolicyMapping")]
public class PolicyMapping
{
    [Key]
    public Guid Id { get; set; }
    public Policy PolicyA { get; set; }
    public Policy PolicyB { get; set; } 
    public Lookup_Bank Bank { get; set; }       
}

[Table("Policy")]
public class Policy
{
    [Key]
    public Guid Id { get; set; }        
    public string PolicyNm { get; set; }    
    public string Description { get; set; } 
}

[Table("Lookup_Bank")]
public class Lookup_Bank
{
    [Key]
    public Guid Id { get; set; }        
    public string Name { get; set; }            
    public string Code { get; set; }            
}

I am working on the edit screen for policy mapping where you can have the same values for PolicyA and PolicyB attributes.

After using automapper for DTO to the entity, here is my entity object looks like:

var policyMapping = new PolicyMapping
{
    Id = "b27fb632-330b-46be-a649-2e2463d58626",
    PolicyA = new Policy
    {
        Id = "a4f1cf6f-034d-4727-ab8f-49e95b2c9d23",
        PolicyNm = null,
        Description = null
    },
    PolicyB = new Policy
    {
        Id = "a4f1cf6f-034d-4727-ab8f-49e95b2c9d23",
        PolicyNm = null,
        Description = null
    },
    Bank = new Lookup_Bank()
    {
        Id = "98ed2bae-631b-490c-8ddf-3e02232d4231",
        Name = null,
        Code = null
    }
}

I am mapping only selected id value of dropdown to entity id using automapper. Values are present for Code, Description and other attributes in the database. It's just not getting populated after automapper.

dbContext.PolicyMapping.Attach(policyMapping);
dbContext.SaveChanges();

This is the error, I am getting

The instance of entity type Policy cannot be tracked because another instance with the same key value for {'a4f1cf6f-034d-4727-ab8f-49e95b2c9d23'} is already being tracked.
When attaching existing entities, ensure that only one entity instance with a given key value is attached.

The reason for the error maybe because I am attaching two different entities with the same Id. I am still not sure how can I make it work in the most efficient way?

Solution 1: (not efficient)

var fromdatabase = dbContext.PolicyMapping.Include(x => x.PolicyA)
        .Include(x => x.Bank)
        .Include(x => x.PolicyB)        
        .FirstOrDefault(x => x.Id == policyMapping.Id);

fromdatabase.PolicyA = dbContext.Policy.FirstOrDefault(x => x.Id == policyMapping.PolicyA.Id);
fromdatabase.PolicyB = dbContext.Policy.FirstOrDefault(x => x.Id == policyMapping.PolicyB.Id);

dbContext.PolicyMapping.Attach(fromdatabase);
dbContext.SaveChanges();

This is working. But I would like to avoid a trip to the database just to fetch the entire entity.

Edit: Based on Xing's answer

@Xing, pointed out to change the Model structure by adding navigational properties and some changes in OnModelCreating method. (This method is currently blank in my code)

However, I went through a couple of articles (This & This) related to EF Core Code First approach, none of them are saying about navigational properties and all.

I am wondering how they are updating the column in this scenario?

1
0
8/15/2019 3:26:35 PM

Popular Answer

If you just would like to update the Id of the navigation properties, you could add foreign key for them and update it.

1.Model:

public class PolicyMapping
{
    [Key]
    public Guid Id { get; set; }

    public Guid PolicyAId { get; set; }      
    public Policy PolicyA { get; set; }

    public Guid PolicyBId { get; set; }
    public Policy PolicyB { get; set; }

    public Guid BankId { get; set; }
    public Lookup_Bank Bank { get; set; }
}

2.DbContext:

protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        base.OnModelCreating(modelBuilder);

        modelBuilder.Entity<PolicyMapping>()
            .HasOne(x => x.PolicyA)
            .WithOne()
            .HasForeignKey<PolicyMapping>(p => p.PolicyAId)
            .OnDelete(DeleteBehavior.Restrict);

        modelBuilder.Entity<PolicyMapping>()
            .HasOne(x => x.PolicyB)
            .WithOne()
            .HasForeignKey<PolicyMapping>(p => p.PolicyBId)
            .OnDelete(DeleteBehavior.Restrict);

        modelBuilder.Entity<PolicyMapping>()
            .HasOne(x => x.Bank)
            .WithOne()
            .HasForeignKey<PolicyMapping>(p => p.BankId)
            .OnDelete(DeleteBehavior.Restrict);

    }

3.Add migrations.

4.Controller:

 var policyMapping = new PolicyMapping
        {
            Id = new Guid("b27fb632-330b-46be-a649-2e2463d58626"),
            PolicyAId = new Guid("a4f1cf6f-034d-4727-ab8f-49e95b2c9d23"),
            PolicyBId = new Guid("a4f1cf6f-034d-4727-ab8f-49e95b2c9d23"),
            BankId = new Guid("98ed2bae-631b-490c-8ddf-3e02232d4231")

        };
        dbContext.PolicyMapping.Attach(policyMapping);
        dbContext.Entry(policyMapping).Property("PolicyAId").IsModified = true;
        dbContext.Entry(policyMapping).Property("PolicyBId").IsModified = true;
        dbContext.Entry(policyMapping).Property("BankId").IsModified = true;
        dbContext.SaveChanges();

Then you could retrieve PolicyA or PolicyB from their foreign key PolicyAId or PolicyBId

var policyA = dbContext.Policy.FirstOrDefault(x => x.Id == policyMapping.PolicyAId);
0
8/15/2019 6:51:08 AM


Related Questions





Related

Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow