Should we use Data Repository pattern in MVC application using Entity Framework Code First approach?

Question

I have developed many application now with Entity Framework Code First approach. In all of the I use Data Repository pattern. This Data Repository pattern queries over single entity at a Time. for e.g,

I have 2 models Employee and Department

So to fetch all employees and department I would create 2 data repository instances. e.g

var empRepository = new DataRepository<Employee>();
var allEmployees = empRepository.GetAll();

var depRepository = new DataRepository<Department>();
var alldepartment = depRepository.GetAll();

Now, This pattern works great in most of the case. Now, When I want to perform join I can not do that in this pattern. I can perform join only after I have fetched all records of both entities and then i can use join on in-memory data. this creates extra overhead of 2 queries in my logic. Does any one have good pattern or solution that can be used with DataRepository pattern. Please suggest any alternatives to this pattern.

Answer 1

I actually just implemented a Join function in my generic repository just yesterday. It's a lot easier to do than how everyone's making it out to be, and you can use some cool features of Entity Framework while doing so.

To get started, I'm using a repository similar to what I wrote in this blog post. You'll notice that a number of the methods are returning IQueryable. This is no accident. IQueryable will allow to still use deferred execution, meaning that the query won't be run on the database until something forces it to (i.e. a loop or a .ToList() call). You'll see that, with putting the Join on this type of repository, that you won't end up needing to load up all the entities into memory to do the join, since all you'd be exposing is an IQueryable.

That being said, here's how I've got the Join in my repository, based off of the Queryable version of the Join method:

public IQueryable<TResult> Join<TInner, TKey, TResult>(IRepository<TInner> innerRepository, Expression<Func<T, TKey>> outerSelector, Expression<Func<TInner, TKey>> innerSelector, Expression<Func<T, TInner, TResult>> resultSelector) where TInner : class
{
  return DbSet.Join(innerRepository.All(), outerSelector, innerSelector, resultSelector);
}

This, then, only makes one query to the database to get all of the information that you're requesting (again, since it only passes an IQueryable in the All() method).

Answer 2

with Repository pattern you can join two tables as in normal scenario.say you have defined your repository like this :

 public interface IRepository<T>
    {
        T GetById(object id);
        void Insert(T entity);
        void Update(T entity);
        void Delete(T entity);
        IQueryable<T> Table { get; }
    }

then you can join two table as following :

from p in _someRepository.Table
join p2 in _someOtherRepository.Table on p.FOO equals p2.BAR

with this approach there is no need to load all of table entries into memory.