2

I am currently rapid prototyping a WPF application following the MVVM pattern and using the Entity Framework and SQL Servre CE.

I am experimenting with the AsyncCommand by Stephen Cleary from the MSDN blog.

Everything is working well with the exception of Context.SaveChangesAsync() which is still blocking the UI.

This is the synchronous code which takes about 7 seconds for 10000 "Entities" (a very simple data type):

public int Insert( IEnumerable<Entity> entities )
{
    using( Context context = new Context( ConnectionString ) )
    {
        context.Entities.AddRange( entities );
        return context.SaveChanges();
    }
}

This is the asynchronous version which is still blocking the UI (I also tried a version without the using statement but that did not help):

public async Task<int> InsertAsync( IEnumerable<Entity> entities )
{
  using( Context context = new Context( ConnectionString ) )
  {
    context.Entities.AddRange( entities );
    return await context.SaveChangesAsync();
  }
}

The bulk of the work happens in SaveChangesAsync() and wrapping AddRange does not help

await Task.Run( () => context.Entities.AddRange(entities));

Using context.SaveChangesAsync().ConfigureAwait(false) does not help either.

However, if I simply wrap the synchronous version then it is NOT blocking:

public async Task<int> InsertAsync( IEnumerable<Entity> entities )
{
   return await Task<int>.Run( () => Insert( entities ) ); 
}

This code is called from the ViewModel using an AsyncCommand (see link above for the MSDN article):

CreateRandomEntitiesAsyncCommand = AsyncCommand.Create( 
    token => CreateRandomEntitiesAsync(token));

public IAsyncCommand CreateRandomPatientsAsyncCommand { get; private set;  }

public async Task CreateRandomAsync()
{
   int numberToGenerate = 10000;

   // Get all existing unique ids from the data layer, this does not block
   IEnumerable<string> existingUniqueIDs = await dataLayer.GetUniqueIDsNoTrackingAsync();

   // randomly create 10000 entities, ensuring they are unique, does not block either
   Entity[] entities = await Task.Run( () => 
     CreateEntities(numberToGenerate, existingUniqueIDs));

   // this does block, see above
   await dataLayer.InsertAsync( entities );

   // ...
   // Re-fetch all entities from the db and issue PropertyChanged 
   // for the Entities property to refresh the UI.
   // The code above is still blocking if this is commented out.
}

What am I making wrong?

Intermediate update

It seems this issue is related to SQL Server CE and its "underlying implementation" possibly mapping the asynchronous Entity Framework code to synchronous code so that it is executed on the UI thread and thus blocking the UI.

Also see: Blocking behaviour with Entity Framework Async methods and SQL Server Compact

I am using (via NuGet):

  • EntityFramework.SqlServerCompact v6.1.3 by Microsoft
  • Microsoft.SqlServerCompact v4.0.8876.1 [Is this the ADO.Net provider?]
Community
  • 1
  • 1
naitsirhc
  • 133
  • 2
  • 12
  • 1
    What is the type of the database used? – Ivan Stoev Jan 31 '17 at 12:57
  • Rather a guess than an answer but I assume this behavior is caused by the fact that the first `async` version only does the `Save` part async'ly while the second does everything. If the amount of inserted stuff is large then the change tracker will have a lot of work to do and the change tracker is not a blazingly fast animal. Remember, up until the `await` keyword your code runs synchronously. But by wrapping things in a `Task.Run()` you are essentially asynchronizing the part where the change tracker is at work as well. – Balázs Jan 31 '17 at 12:57
  • How is `CreateRandomAsync()` being called ? – Matias Cicero Jan 31 '17 at 13:10
  • @IvanStoev : We are using SQLCE (updated the question). – naitsirhc Jan 31 '17 at 13:22
  • @Balázs: Good point. But e.g. wrapping add range too does not help and also, according to VS, almost all the time is spent in SaveChanges (I updated the question). – naitsirhc Jan 31 '17 at 13:24
  • @MatiasCicero: I updated the question. The code is called from an AsyncCommand the View binds to. – naitsirhc Jan 31 '17 at 13:31
  • If `await` seems to block it's because the thread it's trying to return to (the UI thread in this case?) is doing something else. What happens when you debug the application? Try running until the application appears to block then pause. Which methods do you see in "Debug > Windows > Parallel Stacks "? – Panagiotis Kanavos Jan 31 '17 at 13:46
  • 1
    You might need to check your db provider because by default all `Async` methods of `DbConnection` and `DbCommand` (which EF relies on) are **synchronous** (!?). SqlServer provider for sure overrides them, but not sure for `Ce` version. – Ivan Stoev Jan 31 '17 at 13:51
  • @PanagiotisKanavos,@Ivan: Your comments appear to be related. If I look at the parallel stacks as advised then I see a single thread with the method in question at the top. Does this indicate the SaveChangesAsync method is running on the UI thread and thus blocking it? – naitsirhc Jan 31 '17 at 14:08
  • And in fact: If I am using the version which runs the synchronous code on a task then I am seeing two threads in the parallel stacks window, one for the UI thread with the Display method on the top and the other one with the Insert method on the top. – naitsirhc Jan 31 '17 at 14:24
  • @c__k which provider did you use for CE? Which package and version? The source is probably online, so we/you could check if it implements asynchronous operations. There may be a note in the release notes or documentation. – Panagiotis Kanavos Jan 31 '17 at 15:17
  • @PanagiotisKanavos: I must admit, I do not know which provider I am using. I just got the libraries from Microsoft via Nuget (see above, I updated the question with an "intermediate update" at the end). I am assuming the Microsoft.SqlServerCompact library is the provider. Are there other? I was trying to locate the sources but could not find any so far. – naitsirhc Jan 31 '17 at 17:18
  • Possible duplicate of [Blocking behaviour with Entity Framework Async methods and SQL Server Compact](http://stackoverflow.com/questions/28675265/blocking-behaviour-with-entity-framework-async-methods-and-sql-server-compact) – ErikEJ Jan 31 '17 at 17:31
  • @ErikEJ: I know, that is why linked to it. I also changed the tags in order to draw your attention to this one. In particular I would like to know how to handle this issue. In your comments you were suggesting switching to SQL Express but that takes away a lot of the advantages of CE. Would it be okay to wrap the calls in Task.Run() or does that have a design smell? Would I shoot myself in the foot with that? Also other commenters were asking which provider I was using, are there other providers for CE available? Should I open another question for this? – naitsirhc Jan 31 '17 at 17:55
  • There is only one ADO.NET provider for SQL Server Compact – ErikEJ Feb 01 '17 at 07:21
  • @c__k I'm struggling with the same problem. How did you solve finally? Would be recommended wrapping the sync version in a Task and await for it? – jacktric Jul 10 '18 at 10:46
  • @jacktric As mentioned by ErikEJ SaveChangesAsync is not supported by CE. I believe I initially worked around that by wrapping the call in Task.Run(). However I had to abbandon CE later on because it does not support multiple schemas. – naitsirhc Jul 12 '18 at 07:16

0 Answers0