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.