EF foreign key constraints and repository pattern

Question

I have this simple Delete Get and Post methods in a asp.net mvc application

public ActionResult Delete(int ehrId, int id)
        {
            EHR ehr = ehrRepository.FindById(ehrId);
            PhysicalTest test = ehr.PhysicalTests.Where(t => t.ID == id).Single();
            return View(test);
        }


        [HttpPost, ActionName("Delete")]
        public ActionResult DeleteConfirmed(int ehrId, int id)
        {
            EHR ehr = ehrRepository.FindById(ehrId);
            PhysicalTest test = ehr.PhysicalTests.Where(t => t.ID == id).Single();
            ehr.PhysicalTests.Remove(test);
            unitOfWork.Commit();
            TempData["Success"] = "You have deleted the Physical Test Succesfully";
            return RedirectToAction("Index");
        }

the problem is that when I try to delete a child object this way and EF will complain

The operation failed: The relationship could not be changed because one or more of the foreign-key properties is non-nullable. When a change is made to a relationship, the related foreign-key property is set to a null value. If the foreign-key does not support null values, a new relationship must be defined, the foreign-key property must be assigned another non-null value, or the unrelated object must be deleted.

One answer is to use a PhysicalTest(child element) repository instead of a EHRRepository.. but that doesnt seem like a good solution cause I want to enforce the security of always querying through the parent object to avoid a user from editing/deleting a physicalTest that doesnt belong to him.

I would love to just limit my repositories to just aggregate roots.

Heres my current SqlRepository generic implementation.... Im open to suggestions.

public class SqlRepository<T> : IRepository<T>
                                    where T : class, IEntity {

        internal SummumnetDB context;
        internal DbSet<T> _objectSet;

        public SqlRepository(SummumnetDB context)
        {
            this.context = context;
            this._objectSet = context.Set<T>();
        }


       public IQueryable<T> Find(Expression<Func<T, bool>> predicate) {
            return _objectSet.Where(predicate);
        }

        public void Add(T newEntity) {
            _objectSet.Add(newEntity);
        }

        public void Remove(T entity) {
            _objectSet.Remove(entity);
        }

        public IQueryable<T> FindAll()
        {
            return _objectSet;
        }

        public T FindById(int id)
        {
            return _objectSet.Single(o => o.ID == id);
        }


    }

score 0 · Accepted Answer · edited May 23 '17 at 12:19

Your EHR and PhysicalTest forms aggregate where EHR is aggregate root for PhysicalTest because PhyscialTest cannot exist without EHR (your exception says that FK in PhysicalTest cannot be null). Repository should exist per aggregate root and it should offer specific method to deal with relations.

Yes it will not be generic because generic approach for entities with different configurations and requirements doesn't work.

What is the problem with your code? Calling ehr.PhysicalTests.Remove(test) will not delete test. It will only sets its FK to null. To delete test as well you must really call context.DeleteObject(test). To allow direct deleting you must use identifying relation.

EF foreign key constraints and repository pattern

1 Answers1