Self referencing entity framework model

Question

Thanks in advance from your help.

I'm trying to make a database for a little rpg with a friend. Up until now we've been keeping track of things like attack types in a JSON file (my friend is not tech savvy so we compromised early on just plain text files). I want to make him a website so that he can more easily edit the relationships between our ideas (types, monster species, possible attacks, etc.)

So the data I'm pulling from looks like this:

{
    "name": "water", 
    "isElement": true, 
    "defendAgainst": [
      "fire", 
      "undead", 
      "atomic", 
      "food"
    ], 
    "immuneTo": [
      "water"
    ], 
    "weakTo": [
      "electric", 
      "zoetic", 
      "sonic", 
      "eldritch"
    ]
  }, ... and so on

In this example, Water is an element, it can defend against fire type, it's immune to water attacks, and it's weak to sonic attacks.

I made a thing that translates the JSON into an Object and I end up with something like:

public class monsterType
{
    [Key]
    public string name { get; set; }
    public ICollection<monsterType> weakTo { get; set; }
    public ICollection<monsterType> immuneTo { get; set; }
    public ICollection<monsterType> defendAgainst { get; set; }
    public bool isElement { get; set; }
}

I translate from the JSON by doing:

        // set up the types references
        foreach (typeJSON ty in typeListFromFile){
            monsterType realTypeReference = lookUpMonsterTypeFromJSONtype(ty, realTypes);
            //convert those lists
            ICollection<monsterType> mtWTs = ty.weakTo.Select(w => lookUpMonsterTypeFromName(w, realTypes)).ToList();
            ICollection<monsterType> mtDAs = ty.defendAgainst.Select(w => lookUpMonsterTypeFromName(w, realTypes)).ToList();
            ICollection<monsterType> mtITs = ty.immuneTo.Select(w => lookUpMonsterTypeFromName(w, realTypes)).ToList();
            //set them on the real type reference
            realTypeReference.weakTo = mtWTs;
            realTypeReference.defendAgainst = mtDAs;
            realTypeReference.immuneTo = mtITs;
        }

The data goes into instances of monsterType just fine. I'm sitting pretty with 40 monsterTypes, and they all refer to each other just great.

I'm using this project to teach myself MVC and Entity Framework (code-first?), and though I've played with like Flask in the past I'm a little out of the water here in C#.net land so please forgive me if I'm missing something completely obvious.

I've set up a db connection with a SQL Server database, and it's creating the realTypes table OK ---- but it's missing the immuneTo, weakTo, and defendAgainst columns I'd expect and I think it's because this is all self-referential.

Alright so I made a data context:

public class monsterDataContext : DbContext
{
    public monsterDataContext() : base("name=blood")
    {
    }

    public DbSet<monsterType> Types { get; set; }
}

And I just loop through my list and save my changes:

   using (var theDB = new monsterDataContext())
   {
       foreach (monsterType ty in realTypes)
       {
           theDB.Types.Add(ty);
       }
       theDB.SaveChanges();
    }

When I do this, it errors on theDB.SaveChanges() with:

Multiplicity constraint violated. The role 'monsterType_defendAgainst_Source' of the relationship 'ParseMonsterYaml.monsterType_defendAgainst' has multiplicity 1 or 0..1.

I'm not exactly sure why that's a problem?

It also creates the table, which is great, but the columns don't really have what I'd expect:

name, isElement, monsterType_name, monsterType_name1, monsterType_name2

but that doesn't really matter as much as the error. No data is getting loaded into the table, it's empty.

What am I doing wrong? Something fundamental? Am I running into something horrible? Is there a better way?

Answer 1

When you want custom column names, you have to tell EF them names, you can do this as below.

protected override void OnModelCreating(DbModelBuilder modelBuilder)
        {
            modelBuilder.Entity<MonsterType>()
                .HasRequired(t => t.DefendAgainst)
                .WithMany()
                .Map(configuration => configuration.MapKey("DefendAgainst"));

            modelBuilder.Entity<MonsterType>()
               .HasRequired(t => t.ImmuneTo)
               .WithMany()
               .Map(configuration => configuration.MapKey("ImmuneTo"));

            modelBuilder.Entity<MonsterType>()
              .HasRequired(t => t.WeakTo)
              .WithMany()
              .Map(configuration => configuration.MapKey("WeakTo"));


           base.OnModelCreating(modelBuilder);
        }

About your error, by default your one-many relationship columns are created NOT NULL, that's why when you save with missing data, it violates the SQL constraint. if DefendAgaint, WeekTo and ImmuneTo can be null then in the above code replace .HasRequired with .HasOptional as below

protected override void OnModelCreating(DbModelBuilder modelBuilder)
        {
            modelBuilder.Entity<MonsterType>()
                .HasOptional(t => t.DefendAgainst)
                .WithMany()
                .Map(configuration => configuration.MapKey("DefendAgainst"));

            modelBuilder.Entity<MonsterType>()
               .HasOptional(t => t.ImmuneTo)
               .WithMany()
               .Map(configuration => configuration.MapKey("ImmuneTo"));

            modelBuilder.Entity<MonsterType>()
              .HasOptional(t => t.WeakTo)
              .WithMany()
              .Map(configuration => configuration.MapKey("WeakTo"));


           base.OnModelCreating(modelBuilder);
        }

this generates this SQL

create table [dbo].[MonsterTypes] (
    [Name] [nvarchar](128) not null,
    [IsElement] [bit] not null,
    [DefendAgainst] [nvarchar](128) null,
    [ImmuneTo] [nvarchar](128) null,
    [WeakTo] [nvarchar](128) null,
    primary key ([Name])
);
alter table [dbo].[MonsterTypes] add constraint [MonsterType_DefendAgainst] foreign key ([DefendAgainst]) references [dbo].[MonsterTypes]([Name]);
alter table [dbo].[MonsterTypes] add constraint [MonsterType_ImmuneTo] foreign key ([ImmuneTo]) references [dbo].[MonsterTypes]([Name]);
alter table [dbo].[MonsterTypes] add constraint [MonsterType_WeakTo] foreign key ([WeakTo]) references [dbo].[MonsterTypes]([Name]);

Hope I have understood this right!