Configure One-None/One Relationship with Multiple Tables using Entity

Question

I'm in a situation where one table has two One-None/One Relationships. How do I implement this using Entity Framework Code-First?

I've seen the following links

• https://www.safaribooksonline.com/library/view/programming-entity-framework/9781449317867/ch04s07.html
• https://cpratt.co/0-1-to-1-relationships-in-entity-framework/
• https://www.tektutorialshub.com/one-to-one-relationship-entity-framework/

Where essentially it's said that the dependent end needs to have a primary key that is the same as that of the principal end. But I'm weary of implementing this with more than one One-None/One Relationship without confirmation and proper knowledge of what's going on. Furthermore I am not sure how to construct statements as it does not have a conventional Foreign Key.

I've also seen Configuring multiple 1 to 0..1 relationships between tables entity framework which confused me beyond recognition.

See below for the relevant part of my DB Diagram: So Essentially, a Player shouldn't be saved without a DKImage, similarly a Product shouldn't be saved without a DKImage.

Below is the code for Models: Players, Products, DKImages (I know it's not correct, I only implemented it this way so I can generate the database and show the diagram)

Player

public enum Positions { PG, SG, SF, PF, C }

public class Player
{
    [Key]
    [ForeignKey("Images")]
    public int PlayerID { get; set; }

    [Required]
    public string PlayerName { get; set; }

    [Required]
    public string PlayerLastName { get; set; }

    [Required]
    public int PlayerAge { get; set; }

    [Required]
    public Positions Position { get; set; }

    [Required]
    public bool Starter { get; set; }

    [Required]
    [Display(Name = "Active / Not Active")]
    public bool Status { get; set; }

    //Foreign Keys
    public int PlayerStatsID { get; set; }

    //Navigation Properties
    [ForeignKey("PlayerStatsID")]
    public virtual IQueryable<PlayerStats> PlayerStats { get; set; }
    public virtual DKImages Images { get; set; }
}

DKImages

public class DKImages
{
    [Key]
    public int ImageID { get; set; }
    [Required]
    public string ImageURL { get; set; }
    [Required]
    public DateTime DateUploaded { get; set; }

    //Foreign Keys
    [Required]
    public int CategoryID { get; set; }

    //Navigation Properties
    public virtual Products Products { get; set; }
    public virtual Category Category { get; set; }
    public virtual Player Player { get; set; }
}

Products

public class Products
{
    [ForeignKey("Images")]
    [Key]
    public int ProductID { get; set; }
    [Required]
    public string ProductName { get; set; }
    [Required]
    public DateTime DateAdded { get; set; }

    //Foreign Keys
    [Required]
    public int ProductTypeID { get; set; }

    //Navigation Properties
    [ForeignKey("ProductTypeID")]
    public virtual ProductType ProductType { get; set; }
    public virtual DKImages Images { get; set; }
}

Edit

I have been told that the code above is correct. If so then how do I create CRUD LINQ Statements (Or any method of constructing CRUD statements for that matter) with the above code.

Answer 1

What you want here is referred to as polymorphic associations: several entities having child entities of one type. They're typically used for comments, remarks, files etc. and usually applied to 1:n associations. In your case there are polymorphic 1:1 associations. Basically these associations look like this (using a bit more generic names):

How to implement them?

Entity Framework 6

In EF6 that's problem. EF6 implements 1:1 associations as shared primary keys: the child's primary key is also a foreign key to its parent's primary key. That would mean that there should be two FKs on Image.ID , one pointing to Person.ID and another one pointing to Product.ID. Technically that's not a problem, semantically it is. Two parent entities now own the same image or, stated differently, an image should always belong to two different parents. In real life, that's nonsense.

The solution could be to reverse the references:

But now there's another problem. The entity that's referred to is named the principal, the other entity is dependent. In the second diagram, Image is the principal, so in order to create a Person, its image must be inserted first and then the person copies its primary key. That's counter-intuitive and most likely also impractical. It's impossible if images are optional.

Nevertheless, since in your case you want images to be required let me show how this association is mapped in EF6.

Let's take this simple model:

public class Person
{
    public int ID { get; set; }
    public string Name { get; set; }
    public virtual Image Image { get; set; }
}

public class Product
{
    public int ID { get; set; }
    public string Name { get; set; }
    public virtual Image Image { get; set; }
}

public class Image
{
    public int ImgID { get; set; } // Named for distinction
    public string Url { get; set; }
}

The required mapping is:

modelBuilder.Entity<Image>().HasKey(pd => pd.ImgID);
modelBuilder.Entity<Person>().HasRequired(p => p.Image).WithRequiredDependent();
modelBuilder.Entity<Product>().HasRequired(p => p.Image).WithRequiredDependent();

As you see, Image has two required dependents. Perhaps that's better than two required parents, but it's still weird. Fortunately, in reality it's not a problem, because EF doesn't validate these associations. You can even insert an image without a "required" dependent. I don't know why EF doesn't validate this, but here it comes in handy. The part WithRequiredDependent might as well have been WithOptional, it doesn't make a difference for the generated data model, but at least this mapping conveys your intentions.

An alternative approach could be inheritance. If Person and Product inherit from one base class this base class could be the principal in a 1:1 association with Image. However, I think this is abusing a design pattern. People and products have nothing in common. From a design perspective there's no reason for them to be part of one inheritance tree.

Therefore, in EF6 I think the most feasible solution is to use the third alternative: separate image tables per entity.

Entity Framework Core

In EF-core 1:1 associations can be implemented the EF6 way, but it's also possible to use a separate foreign key field in the dependent entity. Doing so, the polymorphic case looks like this:

The Image class is different:

public class Image
{
    public Image()
    { }
    public int ImgID { get; set; }
    public int? PersonID { get; set; }
    public int? ProductID { get; set; }
    public string Url { get; set; }
}

And the mapping:

modelBuilder.Entity<Person>().Property(p => p.ID).UseSqlServerIdentityColumn();
modelBuilder.Entity<Person>()
    .HasOne(p => p.Image)
    .WithOne()
    .HasForeignKey<Image>(p => p.PersonID);
modelBuilder.Entity<Product>().Property(p => p.ID).UseSqlServerIdentityColumn();
modelBuilder.Entity<Product>()
    .HasOne(p => p.Image)
    .WithOne()
    .HasForeignKey<Image>(p => p.ProductID);
modelBuilder.Entity<Image>().HasKey(p => p.ImgID);

Watch the nullable foreign keys. They're necessary because an image belongs to either a Person or a Product. That's one drawback of this design. Another is that you need a new foreign key field for each new entity you want to own images. Normally you want to avoid such sparse columns. There's also an advantage as compared to the EF6 implementation: this model allows bidirectional navigation. Image may be extended with Person and Product navigation properties.

EF does a pretty good job translating this into a database design. Each foreign key has a filtered unique index, for example for Person:

CREATE UNIQUE NONCLUSTERED INDEX [IX_Image_PersonID] ON [dbo].[Image]
(
    [PersonID] ASC
)
WHERE ([PersonID] IS NOT NULL)

This turns the association into a genuine 1:1 association on the database side. Without the unique index it would be a 1:n association from the database's perspective.

Answer 2

An exemple in your Player table would be this :

public class Player
{

    // All the rest you already coded

    [Required]
    public int ImageID

    [ForeignKey("ImageID")]
    public virtual DKImage DKImage {get;set;}
}

This would force a player to have a DKImage, but as said in the comments, this create a one to many relationship.

Another way out would be to put all Player fields into the DKImage table, those fields would be null if there is no player associated to this DKImage.

Edit for 1 to 1..0

Ivan Stoev's link got some pretty interesting insight on how to accomplish this :

https://weblogs.asp.net/manavi/associations-in-ef-4-1-code-first-part-3-shared-primary-key-associations

It seems like you will have to put a bit more code in your class :

protected override void OnModelCreating(DbModelBuilder modelBuilder)
{
    modelBuilder.Entity<DKImage>().HasOptional(t => t.Player).WithRequired();

}

If the tutorial is correct, this would read as :

"DKImage entity has an optional association with one Player object but this association is required for Player entity".

I have not tested it yet.