Entity Framework 6: How to map array elements to table columns

Question

[I'm surprised that I didn't find an existing question with an answer to this conceptual question. Maybe I just didn't search the right keywords.]

Consider an entity type "Singer" with some of its own properties. Next, consider an entity type "Quartet" that has exactly four Singers in it, assigned to the roles "Tenor", "Lead", "Baritone", and "Bass" in the quartet.

The simplest way to model this code first is to just add four Singer navigation properties to the Quartet entity class, with the names of the four singer roles in that quartet. This would lead to a Quartet table and a Singers table with four rows with foreign keys referencing the quartet, and we have a one-to-four relationship. Not horrible, and this works, but it leads to some awkwardness in the program later.

There are numerous operations that would either need to be iterated over the four singers and done for each one, or conditional queries that need to operate on just one of the singers, depending on the value of some external enum that indicates which singer to act on.

Ideally, instead of having four separate Singer navigation properties, then, I'd like to have an array of Singers with a fixed size of four, where the elements of the array correspond directly to the enum values, and I could iterate over the array or go directly to a specific element based on the enum.

But this doesn't seem to model well in EF with SQL Server.

How can I accomplish what I need here?

Answer 1

Consider a next code-sample:

public class Singer {
    public int Id { get; set; }
    public string Name { get; set; }
    public SingerType SingerType { get; set; }
    public virtual Quartet Quartet { get; set; }
}

public class Quartet {
    public int Id { get; set; }
    public virtual List<Singer> Singers { get; set; }
    public string Name { get; set; }

    public Singer GetByType(SingerType type) {
        return Singers.FirstOrDefault(e => e.SingerType == type);
    }

    public void AddSinger(Singer singer) {
        if (Singers.Any(e => e.SingerType == singer.SingerType)) {
            throw new Exception($"You cannot add additional-{singer.SingerType} to quartet->{this.Name}");
        }
        if (Singers.Count > 3) {
            throw new Exception($"You cannot add additional singer->{singer.Name} to quartet->{this.Name} cause quartet already more than 3 members");
        }
        Singers.Add(singer);
    }
}

public class MyFancyClass {
    private readonly Context _context;

    public MyFancyClass(Context context) {
        _context = context;
    }

    public Quartet DoWhatEverWithQartet(string name) {
        var myQuartet = _context.Quartets.FirstOrDefault(e => e.Name == name);
        foreach (var singer in myQuartet.Singers) {
            // Do whatever logic with singer
        }
        return myQuartet;
    }

    public void DoWhatEverLogicWithBassOfQuartet(string name) {
        var myQuartet = _context.Quartets.FirstOrDefault(e => e.Name == name);
        var bass = myQuartet.GetByType(SingerType.Bass);
        // Do whatever logic with bass
    }
}

public enum SingerType {
    Unknown = 0,
    Bass = 1,
    Bariton = 2,
    Lead = 3,
    Tenor = 4,
    Bullshit = 5,
    WhatEver = 6,
}

public class Context : DbContext {

    public IDbSet<Singer> Singers { get; set; }
    public IDbSet<Quartet> Quartets { get; set; }

    protected override void OnModelCreating(DbModelBuilder modelBuilder) {
        modelBuilder.Configurations.Add(new QuartetConfiguration());
        base.OnModelCreating(modelBuilder);
    }
}

public class QuartetConfiguration : EntityTypeConfiguration<Quartet> {
    public QuartetConfiguration() {
        HasKey(e => e.Id);
        HasMany(e => e.Singers).WithRequired(e => e.Quartet).WillCascadeOnDelete(false);
    }
}

In that case you have restriction on how much singers quartet will have on BL level, and you have a capability to iterate over all singers in Quartet.