none
Mapping a one to many relationship using fluent api RRS feed

  • Question


  • Hi I can't map my domains to match the database, since database is already created, I can't create or remove any other extra table.

    Database

    CREATE TABLE PLAYER (Id Int, Name varchar(100), PRIMARY KEY (Id))
    CREATE TABLE POSITION (Id Int, Name varchar(100), PRIMARY KEY (Id))
    CREATE TABLE CHAMPIONSHIP (Id Int, Name varchar(100), PRIMARY KEY (Id))
    
    CREATE TABLE RELATED (PlayerId Int, PositionId Int, ChampionshipId int,
    FOREIGN KEY (PlayerId) references PLAYER(id),
    FOREIGN KEY (PositionId) references POSITION(id),
    FOREIGN KEY (ChampionshipId) references CHAMPIONSHIP(id),
    CONSTRAINT AK_RELATED UNIQUE(PlayerId,PositionId,ChampionshipId))

    Domains:

    public class Player { public Player() { this.Relateds = new List<Related>(); }

    public int Id {get;set;} public string Name { get; set; } public virtual ICollection<Related> Relateds { get; set; } } public class Position {

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

    public virtual ICollection<Related> Relateds { get; set; } } public class Championship {

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

    public virtual ICollection<Related> Relateds { get; set; } } public class Related { public int PlayerId { get; set; } public Player Player{ get; set; } public int PositionId { get; set; } public Position Position { get; set; } public int ChamionshipId { get; set; } public Championship Championship { get; set; } }

    FLUENT API:

    modelBuilder.Entity<Player>().HasKey(m => m.Id);
    modelBuilder.Entity<Player>().Property(m => m.Name).IsRequired();
    modelBuilder.Entity<Position>().HasKey(m => m.Id);
    modelBuilder.Entity<Position>().Property(m => m.Name).IsRequired();
    modelBuilder.Entity<Championship>().HasKey(m => m.Id);
    modelBuilder.Entity<Championship>().Property(m => m.Name).IsRequired();

    Seed.cs

    public DbSet<Player> Players { get; set; }
    public DbSet<Position> Positions { get; set; }
    public DbSet<Championship> Championships { get; set; }
    
    private void Seed2(ApplicationDbContext context)
            {
                string[] playerNames = new string[] { "Adam", "John", "Lucas" };
                string[] positionNames = new string[] { "Sniper", "Camper", "Assalt" };
                string[] championshipNames = new string[] { "BF4", "CS1.6", "TF2" };
                
                int relations = 3;
    
                try
                {
                    foreach (var name in playerNames)
                    {
                        if (!this.Players.Any(m => m.Name == name))
                        {
                            Player player = new Player { Name = name };
                            context.Players.Add(player);
                        }
                    }
    
                    foreach (var name in positionNames)
                    {
                        if (!this.Players.Any(m => m.Name == name))
                        {
                            Position position = new Position { Name = name };
                            context.Positions.Add(position);
                        }
                    }
    
                    foreach (var name in championshipNames)
                    {
                        if (!this.Championship.Any(m => m.Name == name))
                        {
                            Championship championship = new Championship { Name = name };
                            context.Championships.Add(championship);
                        }
                    }
    
                    context.SaveChanges();
    
                    for (int i = 1; i <= relations; i++)
                    {
                        var player = context.Players.ToList()[i];
                        var position = context.Positions.ToList()[i];
                        var championship = context.Championships.ToList()[i];
    
                        if (!context.Relateds.Any(m =>
                            context.Players.Any(t => m.PlayerId == t.Id)
                            && context.Positions.Any(t => m.PositionId == t.Id)
                            && context.Championships.Any(t => m.ChampionshipId == t.Id)))
                        {
                            Related related = new Related
                            {
                                Player = player,
                                Position = position,
                                Championship = championship
                            };
    
                            player.Relateds.Add(related);
                            context.Entry(player).State = EntityState.Modified;
                        }
                    }
    
                    context.SaveChanges();
                }
                catch (Exception ex)
                {
                    throw;
                }
            }

    I'm not able to insert into the Related table, I think the fluent api is not maped well, how can I fix it?



    Wednesday, February 4, 2015 4:38 PM

