CODEWITHSUNDEEP
c#entity-frameworkef-code-firstentity-framework-6entity-framework-migrations
Robert Hoffmann
Robert Hoffmann

Reputation: 2416

Navigation property without foreign key, or fluent api version?

I have a table of users, where a user can be the manager of another user (this is optional, thus int? IdManager)

[Table("users")]
public partial class User
{
    #region Properties
    [DatabaseGenerated(DatabaseGeneratedOption.Identity)]
    [Key]
    [Required]
    [Column("Id")]
    public int Id { get; set; }
    #endregion

    #region DataNavigation
    /// <summary>
    /// Navigation Id for getting parent Field
    /// WARNING: this works, but need to remove foreign key creation in migration file !
    /// </summary>
    [ForeignKey("Manager")]
    [Column("IdManager")]
    public int? IdManager { get; set; }

    /// <summary>
    /// Navigation object to parent Field
    /// </summary>
    [ScriptIgnore]
    [JsonIgnore]
    [IgnoreDataMember]
    public virtual User Manager { get; set; }
    #endregion
}

Now in code first, the above code generates this

public partial class manager : DbMigration
{
    public override void Up()
    {
        AddColumn("users", "IdManager", c => c.Int());
        CreateIndex("users", "IdManager");
        AddForeignKey("users", "IdManager", "users", "Id");
    }

    public override void Down()
    {
        DropForeignKey("users", "IdManager", "users");
        DropIndex("users", new[] { "IdManager" });
        DropColumn("users", "IdManager");
    }
}

However it breaks while updating the database with Cannot add foreign key constraint

But if i delete the foreign key creation

public partial class manager : DbMigration
{
    public override void Up()
    {
        AddColumn("users", "IdManager", c => c.Int());
        CreateIndex("users", "IdManager");
    }

    public override void Down()
    {
        DropIndex("users", new[] { "IdManager" });
        DropColumn("users", "IdManager");
    }
}

Everything works fine in dbContext and Linq

...but, i have to leave [ForeignKey("Manager")] on my user object (for linq & dbcontext), which is not so good, since at a later time it will certainly blow up in someones face again.

Any idea on how i can map this properly either via Data Attributes or fluent api ?

Thanks!

Upvotes: 1

Views: 7730

Answers (2)

blogbydev
blogbydev

Reputation: 1495

I tried the other way, created one such Employee table and added as a model, it got me this:

public partial class Employee
{
    public Employee()
    {
        Subordinates = new HashSet<Employee>();
    }
    public int Id { get; set; }
    public string Name { get; set; }
    public int? ManagerId { get; set; }
    public virtual Employee Manager { get; set; }
    public virtual ICollection<Employee> Subordinates { get; set; }
}

public class EmployeeConfiguration: EntityTypeConfiguration<Employee>
{
    public EmployeeConfiguration()
    {
        ToTable("EmployeeDbContext", "dbo");
        HasKey(p => p.Id).Property(p => p.Id)
            .HasDatabaseGeneratedOption(DatabaseGeneratedOption.Identity);
        Property(p => p.Name).HasMaxLength(50);
        HasMany(p => p.Subordinates).WithOptional(p => p.Manager)
                                 .HasForeignKey(p => p.ManagerId);
    }
}

Upvotes: 0

Francesc Castells
Francesc Castells

Reputation: 2857

I haven't tested it, but in fluent api it should be like this:

modelBuilder.Entity<User>()
   .HasOptional(u=>u.Manager)
   .WithMany()
   .HasForeignKey(u=>u.IdManager)
   .WillCascadeOnDelete(false);

So, a User has an optional Manager, which can have many users linking to it without the Manager having a collection of Users (WithMany without parameters), with the given foreign key property (IdManager) and with cascade delete disabled.

Upvotes: 4

Related Questions