CODEWITHSUNDEEP
c#sql-serverentity-frameworkinheritance
Romain Vergnory
Romain Vergnory

Reputation: 1598

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 ?

Upvotes: 6

Views: 7409

Answers (2)

Alexander Derck
Alexander Derck

Reputation: 14498

I see two strange things in the code you provided:

1)

modelBuilder.Entity<Rule>().HasRequired(s => s.Statute)...

This part says the property Statute is required in Rule, however it's nowhere to be seen. How does this even compile?

2)

modelBuilder.Entity<Rule>().ToTable("Rules", TimmiSchemaName);

The point of TPT is that you use one of your tables as an abstract class which means you will use that table for the models that derive from it. You shouldn't be able to fetch a Rule, but you can fetch an OverTimeRule which is a Rule. It's also important that the primary key of the derived tables are the exact same primary key of your base table. Your context class should look something like this:

public class MyContext : DbContext
{
    public DbSet<Rule> Rules {get; set;}

    protected override void OnModelCreating(DbModelBuilder modelBuilder)
    {
        modelBuilder.Entity<OverTimeRule>().ToTable("OverTimeRules");
        ...
    }
}

Really great article here: http://weblogs.asp.net/manavi/inheritance-mapping-strategies-with-entity-framework-code-first-ctp5-part-2-table-per-type-tpt

Upvotes: 1

Romain Vergnory
Romain Vergnory

Reputation: 1598

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.

Upvotes: 2

Related Questions