C#•5w ago

Duplicate objects in a DB Query

I'm querying entries from a table in a MySQL database with EFC. When I look at the table in Rider (or via cli for that matter) the table looks like the first image. However, if I start it in debugger mode and look at what the database query returns in my code, I get the first entry in multiple positions How is that even possible?

20 Replies

KaenguruuDevOP•5w ago

I've already dropped the table and created it so I really don't know whats happening here, the issue is not present on other tables which leads me to believe it's an issue related to the table

Sehra•5w ago

are you creating the table with efcore or manually with create table?

KaenguruuDevOP•5w ago

manually I'm not yet that deep in this and basically just put efc on an existing mysql db It seemed to work for some time but today it just suddendly broke

Sehra•5w ago

show the create table and how you mapped it in code

KaenguruuDevOP•5w ago

create table achievements
(
    userId        bigint                             not null,
    achievementId int                                not null,
    achievedAt    datetime default CURRENT_TIMESTAMP not null
);

create table achievements
(
    userId        bigint                             not null,
    achievementId int                                not null,
    achievedAt    datetime default CURRENT_TIMESTAMP not null
);

public DbSet<Achievement> Achievements { get; set; }

protected override void OnModelCreating(ModelBuilder modelBuilder)
{
   modelBuilder.Entity<Achievement>().ToTable("achievements");
}

public DbSet<Achievement> Achievements { get; set; }

protected override void OnModelCreating(ModelBuilder modelBuilder)
{
   modelBuilder.Entity<Achievement>().ToTable("achievements");
}

Angius•5w ago

You have no primary key

Sehra•5w ago

i think you want a composite key there, and tell efcore about it. otherwise efcore will think the key is achievementId from the entity name

KaenguruuDevOP•5w ago

ok let me try

Angius•5w ago

I also wonder how the database is structured achievements here seems to me like it would be a join table between User and Achievement But it's not

KaenguruuDevOP•5w ago

So we have a User table but the achievement id there is from a json file because they are managed by someone who isn't very familiar with dbs. So basically if a user triggers an event that would unlock an achievement, it will check the database if for that user, there already is an entry in this achievements table (which is also used to display a list of all achievements the user has unlocked) I hope that isn't too confusing

Angius•5w ago

Can an achievement be owned by only a single user? Or do you have a table with actual achievements, and what you've shown is just a join table? Ah Wait Yeah, you do

KaenguruuDevOP•5w ago

no anybody can unlock the achievement (I just realized something I missed to tell you: This table isn't a good representation of how it would normally look since in a normal test we'd only have one entry for each achievement for the user)

Angius•5w ago

Yeah, you probably need a composite key to make sure that a given pair of (UserId, AchievementId) cannot be duplicated

KaenguruuDevOP•5w ago

ok that makes sense

Angius•5w ago

I'd still recommend you go code-first though Having to handle the database separately from the code is a pita

KaenguruuDevOP•5w ago

can you explain what you mean with that?

Angius•5w ago

Code-first: you declare your classes in code, create a migration, and that generates the database

KaenguruuDevOP•5w ago

oh I see when I first heard of that it sounded way too scary so I just decided to not bother -- in hindsight not the smartest way of thinking

Angius•5w ago

For example,

public class Skunga
{
    public int Id { get; set; }
    public string Name { get; set; }
    public Bunga Bunga { get; set; }
    public int BungaId { get; set; }
}

public class Skunga
{
    public int Id { get; set; }
    public string Name { get; set; }
    public Bunga Bunga { get; set; }
    public int BungaId { get; set; }
}

will create a table Skungas with primary key Id of type INT, column Name of type TEXT, and foreign key to Bungas table

KaenguruuDevOP•5w ago

thank you so much for the help

Gaming

Programming

Duplicate objects in a DB Query

Did you find this page helpful?