ASP MVC Many to Many Edit

Question

My database has 3 tables involved in this problem: Employee, Reports, EmployeeReports. As EmployeeReports only as foreign keys to the other 2 tables, EF converts the entities to a many to many relationship as show here:

    public Employee()
    {
        this.Reports = new HashSet<Report>();
    }
    public int EmployeeID { get; set; }
    public string EmployeeName { get; set; }
    public int EmployerIdFk { get; set; }
    public virtual Employer Employer { get; set; }
    public virtual Passport Passport { get; set; }
      [System.Diagnostics.CodeAnalysis.SuppressMessage  "Microsoft.Usage", "CA2227:CollectionPropertiesShouldBeReadOnly")]
    public virtual ICollection<Report> Reports { get; set; }
    }
    }       
   public partial class Report
   {
    [System.Diagnostics.CodeAnalysis.SuppressMessage("Microsoft.Usage", "CA2214:DoNotCallOverridableMethodsInConstructors")]
    public Report()
    {
        this.Employees = new HashSet<Employee>();
    }

    public int ReportID { get; set; }
    public string ReportName { get; set; }
    public string ReportFilePath { get; set; }

    [System.Diagnostics.CodeAnalysis.SuppressMessage   ("Microsoft.Usage", "CA2227:CollectionPropertiesShouldBeReadOnly")]
    public virtual ICollection<Employee> Employees { get; set; }
   }

I'm trying to relate a selected employee to a selected report using the many to many arrangement (there is an employee and a report with ids of 1).

int eID, rID = 1;
        var employee = db.Employees.Include(e => e.Reports).FirstOrDefault(e => e.EmployeeID == eID);
        var report = db.Reports;//.Single(e => e.ReportID == rID);
         foreach (var item in report)
            {
                if (item.ReportID == rID)
                { employee.Reports.Add(item); }
            }
         db.SaveChanges();

I've tried many variations on this code but it always error out with:

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

How can I correct this error? Thanks

score 0 · Answer 1 · edited May 23 '17 at 12:31

The credit for this solution goes to Kavitha Reddy who posted back in Jan '15 Unable to update the EntitySet - because it has a DefiningQuery and no <UpdateFunction> element exist This is the post:

Right click on the edmx file, select Open with, XML editor
Locate the entity in the edmx:StorageModels element
Remove the DefiningQuery entirely
Rename the store:Schema="dbo" to Schema="dbo" (otherwise, the code will generate an error saying the name is invalid)

I followed these steps and my error message disappeared. It's pretty disturbing that this situation still exists in EF in Apr '16. I was trying out the Many to Many EF feature mostly to see if it would be useful in my application. After this frustrating experience I doubt I'd ever use it. But, many thanks to Kavitha for solving the puzzle!

ASP MVC Many to Many Edit

1 Answers1