Entity Framework and a Dynamics NAV database

Question

I'm currently working on an Intranet application project, using ASP.NET MVC 3. One of the primary requirements is that all data created with the application must be available in the clients' ERP software as well; they work with MS Dynamics NAV. Accessing the data is not really a problem, as NAV uses SQL-Server as its database.

In fact I already have a working prototype, that uses Entity Framework for data access. The main issue here is that my company also develops quite a lot of custom functionality within the ERP, and thus some of the database tables I have to read from and write to often change. This would not really be a problem in a usual situation, but here, each and every field in the database is marked as not null, a very annoying feature of Dynamics NAV. That means that every field added to a table breaks my code, as Entity Framework tries to insert null when it doesn't know of a field.

Can any of you think of a solution that would not require regenerating the model after each change in the tables ?

Edit : Unfortunately our client still uses version 4.0 of Nav, so webservices are not an option.. I have already developed another application for them (part of a Warehouse management system which runs on mobile terminals), accessing SQL Server directly, but it was not quite the same scale.

Answer 1

If your requirement is to integrate with ERP like Dynamics NAV you should pass all data access through its application server. I think Dynamics NAV expose web services for communication with application server. Accessing its tables directly is way to disaster especially due to transactions and possible deadlocks. EF will make this even worse because you will not be able to tweak queries.

Application integration through database requires full control over SQL generated by all accessing applications.

The answer to your main question is no. The point of database first development is to regenerate your model after each database modification - especially if each database modification is breaking change (another reason why integration through database is not a good choice for you).