EF - Code First - Having Multiple One to Zero or One Relationships

Question

i'm developing some scheduling classes and i hit a brick wall after creating multiple One to Zero or One relationships with code first.

Agenda Class

public class Agenda
{
    public int AgendaId { get; set; }

    //Possible Owners of this Agenda
    public int? RoomId { get; set; }
    public Room Room { get; set; }

    public int? TrainerId { get; set; }
    public Trainer Trainer { get; set; }
}

Room Class

public class Room
{
    public int RoomId { get; set; }

    public string description { get; set; }

    //Without the [Required] gives me an error
    //Unable to determine the principal end of an association between the types...
    public int AgendaId { get; set; }
    [Required]
    public Agenda Agenda { get; set; }
}

Trainer Class

public class Trainer
{
    public int TrainerId { get; set; }

    public string name { get; set; }

    //Without the [Required] gives me an error
    //Unable to determine the principal end of an association between the types...
    public int AgendaId { get; set; }
    [Required]
    public Agenda Agenda { get; set; }
}

I need to always have an Agenda when i create a Room or a Trainer, and an Agenda can only belong to a Room or a Trainer and never to both.

So far i managed to create the relationships but now i faced another problem, when i try to insert a new record to the tables Room or Trainer is says:

Cannot insert explicit value for identity column in table when IDENTITY_INSERT is set to OFF

Code First just created both ends as principals/dependent on each other and for example: i cannot insert a new Room/Trainer without an Agenda, nor an Agenda without a Room/Trainer.

Thank you all for your help.

Answer 1

One to Zero/One Relationships are projected into the database by sharing the same ID on the related entities

public class Agenda
{
    [Key]
    public int Id { get; set; }
    public virtual AgendaRoom Room { get; set; }
    public virtual AgendaTrainer Trainer { get; set; }
}

public abstract class AgendaOneToOne
{
    [Key, ForeignKey( nameof( Agenda ) )]
    public int? Id { get; set; }
    public virtual Agenda Agenda { get; set; }
}

public class AgendaTrainer : AgendaOneToOne
{
    public string Name { get; set; }
}

class AgendaRoom : AgendaOneToOne
{
    public string Description { get; set; }
}

Here you have One-To-Zero/One relation for Agenda.Room and Agenda.Trainer

There is a tutorial for that kind of relations.

Update

It seems you are looking for something different ... so check out this model

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

    protected override void OnModelCreating( DbModelBuilder modelBuilder )
    {
        base.OnModelCreating( modelBuilder );

        var trainer = modelBuilder.Entity<Trainer>();
        trainer.HasKey( e => e.Id );
        trainer.Property( e => e.Name ).IsRequired().HasMaxLength( 100 );

        var room = modelBuilder.Entity<Room>();
        room.HasKey( e => e.Id );
        room.Property( e => e.Name ).IsRequired().HasMaxLength( 100 );

        var agenda_owner = modelBuilder.Entity<AgendaOwner>();
        agenda_owner.HasKey( e => e.Id );
        agenda_owner.Property( e => e.Information ).IsOptional().HasMaxLength( 500 );

        var agenda = modelBuilder.Entity<Agenda>();
        agenda.HasKey( e => e.Id );
        agenda.HasRequired( e => e.Owner ).WithRequiredPrincipal( e => e.Agenda );

        var agenda_room = modelBuilder.Entity<AgendaRoom>();
        agenda_room.HasRequired( e => e.Room ).WithMany( e => e.Agendas ).HasForeignKey( e => e.Room_Id ).WillCascadeOnDelete( false );
        agenda_room.Property( e => e.RoomInformation ).IsOptional().HasMaxLength( 500 );

        var agenda_trainer = modelBuilder.Entity<AgendaTrainer>();
        agenda_trainer.HasRequired( e => e.Trainer ).WithMany( e => e.Agendas ).HasForeignKey( e => e.Trainer_Id ).WillCascadeOnDelete( false );
        agenda_trainer.Property( e => e.TrainerInformation ).IsOptional().HasMaxLength( 500 );
    }

    public virtual DbSet<Agenda> Agendas { get; set; }
    public virtual DbSet<AgendaOwner> AgendaOwners { get; set; }
    public virtual DbSet<Room> Rooms { get; set; }
    public virtual DbSet<Trainer> Trainers { get; set; }

}

public class Trainer
{
    public int Id { get; set; }
    public string Name { get; set; }

    public virtual ICollection<AgendaTrainer> Agendas { get; set; }
}

public class Room
{
    public int Id { get; set; }
    public string Name { get; set; }

    public virtual ICollection<AgendaRoom> Agendas { get; set; }
}

public class Agenda
{
    public int Id { get; set; }
    public virtual AgendaOwner Owner { get; set; }
}

public class AgendaOwner
{
    public int Id { get; set; }
    public virtual Agenda Agenda { get; set; }
    public string Information { get; set; }
}

public class AgendaTrainer : AgendaOwner
{
    public int? Trainer_Id { get; set; }
    public virtual Trainer Trainer { get; set; }

    public string TrainerInformation { get; set; }
}

public class AgendaRoom : AgendaOwner
{
    public int? Room_Id { get; set; }
    public virtual Room Room { get; set; }

    public string RoomInformation { get; set; }
}

and a simple use case

static void Main( string[] args )
{

    using ( var db = new DataContext() )
    {
        Room room_big = db.Rooms.FirstOrDefault( e => e.Name == "Big" );
        if ( room_big == null )
        {
            room_big = new Room
            {
                Name = "Big",
            };
            db.Rooms.Add( room_big );
        }

        var agenda_1 = new Agenda
        {
            Owner = new AgendaRoom
            {
                Room = room_big,
                Information = "Some information",
                RoomInformation = "Some information for the room",
            },
        };
        db.Agendas.Add( agenda_1 );
        db.SaveChanges();

        Trainer trainer_peter = db.Trainers.FirstOrDefault( e => e.Name == "Peter" );
        if ( trainer_peter == null )
        {
            trainer_peter = new Trainer
            {
                Name = "Peter",                        
            };
            db.Trainers.Add( trainer_peter );
        }

        var agenda_2 = new Agenda
        {
            Owner = new AgendaTrainer
            {
                Trainer = trainer_peter,
                Information = "Some information",
                TrainerInformation = "Some information for the trainer",
            },
        };

        db.Agendas.Add( agenda_2 );
        db.SaveChanges();

    }

}

Answer 2

[DatabaseGenerated(DatabaseGeneratedOption.Identity)]
[Key]
public int AgendaId { get; set; }

You should add this attribute for identitiy problem ,that should solve the problem. By the way you need the define what is primary key for this maybe this occurs the problem.