Entity framework delete without key

Question

I'm using Entity Framework 5 and I'm trying to delete an entity that doesn't have a primary key. By this I mean my table doesn't even have a primary key.

The scenario is as follows:

I have 3 tables which have the following flow:

MainTeam -> UserPerTeam <- User

My Mainteam table has primary key ID is in relation with MainTeamID in table UserPerTeam.

My User table has primary key ID in relation with UserID in table UserPerTeam.

UserPerTeam has these 2 relations and some other fields needed in that table.

So now when I use the following code, my Entity Framework breaks on the fact that it doesn't have a primary key:

var upt = new FighterPerTeam { UserID = userId, TeamID = teamId };
Entities.UserPerTeam.Attach(upt);
Entities.UserPerTeam.Remove(upt);
Entities.SaveChanges();

So now I get the almighty error:

Unable to update the EntitySet 'UserPerTeam' because it has a DefiningQuery and no element exists in the element to support the current operation.

I'm just wondering on how I can fix this is a safe way. I know I can use the following code, but I don't really like using this as it seems un-Entity Framework.

        var cmd = ("Delete from UserPerTeam where userID = " + userId + " and teamId = " + teamId);
        Entities.Database.ExecuteSqlCommand(cmd);

Almost forgot to mention that I'm using Lazy Loading and that I only want to remove the entity in the UserPerTeam table. If your solution also deletes the MainTeam or User object, then I can't use it.

I'm welcoming all suggestions.

Stating the obvious, but you could add a primary identity key to your UserPerTeam join table(what I usually do). You could also make the userid/teamid a composite primary key using fluent mapping. — AaronLS
– AaronLS, Commented Mar 20, 2013 at 22:03
Tables without primary key are read only in entity framework unless stored procedures are mapped to their insert, update and delete operations. Mark both columns in junction table as composite key in the database. — Ladislav Mrnka
– Ladislav Mrnka, Commented Mar 20, 2013 at 22:11
Thank you @LadislavMrnka as that solved the problem for me. I do hate the fact that I needed to delete the table in my edmx and then use update -> add new table. Just using the update -> refresh is still broken when it comes to stuff like this I guess. Can you add this as an answer so I can accept it? — whodares
– whodares, Commented Mar 20, 2013 at 22:23

Ladislav Mrnka · Accepted Answer · 2013-03-21 08:15:40Z

5

Tables without primary key are read only in entity framework unless stored procedures are mapped to their insert, update and delete operations. Mark both columns in junction table as composite key in the database.

answered Mar 21, 2013 at 8:15

Ladislav Mrnka

365k60 gold badges666 silver badges673 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Entity framework delete without key

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related