EF 6 Code-First Many to Many relationship with same entity on non-primary key

Question

EF 6 Code-First Many to Many relationship with same entity on non-primary key

.net c# database entity-framework entity-framework-6

Question

I have an EF 6 Task entity, which looks like this:

public class Task
{
    public Guid TaskId {get; set;}
    public TaskTypeEnum TaskType {get; set;}
    public TaskStatusEnum TaskStatus {get; set;}

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

Tasks of a certain type can be dependent on all other tasks of a certain type to finish running before they run, and those dependencies should be defined in a table like this:

public class TaskTypeDependency
{
    public TaskTypeEnum TaskType {get; set;}
    public TaskTypeEnum DependsOnTaskType {get; set;}
}

Example of what I want- Task A has Type 1, Task B and C have Type 2. I have a TaskTypeDependency of

TaskType | DependsOnTaskType
----------------------------
1        |  2

At runtime, I want to get all the tasks that task is dependent on, which for Task A would be Task B and Task C, to check if the dependent tasks have completed. Is there a way to set this relationship up in Code-First, maybe with Fluent API? Or am I stuck using LINQ to sort this all out without a virtual property?

user3802230

View more on Stack Overflow

Marcell Toth · Accepted Answer · 2/13/2019 5:34:39 PM

Welcome to StackOverflow!

As far as I know, EF cannot handle relationships of this kind.

Your problem isn't that the principal key isn't the primary key. Your main problem is that those keys (TaskTypes) are not unique.

Solution proposal

You can work around this limitation by encapsulating the logic of loading the dependent tasks in a repository. While many people advice against building a repository pattern on top of EF, and most of the times I'm on that side too, this is a great example of why it can be useful.

I would create a method that populates dependencies using LINQ, maybe an extension method, something along the lines of:

private static void LoadDependentTasks(this Task task, IEnumerable<Task> allTasks){
    task.Dependencies = allTasks.Where(yourCustomSelector).ToList();
}

Then in your repository you can use this method on the Tasks you loaded before you return them. E.g:

public Task GetById(Guid taskId){
    Task t = _context.Tasks.Find(taskId);
    t.LoadDependentTasks(_context.Tasks);
}

Then when your business logic calls your repository, it will receive objects that already have their Dependencies property filled.

There is one optimization that is worth mentioning here, though. It isn't as important for getting a single task, but makes a lot of difference when loading all of them. If you implement the GetAll method naively, as follows, it will retrieve the list of Tasks from the server n times, which is not great.

public Task GetAll(){
    List<Task> allTasks = _context.Tasks.ToList();
    foreach(var task in allTasks)
        task.LoadDependentTasks(_context.Tasks);
    return allTasks;
}

You should pass the already existing allTasks variable to the method instead. If - in another scenario - you don't have a list of tasks already, another solution is to call _context.Tasks.Load(), and use _context.Tasks.Load thereafter.

EF 6 Code-First Many to Many relationship with same entity on non-primary key

Question

Fastest Entity Framework Extensions

Accepted Answer

As far as I know, EF cannot handle relationships of this kind.

Solution proposal

Performance

Expression Evaluator

Related