EF is an ORM tool for Microsoft. It helps developer in interacting with the database (CRUD) and running a custom stored procedure. I’ve been using it and it’s all running perfectly fine. But I have a problem with querying an entity, when I try to query an entity by default it will run a SELECT * or selecting all columns, but for some data intensive application I might just require 2 columns instead of all columns. I’m not too concerned with a simple table with a few columns but how about when you have a or more than one BLOB/Varbinary/NVARCHAR(MAX) and you need 100 or 1000 rows records with a 2 single columns or even just an ID column. By default it will return all the columns but then you can use PROJECTION
[code language=”csharp”]
var q = from r in Context.TableName
select new
{
Id = r.Id,
Status = r.Status
}
[/code]
Let’s take it to another level, I’m building a common library for all entities and can be used dynamically by passing the column to the function
[code language=”csharp”]
/// <summary>
/// Dynamic Select query that loads only specific column passed in parameter and also based on the where clause that passed as a parameter
/// </summary>
/// <typeparam name="U"></typeparam>
/// <param name="columns"></param>
/// <param name="where"></param>
/// <returns></returns>
public IEnumerable<U> GetBy<U>(Expression<Func<T, U>> columns, Expression<Func<T, bool>> where)
{
return dbset.Where(where).Select<T,U>(columns);
}
[/code]
And it can be called as below and it will return me an IEnumerable of Anonymous object which then I need to process it manually to map with the actual entity
[code language=”csharp”]
_userRepository.GetBy(u => new { u.UserName, u.FirstName }, f => f.UserName == UserName)
[/code]
So the question is now how do I modify this function to return me the actual entity or else make a single function that just work across all entities without requiring manual mapping.
We can use Automapper – (you can get this from NuGet) to map your Anonymous Object with your base Entity function, so this combines the Generic <T> of entity with the projection as the parameter to select
a few specific columns) and it will return back the collection of the entity that you requested
[code language=”csharp”]
/// <summary>
/// Dynamic Select query that loads only specific column passed in parameter and also based on the where clause that passed as a parameter
/// </summary>
/// <typeparam name="U"></typeparam>
/// <param name="columns"></param>
/// <param name="where"></param>
/// <returns></returns>
public IEnumerable<T> GetBy<U>(Expression<Func<T, U>> columns, Expression<Func<T, bool>> where)
{
//Create an initial mapping between anonymous object and returned entity type
Mapper.CreateMap<U, T>();
var anonymousList = dbset.Where(where).Select<T, U>(columns);
return Mapper.Map<IEnumerable<U>, IEnumerable<T>>(anonymousList);
}
[/code]