C#•2y ago

✅ I want to get duplicate items in my List (EF Core)

I am trying to get a list of Cards from my database, and I am able to successfully get a List of Guids, where there are duplicate values, but when I try to use an efficient .Where() using .Contains(), it only returns 1 of the Card that shows up multiple times in the list. Here is my code:

var deckList = await _context.DeckContents
            .Where(i => i.DeckId.ToString() == deckId)
            .Select(i => i.CardId)
            .ToListAsync();

var cardList = await _context.Cards
            .Where(i => deckList.Contains(i.Id))
            .ToListAsync();

var deckList = await _context.DeckContents
            .Where(i => i.DeckId.ToString() == deckId)
            .Select(i => i.CardId)
            .ToListAsync();

var cardList = await _context.Cards
            .Where(i => deckList.Contains(i.Id))
            .ToListAsync();

cardList is a List<Card>, and originally my code did a foreach loop over the List of Guids, but that caused a lot of DB queries, so I found that doing .Contains helps, and it works fine, except it doesn't add duplicates, when I want it to. Hope this makes sense! Thanks!

21 Replies

hoofedearOP•2y ago

For example, if deckList contains 17 values, where 6 are the same value, cardList will return 12 items instead of 17 (only 1 instance of the duplicate value)

Anton•2y ago

this means your card ids are identical and also this code is weird look at the queries it generates

Becquerel•2y ago

you are using i.Id instead of i.CardId, is that relevant?

hoofedearOP•2y ago

Well what I have is a table of Cards that are identified by their ID, so there will be instances where a deck list will have multiple of the same ID Yes, but I need to account for that, is there perhaps a better way to loop over a list of IDs like that without using foreach? Rider complains to me about excessive DB calls when I used foreach before 😅

Anton•2y ago

yes, with a join in sql which you can get in a variety of ways in ef core the easiest way is probably navigation properties but you should really learn some sql basics

hoofedearOP•2y ago

Ah okay I see, I did try a join before but it returned 0 cards but I bet my logic was messed up. I’ll look into that, thank you!

D.Mentia•2y ago

