CODEWITHSUNDEEP
sql-serverentity-frameworkcode-firstentity-framework-migrations
Ziregbe Otee
Ziregbe Otee

Reputation: 554

SQL Server the column name is specified more than once in the set clause. Entity framework issue

When I do an insert using EF6, I get this error

The column name 'employee_id' is specified more than once in the SET clause. A column cannot be assigned more than one value in the same SET clause. Modify the SET clause to make sure that a column is updated only once. If the SET clause updates columns of a view, then the column name 'employee_id' may appear twice in the view definition

My models looked like this:

public class Entity
{
        public Entity()
        {
            IsActive = true;
            IsDeleted = false;
            DateCreated = DateTime.Now;
        }

        [Key, DatabaseGenerated(DatabaseGeneratedOption.Identity)]
        public long ID { get; set; }
        public int CompanyID { get; set; }
        public int SubID { get; set; }
        public DateTime DateCreated { get; set; }

        public bool IsTransient()
        {
            return EqualityComparer<long>.Default.Equals(ID, default(long));
        }

        public bool IsDeleted { get; set; }
        public bool IsActive { get; set; }
}

public partial class NextOfKin : Entity
{
        [Required]
        public long employee_id { get; set; }

        [StringLength(100)]
        [Required]
        public string nok_first_name { get; set; }

        [StringLength(100)]
        [Required]
        public string nok_last_name { get; set; }

        [StringLength(300)]
        [Required]
        public string nok_address { get; set; }

        [StringLength(100)]
        public string nok_email { get; set; }

        [StringLength(100)]
        public string nok_phone { get; set; }

        [StringLength(100)]
        public string nok_employer { get; set; }

        [StringLength(300)]
        public string nok_work_address { get; set; }

        [StringLength(100)]
        [Required]
        public string nok_relationship { get; set; }

        public virtual Employee Employee { get; set; }
}

public class Employee : Entity
{
        //Person Records
        public long UserId { get; set; }
        public int TitleId { get; set; }
        public int? ReligionId { get; set; }
        public string SerialNo { get; set; }
        [StringLength(100)]
        [Required]
        public string FirstName { get; set; }
        [StringLength(100)]
        [Required]
        public string LastName { get; set; }
}

My insert code into next of kin was like this.

NextOfKin nextOfKin = new NextOfKin();

nextOfKin.employee_id = newEmployee.ID;
nextOfKin.nok_first_name = "Friday";
nextOfKin.nok_last_name = "Ben";
nextOfKin.nok_address = "XXX";
nextOfKin.nok_email = "[email protected]";
nextOfKin.nok_phone = "023938494";
nextOfKin.nok_employer = "50 Queens Street";
nextOfKin.nok_work_address = "51 Queens Street";
nextOfKin.nok_relationship = "Neighbour";

db.NextOfKins.Add(nextOfKin);
db.SaveChanges();

Upvotes: 2

Views: 4068

Answers (2)

Kirsten
Kirsten

Reputation: 18140

I got an error like this using EF Core

'PropertyNameID' is specified more than once in the SET clause or column list of an INSERT. A column cannot be assigned more than one value in the same clause. Modify the clause to make sure that a column is updated only once. If this statement updates or inserts columns into a view, column aliasing can conceal the duplication in your code.

It turned out that I had the case wrong in my relatonship

In My business object I had the foreign key set with the wrong case.

public int PropertyNameID { get; set; }
[ForeignKey("PropertyNameId")] public virtual PropertyNameExt PropertyName { get; set; }

Should have been

[ForeignKey("PropertyNameID")] public virtual PropertyNameExt PropertyName { get; set; }

Upvotes: 1

Ziregbe Otee
Ziregbe Otee

Reputation: 554

To fix this, remove the relationship on next of kin model, then do migration. To remove, remove public virtual Employee Employee { get; set; } from NextOfKin model.

The reason for this issue is as follow:

  1. Relationships are only created properly if you name the reference property properly. In this case you should use EmployeeID instead of employee_id for the relationship between next of kin and employee.

  2. The Employee model does not have a link back to the next of kin model. If it's a one to many you can add the property below to the Employee model.

    public virtual List NextOfKins{get; set;} //if you need lazy loading

    or

    public List NextOfKins{get; set;} //if you don't need lazy loading

Upvotes: 0

Related Questions