linq to sql in multithreading by wrapping the linq query in Task.Run()

Question

Is it good to wrap the linq 2 sql query into the Task.Run method as shown below

var keywordlistquery = await Task.Run(() =>
        {
            using (DataContext context = new DataContext(connection))
            {
                context.ObjectTrackingEnabled = false;
                return from keyword in context.GetTable<KeywordsList>()
                       select new
                       {
                           keyword.search_text,
                           keyword.search_keyword
                       };
            }
        });

Is the above code thread safe, will it have any issues while working on production.Is there any alternate better way of writing the above code.

Answer 1

A good answer here depends a lot on what the intent of the code is.

In general though, keep in mind that Linq to SQL technologies were built and then discontinued before native async and await patterns were implemented in .Net.

So, unless you are very comfortable with maintaining async tasks manually, it might be a good idea not to try use async with Linq to SQL at all. Odds are, you will not get much of a performance boost unless the server is expected to handle very high levels of request concurrency, but manually mucking around with async tasks is a fantastic way to introduce really hard to detect bugs that end up accidentally blocking request threads.

If you do need to handle async in code like this, there are a couple of solutions.

First understand that the code above creates a query, but doesn't execute it. What it returns is an IQuerable... basically, think of it as a SQL statement that hasn't been run. Linq to SQL will not run the query until a method like ToArray or ToList is called, or until it is used in a foreach loop or similar.

Also, it becomes difficult to work with anonymous types like this when you are using return statements. You will likely need to create DTO classes and use select projections to instantiate them

Second, you are wrapping the context in a using block (which is a good practice), but if you return the query before it is actually executed then the context gets disposed. The caller will get an IQueryable, but when it tries to use it you'll end up with an exception because the context has been disposed.

So.... there are two options here depending on if this code is intended to return actual data, or return just a query that the caller can then further modify.

Case 1) return data:

public async Task<object> DoThings(CancellationToken token)
{
    var keywordlistquery = await Task.Run(() =>
    {
        using (var context = new DataClasses1DataContext())
        {
            context.ObjectTrackingEnabled = false;
            return from keyword in context.GetTable<KeywordsList>()
                    select new
                    {
                        keyword.search_text,
                        keyword.search_keyword
                    };
        }
    }, token);
    return keywordlistquery;
}

Note here that the method itself should be async, and you should always try to use a cancellation token when possible. This calls ToArray to force the query to execute now and return the data. Keep in mind though that this will return the WHOLE table. If the caller wants to supply a where clause or whatever, the code will still load all the data.

Case 2: return IQuerable

In case 2, you want your method to return just the query. This way, the caller can modify the query before it gets executed. This allows the caller to add statements to include a where clause or order the results or whatever; and have those statements included in the TSQL that gets generated.

In this case, the trick is that the caller must be in control of the lifespan of the data context, and since the method isn't actually executing results, it doesn't need to be async.

public async Task CallingMethod()
{
    using (var context = new DataClasses1DataContext())
    {
        var token = new CancellationToken();
        context.ObjectTrackingEnabled = false;
        var query = DoThings(context);
        var result = await Task.Run(() => query.ToArray(),  token);
    }
}

public IQueryable<object> DoThings(DataContext context)
{
    var keywordlistquery = from keyword in context.GetTable<KeywordsList>()
        select new
        {
            keyword.search_text,
            keyword.search_keyword
        };
    return keywordlistquery;
}

As I mentioned before though, select new anonynous doesn't work that well in cases like this. It would be better to create a DTO class and select a new one of those, or return the whole table.