Arif YILMAZ Arif YILMAZ - 11 months ago 46
C# Question

GenericDataRepository with Compiled Queries

I have a MVC project with many layers(View,Model,DataAccess,Business) and I am using GenericDataRepository in my DataAccessLayer. I would like to enhance the GenericDataRepository's performance with CompiledQueries.

here is my GenericDataRepository with only one function to keep it simple...

public class GenericDataRepository<T> : IGenericDataRepository<T> where T : class
public List<T> Get(Expression<Func<T, bool>> filter = null, Func<IQueryable<T>, IOrderedQueryable<T>> orderBy = null, params Expression<Func<T, object>>[] includes)
IQueryable<T> query = null;
using (var context = new ZenHaberDBEntities())
query = context.Set<T>();

foreach (Expression<Func<T, object>> include in includes)
query = query.Include(include);

if (filter != null)
query = query.Where(filter);

if (orderBy != null)
query = orderBy(query);

return query.ToList();

and this is the code in BusinessLayer that uses GenericDataRepository to pull data from DB.

public IList<Article> GetAllArticles()
return _articleRepository.Get();

Can I add GetAllArticles() function to CompiledQueries? Does anyone have any experience on this?

here is my context

public partial class ZenHaberDBEntities : DbContext
public ZenHaberDBEntities()
: base("name=ZenHaberDBEntities")
this.Configuration.LazyLoadingEnabled = false;

protected override void OnModelCreating(DbModelBuilder modelBuilder)
throw new UnintentionalCodeFirstException();

public virtual DbSet<Article> Article { get; set; }
public virtual DbSet<ArticleMedia> ArticleMedia { get; set; }
public virtual DbSet<ArticleTagRelation> ArticleTagRelation { get; set; }
public virtual DbSet<Category> Category { get; set; }
public virtual DbSet<Currency> Currency { get; set; }
public virtual DbSet<Tag> Tag { get; set; }
public virtual DbSet<Weather> Weather { get; set; }

public static class DbContextExtensions
public static ObjectContext ToObjectContext(this DbContext dbContext)
return (dbContext as IObjectContextAdapter).ObjectContext;

Answer Source

CompiledQueries are only compatible with ObjectContext-derived models, and it is not compatible with DbContext-derived models.Hence you have derived it using DbContext where you cannot use CompiledQueries.

But you have so many other options to improve the performance of your Linq quires.Below article shows lot of details about it.

Performance Considerations for EF

Note : If you need to see about the CompiledQuery section, please see it under this title on the above link : 3.3 Using CompiledQuery to improve performance with LINQ queries