Code First Entity Framework Or Not?

Question

Not sure if this is the right place to ask this question or not but here goes.

There's a big debate within the office at the moment about the design direction the current project has gone down and I've decided to seek additional opinions.  We are replacing a java/MySQL document management system with wpf/wcf .net & sql server system.

Development of the GUI and associated services is well under way and the current design choice is code first entity framework.  The meta data within the database isn't too large, several tables with up to 4 million rows, however the current document table within the MySQL database is approx 1TB in size.

We are utilising the new file table feature of SQL 2012 but have yet to create a fully migrated database.  There is a possibility that in the future an alternative document repository may be used such as filenet or sharepoint hence the decision to go with code first. 

A second arm of the project is about to start development which will run parallel to the main application development.  This arm is focusing on interfacing the new system with existing subsidiary systems and focuses on bulk importing and exporting of documents and the associated querying of the system to identify the required documents.

One of the key requirements of the second arm of the project is performance as the existing import/export & query elements of the old system can cause live performance issues.

The developers on the second arm of the project are keen to go with stored procedures and a tuned db, justification for this is performance and limiting the amount of data pinging around the network, as well as enhanced security.

Performance is an issue for the GUI developers who are struggling to meet the current performance needs but the developers do not think stored procedures will provide any additional benefit.

The debate rages on and a decision needs to be made soon so that the second development team can start work.  

So the question is to Code First? or not to Code First?

Your opinions are greatly appreciated.

Answer 1

Is seems that you missed the important point: Using a layered architecture like you intentend to use is highly abstracted from the database level itself. The GUI layer should be absolutly decoupled from the database layer, thus it even should not know that there is a RDBMS behind it. So talking about stored procedures from the GUI perspective level makes no sense at all for me.

The performance key for a WPF/WCF solution is clearly a good designed interface - well it will be surely more than one implemented interface in reality - at the WCF boundary. Here you also still don't need to talk about the database level. It's in the first place about througput. How many clients request how many objects.

The performance needed for integration with the subsidiary systems depends - just a guess - simply on two things: A really fast network access and an appropriate storage using clearly more than one volume for the database and its file groups. The details strongly depend on the inner structure of your application.

On this level using stored procedures is a good practice. But not only for performance reasons. Also access control is much easier to handle with stored procedures.

Okay, I haven't played that much with FileTable and stored procedures for gated access to it. Clearly using the native access to the files has it charme, but this will imho require then additional access and guarding mechanisms to guarantee integrity and security of your data.

Answer 2

Thanks for your response Stefan.  The abstraction point you raise is valid and understood, I think I should have been clearer in describing the scenario.  

The team that are developing the front end don't want any code in SQL so no stored procedures.  The export team are keen for it but the idea is to use the same methodology across both teams.

The front end team have to cater for 500 simultaneous users accessing  1 - 2 cases and associated documents at a time.

The import export team have to account for a handful of services accessing hundreds of cases and thousands of documents at a time.

I'm with you in that I would prefer transactional access via SQL for security and integrity.

---

Scott Kirwan-Thomas