using Breezejs to capture Foreign Key Exception on SaveChanges()

Question

I am using breezejs in a ASP.NET MVC 4 SPA and durandal project w/ EF 5.0 Database first setup and things are working fine except for a certain scenario.

Forgive me if I seem somewhat of a noob in regards to javascript, and the SPA stuff. I am learning as I go.

here are my 2 models from EF

Project.cs

public partial class Project
{
        public Project()
        {
            this.Timesheets = new HashSet<Timesheet>();
        }

        public int ProjectId { get; set; }
        public Nullable<int> ClientId { get; set; }
        public string ProjectName { get; set; }
        public string ProjectDescription { get; set; }
        public Nullable<decimal> ProjectRate { get; set; }

        public virtual Client Client { get; set; }
        public virtual ICollection<Timesheet> Timesheets { get; set; }
}

Client.cs

public partial class Client
{
    public Client()
    {
        this.Projects = new HashSet<Project>();
        this.Timesheets = new HashSet<Timesheet>();
    }

    public int ClientId { get; set; }
    public string ClientNo { get; set; }
    public string ClientName { get; set; }
    public string CompanyName { get; set; }
    public string AddressLine1 { get; set; }
    public string AddressLine2 { get; set; }
    public string City { get; set; }
    public string State { get; set; }
    public string Zip { get; set; }
    public string County { get; set; }
    public Nullable<decimal> ClientRate { get; set; }
    public string EmailAddress { get; set; }

    public virtual ICollection<Project> Projects { get; set; }
    public virtual ICollection<Timesheet> Timesheets { get; set; }
}

an excerpt from my Client viewmodel

function deleteClient(model) {
    app.showMessage('Are you sure you want to DELETE this record?', 'Delete a Record', ['Yes', 'No'])
   .then(function (dialogresult) {
       if (dialogresult == "Yes") {
           models.remove(model);
           model.entityAspect.setDeleted();
           return saveRecord(model);
       }
   });
};

function saveRecord(model) {
    return repository.saveEntity(model)
    .fail(handleFailed);

    function handleFailed(error) {
        var err = "Error retrieving Clients : " + error.message;
        error(err);
        logger.error(err, null, null, true);
        return;
    }
};

excerpt from repository.js (datacontext)

function saveEntity(masterEntity) {
    return manager.saveChanges().fail(saveFailed);

    function saveFailed(error) {
            logger.error("Error saving : " + error.message, null, null, true);
    }
}

here is excerpt from my ProjectBillingController.cs

   [HttpPost]
    public SaveResult SaveChanges(JObject saveBundle)
    {
        return _contextProvider.SaveChanges(saveBundle);
    }

Here is my scenario,

I have a row Project "P1" which has a reference to Client "ABC". If I try to delete Client "ABC" breeze throws an exception on inside the SaveChanges in my WEBAPI Controller "ProjectBillingController.cs".

I know that in that in SQL world, this should be the expected error.

But my questions are...

• Can I capture this constraint error on the client side before it gets to the server "ProjectBillingController.cs"?
• The only way I see resolving this issue is to do another query to see if there are any rows in Project entity with that Client and if so halt the delete otherwise continue with the delete. Is there another way to accomplish this?
• Should I write this "Validation logic" inside my viewmodel, or datacontent (repository)

Answer 1

On the table properties of Project, you should have a FK relation with Client right? On this relation, the delete rule should be set to CASCADE (Note that this will delete all the Client's related projects when you delete a Client).

So, step by step: 1. In Visual Studio, check the Server Explorer 2. Right click table "Project" 3. Select "Table Properties"

Check what I mentioned regarding the delete rule, and change it accordingly.

Tiago

Answer 2

I don't believe there is a way to detect on the client that deleting a parent should cascade delete to any child related entities. You can't catch "the error" before calling the server. You can compensate on the client ... as described below.

I think I prefer Tiago's suggestion that you configure your database (and/or EF) to cascade delete automatically.

If that's not possible, you can handle it programmatically on client or server. You could download the child entities (or just their keys) and delete them on the client when you delete the parent. Or you could the same on the server in your controller (or, far better, in a helper class on the server). These are more tedious alternatives that can be necessary sometimes.

If you write the logic on the client, please do so in your "datacontext". This isn't a ViewModel concern.

FWIW, I wouldn't call this "validation logic". You're not validating the data. Validation logic never changes data; it can only pronounce the data valid or invalid.

You are operating on the data according to a business model rule that says the children of instances of this parent EntityType must be deleted when the parent is deleted. There is nothing intrinsically obvious about this rule. From a schema perspective, a "Color" is a "parent" of a Product type because it has a 1-to-many relationship to Product. But, if I deleted the color "Red", I would NOT want all of my red products to be deleted automatically. Because cascade delete is inherently dangerous, you must go out of your way to configure the server components to support it.

Delete is a nasty business. Personally, I avoid delete in my apps if I can and use some form of soft delete instead. I admit that is not always an option.