How to design a database interface

Question

In order to provide access to the objects in my database, I created an interface for all the team members to be used like this (simplified example):

public interface IDatabase
{
   ObservableCollection<Animal> Animals{ get; }
}

I didn't want the team to access internals like the database context or some oracle objects (encapsulation)...

I implemented two specific classes to be used in real life and for unit tests:

public class TestDatabase : IDatabase
{ }

public class OracleDatabase : IDatabase
{ }

After some usage the team members are asking for more and more functions, and I have to add methods to my interface:

public interface IDatabase
{
   ObservableCollection<Animal> Animals{ get; }
   ObservableCollection<Animal> Animals(Gender gender);
   ObservableCollection<Animal> Animals(Gender gender, Race race);
}

Some of the filtering and sorting stuff could of course be done by the developer himself, but it is better located inside the database.

---

My problem now is that my interface is exploding, it's getting more specialized functions each day, it is far from stable and keeps changing all the time.

Is my design flawed right from the start?

Some ideas to solve that issue:

1. expose the database context object to all developers (bad I think)
2. add a function that accepts a linq query

Answer 1

You are trying to reinvent the Repository/UnitOfWork patterns and you are doing it not quite correct.

A correct approach would be close to this:

// shared between repositories
public interface IGenericRepository<T> 
{
    T CreateNew();

    void Delete( T item );
    void Update( T item );
    void Insert( T item );

    IEnumerable<T> FindAll();
    T FindOne( int id );
}

// specific repositories
public interface IAnimalRepository : IGenericRepository<Animal>
{
    IEnumerable<Animal> FindByNumberOfLegs( int NumberOfLegs );
    // ... anything specific follows
}

public interface IHumanRepository : IGenericRepository<Human>
{
    IEnumerable<Human> FindByGender( Gender gender );
    //  ... specific repository logic follows
}

// unit of work - a service for clients
public interface IUnitOfWork : IDisposable
{
    IAnimalRepository AnimalRepository { get; }
    IHumanRepository  HumanRepository { get; }
    // .. other repositories follow

    void SaveChanges(); 
}

This way your service layer depends on the repository layer and you can switch between implementations easily, for example for unit testing. Your clients write

// example code
using ( IUnitOfWork uow = new YourImplementationOfUnitOfWork() )
{
   var animals = uow.AnimalRepository.FindByNumberOfLegs( 3 );

   var person = uow.HumanRepository.CreateNew();
   person.Name = "John";
   uow.HumanRepository.Insert( person );

   uow.SaveChanges();
}

If you plan to restrict the number of methods, you can just modify slightly the repository interface:

// shared between repositories
public interface IGenericRepository<T> 
{
    T CreateNew();

    void Delete( T item );
    void Update( T item );
    void Insert( T item );

    IQueryable<T> Query { get; }
}

This way your clients can use LINQ:

// example code
using ( IUnitOfWork uow = new YourImplementationOfUnitOfWork() )
{
   var animals = uow.AnimalRepository.Query.Where( a => a.NumberOfLegs == 3 );

   var person = uow.HumanRepository.CreateNew();
   person.Name = "John";
   uow.HumanRepository.Insert( person );

   uow.SaveChanges();
}

Answer 2

Could I suggest applying the interface segregation principle? i.e. separate your interfaces into logical groups. This will also allow the users of your interface to not implement parts of it they don't use / need. Stability should also increase as you'll have more discreet pieces of testable code.