Entity Framework + SQL Server Compact + WPF/WinForms = Sluggish UI?

Question

Entity Framework doesn't allow sharing the same entity between multiple database contexts. Therefore, I have to use only one database context in a GUI application (be it WPF or WinForms), because entities need to interact with each other.

SQL Server Compact doesn't allow sharing the same database connection between multiple threads. If I try creating a connection on one thread and running SQL query on another, my application is likely to crash.

Therefore, I have to create EF database context on one thread and run all queries on that thread. I've used GUI thread for that, because almost all queries are very fast. However, now I have a slow query and want to show an animated progress bar while it's being executed.

But I can't do that, because if I run a query on a different thread, my app crashes with AV. Furthermore, EF seems to complain if I run multiple queries simultaneously, even without SQL CE involved. Moving all queries to a different thread, covering all code with crazy amounts of async/await, callbacks, locks and other threading stuff sounds scary too, as I want to keep the code simple if it's possible.

Question: What is the correct way to work with EF database contexts and SQL Server Compact in a multi-threaded GUI application? Is there any way to offload individual queries to a different thread without making the whole application asynchronous, i.e. is there a simple way to do it?

Answer 1

SQL Server CE supports multithreading. But its objects like SqlCeConnection or SqlCeTransaction are not thread-safe. Each thread should use a separate connection. An Entity Framework DataContext instance is designed to last for one unit of work (bussiness transaction). The recommendation is a context per a form.

You can either redesign your application and store/transfer data using DTOs. Or you can use Attach/Detach features of Entity Framework (here or here). Or combine both.