Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

EF and TPT : the column name is specified more than once in the SET clause

I'm using EF 6 and use a TPT strategy to model my problem. Rule is an abstract class. OvertimeRule is a concrete class, inheriting from Rule.

Rule looks like this :

public abstract class Rule
{   
    public int Id { get; set; }
    public PeriodType PeriodType { get; set; }
    public int SortOrder { get; set; }
    public int StatuteId { get; set; }
    public bool IsActive { get; set; }
}

OvertimeRule looks like this :

public partial class OvertimeRule : Rule
{
    public decimal? ThresholdCoefficient { get; set; }
    public decimal? LimitCoefficient { get; set; }
}

When I create a new OvertimeRule and try to save it, EF first generates this query :

INSERT [dbo].[Rules]([PeriodType], [SortOrder], [StatuteId], [IsActive], [StatuteID])
VALUES (@0, @1, @2, @3, @4, @5, @6, NULL)

As you can see, EF adds an StatuteID column to the insert, which does not exists anywhere in the solution and makes this an invalid SQL query.

SQL then rightfully throws : The column name 'StatuteId' 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 'StatuteId' may appear twice in the view definition.

The mapping looks like this :

        modelBuilder.Entity<Rule>().ToTable("Rules", TimmiSchemaName);
        modelBuilder.Entity<Rule>().HasRequired(s => s.Statute).WithMany(s => s.Rules).HasForeignKey(r => r.StatuteId);
        modelBuilder.Entity<OvertimeRule>().ToTable("OverTimeRules", TimmiSchemaName);

Could anyone tell me what could trigger this behavior ?

like image 705
Romain Vergnory Avatar asked Jan 28 '16 11:01

Romain Vergnory


1 Answers

This one was tricky.

modelBuilder.Entity<Rule>().HasRequired(s => s.Statute).WithMany().HasForeignKey(r => r.StatuteId);

is actually incorrect, and should have been

modelBuilder.Entity<Rule>().HasRequired(s => s.Statute).WithMany(s => s.Rules).HasForeignKey(r => r.StatuteId);

as the property statute.Rules existed on the other side of the foreign key.

Without it, EF tries to auto map the property to a sql column from the Rules table, which he guessed should be StatuteID.

like image 106
Romain Vergnory Avatar answered Oct 24 '22 19:10

Romain Vergnory