Question 263410 in ASP.NET Question posted in
The official documentation can be found here.

Asp.net – Correct way to approach composite foreign key

I have multiple classes X, Y, Z:

public class X
{
   public int Id { get; set; }
   public virtual List<Image> Images { get; set; }
}

public class Y
{
   public int Id { get; set; }
   public virtual List<Image> Images { get; set; }
}

public class Z
{
   public int Id { get; set; }
   public virtual List<Image> Images { get; set; }
}

Each class has an one-to-many relation with the Image class.

I want to create a single column which can references to all those classes X, Y, Z.

Here’s my Image class:

public class Image
{
    public int Id { get; set; }

    public int RefId { get; set; }
    public virtual X X { get; set; }
    public virtual Y Y { get; set; }
    public virtual Z Z { get; set; }
}

Is this a good way to minimize the number of foreign keys? Or is there some other approach I can try to make my fetch query faster?

Also what changes do I need to make in my fluent API?

Tags:

2

Answers


  1. 0

    Same column for X,Y,Z won’t work, since RefId is in Image. Consider this: an Image with RefId = 1, and X/Y/Z all have data with Id = 1, the Image won’t know if it should be associated with X or Y or Z.

    Besides, what if a new table K uses Image? You’d need to add new column for K in Image then, and every time a new table uses it.

    Reconsider the requirements. Do you really need to access X/Y/Z from Image? For similar usecase, usually I only access Image through instances of X/Y/Z.

    If you still want to keep the structure, a C#-only property might make things easier a little:

    // ...
// Make X, Y, Z all inherit SomeInterface first
// ...

public class Image
{
    public int Id { get; set; }

    public int RefId { get; set; }
    public virtual X X { get; set; }
    public virtual Y Y { get; set; }
    public virtual Z Z { get; set; }

    public IEnumerable<SomeInterface> Things => new [] {X,Y,Z}.Where(si => si != null).ToArray();
}

    Still, I’d suggest rethink about the whole structure.

    Login or Signup to reply.
  2. 0

    In EF Core, you can use generic types to define tables. In a table-per-heirachy, child types can share the same database column and can define navigations.

    So combining all of these facts together;

    interface IHaveImages<T>
    where T : IHaveImages<T> {
    int Id { get; set; }
    List<Image<T>> Images { get; set; }
}
class X : IHaveImages<X> {
    int Id { get; set; }
    List<Image<X>> Images { get; set; }
}
abstract class Image {
    int Id { get; set; }
    string Class { get; set; }
    int RefId { get; set; }
}
class Image<T> : Image
    where T : IHaveImages<T> {
    T Parent { get; set; }
}

modelBuilder.Entity<Image>(b => {
    b.HasDiscriminator(i => i.Class)
        .HasValue<Image<X>>("X");
});

modelBuilder.Entity<X>(b => {
    b.HasMany(x => x.Images)
        .WithOne(i => i.Parent)
        .HasForeignKey(i => i.RefId);
});

    Though obviously, since the foreign keys share the same database column, you won’t be able to create a real foreign key in the database.

    If you wanted a database column per table so that you can create real foreign keys, you can map the RefId property in each child type to a unique column, without polluting your C# types with extra properties.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search