CODEWITHSUNDEEP
c#entity-frameworkrepository-pattern
ExpertGenie
ExpertGenie

Reputation: 195

Multiple joins in linq using repository pattern generics

I have implemented a repository pattern that contains a list of generic methods to perform CRUD operations to the database. This is illustrated below.

public Repository(DbContext context)
    {
        _entities = context;
        _dbset = context.Set<T>();
    }

    public IEnmerable<T> GetAll()
    {

        return _dbset.AsEnumerable<T>();
    }

Next, I have a service layer that is implements the above repository class.

TruckService

In this class, to make use of the repository generic methods, I have the below code: 

private IRepository<Workers> _repositoryWorkers;
private IRepository<Jobs> _repositoryJobs;
private IRepository<Depts> _repositoryDepts;


public WorkerService(IRepository<Workers> repositoryWorkers, IRepository<Jobs> repositoryJobs, IRepository<Depts> repositoryDepts) 
{
  _repositoryWorkers = repositoryWorkers;
  _repositoryJobs = repositoryJobs;
  _repositoryDepts = repositoryDepts
}

// a query that makes multiple joins to retrieve data.
from h in _repositoryWorkers.GetAll()
join p in _repositoryJobs.GetAll() on h.Id equals p.Id
join q in _repositoryDepts.GetAll() on h.Id equals q.Id

So, in the above TruckService class, I have implemented the IRepository interface to inherits the generic methods. In this class, I am making a query that performs multiple joins to obtain data from the database. I am getting all the data correctly but I have read somewhere that this approach is :

mixing and matching repository-based queries and LINQ queries. Rather than doing a true join, you're fetching all the rows for each table and then joining them in-memory

So, in this case could someone please advise me a better / efficient approach of doing this multiple joins ?

*Note that I am new with EF, and this is my first project using repository pattern.

Upvotes: 1

Views: 4072

Answers (2)

axelio
axelio

Reputation: 197

I would suggest you to return IQueryable<T> from generic repository, so you can modify later the query in specific inheriting repositories.

However in order to have Include in generic repostiory, you can pass to GetAll method params Func<T, object>[] with properties to include and then aggregate this into query. See example here on this gist.

However, you won't be able to use ThenInclude. Consider going for returning IQueryable so you can later modify query.

Upvotes: 0

M Bakardzhiev
M Bakardzhiev

Reputation: 632

Returning IEnumerable set of all entities in your database is a perfect way to shoot yourself in the foot. Avoid doing this, because it loads all data in-memory.

You can use IQueryable, unlike IEnumerable, it executes query in the database, you can add filtering to it, and it won't actually perform the query and load results until you materialize (like calling ToList(), FirstOrDefault() and several others).

Foreign keys, navigation properties are all tools you can use to navigate between entities easily, and save yourself a lot of explicit joins. You can use things like eager loading for navigation properties you expect to use often. One of the main goals of EF is to help you write less code. It saves you lot of time and also makes the places where you can mistake less (less code, less mistakes, but still be careful when using it).

Whether you want to use repository pattern or not, it's your choice. Some people see it is an unnecessary abstraction over the abstraction already provided by EF context and sets. Some people see it as a necessity that separates business logic from database access. However, it is important to read a lot about it, before using it, especially if you are new to EF. Here is what Microsoft give as an example in their documentation.

Upvotes: 2

Related Questions