Repository design for different providers

Question

If the goal is to be able to swap out backend DB providers (EF, OleDB, ODBC, etc) then I'm wondering where the different DB CRUD changes? Currently I have an interface (IGenericContext) that has Add/Update/Delete/Select functions. I then make an ADOContext which implements this and inside these functions is where I do the SQL statements. For Add/Update/Delete this is fine, but select is interesting as I just take a string which is the sql. So in my repository where I have functions like, GetAllTasks() I just call that context.Select function with the sql statement.

However, if I then create an EFContext you wouldn't be able to call this Select function with the string sql. You'd do something that involved Linq which means my Repository needs the code change if I switch the context. Now that's better than the business layer needing a change, but it still seems like it would be nice to not have to change the repository as it's really the context that should handle those things and need to be changed and for Update/Add/Delete it does, but selecting seems more unique by context type.

How would you handle something like that? Imagine I created a text file context for that matter. It seems like it's fairly easy to hide the details of Add/Update/Delete but it's harder to hide the details of select from context from within the repository.

Answer 1

Your IGenericContextinterface won't change, but the underlying implementation will.

That's how this works. The Interface shields you from having to make changes to the consumers of the interface, if you conform to the interface when you write a new IGenericContext implementation. It does not, however, relieve you of having to write the new implementation.

Incidentally, your assertion that you can't use SQL with EF is not true. You can still get results from EF with raw SQL if you want to:

var students = context.Students.SqlQuery("SELECT * FROM dbo.Student").ToList();

or

var student = context.ExecuteQuery<Student>(
    "SELECT * FROM dbo.Student WHERE StudentID = {0}", 1234)
    .FirstOrDefault();

Answer 2

The Repository pattern is a layer on top of the Data Mapping pattern. From Fowler:

Mediates between the domain and data mapping layers using a collection-like interface for accessing domain objects

Without a mapping layer in place, you should be using Table Data Gateway (also known as Data Access Object) and/or Transaction Script instead.

The goal of the repository pattern isn't to allow for swapping providers per se. That is a side effect. And by providers it happens to mean different O/RM mappers like EF, NHibernate, etc. The true benefit is that it gives you a seam that allows you to test your domain logic without hitting the database by using an "In-memory" repository/unit of work that you can build your unit tests around.