Why is Entity Framework creating an extra column that doesn't map to any field and is always null?

Question

I have my POCO set up like this:

public class Notification : BaseClass
{

    public User Receiver { get; set; }

    [Required, Index]
    public long ReceiverID { get; set; }

    public virtual User ContextualUser { get; set; }

    public long? ContextualUserID { get; set; }

    public virtual User Actor { get; set; }

    public long? ActorID { get; set; }

    public string Content { get; set; }

    public string Uri { get; set; }

    [Required]
    public string Type { get; set; }

}

My base class has only three properties: ID, CreateDate, and IsDeleted, none are related to User class:

public abstract class BaseClass
{
    [Key]
    public long ID { get; set; }

    [Required]
    public DateTime CreateDate { get; set; }

    public bool IsDeleted { get; set; }

    public BaseClass()
    {
        CreateDate = DateTime.UtcNow;
    }
}

However, Entity Framework creates an extra column, User_ID, which is always null, as seen below:

It doesn't apparently map to anything. None of my other entities (either deriving from the same base class or not) have this phantom column. Why is this column created? I am on EF 6.1.3 code-first with SQL Azure.

UPDATE: I've also ran update-database in PM console but nothing has changed, I still have this column.

UPDATE 2: There is even an index and a foreign key defined on column. I've tried dropping it (it's not a live app yet) but it's being referenced by an index:

alter table notifications drop column User_ID

Msg 5074, Level 16, State 1, Line 1
The index 'IX_User_ID' is dependent on column 'User_ID'.
Msg 5074, Level 16, State 1, Line 1
The object 'FK_dbo.Notifications_dbo.Users_User_ID' is dependent on column 'User_ID'.
Msg 4922, Level 16, State 9, Line 1
ALTER TABLE DROP COLUMN User_ID failed because one or more objects access this column.

@FabioLuz I am dead sure. it has only the three properties that I've said: ID, CreateDate, IsDeleted. that's it. Just re-checked to make sure. — Can Poyrazoğlu, Feb 22 '16 at 13:20
@FabioLuz no. I'm not using Fluent API anywhere in my application. I'm all-annotation in that sense. — Can Poyrazoğlu, Feb 22 '16 at 13:21
@CanPoyrazoğlu how does your base class look like? also , are you using EF migrations, there could be a column being added there for some reason. — SOfanatic, Feb 22 '16 at 13:22
@SOfanatic I've also added the code for my base class to avoid confusion. I've used EF migrations (none of them were explicit, only implicit), but ran `update-database` after each migration, and ran it again just now to be sure. — Can Poyrazoğlu, Feb 22 '16 at 13:24
@CanPoyrazoğlu create a new database, change the EF's database target to the new database. Then, create a new migration and see if the User_ID column is there — Fabio, Feb 22 '16 at 13:25
@CanPoyrazoğlu not sure if this could be it, but the `User Receiver` property is missing the `virtual` keyword — SOfanatic, Feb 22 '16 at 13:29
@CanPoyrazoğlu I'm afraid that the `User_ID` is the foreign key of one of the `User` properties. Try to set the foreign key explicitly. Like this: `[ForeignKey("ContextualUserID")]` `[ForeignKey("ActorID")]` and `[Receiver("ReceiverID")]`. You can put theses attributes on top of the `User` properties — Fabio, Feb 22 '16 at 13:30
@FabioLuz it was navigation property on my **User** class configured incorrectly. because `Notification` didn't have a property called `User` but rather `Receiver`, I've forgot to add the correct name (Receiver) as an inverse property on my User class' `Notifications` collection. I've added the annotation ` [InverseProperty("Receiver")]` and it now works correctly. — Can Poyrazoğlu, Feb 22 '16 at 13:44

score 5 · Accepted Answer · answered Feb 22 '16 at 13:45

It was navigation property on my User class configured incorrectly. Because Notification class didn't have a property called User but rather Receiver, and because I've forgot to add the correct name (Receiver) as an inverse property on my User class which had a Notifications collection, this extra field was generated for navigating from User to Notifications. I've added the annotation [InverseProperty("Receiver")] to my collection on User class and it now works correctly.

score 0 · Answer 2 · edited May 23 '17 at 11:52

you can use data annonations for this :

public abstract class BaseClass
{
    [Key]
    public long ID { get; set; }

    [Required]
    public DateTime CreateDate { get; set; }

    public bool IsDeleted { get; set; }

    public BaseClass()
    {
        CreateDate = DateTime.UtcNow;
    }
}

public class Notification : BaseClass
{
    [ForeignKey("ReceiverID")]
    [InverseProperty("Receivers")]
    public User Receiver { get; set; }

    [Required, Index]
    public long ReceiverID { get; set; }


    [ForeignKey("ContextualUserID")]
    [InverseProperty("ContextualUsers")]
    public virtual User ContextualUser { get; set; }

    public long? ContextualUserID { get; set; }


    [ForeignKey("ActorID")]
    [InverseProperty("Actors")]
    public virtual User Actor { get; set; }

    public long? ActorID { get; set; }

    public string Content { get; set; }

    public string Uri { get; set; }

    [Required]
    public string Type { get; set; }

}

public class User
{
    [InverseProperty("Receiver")]
    public ICollection<Notification> Receivers { get; set; }

    [InverseProperty("ContextualUser")]
    public ICollection<Notification> ContextualUsers { get; set; }

    [InverseProperty("Actor")]
    public ICollection<Notification> Actors { get; set; }
}

for more information :

Many to many relation

Navigation property

Why is Entity Framework creating an extra column that doesn't map to any field and is always null?

2 Answers2