Adding data in Entity Framework in tables with Foreign Key

Question

I am struggling to Insert data in a table with Foreign key relationship.I did read a
few articles on how to do this but did not help much and decided to open up a new question.

Scenario :

User table has a foreign key on role table ID.

On the role table Id is integer and is auto incremented

Here is my code,

        SalesTrainerEntities db = new SalesTrainerEntities();

        var user = new User();
        var role = db.Role.FirstOrDefault(r => r.ID == 1);

        user.UserName = "test";
        user.Pass = "123";
        user.CreatedBy = "test";
        user.DtCreated = DateTime.Now;
        user.Role = role;

        //user.RoleId = 1;
        //user.EntityKey = new EntityKey("Role", "ID", 1);
        //user.RoleReference.EntityKey = new EntityKey("Role", "ID", 1);

        db.AddToUser(user);

        db.SaveChanges();

On Save Changes I get error stating

Unable to update the EntitySet 'User' because it has a DefiningQuery and no    <InsertFunction> element exists in the <ModificationFunctionMapping> element to support the current operation.

Any pointers will be highly appreciated.

Regards,

Sab

Look here, maybe it'll help you.

Patryk Ćwiek
– Patryk Ćwiek

2012-06-15 10:28:51 +00:00
Commented Jun 15, 2012 at 10:28 — Patryk Ćwiek
– Patryk Ćwiek, Commented Jun 15, 2012 at 10:28
Missing info: db script and mapping of User table.

Gert Arnold
– Gert Arnold

2021-08-15 14:29:00 +00:00
Commented Aug 15, 2021 at 14:29 — Gert Arnold
– Gert Arnold, Commented Aug 15, 2021 at 14:29

D.Rosado · Accepted Answer · 2012-06-19 11:58:39Z

0

I used to add children entities this way, I'm assuming a relation 1:N between user and role.

using (Entities db = new Entities())
{              
    var user = new User();
    // fill user data..

    var newRole = db.Role.FirstOrDefault(r => r.ID == 1);
    if(newRole!=null)
    {
        user.roles.AddObject(newRole);
        db.users.AddObject(user);
        db.SaveChanges();
    }
}

//Edit - Updating db

using (Entities db = new Entities())
{ 
    var existingUser = db.User.FirstOrDefault(r => r.ID == 100);
    if(existingUser !=null)
    {
       existingUser.Name = "New name";
       db.SaveChanges();
    }
}

edited Jun 19, 2012 at 11:58

answered Jun 15, 2012 at 10:33

D.Rosado

5,7943 gold badges40 silver badges56 bronze badges

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

2 Comments

user1131926 Over a year ago

Hi I am now stuck when updating the data

user1131926 Over a year ago

Thanks Rosado .I had to get rid of the foreign key relation by manually editing the edmx file and also any associations that it had created.Anyway its working now.

Collectives™ on Stack Overflow

Adding data in Entity Framework in tables with Foreign Key

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related