Raimonds Raimonds - 2 days ago 5
C# Question

EF6 Schema specified is not valid. Errors: The relationship x was not loaded because the type y is not available

This is asked many times, I know where the exact issue is but I am trying to avoid it.

Simplified POCO:

public class TaskEntity
{
public int TaskId { get; set; }

public int? AssignedToId { get; set; }
public virtual UserEntity AssignedTo { get; set; }

public int CreatedById { get; set; }
public virtual UserEntity CreatedBy { get; set; }

public int? ClosedById { get; set; }
public virtual UserEntity ClosedBy { get; set; }
}

public class UserEntity
{
public List<TaskEntity> TaskId { get; set; }

public int UserId { get; set; }
public string Name { get; set; }
}


Mappings:

public class TaskMap : EntityTypeConfiguration<TaskEntity>
{
public TaskMap()
{
ToTable("tTasks");
HasKey(x => x.TaskId);

HasRequired(x => x.CreatedBy).WithMany(x => x.TaskId).HasForeignKey(x => x.CreatedById).WillCascadeOnDelete(false);
HasOptional(x => x.ClosedBy).WithMany(x => x.TaskId).HasForeignKey(x => x.ClosedById).WillCascadeOnDelete(false);
HasOptional(x => x.AssignedTo).WithMany(x => x.TaskId).HasForeignKey(x => x.AssignedToId).WillCascadeOnDelete(false);

}
}


I have read that I should separate UserEntity in 3 different classes and make them inherit from TaskEntity, but this doesn't sounds right as It will be exactly the same user object for all these cases.

I am expecting to have table structure as follows:

tTasks

TaskId | [FK]AssignedToId | [FK]CreatedById | [FK]ClosedById

tUsers

UserId | Name

Could someone point what am I doing wrong here. Do I need to adjust my mapping somehow in order to get my table created as I expect

Answer

The answer is yes. You should adjust your mapping. What you're doing wrong is in this line:

public List<TaskEntity> TaskId { get; set; }.

In EF you cannot get in the same navigation property all Tasks related to the UserEntity with different foreign keys. That means you need a navigation property in UserEntity to be mapped against every navigation property in TaskEntity. And as you have 3 navigation properties in every class you will need to specify which is against which. You'll get this:

public class TaskEntity
{
    public int TaskId { get; set; }

    public int? AssignedToId { get; set; }
    [InverseProperty("AssignedTasks")]
    public virtual UserEntity AssignedTo { get; set; }

    public int CreatedById { get; set; }
    [InverseProperty("CreatedTasks")]
    public virtual UserEntity CreatedBy { get; set; }

    public int? ClosedById { get; set; }
    [InverseProperty("ClosedTasks")]
    public virtual UserEntity ClosedBy { get; set; }
}

public class UserEntity
{
    public int UserId { get; set; }
    public string Name { get; set; }

    [InverseProperty("AssignedTo")]
    public virtual ICollection<TaskEntity> AssignedTasks {get; set; }
    [InverseProperty("CreatedBy")]
    public virtual ICollection<TaskEntity> CreatedTasks {get; set; }
    [InverseProperty("ClosedBy")]
    public virtual ICollection<TaskEntity> ClosedTasks {get; set; }
}

With this all the mapping is done with the annotations and you can remove the TaskMap class.

You can add a List<TaskEntity> Tasks to your UserEntity that aggregates the results from the 3 previous navigation properties, but the aggregation will be done after the data is loaded and you cannot use it in Linq queries.

Comments