Threading Problem Calling DB Query

Kevin Marois

I'm posting this here because I believe this to be a threading issue, not a DB issue. FYI, I'm using SQLite with dotConnect and EF. I'm calling a DAL function from two different places on start of my app. Basically there are two lists both being populated from a DAL call. Both places use Background workers to make the same call:

private void loadWorker_DoWork(object sender, DoWorkEventArgs e)
{
var sensors = AppCore.AppDAL.GetSensors();
e.Result = sensors;
}

Then in the DAL I have a call to get the sensors:

public List GetSensors()
{
lock (lockObj)
{
var query = getSensorQuery();
var results = query.OrderBy(x => x.SensorName).ToList();

    // THIS CALLS GetSensorCategoryBELOW
    results.ForEach(x => x.Category = GetSensorCategory(x.SensorTypeID));

    return results;
}

}

private IQueryable getSensorQuery()
{
var results = (from s in dataContext.Sensors
select new SensorEntity
{
ID = s.ID,
SensorTypeID = s.SensorTypeID,
SensorName = s.Name,
Description = s.Description,
Address = s.SourceAddress.Value,
LastActivityDT = s.LastUpdate
});

return results;

}

Then for each sensor I get it's Category

public SensorCategoryEntity GetSensorCategory(long ID)
{
var query = getSensorCategoryQuery();
var results = query.Where(x => x.ID == ID).FirstOrDefault(); //<== FAILS HERE
return results;
}

private IQueryable getSensorCategoryQuery()
{
var query = (from sc in dataContext.SensorTypes
select new SensorCategoryEntity
{
ID = sc.ID,
Name = sc.Name
});

return query;

}

At the point indicated I get "Object cannot be cast from DBNull to other types." If I put in a Thread.Sleep, like below, then it works fine. if I block out the second call, it works fine.

private void loadWorker_DoWork(object sender, DoWorkEventArgs e)
{
Thread.Sleep(500);

var sensors = Falcon6Core.Falcon6DAL.GetSensors();
e.Result = sensors;

}

I don't really understand what's going on here. I'm not inserting data... it's just a simple query.. Why would t

Lost User

Does https://www.sqlite.org/threadsafe.html[^] help?

Kevin Marois

Not really. Right at the top is says "Serialized. In serialized mode, SQLite can be safely used by multiple threads with no restriction" and "The default mode is serialized." I haven't changed the threading mode, so it should be default.

If it's not broken, fix it until it is

Lost User

dbContext is not thread safe. Based on the few code fragments you've posted, it looks like you're sharing dbContext between your threads. Assuming your "lock" is even a solution, I think it is in the wrong place and should have been put in the top of the loadWorker_DoWork() method. (I would have given each thread their own dbContext).

Mycroft Holmes

I was under the impression you could only have 1 datareader active at any one time, I assume EF spit uses datareader as the underlying connection type. The second connection may be being reject on that basis.

Never underestimate the power of human stupidity RAH