EF lazy loading & fluent API creates additional fi

Question

Here is my code - first, Attachment class:

    public class Attachment{
    (...)
          public int UserId { get; set; }
          public virtual User Author { get; set; }

          public int? BasicMedicalDataId { get; set; }
          public virtual BasicMedicalData MedicalData { get; set; }
    }

Here is BasicMedicalData class:

    public class BasicMedicalData{
    (..)
          public virtual ICollection<Attachment> Attachments { get; set; }
    }

As you can see, Attachment can have optionally one connected BasicMedicalData object. BasicMedicalData can have a lot of Attachments objects.

With these lines in

    protected override void OnModelCreating(DbModelBuilder modelBuilder)
    {
        (..)
        modelBuilder.Entity<Attachment>()
            .HasOptional(u => u.MedicalData)
            .WithMany()
            .HasForeignKey(u => u.BasicMedicalDataId);


        modelBuilder.Entity<Attachment>()
            .HasRequired(a => a.Author)
            .WithMany()
            .HasForeignKey(a => a.UserId);

    }

It creates strange migration:

    public override void Up()
    {
        CreateTable(
            "dbo.Attachments",
            c => new
                {
                    AttachmentId = c.Int(nullable: false, identity: true),
                    name = c.String(),
                    UserId = c.Int(nullable: false),
                    AddDate = c.DateTime(nullable: false),
                    BasicMedicalDataId = c.Int(),
                    del = c.Boolean(nullable: false),
                    BasicMedicalData_BasicMedicalDataId = c.Int(),
                })
            .PrimaryKey(t => t.AttachmentId)
            .ForeignKey("dbo.Users", t => t.UserId, cascadeDelete: true)
            .ForeignKey("dbo.PC_BasicMedicalData", t => t.BasicMedicalData_BasicMedicalDataId)
            .ForeignKey("dbo.PC_BasicMedicalData", t => t.BasicMedicalDataId)
            .Index(t => t.UserId)
            .Index(t => t.BasicMedicalData_BasicMedicalDataId)
            .Index(t => t.BasicMedicalDataId);
    }

Problem is here - it creates additional field in database BasicMedicalData_BasicMedicalDataId and ignores in lazy loading with field BasicMedicalDataId.

When I put manually ID's in BasicMedicalData_BasicMedicalDataId, lazy loading with attachments works nice. But I cannot put here any value with code, because it is not a Attachments property..

How to resolve it? Why it creates 2x the same field with diffrent name? And why lazy loading with field BasicMedicalDataId does not working well?

Answer 1

You have used the WithMany() overload that configures the relationship to be optional:many without a navigation property on the other side of the relationship - but you do have a navigation property on the other side of the relationship.

Try this:

   modelBuilder.Entity<Attachment>()
      //Attachment has an optional relationship with MedicalData
      .HasOptional(u => u.MedicalData)
       //It is many:optional with a navigation property named Attachments  
      .WithMany(d => d.Attachments)
      //The dependent's foreign key is BasicMedicalDataId    
      .HasForeignKey(u => u.BasicMedicalDataId);

Reference: Optional WithMany Method