Saving many-to-many relationship objects in entity framework

Question

I'm trying to save an entity that forms part of a many-to-many relationship in the entity framework and am getting the error: Unable to update the EntitySet 'zRM_OP_defaultloccvgsMapping' because it has a DefiningQuery and no element exists in the element to support the current operation.

My google-fu is weak on this one, but I understand I may have to edit the designer code and hook up a stored procedure to manually do the insert.

My table structure looks like this:

locations: LOCID, blah..

coverages: CVGID, blah..

loccvgs: LOCID, CVGID (no keys)

Help!

Answer 1

I have this exact question, but I've come up with a solution. I'll warn you though, it's a really bad one. In my situation I have 3 tables:

Video - VideoId , int , PK - blah

Comment - CommentId, int, PK - UserId, int (user that made the comment) - CommentText, string

VideoComments - VideoId, int, PK - CommentId, int, PK

First of all, for entity framework to setup the relationship correctly both columns on the association table (VideoComments in my case) must be a primary key.

That said, here is how I am adding a new comment.

 public VideoDTO AddCommentToVideo(VideoDTO pVideo, CommentDTO pComment)
        {
            Video video = context.Videos.Where(v => v.VideoId == 
        pVideo.VideoId).FirstOrDefault();
            User user = context.Users.Where(u => u.UserId == 
        pComment.User.UserId).FirstOrDefault();
            Comment comment = new Comment();
            comment.CommentText = pComment.CommentText;
            comment.User = user;
            comment.Videos.Add(video);
            context.AddToComments(comment);
            context.SaveChanges();

            return pVideo;
        }

This is very bad...like crossing the streams bad. I do 2 queries to get the full objects mandated by Entity Framework so I can create a new comment. I would like a better way to do this, but I don't know one. Maybe this can help.