CODEWITHSUNDEEP
c#entity-framework
Rod
Rod

Reputation: 15457

how to check many to many table for duplicates thru Entity Framework

For example, take the following database tables: Students, Courses, and StudentsCourses.

How do I, in Entity Framework, make sure when adding a new course for a student, that the course doesn't already exist? Which means to me, checking StudentCourses table.

Do I need to write straight sql to check that?

Upvotes: 0

Views: 69

Answers (4)

Colin
Colin

Reputation: 22595

If you have a simple many to many relationship, you may not have a StudentsCourse entity. I like this pattern for adding to a many to many relationship:

public Student
{
   private _Courses = new List<Course>();

   public int ID { get; set; }

   public virtual ICollection Courses 
   {
      get { return _Courses; }
      protected set { _Courses = value; }
   }

   public void AddCourse(Course course)
   {
      //And you can add your duplicate check here
      if(!Courses.Any(c => c.ID == course.ID))
         Courses.Add(course);
   }
}

Unfortunately the Courses property is not a read-only collection so it doesn't prevent someone bypassing that method elsewhere with:

student.Courses.Add(course);

But then the methods suggested in the other answers don't prevent that either

Upvotes: 0

oerkelens
oerkelens

Reputation: 5161

BenjaminPaul's answer works very wel. Another option is to try and retrieve your student, and if it does not exist, create a new one.

You could create a method like this 

public StudentCourse CreateOrUpdate(VM_StudentCourse studentCourse)
{
    StudentCourse dbStudentCourse;
    using (var context = new StudentContext()
    {
        dbStudentCourse = context.StudentsCourses.FirstOrDefault(x => x.StudentId == studentCourse.studentId && x.CourseId == studentCourse.courseId);
        If (dbStudentCourse == null)
        {
           dbStudent = new StudentCourse();
           dbStudent.StudentId = studentCourse.StudentId;
           dbStudent.CourseId = studentCourse.CourseId;
           context.Add(dbStudent);
        }
        dbStudent.OtherProperty1 = studentCourse.SomeProp;
        dbStudent.OtherProperty2 = studentCourse.SomeOtherProp;

        context.SaveChanges();
    }
    return dbStudentCourse;
}

Upvotes: 0

Ric
Ric

Reputation: 13248

Create a method:

public bool IsStudentOnCourse(int studentId, int courseId)
{
    using (var db = new DBContext()) //replace for real context..
    {
        return db.StudentsCourses.Any(x => x.StudentId == studentId && x.CourseId == courseId);
    }
}

Upvotes: 0

BenjaminPaul
BenjaminPaul

Reputation: 2931

using (var context = new StudentContext()
{
 var alreadyExists = context.StudentsCourses.Any(x => x.StudentId == studentId && x.CourseId == courseId);
}

Upvotes: 2

Related Questions