I'm developing a web application using ASP.NET MVC and EF6 to access the database.
One of the features of my web application allow the user to download a Excel file. The query to get the information from the database takes like 5 seconds and I notice that until the query it's done we can't do anything on the rest of the web application.
Is this the normal behaviour of EF, lock the database even with AsNoTracking on the query?
If I'm not doing anything wrong and this is the default behaviour of EF how should I resolve this locking problem?
(Update)
I'm using a SQL Server database and the "lock" happens when for exemple I export the excel file and at the same time do a search that uses the same table.
To organize my code i'm using Repository and UnitOfWork pattern and to create the instances i'm using DI Unity.
The UnitOfWork implementation:
public class UnitOfWork : IUnitOfWork
{
private bool _disposed;
private DbContext _dbContext;
private Dictionary<string, dynamic> _repositories;
private DbContextTransaction _transaction;
public DbContext DbContext
{
get { return _dbContext; }
}
public UnitOfWork(DbContext dbContext)
{
_dbContext = dbContext;
}
public int SaveChanges()
{
return _dbContext.SaveChanges();
}
public IRepository<TEntity> Repository<TEntity>()
{
try
{
if (ServiceLocator.IsLocationProviderSet)
return ServiceLocator.Current.GetInstance<IRepository<TEntity>>();
if (_repositories == null)
_repositories = new Dictionary<string, dynamic>();
var type = typeof(TEntity).Name;
if (_repositories.ContainsKey(type))
return (IRepositoryAsync<TEntity>)_repositories[type];
var repositoryType = typeof(Repository<>);
_repositories.Add(type, Activator.CreateInstance(repositoryType.MakeGenericType(typeof(TEntity)), this));
return _repositories[type];
}
catch(ActivationException ex)
{
throw new ActivationException(string.Format("You need to configure the implementation of the IRepository<{0}> interface.", typeof(TEntity)), ex);
}
}
public void Dispose()
{
Dispose(true);
GC.SuppressFinalize(this);
}
~UnitOfWork()
{
Dispose(false);
}
public void Dispose(bool disposing)
{
if(!_disposed)
{
if(disposing)
{
try
{
_dbContext.Dispose();
_dbContext = null;
}
catch(ObjectDisposedException)
{
//the object has already be disposed
}
_disposed = true;
}
}
}
}
The Repository implementation:
public class Repository<TEntity> : IRepository<TEntity>
where TEntity : class
{
private readonly IUnitOfWork _unitOfWork;
private readonly DbContext _dbContext;
private readonly DbSet<TEntity> _dbSet;
public Repository(IUnitOfWork unitOfWork)
{
_unitOfWork = unitOfWork;
_dbContext = unitOfWork.DbContext;
_dbSet = _dbContext.Set<TEntity>();
}
#region IRepository<TEntity> implementation
public void Insert(TEntity entity)
{
_dbSet.Add(entity);
}
public void Update(TEntity entity)
{
_dbContext.Entry(entity).State = EntityState.Modified;
}
public void Delete(TEntity entity)
{
_dbSet.Remove(entity);
}
public IQueryable<TEntity> Queryable()
{
return _dbSet;
}
public IRepository<TEntity> GetRepository<TEntity>()
{
return _unitOfWork.Repository<TEntity>();
}
#endregion
}
The Unity configuration:
container.RegisterType<DbContext, DbSittiusContext>(new PerRequestLifetimeManager());
container.RegisterType<IUnitOfWork, UnitOfWork>(new PerRequestLifetimeManager());
//Catalog respository register types
container.RegisterType<IRepository<Product>, Repository<Product>>();
UnityServiceLocator locator = new UnityServiceLocator(container);
ServiceLocator.SetLocatorProvider(() => locator);
To create my query have to create a extension method like this:
public static Product FindPublishedAtDateById(this IRepository<Product> repository, int id, DateTime date)
{
return repository.
Queryable().
Where(p => p.Id == id).
Where(p => p.PublishedFrom <= date && (p.PublishedTo == null || p.PublishedTo >= date)).
SingleOrDefault();
}
