Repository pattern and localized lookup tables

Question

I am trying to grabs the idea of the pattern repository and trying to get it implemented in database structures I've already set up in the past. I'm now trying to get the best practice to work with my lookup tables. I've created a test project to play around and this is my database model:

You can see that I have three tables for the lookups: Lookup, Language and LookupLanguage. Language table simply contains the languages.

Lookup tables holds the different types used throughout the models.

And LookupLanguage links the both tables together:

I've created anew project with all the models 1 to 1 to the database tables:

I also created a generic repository and a generic CrudService interface:

public interface ICrudService<T> where T : IsActiveEntity, new()
{
    int Create(T item);
    void Save();
    void Delete(int id);
    T Get(int id);
    IEnumerable<T> GetAll();
    IEnumerable<T> Where(Expression<Func<T, bool>> func, bool showDeleted = false);
    void Restore(int id);
}

Now, according to the following post: When implementing the repository pattern should lookup value / tables get their own Repository? , the repository should hide the underlying database layer. So I think I need a new implementation of a service and/or repository to get the lookups, but then, where do I have to tell in which language I need to have the lookup?

Let's take the status (new, accepted, refused) from the company as an example.

The company model is as follow:

public partial class Company : IsActiveEntity
{
    [Required]
    [MaxLength(50)]
    public string CompanyName { get; set; }
    public System.Guid StatusGuid { get; set; }

    [ForeignKey("StatusGuid")]
    public virtual Lookup Status { get; set; }
}

I guess I don't need to have a separate implementation of a repository? But I need a separate implementation CompanyService.

interface ICompanyService : ICrudService<Company>
{
    IQueryable<LookupLanguage> GetStatuses(Guid languageguid);
    LookupLanguage GetStatus(Guid statusguid, Guid languageguid);
}

Is this the correct approach, or do I miss something here?

Answer 1

Creating a Generic LookupRepository in your case in a better option because of your table schema and maintainence perspective.

I'm not sure whether you are using both Service Locator and Repository pattern or just Repository because of the name ICompanyService. But regardless, I agree that Repositories should not represent tables 1-1 always but they do most of the times.

The SO link you provided has a different table structure than yours. You have a generic lookup table vs the link has a separate table for each lookup. In the case where you have separate tables it makes sense to have the lookup repository method go with the entity repository since you will have a separate code to fetch the data for each lookup(as they have separate tables with different schema).

But in you case you have a single table that stores all the lookup types for each language and it makes sense to have a single LookupRepository that returns all the various types of lookups based on Language and LookupType. If you create each lookup method in separate entity repositories (like GetStatuses in CompanyRepository and GetStatuses in ContactRepository) you will have to repeat the logic in the method for each repository.

Think if you change the schema of the lookup table (say add a column) and you want to test all places the lookups are used it will be nightmare if you have lookup methods all over the place and pretty easy if you have one method in LookupRepository.

interface ILookupService : ICrudService<Lookup>
{
    IQueryable<Lookup> GetStatuses(Guid languageguid, LookupType lookupType);
    Lookup GetStatus(Guid statusguid, Guid languageguid, LookupType lookupType);
}

Answer 2

As regards your question, "Is this the correct approach" - this entirely depends on your specific needs.

What you have done doesn't seem to have any real issues. You have implemented the repository pattern using generics which is great. You are using interfaces for your repositories which allows for easier unit testing, also great!

One of your tags seems to indicate you are interested in the Entity Framework. You do not seem to be using that. The Entity Framework would simplify your code by creating the boiler plate classes for you. You can still use your repository pattern code with the classes created by the Entity Framework.

It seems that you are confusing the idea of a service and a repository. A repository is a general object which allows you to get data from a store without caring about the implementation. In your example, ICompanyService is a repository.