Entity Framework 4 Error: Unable to update the EntitySet because it has a DefiningQuery

Question

Okay, here's the scenario. I have 3 tables. One called aspnet_Users one called Categories and a linking table called User_Categories. aspnet_Users and Categories both have primary keys (UserId and ID respectively). The linking table has only two columns: CategoryID and UserId, and there are foreign key relationships setup for each column AND I have a unique key setup for the two columns on User_Categories. This sets up a many-to-many relationship between the aspnet_Users table and the Categories table. I generated my entities edmx file from this database setup, and everything looks perfect and works for almost all operations.

What I want to do is add a new category from a form (which works perfecly by itself), and also, at the same time, associate a specific user with that newly submitted category. When I try to do this I get the error in my subject line. Here is the code I'm using to try this (ctx is my entities context object):

public ActionResult Create(Category category, Guid userId)
{
    aspnet_Users user = ctx.aspnet_Users.SingleOrDefault(x => x.UserId == userId);
    ctx.Categories.AddObject(category);
    user.Categories.Add(category);
    ctx.SaveChanges();;
    return RedirectToAction("Index");
}

Why doesn't this work?

Answer 1

I assume the full exception message is something similar to:

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

This will occur if your DB's table doesn't have a primary key defined.

Add a primary key to your table (using SQL Server Management Studio or whatever), and update your .edmx model from the database.

Answer 2

Error: Unable to update the EntitySet because it has a DefiningQuery

Twice I got this error, twice I searched for answers everywhere, and in the end my mappings was messed up or the database had no primary key or something. I suggest checking it all out...