How to save data with a View and stored procedure using Breeze

Question

I am using BreezeJs on the server via WebAPI. I have inherited a 3rd party app which pulls data from a variety of lookup tables and custom field tables. To simplify things, I created a SQL View which pulls all the data together and gives them good column names. This Entity is called a "Game" and my mobile app uses Breeze on the client to get games. This all works fine.

I am unsure how to proceed when it comes to Saving.

I need to use the standard Breeze save syntax but on the server I assume something needs to intercept the Save method and run a custom stored procedure to save each column to the correct table.

So my question is: Am I on the right track on how to save entities with Views (not tables) and Breeze?

Answer 1

What you are attempting makes perfect sense. Take a look at the discussion of save interception here: http://www.getbreezenow.com/documentation/contextprovider.

However, given that breeze can support your object graph directly. It might more sense to work with entities that are structured to be closer to your table structure and use breeze navigation properties to access related data. You can still load your data in bulk via either anonymous projection queries that returns collections of entities ( good for lookup tables) or use the EntityQuery.extend method to look related data in a single pass.

Obviously, the decision about which way to go is dependent on the complexity and amount of additional code you need to write. But if you only have a few views then I might go with your suggested approach. As the number of views grows, the alternative approach I've suggested may make more sense.

Answer 2

You've abstracted away the complexity of reading the game data using a view, now all you need to do is abstract away the writing of the data (create/update/delete) using instead of triggers. This approach will make your view behave just like a table so your application code can forget that it's a view altogether and simply treat it like any other table.

TechNet article:

The primary advantage of INSTEAD OF triggers is that they enable views that would not be updatable to support updates. A view based on multiple base tables must use an INSTEAD OF trigger to support inserts, updates, and deletes that reference data in more than one table.