Sunday, July 5, 2015

Include dependent entity in Repository Generic Repository Pattern.

Sometime you may come across with the error that “Data reader associated with open connection which must be close first”

This error is because of lazy loading in Entity Framework. Sometime we use navigation property to read column from child table in time of parent table fetch. Just have a look on below query.

var result = (from m in UnitOfWork.GetRepository<TABLE_A>().Where(f => f.MasterCollegeId == Id)

                          select new SOME_DTO
                          {
                              CompanyId = m.CompanyId,
                              CompanyLogo = m.CompanyMaster.CompanyLogo,
                              CompanyName = m.CompanyMaster.CompanyName,
                             
                          }).ToList();

Here TABLE_A is parent table and “CompanyMaster” is child table(which we are not seeing in Include) and in time of select query generation we are taking “CompanyLogo” and “CompanyName” column which belongs to “CompanyMaster” table.

Now, If we try to serialize result in another layer, It may throw “Connection associated with data reader is open which need to close first” this exception.

This is Just because we did not include the dependent entity in Query. Here is solution with respect of Repository/Generic Repository pattern.

Step 1 ) Just Get DbSet object from Repository.

public virtual DbSet<T> GetQueryable()
        {
            return _dbset as DbSet<T>;
        }

In case of Generic Repository, we have to return DbSet<T>.

Step 2) Add Include entity using Include() function. Here is modified query.

var result = (from m in UnitOfWork.GetRepository<TABLE_A>().GetQueryable().Include("CompanyMaster").Where(f => f.MasterCollegeId == Id)

                          select new SOME_DTO
                          {
                              CompanyId = m.CompanyId,
                              CompanyLogo = m.CompanyMaster.CompanyLogo,
                              CompanyName = m.CompanyMaster.CompanyName,
                             
                          }).ToList();



And your problem has solved. J

No comments:

Post a Comment