I am currently using one of the many repository patterns available online to perform CRUD operations with EF6. I am happy to use it as is but I have recently been handed a few legacy projects that have database tables with a very high number of columns. I would like a way to make my application as well as future applications smoother by devising a way to select only a subset of columns.
Current method.
public virtual TEntity Get(Expression<Func<TEntity, bool>> where,
params Expression<Func<TEntity, object>>[] navigationProperties)
{
TEntity item = null;
IQueryable<TEntity> dbQuery = this.Context.Set<TEntity>();
//Apply eager loading
foreach (Expression<Func<TEntity, object>> navigationProperty in navigationProperties)
dbQuery = dbQuery.Include<TEntity, object>(navigationProperty);
item = dbQuery
.AsNoTracking() //Don't track any changes for the selected item
.FirstOrDefault(where); //Apply where clause
return item;
}
I would like to enhance that method to retrieve only the columns I require but still return TEntity. I do know I have to inject a Select after the '.AsNoTracking()' but I am unsure as to how I could pass the properties in as I am only starting out with Expression Trees. In essence I would like to be able to do this.
public class Employee
{
public int EmployeeId { get;set; }
public string EmployeeRole { get;set; }
public string EmployeeFirstName { get;set; }
public string EmployeeLastName { get;set; }
public string DOB { get;set; }
...
}
Employee employee = EmployeeRepository.Get(where: e => e.EmployeeRole == "Developer",
columns: x => x.EmployeeFirstName, x => x.EmployeeLastName,
navigationProperties: null);
Where columns is a list of expressions specifying the columns to be added to the Select clause. Any help would be appreciated. Thanks in advance...
Update.
I ended up with using a DTO to do the necessary querying and extraction as I couldn't find an elegant way to perform it generically. There was a solution developed by a colleague of mine but it made the repository far too complex and would have been hard to manage in the future. So I create a StaffBasicInformation class to hold the subset of columns I use regularly. I also created an interface for it if I needed in the future. The below code sample shows the final implementation of retrieving data for the DTO.
public virtual IStaffBasicInformation GetStaffBasicInformation<TEntity2>(Expression<Func<TEntity2, bool>> where)
where TEntity2 : ActiveStaffMember
{
TEntity2 item = null;
StaffBasicInformation resultItem = null;
IQueryable<TEntity2> dbQuery = this.Context.Set<TEntity2>();
resultItem =
dbQuery.Where(where)
.Select(x => new StaffBasicInformation
{
GivenName = x.GivenName,
Department = x.Department,
Description = x.Description,
DisplayName = x.DisplayName,
Gender = x.Gender,
IID = x.IID,
Mail = x.Mail,
Title = x.Title,
ID = x.Id
})
.FirstOrDefault();
return resultItem;
}
