CODEWITHSUNDEEP
asp.net-mvcentity-framework-core
tocoolforscool
tocoolforscool

Reputation: 452

How to make a join table using EF core code first

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)).

Upvotes: 9

Views: 37520

Answers (2)

Soheil Alizadeh
Soheil Alizadeh

Reputation: 3066

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

Upvotes: 7

Shyju
Shyju

Reputation: 218732

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>

Upvotes: 2

Related Questions