Answers

  • Hello,

    >>I'm not able to insert into the Related table, I think the fluent api is not maped well, how can I fix it?

    As you mentions, your database already exists, so I assume that you are using database first approach, then, as far as I know, the fluent API is made only for Code-First approach and we don't need the fluent API if we create your model via Database-First.

    For database first approach, we just need to import existed tables to the designer surface by “Update Model From Database”. And with your provided model, I noticed that the related table does not contain a primary constraint, in Entity Framework, a table without the primary key would be treated as a view that is read only. So please create a primary constraint for the related table as:

    CREATE TABLE RELATED (PlayerId Int, PositionId Int, ChampionshipId int,
    
    FOREIGN KEY (PlayerId) references PLAYER(id),
    
    FOREIGN KEY (PositionId) references POSITION(id),
    
    FOREIGN KEY (ChampionshipId) references CHAMPIONSHIP(id),
    
    PRIMARY KEY CLUSTERED (PlayerId ASC, PositionId ASC,ChampionshipId asc),
    
    CONSTRAINT AK_RELATED UNIQUE(PlayerId,PositionId,ChampionshipId))
    

    With your provided tables, after adding this constraint, the Related table could add a new row as:

    using (DFDBEntities db=new DFDBEntities())
    
                    {
    
                        RELATED related = new RELATED() { PlayerId = 1, ChampionshipId = 1, PositionId = 1 };
    
                        db.RELATEDs.Add(related);
    
                        db.SaveChanges();
    
                    }
    

    Regards.


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Thursday, February 5, 2015 5:45 AM
    Moderator
  • Just to the one who land here....

    Actually the relation is One-to-Many (I have started the question as Many-To-Many).

    Fluen API:

    modelBuilder.Entity<Player>().HasKey(m => m.Id);
    modelBuilder.Entity<Player>().Property(m => m.Name).IsRequired();
    modelBuilder.Entity<Position>().HasKey(m => m.Id);
    modelBuilder.Entity<Position>().Property(m => m.Name).IsRequired();
    modelBuilder.Entity<Championship>().HasKey(m => m.Id);
    modelBuilder.Entity<Championship>().Property(m => m.Name).IsRequired();
    modelBuilder.Entity<Related>().HasKey(m => new { t.PlayerId, t.PositionId, t.ChampionshipId });
    
    modelBuilder.Entity<Related>().HasRequired<Player>(m => m.Player).WithMany(m => m.Relateds).HasForeignKey(t => t.PlayerId);
    modelBuilder.Entity<Related>().HasRequired<Position>(m => m.Position).WithMany(m => m.Relateds).HasForeignKey(t => t.PositionId);
    modelBuilder.Entity<Related>().HasRequired<Championship>(m  => m.Championship).WithMany(m => m.Relateds).HasForeignKey(t => t.ChampionshipId);
    

    And as the Fred BAO said, the following code works :)

    using (DFDBEntities db=new DFDBEntities())
    {
        RELATED related = new RELATED() 
        { 
           PlayerId = 1, 
           ChampionshipId = 1, 
           PositionId = 1 
        };
    
        db.RELATEDs.Add(related);
        db.SaveChanges();
    }
    Friday, February 6, 2015 1:03 PM

All replies

  • Hello,

    >>I'm not able to insert into the Related table, I think the fluent api is not maped well, how can I fix it?

    As you mentions, your database already exists, so I assume that you are using database first approach, then, as far as I know, the fluent API is made only for Code-First approach and we don't need the fluent API if we create your model via Database-First.

    For database first approach, we just need to import existed tables to the designer surface by “Update Model From Database”. And with your provided model, I noticed that the related table does not contain a primary constraint, in Entity Framework, a table without the primary key would be treated as a view that is read only. So please create a primary constraint for the related table as:

    CREATE TABLE RELATED (PlayerId Int, PositionId Int, ChampionshipId int,
    
    FOREIGN KEY (PlayerId) references PLAYER(id),
    
    FOREIGN KEY (PositionId) references POSITION(id),
    
    FOREIGN KEY (ChampionshipId) references CHAMPIONSHIP(id),
    
    PRIMARY KEY CLUSTERED (PlayerId ASC, PositionId ASC,ChampionshipId asc),
    
    CONSTRAINT AK_RELATED UNIQUE(PlayerId,PositionId,ChampionshipId))
    

    With your provided tables, after adding this constraint, the Related table could add a new row as:

    using (DFDBEntities db=new DFDBEntities())
    
                    {
    
                        RELATED related = new RELATED() { PlayerId = 1, ChampionshipId = 1, PositionId = 1 };
    
                        db.RELATEDs.Add(related);
    
                        db.SaveChanges();
    
                    }
    

    Regards.


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Thursday, February 5, 2015 5:45 AM
    Moderator
  • Just to the one who land here....

    Actually the relation is One-to-Many (I have started the question as Many-To-Many).

    Fluen API:

    modelBuilder.Entity<Player>().HasKey(m => m.Id);
    modelBuilder.Entity<Player>().Property(m => m.Name).IsRequired();
    modelBuilder.Entity<Position>().HasKey(m => m.Id);
    modelBuilder.Entity<Position>().Property(m => m.Name).IsRequired();
    modelBuilder.Entity<Championship>().HasKey(m => m.Id);
    modelBuilder.Entity<Championship>().Property(m => m.Name).IsRequired();
    modelBuilder.Entity<Related>().HasKey(m => new { t.PlayerId, t.PositionId, t.ChampionshipId });
    
    modelBuilder.Entity<Related>().HasRequired<Player>(m => m.Player).WithMany(m => m.Relateds).HasForeignKey(t => t.PlayerId);
    modelBuilder.Entity<Related>().HasRequired<Position>(m => m.Position).WithMany(m => m.Relateds).HasForeignKey(t => t.PositionId);
    modelBuilder.Entity<Related>().HasRequired<Championship>(m  => m.Championship).WithMany(m => m.Relateds).HasForeignKey(t => t.ChampionshipId);
    

    And as the Fred BAO said, the following code works :)

    using (DFDBEntities db=new DFDBEntities())
    {
        RELATED related = new RELATED() 
        { 
           PlayerId = 1, 
           ChampionshipId = 1, 
           PositionId = 1 
        };
    
        db.RELATEDs.Add(related);
        db.SaveChanges();
    }
    Friday, February 6, 2015 1:03 PM