Doing a foreach is fine, as long as you do ToListAsync first; ToListAsync pulls it out of the database, one query. (Or more specifically, as long as it's not an IEnumerable anymore then the query is done) What's inefficient here is that it's doing two queries

var cardList = await _context.Cards
            .Where(c => c.DeckId.ToString() == deckId)
            .ToListAsync();

var cardList = await _context.Cards
            .Where(c => c.DeckId.ToString() == deckId)
            .ToListAsync();

Just pull out the cards directly if you want a list of them, and you generally shouldn't be using IDs, EF can handle the navigations, so it'd just be var cardList = myDeck.Cards

hoofedearOP•2y ago

Thanks for the tip! And could you explain the idea of not using IDs? What I have is a table called DeckContents and it’s basically a M-M relation of DeckIDs and CardIDs, so what I do is when I pull the deckList, I get all CardIDs that match the DeckID I’m grabbing. Is there a better way?

D.Mentia•2y ago

Just put a List<Card> in your DeckContents class and a List<Deck> in your Card class and wire up the EF configuration to tell it they're many to many

hoofedearOP•2y ago

😳 that’s so cool. I’ll look into that, thanks! Hm okay so what I have so far is a List<Card> inside of my DeckContents class, with the DeckId as the Key. Is that right? Because a Deck has the details of the Deck (Name, description, Owner) and DeckContents is purely for keeping track of what Cards are in the Deck Is that the only list I need? Or do I need to put something in the Card class to relate them? Inside of DeckContent class: public virtual List<Card> DeckList { get; set; } Inside of ApplicationDbContext class builder: builder.Entity<DeckContent>().HasMany(i => i.DeckList).WithMany();

Anton•2y ago

read the documentation on navigation properties

Angius•2y ago

virtual 🤢 And do you want a many-to-many relationship? If so, then neither the deck would hold the ID of the card, nor the card would hold the ID of the deck

hoofedearOP•2y ago

I think I'm actually going for a one-to-many, and I did remove the virtual haha I got it working how I want, where it returns a List of Cards, but it's still omitting duplicate Cards ://

var cardIds = new List<string>
        {
            "F48B3F46-0E62-4F44-8064-857CD3040659",
            "8435C03C-B9A2-40FA-A979-879BD120A011",
            "41AC2C18-A26B-4683-B326-97F0E75B4F2A",
            "d99a9a7d-d9ca-4c11-80ab-e39d5943a315",
            "AC7BA3F5-3AD5-47E7-86C9-31954F015B91",
            "46F4AE10-A3DA-4D4F-8706-87CCB076C1F2",
            "069B3C69-EE4C-4FC9-981E-62DB77F9821D",
            "3AF09CFD-1DD1-45A8-869B-B21BCB03F7CE",
            "5E6F3C49-DBFA-4EAB-A561-656E74266834",
            "4E9A34A4-2F2C-4C9C-A8D4-73953B62E189",
            "7C337AF5-35BF-4A80-8CC0-BB9AF20305B3",
            "E48E23FE-F0ED-4C5A-B90B-30CF096A9D02",
            "5C3593AA-E33C-4482-A0BB-C843AC70A824",
            "52C79D57-A8EB-427D-86AC-B203C0BB7CDA",
            "52C79D57-A8EB-427D-86AC-B203C0BB7CDA",
            "52C79D57-A8EB-427D-86AC-B203C0BB7CDA",
            "52C79D57-A8EB-427D-86AC-B203C0BB7CDA",
            "52C79D57-A8EB-427D-86AC-B203C0BB7CDA",
            "52C79D57-A8EB-427D-86AC-B203C0BB7CDA",
            "52C79D57-A8EB-427D-86AC-B203C0BB7CDA"
        };

        var cards = await context.Cards.ToListAsync();

        var deckList = new List<Card>();
        
        foreach (var id in cardIds)
        {
            var c = cards.FirstOrDefault(i => string.Equals(i.Id.ToString(), id, StringComparison.OrdinalIgnoreCase));
            if (c != null) deckList.Add(c);
            else
            {
                Console.WriteLine("Unable to find card.");
            }
        }

var cardIds = new List<string>
        {
            "F48B3F46-0E62-4F44-8064-857CD3040659",
            "8435C03C-B9A2-40FA-A979-879BD120A011",
            "41AC2C18-A26B-4683-B326-97F0E75B4F2A",
            "d99a9a7d-d9ca-4c11-80ab-e39d5943a315",
            "AC7BA3F5-3AD5-47E7-86C9-31954F015B91",
            "46F4AE10-A3DA-4D4F-8706-87CCB076C1F2",
            "069B3C69-EE4C-4FC9-981E-62DB77F9821D",
            "3AF09CFD-1DD1-45A8-869B-B21BCB03F7CE",
            "5E6F3C49-DBFA-4EAB-A561-656E74266834",
            "4E9A34A4-2F2C-4C9C-A8D4-73953B62E189",
            "7C337AF5-35BF-4A80-8CC0-BB9AF20305B3",
            "E48E23FE-F0ED-4C5A-B90B-30CF096A9D02",
            "5C3593AA-E33C-4482-A0BB-C843AC70A824",
            "52C79D57-A8EB-427D-86AC-B203C0BB7CDA",
            "52C79D57-A8EB-427D-86AC-B203C0BB7CDA",
            "52C79D57-A8EB-427D-86AC-B203C0BB7CDA",
            "52C79D57-A8EB-427D-86AC-B203C0BB7CDA",
            "52C79D57-A8EB-427D-86AC-B203C0BB7CDA",
            "52C79D57-A8EB-427D-86AC-B203C0BB7CDA",
            "52C79D57-A8EB-427D-86AC-B203C0BB7CDA"
        };

        var cards = await context.Cards.ToListAsync();

        var deckList = new List<Card>();
        
        foreach (var id in cardIds)
        {
            var c = cards.FirstOrDefault(i => string.Equals(i.Id.ToString(), id, StringComparison.OrdinalIgnoreCase));
            if (c != null) deckList.Add(c);
            else
            {
                Console.WriteLine("Unable to find card.");
            }
        }

This is from my seeder, and it's a list of 20 cards, but when I retrieve the list of Cards from the database, it only returns 14

Angius•2y ago

Ah, I think I know why Relationships in a many-to-many are exclusive, meaning only one relationship between given elements can exist at any time If you want one-to-many, meaning one card can belong only to one, single deck, that problem goes away

hoofedearOP•2y ago

Ahhh okay so I do want many to many, hm Because any number of decks can have any number of cards Or is that one-to-many? 😵‍💫 If it helps, I’m working with Magic the Gathering cards, so imagine many decks with many cards, even duplicates of the same card

Angius•2y ago

It's many-to-many, yes And there is a way to solve it, just need to configure the join entity and introduce another identifier

public class Deck
{
    public long Id { get; set; }
    public List<Card> Cards { get; set; }
}

public class Card
{
    public long Id { get; set; }
    public List<Deck> Decks{ get; set; }
}

public class CardInDeck
{
    public Card Card { get; set; }
    public long CardId { get; set; }
    public Deck Deck { get; set; }
    public long DeckId { get; set; }
    public Guid Guid { get; set; }
}

public class Deck
{
    public long Id { get; set; }
    public List<Card> Cards { get; set; }
}

public class Card
{
    public long Id { get; set; }
    public List<Deck> Decks{ get; set; }
}

public class CardInDeck
{
    public Card Card { get; set; }
    public long CardId { get; set; }
    public Deck Deck { get; set; }
    public long DeckId { get; set; }
    public Guid Guid { get; set; }
}

builder.Entity<Deck>()
    .HasMany(d => d.Cards)
    .WithMany(c => c.Decks)
    .UsingEntity<CardInDeck>(
        j => j
            .HasOne(cid => cid.Card)
            .WithMany()
            .HasForeignKey(cid => cid.CardId),
        j => j
            .HasOne(cid => cid.Deck)
            .WithMany()
            .HasForeignKey(cid => cid.DeckId),
        j => {
            j.PrimaryKey(cid => cid.Guid);
            j.HasKey(cid => new { cid.CardId, cid.DeckId });
        }
);

builder.Entity<Deck>()
    .HasMany(d => d.Cards)
    .WithMany(c => c.Decks)
    .UsingEntity<CardInDeck>(
        j => j
            .HasOne(cid => cid.Card)
            .WithMany()
            .HasForeignKey(cid => cid.CardId),
        j => j
            .HasOne(cid => cid.Deck)
            .WithMany()
            .HasForeignKey(cid => cid.DeckId),
        j => {
            j.PrimaryKey(cid => cid.Guid);
            j.HasKey(cid => new { cid.CardId, cid.DeckId });
        }
);

Something like this

hoofedearOP•2y ago

I’ll try that! Thanks! The only part of this that doesn't work is this section:

j => {
  j.PrimaryKey(cid => cid.Guid);
  j.HasKey(cid => new { cid.CardId, cid.DeckId });
}

j => {
  j.PrimaryKey(cid => cid.Guid);
  j.HasKey(cid => new { cid.CardId, cid.DeckId });
}

It's saying that it can't resolve symbol PrimaryKey, like it isn't a valid part of j So I tried it out without that section and I got this error after trying to save data to the database:

System.InvalidOperationException: The value of 'DeckContent.CardId' is unknown when attempting to save changes. This is because the property is also part of a foreign key for which the principal entity in the relationship is not known.

System.InvalidOperationException: The value of 'DeckContent.CardId' is unknown when attempting to save changes. This is because the property is also part of a foreign key for which the principal entity in the relationship is not known.

*without just the j.PrimaryKey part, I did add the j.HasKey part

D.Mentia•2y ago

IDK about how to solve that problem, but it also might be worth thinking about having CardInDeck contain a Quantity, and then not dealing with duplicates at all. This deck has 4 of this ID, done Depending on how you use it and why you need to know duplicates, and if that makes it easier.

hoofedearOP•2y ago

Somehow, I figured it out and got it to work, I just had to give the DeckContent class its own ID to use as its PK, it now returns a List of Cards, including duplicates!

hoofedearOP•2y ago

This is how I'm able to get the list now

Accord•2y ago

Was this issue resolved? If so, run /close - otherwise I will mark this as stale and this post will be archived until there is new activity.

Gaming

Programming

✅ I want to get duplicate items in my List (EF Core)