Entity Framework - Main table with two users with different name field, relation with a single field of foreign key

Question

I'm using a EF with .Net Core 2.2. And I cannot figurated out how to solve the following problem:

I have the PQL and PM fields, these fields are the key of a user (Based in the table User). So, How I can create two relations of two fields to the same field (In the secondary table) using EF...

Example, a requirement is to create a query to get all projects per user, where the user be the PM or the PQL...

The main table is:

public class ProjectHeader
    {
        [Key]
        public int IdProjectHeader { get; set; }
        [StringLength(200)]
        public string ProjectName { get; set; }

        [ForeignKey("FK_IdUser")]
        [Column("IdUser")]
        public int PM { get; set; }
        [ForeignKey("FK_IdUser")]
        [Column("IdUser")]
        public int PQL { get; set; }

        // NOT LINK WITH USER TABLE CORRECTLY
        public User User { get; set; }
    }

The User table is:

public class User
    {
        [Key]
        public int IdUser { get; set; }
        [Required]
        [StringLength(100)]
        public string UserName { get; set; }
        [Required]
        [StringLength(50)]
        public string ShortName { get; set; }
        [Required]
        [StringLength(50)]
        public string Email { get; set; }
    }

score 2 · Answer 1 · answered Aug 10 '19 at 21:47

A link to a question similar to this one. I would suggest you to research and use Entity Framework's Fluent API. Basically in your context class (that inherits DbContext class) you override the OnModelCreating method like so:

class MyContext : DbContext
{
    public DbSet<Blog> Blogs { get; set; }
    public DbSet<Post> Posts { get; set; }

    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        modelBuilder.Entity<Post>()
            .HasOne(p => p.Blog)
            .WithMany(b => b.Posts);
    }
}

Inside you can configure some advanced relations for your database. You can look it up here.

I will check it out. I'm checking the way to do it directly on the entities (If it's possible)... otherwise, I will create a Context to manipulate those cases. Thanks for your contribution! — MiBol, Aug 10 '19 at 21:51

score 0 · Accepted Answer · answered Aug 13 '19 at 00:33

I solved the problem... on main table I included:

[ForeignKey("PM")]
public User PmUser { get; set; }
[ForeignKey("PQL")]
public User PqlUser { get; set; }

...using the User Entity for both fields (Using a Foreign Key - Equal to the field on the main table)... so, the PM match with idUser... and PQL match with idUser

Now... I have a circular reference when I tried to update the database using Code-first...

So, in the class public class ApplicationDbContext : DbContext I override the foreign keys:

protected override void OnModelCreating(ModelBuilder modelBuilder)
{
    modelBuilder.Entity<ProjectHeader>().HasOne(m => m.PmUser).WithMany().OnDelete(DeleteBehavior.ClientSetNull);
    modelBuilder.Entity<ProjectHeader>().HasOne(m => m.PqlUser).WithMany().OnDelete(DeleteBehavior.ClientSetNull);
}

Entity Framework - Main table with two users with different name field, relation with a single field of foreign key

2 Answers2