Using keywords async/await in database queries (Windows Phone 8)

I have a local database in Windows Phone 8 app. The app includes a lot of queries to the database and I don't want bad effect on the responsiveness of UI.

For example I have a table of users and method to get a user from database by id.

Current variant

public class CacheDataContext : DataContext
{
    public static string DBConnectionString = "Data Source=isostore:/Cache.sdf";

    public CacheDataContext(string connectionString)
            : base(connectionString) { }

    public static AutoResetEvent OperationOnDatabaseUser = new AutoResetEvent(true);

    public Table UserItems;
}

public class CacheDataContextUser : CacheDataContext
{
    public CacheDataContextUser(string connectionString)
        : base(connectionString) { }

    public User GetUser(string id)
    {
        try
        {
            OperationOnDatabaseUser.WaitOne();
            using (CacheDataContext context = new CacheDataContext(DBConnectionString))
            {
                //find user in the data base and return 
            }
        }
        finally
        {
            OperationOnDatabaseUser.Set();
        }
    }
}

I need ensure safety of the data if at the same time on database wallow different requests to add, modify, delete data. For this I use AutoResetEvent. Not sure what I'm doing it right, but so far no problems.

I can get user from the database:

using (DataBaseUser = new CacheDataContextFriends(ConnectionString))
{
   var user = DataBaseUser.GetUser(id);
}

Async/await

But I want work with the database using keywords async/await.

```
public class CacheDataContextUser : CacheDataContext
{
public CacheDataContextUser(string connectionString)
: base(connectionString) { }

private object threadLock = new object();

public Task GetUser(string id)
{
using (CacheDataContext context = new CacheDataContext(DBConnectionString))
{
var result = await Task.Factory.StartNew(() =>

Before even addressing the actual question, have you actually proven that database queries are harming your responsiveness? Since the queries are to local storage it's possible that they are fast enough to not require using asynchrony, particularly if they are simple queries or the database is small.

Next, I notice you've assumed that the database itself is not threadsafe, i.e. that you must only allow access from one thread at a time. Are you sure that's actually true? Many (most?) database handle concurrency themselves, so you may be adding an unnecessary layer of synchronization. I looked around a bit, but could not find anything specifically documenting concurrent access to isolated storage databases. I would start by researching that, or possibly asking a question on StackOverflow. If the database does allow concurrent access then you just need to worry about update conflicts, which you could hopefully avoid in a single-user phone application.

What I'm getting at here is that multi-threading and locking is hard. Don't do it unless you're sure you have a good reason to do it.

If you really must to multi-threading, then the C# lock keyword is a good place to start. Unfortunately, your example probably will not work properly because each CacheDataUserContext instance will have it's own lock object - so if you create more than one instance they could conflict with each other.

You "Current Variant" actually gets this more right, because your AutoResetEvent is a static variable, so there is only a single instance of it across the system. However, as I understand DataContext it lets you use Linq statements against the database, which will not know anything about your lock and hence will not be synchronized.

I think you'd have to create a separate application layer to wrap the DataContext and expose just the certain operations that your application needs. This is generally called the "Repository Pattern". Inside the repository you could create a single lock object, wrap a lock around all accesses to a DataContext, and use Task.Factory.StartNew inside each of the repository methods to make them asynchronous.

StackExchange Code Review Q#40068, answer score: 3