CODEWITHSUNDEEP
c#entity-framework-6
buks
buks

Reputation: 435

SqlException: Cannot insert duplicate key row in object 'dbo.Users' with unique index

I am using Entity Framework 6 + C# + WPF. I have such models (ok, a bit simpler version)

    public class User
    {
        [Key]
        public long UserId { get; set; }
        [Column(TypeName = "VARCHAR")]
        [StringLength(100)]
        [Index("UserName", IsUnique = true)]
        public string UserName { get; set; }
    }

    public class LogEntry : IEquatable<LogEntry>
    {
        [Key]
        [DatabaseGenerated(DatabaseGeneratedOption.Identity)]
        public long Id { get; set; }
        public long UserId { get; set; }
        [ForeignKey("UserId")]
        public virtual User User { get; set; }= new User();
        public string Message {get;set;}

    }

When I try to add new LogEntries into the database, I am getting an error:

SqlException: Cannot insert duplicate key row in object 'dbo.Users' with unique index UserName

But, I do not want to add the referenced user to the database...

LogEntry.UserId has the id of already existing row in table Users, just like LogEntry.User has the id of an existing row and userName of an existing record. I just want to save a reference (foreign key) to that table, not to add new record.

user = context.Users.FirstOrDefault(c => c.UserName == user.UserName);

foreach (LogEntry item3 in items)
{
    item3.User = user;
    item3.UserId = user.UserId; ;
}
.
.
.
context.Logs.AddRange(logs); 
context.SaveChanges();

How can I do it? What am I doing wrong?

Upvotes: 2

Views: 2113

Answers (2)

Ricardo Rodrigues
Ricardo Rodrigues

Reputation: 482

Use this : user = context.Users.AsNoTracking().FirstOrDefault(c => c.UserName == user.UserName); so that your "user" is not cached on your "context".

Then save it:

                context.Users.Attach(user);
                context.Entry(user).State = System.Data.Entity.EntityState.Modified;
                context.SaveChanges();

Upvotes: 1

YungDeiza
YungDeiza

Reputation: 4608

EF thinks you want to add a new user to the DB because the User property is being assigned with what it thinks is a new user. If you just make it null but assign UserId to the correct value it should retain the reference without adding a new user.

foreach (LogEntry item3 in items)
{
    item3.User = null; //Don't need to do this if it's already null
    item3.UserId = user.UserId;
}

Upvotes: 3

Related Questions