EF Core one to many to many to one relationship

data-annotations ef-fluent-api entity-framework-core relationships

Question

I've worked with databases for a long time now but am new to Entity Framework. I handle both the aspects of programming and database development. As a db developer, I try to keep it clean so this structure that I came up with works well for me but I'm not sure if Entity Framework even supports it for I've tried for several days, using different scenarios, Data Annotations as well as Fluent API but couldn't get this to work.

What I'm trying to do might be a bit unconventional but what I'm trying to avoid is having to duplicate a file table for each area hence I define 1 file table that can be used by multiple areas using a Relationship. Thus, what I have is: one [company, employee, or project] can have many files (one to many). Similarly, the file table can be sourced by any area (many to many, in this case, it's not the data but rather the structure, hopefully that makes sense). The file records are related to only 1 area [company, employee, or project] (many to one).

The obvious advantage to this method is that I can avoiding having to manage 3 file tables but it doesn't end there. As you can see from the FileAccess table, instead of having multiple tables here or multiple fields to represent pointers to the multiple tables, I only need to manage 1 table for file access. The key is in the RelationTable and RelationId rather than the specific File.Id.

Below is a simplified example of the structure I'm trying to accomplish. Can it be done in Entity Framework?

  public class Company
  {
    public Guid Id { get; set; }
    public string Name { get; set; }
    public virtual ICollection<File> Files { get; set; }
  }

  public class Employee
  {
    public int Id { get; set; }
    public string Name { get; set; }
    public virtual ICollection<File> Files { get; set; }
  }

  public class Project
  {
    public int Id { get; set; }
    public Guid? CompanyId { get; set; }
    public string ProjectNo {get; set; }
    public virtual ICollection<File> Files { get; set; }
  }

  public class File
  {
    public int Id { get; set; }
    public Int16 RelationTable { get; set; } 0=Company, 1=Employee, 2=Project
    public string RelationId { get; set; } Company.Id, Employee.Id, Project.Id
    public string FileName { get; set; }
  }

  public class FileAccess
  {
    public int Id { get; set; }
    public int EmployeeId { get; set; }
    public Int16 RelationTable { get; set; } 0=Company, 1=Employee, 2=Project
    public string RelationId { get; set; } Company.Id, Employee.Id, Project.Id
    public string AccessType
  }
1
0
6/2/2017 3:46:38 PM

Accepted Answer

As Ivan pointed out, EF doesn't support this due to the foreign key limitations but I was able to come up with a working solution. However, I must warn you that I'm only on my 3rd week of EF so I don't know what ramifications this may cause but this is what I did, for those who may be interested.

As it turns out (through trial and error), EF just needs the OnModelCreating to wire up the relationship between the objects, it doesn't really need the FK to be created thus I defined the relationship this way:

  modelBuilder.Entity<File>()
  .HasIndex(k => new { k.RelationTable, k.RelationId }); //for performance

  modelBuilder.Entity<FileAccess>()
    .HasMany(fa => fa.Files)
    .WithOne(f => f.FileAccess)
    .HasForeignKey(k => new { k.RelationTable, k.RelationId })
    .HasPrincipalKey(k => new { k.RelationTable, k.RelationId });

//Using enumerations to control 
    relationships and adding PERSISTED so it doesn't need to be maintained plus it 
    won't break the Add-Migration with the "non persistent error"

  modelBuilder.Entity<Project>()
    .Property(f => f.RelationTable)
    .HasComputedColumnSql((int)NTGE.Database.Shared.eFileRelTable.Projects + " PERSISTED") //This injects the value so we don't have to store it
    .HasDefaultValue(123); //This doesn't really matter, we just need it so EF doesn't try to insert a value when saving, which will cause an error

  modelBuilder.Entity<Project>()
    .HasMany(p => p.Files)
    .WithOne(f => f.Project)
    .HasForeignKey(k => new { k.RelationTable, k.RelationId })
    .HasPrincipalKey(k => new { k.RelationTable, k.Id });

When you add the above codes and run the Add-Migration, it'll cause it to add the below codes, which will break the Update-Database command so you'll need to comment it out in the Up function.

        //migrationBuilder.AddForeignKey(
        //    name: "FK_Files_Projects_RelationTable_RelationId",
        //    table: "Files",
        //    columns: new[] { "RelationTable", "RelationId" },
        //    principalTable: "Projects",
        //    principalColumns: new[] { "RelationTable", "Id" },
        //    onDelete: ReferentialAction.Cascade);

        //migrationBuilder.AddForeignKey(
        //    name: "FK_Files_FileAccess_RelationTable_RelationId",
        //    table: "Files",
        //    columns: new[] { "RelationTable", "RelationId" },
        //    principalTable: "FileAccess",
        //    principalColumns: new[] { "RelationTable", "RelationId" },
        //    onDelete: ReferentialAction.Cascade);

You'll need to do the same with the Down function else you won't be able to roll back your changes.

        //migrationBuilder.DropForeignKey(
        //    name: "FK_Files_Projects_RelationTable_RelationId",
        //    table: "Files");

        //migrationBuilder.DropForeignKey(
        //    name: "FK_Files_FileAccess_RelationTable_RelationId",
        //    table: "Files");

Now you can do an Update-Database and it should run just fine. Running the app works perfectly fine as well. I'm able to use the EF method to get the Project with the associated files and worked with the FileAccess object as well. However, keep in mind that this is a hack and future versions of EF might not support it. Cheers!

0
6/2/2017 3:49:38 PM


Related Questions





Related

Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow