How to use joins with generic repository pattern - Entity Framework

Question

I have a Generic Repository like below which handles my CRUD, for a single entity its easy to use, problem starts when i try to join my POCOs.

Lets say I have these POCO, they are mapped using fluent api (many to many and One to many relation) :

public class Student
{
    public Student() 
    {
        this.Courses = new HashSet<Course>();
    }

    public int StudentId { get; set; }
    public string StudentName { get; set; }

    //FKs 
    public virtual Standard Standard { get; set; }
    public int StdandardRefId { get; set; }

    public virtual ICollection<Course> Courses { get; set; }
}

public class Course
{
    public Course()
    {
        this.Students = new HashSet<Student>();
    }

    public int CourseId { get; set; }
    public string CourseName { get; set; }

    public virtual ICollection<Student> Students { get; set; }
}
public class Standard
{
    public Standard()
    {
        Students = new List<Student>();
    }
    public int StandardId { get; set; }
    public string Description { get; set; }

    public virtual ICollection<Student> Students { get; set; }
}

Mapping:

protected override void OnModelCreating(DbModelBuilder modelBuilder)
{
    //Many-To-Many
    modelBuilder.Entity<Student>()
                .HasMany<Course>(s => s.Courses)
                .WithMany(c => c.Students)
                .Map(cs =>
                        {
                            cs.MapLeftKey("StudentRefId");
                            cs.MapRightKey("CourseRefId");
                            cs.ToTable("StudentCourse");
                        });
        //One-To-Many
        modelBuilder.Entity<Student>()
                .HasRequired<Standard>(s => s.Standard)
                .WithMany(s => s.Students)
                .HasForeignKey(s => s.StandardId);

}

Generic Repository:

public class Repository<T> : IRepository<T>
    where T : class, IDisposable
{
    internal MyDbContext context;
    internal DbSet<T> dbSet;

    public Repository()
    {
        context = new MyDbContext();
        this.dbSet = context.Set<T>();
    }

    public bool Add(T entity)
    {
        var query = dbSet.Add(entity);

        if (query != null)
            return true;
        return false;
    }

    public bool Update(T entity)
    {
        dbSet.Attach(entity);
        var query = context.Entry(entity).State = EntityState.Modified;

        if (query == EntityState.Modified)
            return true;
        return false;
    }
    public bool Delete(T entity)
    {
        var query = dbSet.Remove(entity);

        if (query != null)
            return true;
        return false;
    }
    public bool Delete(Guid id)
    {
        var query = dbSet.Remove(dbSet.Find(id));

        if (query != null)
            return true;
        return false;
    }
    public T GetById(Guid id)
    {
        var query = dbSet.Find(id);

        if (query != null)
            return query;
        else
            return null;
    }
    public ICollection<T> GetAll()
    {
        return dbSet.AsEnumerable<T>().ToList();
    }

    public void Save()
    {
        context.SaveChanges();
    }
    public void Dispose()
    {
        if (context != null)
        {
            context.Dispose();
            context = null;
        }
    }
}

Now if i want to join Standard to Many-to-Many Table how may i be able to do this?

Answer 1

I took this query where I was using Entity Framework

var result = (from metattachType in _dbContext.METATTACH_TYPE
                join lineItemMetattachType in _dbContext.LINE_ITEM_METATTACH_TYPE on metattachType.ID equals lineItemMetattachType.METATTACH_TYPE_ID
                where (lineItemMetattachType.LINE_ITEM_ID == lineItemId && lineItemMetattachType.IS_DELETED == false
                && metattachType.IS_DELETED == false)
                select new MetattachTypeDto()
                {
                    Id = metattachType.ID,
                    Name = metattachType.NAME
                }).ToList();

and changed it into this where I'm using the repository pattern Linq

            return await _attachmentTypeRepository.GetAll().Where(x => !x.IsDeleted)
                .Join(_lineItemAttachmentTypeRepository.GetAll().Where(x => x.LineItemId == lineItemId && !x.IsDeleted),
                attachmentType => attachmentType.Id,
                lineItemAttachmentType => lineItemAttachmentType.MetattachTypeId,
                (attachmentType, lineItemAttachmentType) => new AttachmentTypeDto
                {
                    Id = attachmentType.Id,
                    Name = attachmentType.Name
                }).ToListAsync().ConfigureAwait(false);

Linq-to-sql

            return (from attachmentType in _attachmentTypeRepository.GetAll()
                    join lineItemAttachmentType in _lineItemAttachmentTypeRepository.GetAll() on attachmentType.Id equals lineItemAttachmentType.MetattachTypeId
                    where (lineItemAttachmentType.LineItemId == lineItemId && !lineItemAttachmentType.IsDeleted && !attachmentType.IsDeleted)
                    select new AttachmentTypeDto()
                    {
                        Id = attachmentType.Id,
                        Name = attachmentType.Name
                    }).ToList();

Also, please know that Linq-to-Sql is 14x faster than Linq...

Answer 2

So based on your edit, I'm assuming you would like to join Students and Standards.

The first thing you have to do is change the repository so that it doesn't instantiate the context. You should pass that in as a parameter and store a reference to it:

public Repository(MyDbContext myCtx)
{
    context = myCtx;
    this.dbSet = context.Set<T>();
}

The second thing you have to do is change your repository to change the GetAll() method to return IQueryable<T> instead of ICollection<T>.

Then change the implementation of GetAll():

return dbSet;

This way you only get back a query and not the evaluated list of all the entities. And then you can do the join with the GetAll() method of the repositories just like you would do it with the db sets:

using (MyDbContext ctx = new MyDbContext())
{
  var studentRep = new Repository<Student>(ctx);
  var standardRep = new Repository<Standard>(ctx);
  var studentToStandard = studentRep.GetAll().Join(standardRep.GetAll(), 
                        student => student.StandardRefId,
                        standard => standard.StandardId,
                        (stud, stand) => new { Student=stud, Standard=stand }).ToList();
}

With this you get an IQueryable<T> in studentToStandard, which will run in the database once you call ToList() on it. Note that you have to pass in the same context to both of the repositories in order for this to work.

I recommend that you check out the Unit Of Work design pattern as well. It helps a lot when dealing with multiple repositories.

https://learn.microsoft.com/en-us/aspnet/mvc/overview/older-versions/getting-started-with-ef-5-using-mvc-4/implementing-the-repository-and-unit-of-work-patterns-in-an-asp-net-mvc-application

This is a more structured and better maintainable way of handling transactions when it comes to multiple entity sets, and promotes better separation of concerns.

Hope I understood your problem correctly and this helps.