How to make a join table using EF core code first

Question

I have these three models:

public class Card
{
    public int ID { get; set; }
    public string Name { get; set; }
    public string Class { get; set; }
    public string Image { get; set; }
}

public class Deck
{
    public int ID {get; set;}
    public string Name {get; set;}
    public string Class {get; set;}
    public virtual ICollection<DeckCard> DeckCards {get; set;}
}

public class DeckCard
{
    public int ID {get; set;}
    public int DeckID {get; set;}
    public int CardID {get; set;}
}

I want to use the DeckCard model as a join table essentially. I need to be able to populate it in my DecksController/Index view. Can anyone give me guidance or point me in the right direction?

Note that the Card table is a static table (I don't know if that's the correct term for it but it will be populated and unchanged with whatever cards the game currently has (it's a deck building website)).

Solution 1

First. You Not need to Create model(DeckCard) for one to many relations so that EF Automatic Create This Table In Your Database.

Second. Add or override OnModelCreating Method in your DbContext Class For Example:

MyApplicationDbContext.cs

 public class MyApplicationDbContext : DbContext
 {
     public DbSet<Card> Cards { get; set; }
      
     public DbSet<Deck> Decks { get; set; }

     //This is the Model Builder
     protected override void OnModelCreating(DbModelBuilder modelBuilder)
     { 
           modelBuilder.Entity<Card>()
                .HasRequired<Card>(_ => _.Card)
                .WithMany(_ => _.Deck);
                
     }

 }

DecksController.cs

 public ActionResult Index()
 { 
      var model = context.Card.AsNoTracking().Include(_ => _.Decks).ToList(); 
        
      return View(model);
 }

For a join query with Eager loading use Include();

also, see below Links:

Getting more performance out of Entity Framework 6

Entity Framework Loading Related Entities

Configure One-to-Many Relationship

Relationships In EF Core

Solution 2

With your current entity structure, you can write a join between all three data sets and then do a group by on the DeckId and derive the results.

I would create 2 view model classes for this grouped data representation for my view.

public class DeckVm
{
    public int Id { set; get; }
    public string Name { set; get; }
    public IEnumerable<CardVm> Cards { set; get; }
}
public class CardVm
{
    public int Id { set; get; }
    public string Name { set; get; }
}

Now the join

var decksWithCards = (from dc in db.DeckCards
                join d in db.Decks on dc.DeckID equals d.ID
                join c in db.Cards on dc.CardID equals c.ID
                select new { DeckId = d.ID, DeckName = d.Name,
                             CardId = c.ID, CardName = c.Name })
    .GroupBy(x => x.DeckId, d => d,
        (ky, v) =>
            new DeckVm
            {
                Id = ky,
                Name = v.FirstOrDefault().DeckName,
                Cards = v.Select(h => new CardVm { Id = h.CardId, Name=h.CardName})
            })
    .ToList();

decksWithCards will be a List<DeckVm> which you can pass to your view. You have to make your view strongly typed to List<DeckVm>

Comments

[tocoolforscool]

I'm using EF core, and I'm going to edit title to specify that, but '.HasRequired' isn't a method of any directive that I'm using. Is there an equivalent for core? Is this code establishing a one to many relationship, isn't that assumed by EF when you define a nav property (public virtual ICollection<Card> Cards {get; set;} an entity of type Card with a property ID will automatically be mapped to the entity with the nav property definition... Right?

[tocoolforscool]

In the 1 to many relationship, the 1 is the Card and the many is the Deck right? According to this code?

[Soheil Alizadeh]

@j3ssi3ftw yes, think this link Is Great For your Issue.

[Soheil Alizadeh]

@j3ssi3ftw in this link that is Microsoft doc Can Help you.

[tocoolforscool]

Should the relationship be many-to-many? Each card will undoubtedly be used for more than one deck.

[Soheil Alizadeh]

@j3ssi3ftw yes, i think. These is your model. I Answer your main question.

[tocoolforscool]

Let us continue this discussion in chat.