Entity framework Invalid column id name (two foreign keys from the same primary table)

Question

I get this error: Invalid column name 'Resource_Id' .

I tried the solutions that I found in this forum, but this does not fix my error.

Here are my classes:

public class Task 
{
    public int Id { get; set; }

    public string Name { get; set; }
    public int ApproverResourceId { get; set; }
    public int WorkingResourceId { get; set; }

    public virtual Resource ApproverResource { get; set; }
    public virtual Resource WorkingResource { get; set; }
}

Resource class

public class Resource
{
    public int Id { get; set; }

    public DateTime CreationDate { get; set; }
    public DateTime UpdateDate { get; set; }

    public virtual ICollection<Task> Tasks { get; set; }
}

Externalresource

public class ExternalResource : Resource
{
    //public int Id { get; set; }

    public string FirstName { get; set; }
    public string LastName { get; set; }
}

And I added this mapping to my CodeFirst Class

protected override void OnModelCreating(DbModelBuilder modelBuilder)
{
    modelBuilder.Entity<Task>()
        .HasRequired(t => t.WorkingResource)
        .WithMany()
        .HasForeignKey(t => t.WorkingResourceId)
        .WillCascadeOnDelete(false);

    modelBuilder.Entity<Task>()
        .HasRequired(t => t.ApproverResource)
        .WithMany()
        .HasForeignKey(t => t.ApproverResourceId)
        .WillCascadeOnDelete(false);
}

Answer 1

You need 2 navigation properties because you have 2 foreign keys:

public class Resource
{
    public int Id { get; set; }

    public DateTime CreationDate { get; set; }
    public DateTime UpdateDate { get; set; }

    public virtual ICollection<Task> ApproverTasks { get; set; }
    public virtual ICollection<Task> WorkingTasks { get; set; }
}

In DbContext, you specify them in WithMany:

protected override void OnModelCreating(DbModelBuilder modelBuilder)
{
    modelBuilder.Entity<Task>()
        .HasRequired(t => t.WorkingResource)
        .WithMany(r => r.WorkingTasks)
        .HasForeignKey(t => t.WorkingResourceId)
        .WillCascadeOnDelete(false);

    modelBuilder.Entity<Task>()
        .HasRequired(t => t.ApproverResource)
        .WithMany(r => r.ApproverTasks)
        .HasForeignKey(t => t.ApproverResourceId)
        .WillCascadeOnDelete(false);

    modelBuilder.Entity<ExternalResource>()
        .ToTable("ExternalResources");
}

You need to explicitly specify the table name for ExternalResources due to type-based inheritance .

Answer 2

, , Key + Column; "ResourceId".

, , "".

Public class User : EntityBase
{
    [MinLength(5), MaxLength(20), Required]
    public string Login { get; set; }
    [MinLength(3), MaxLength(50),Required]
    public string Password { get; set; }
    public virtual List<FriendRequest> SenderFriendRequests { get; set; }
    public virtual List<FriendRequest> ReceiverFriendRequests { get; set; }
}

public class FriendRequest 
{
    [Key, Column(Order=0)]
    public int SenderId { get; set; }
    [Key, Column(Order=1)]
    public int ReceiverId { get; set; }
    public virtual User Sender { get; set; }
    public virtual User Receiver { get; set; }

    [Required]
    public FriendRequestStatus RequestStatus { get; set; }
}

, EF DataException :

:     Server.DAL.Context.FriendRequest:: EntityType 'FriendRequest' . EntityType.     FriendRequests: EntityType: EntitySet "FriendRequests" "FriendRequest", .