I have a database for University journal and trying to make an application to work with it using Entity Framework, WPF and MVVM. It contains tables:
University_Subject (Subject_ID (PK), SubjectName)
University_Teacher (Teacher_ID (PK), other fields with info)
University_TeacherSubject (Teacher_ID (FK), Subject_ID(FK)) - for many-to-many connection upper tables
University_Group (Group_ID (PK), other info)
- Universiti_GroupSubject (Group_ID (FK), Subject_ID (FK)) - for connection Groups and Subjects
- University_Schedule (Lesson_ID (PK), Group_ID (FK), Teacher_ID(FK), Subject_ID(FK), ) - uses IDs from other tables.
Main trouble I've faced with: how to correctly delete Subject Entity? I need to delete subject without connected entities (Teacher have to stay, it can have other subjects; Group also has other subjects)
In SQL deletion looks like:
-Delete row in TeacherSubject table;
-delete row in GroupSubject table;
-delete row in Schedule talbe;
-delete Subject;
But in Entity Framework I don't have TeacherSubject,GroupSubject tables -> they are presented as navigation properties.
Code I've tried to use but there is an exception (An unhandled exception of type 'System.Data.Entity.Infrastructure.DbUpdateException' occurred in EntityFramework.dll Additional information: Unable to update the EntitySet 'University_TeaSubj' because it has a DefiningQuery and no element exists in the element to support the current operation.):
void DeleteSubjectFromDB(object parameter)
{
using (_ujc = new UJContext())
{
var tmp = _ujc.University_Subject.Where(s => s.Subject_ID == SelectedSubject.Subject_ID).FirstOrDefault();
if (tmp!=null)
{
//Delete Lessons with SubjectID from schedule
var lessonlst = _ujc.University_Schedule
.Where(l => l.Subject_ID == tmp.Subject_ID).AsEnumerable();
foreach (var ls in lessonlst)
{
_ujc.University_Schedule.Remove(ls);
}
foreach (var teach in _ujc.University_Teacher)
{
if (teach.University_Subject.Contains(tmp))
teach.University_Subject.Remove(tmp);
}
tmp.University_Teacher.Clear();
tmp.University_Group.Clear();
foreach (var gr in _ujc.University_Group)
{
if (gr.University_Subject.Contains(tmp))
gr.University_Subject.Remove(tmp);
}
_ujc.Entry<University_Subject>(tmp).State =
System.Data.Entity.EntityState.Deleted;
_ujc.SaveChanges();
SelectedSubject = null;
UpdateSubjects();
}
}
}
Sorry, I can't attach DB and EF diagram, because of reputation.
Thank you in advance for the help.