Multiple queries using same datacontext throws SqlException
- by Raj
I've search control with which I'm trying to implement search as user types something. I'm using Linq to SQL to fire queries against the database. Though it works fine usually, when user types the queries really fast some random SqlException is thrown. These are the two different error message I stumbled across recently:
A severe error occurred on the current command. The results, if any, should be discarded.
Invalid attempt to call Read when reader is closed.
Edit: Included code
DataContextFactory class:
public DataContextFactory(IConnectionStringFactory connectionStringFactory)
{
this.dataContext = new RegionDataContext(connectionStringFactory.ConnectionString);
}
public DataContext Context
{
get { return this.dataContext; }
}
public void SaveAll()
{
this.dataContext.SubmitChanges();
}
Registering IDataContextFactory with Unity
// Get connection string from Application.Current settings
ConnectionInfo connectionInfo = Application.Current.Properties["ConnectionInfo"] as ConnectionInfo;
// Register ConnectionStringFactory with Unity container as a Singleton
this.container.RegisterType<IConnectionStringFactory, ConnectionStringFactory>(new ContainerControlledLifetimeManager(),
new InjectionConstructor(connectionInfo.ConnectionString));
// Register DataContextFactory with Unity container
this.container.RegisterType<IDataContextFactory, DataContextFactory>();
Connection string:
Data Source=.\SQLEXPRESS2008;User Instance=true;Integrated Security=true;AttachDbFilename=C:\client.mdf;MultipleActiveResultSets=true;
Using datacontext from a repository class:
// IDataContextFactory dependency is injected by Unity
public CompanyRepository(IDataContextFactory dataContextFactory)
{
this.dataContextFactory = dataContextFactory;
}
// return List<T> of companies
var results = this.dataContextFactory.Context.GetTable<CompanyEntity>()
.Join(this.dataContextFactory.Context.GetTable<RegionEntity>(),
c => c.regioncode,
r => r.regioncode,
(c, r) => new { c = c, r = r })
.Where(t => t.c.summary_region != null)
.Select(t => new { Id = t.c.compcode, Company = t.c.compname, Region = t.r.regionname }).ToList();
What is the work around?
