2

I am new to EF and Database Design. I have been studying about using Repository Pattern and Unit of Work a lot, and I have seen a lot of queries made with LINQ.

But I have one question. If, in future, we change the schema of the database, the repository layer is bound to change, because the entities themselves will change. In other words, the repository layer is tightly coupled with the database structure.

But, if we encapsulate most of the possible transactions using a stored procedure in the database itself, and then import them using EF, then change in the database design will not affect the repository layer so much. In other words, the layers would be like this:

DATABASE TABLES <--->  STORED PROCEDURES <---> EF <---> REPOSITORY <---> APPLICATION LOGIC

Why don't we follow this pattern, or is there any pitfall that I am overlooking? Doesn't this loosely couple the application with the database?

I thought that using LINQ also binds the schema with the application, which is my concern. If in future, I want to further normalise my database, it would mean rewriting the whole LINQ queries. It's one more reason I prefer views instead showing raw tables. It's like using properties instead of fields in classes. It adds a layer of encapsulation.

2

I create my DALs with LINQ. Personally I feel its a lot easier to debug queries using LINQ and I dont enjoy bouncing from MSSQL and VS so in a way It also keep things simple for me. In the past I've come around some very nasty bugs that took a while to point as a (for example) datatype error on a stored procedure, while It would have taken minutes to spot such error on the VS debugger (maybe wouldn't have even compile in the first place).

I do use stored procedures in cases when then query is just to complex and performance is worlds apart from using a LINQ. But this would account for about 10% of any given DAL class, its not really that much.

If you check some other sources you'll notice it mainly comes down to Security and Performance... There are other implications and I dig the argument of the refactoring, but if you refactor your database you still need to recreate the EF mapping and work around those changes on your stored procedures, views, etc.

More on the subject:

Linq2sql vs Stored Procedures

Why I Avoid Stored Procedures (And You Should Too)

In this answer there's an argument for readability that also makes a lot of sense from the software development side of the discussion:

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.