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

Question

Entity Framework does not allow the exchange of the same object between multiple database contexts. Therefore, I should use only one database context in the GUI application (whether it be WPF or WinForms), because the entities must interact with each other.

SQL Server Compact does not allow the same database connection between multiple threads. If I try to create a connection in one thread and execute an SQL query on another, my application is likely to crash.

Therefore, I need to create an EF database context in one thread and run all the queries in this thread. I used the GUI thread for this because almost all requests are very fast. However, now I have a slow request and you want to show an animated progress bar while it is running.

But I can’t do this, because if I run the request in another thread, my application crashes with AV. Moreover, EF seems to complain if I run multiple queries at the same time, even without using SQL CE. Moving all requests to another thread, spanning all the code with a crazy amount of asynchronous / waiting, callbacks, locks and other threads, sounds awful since I want the code to be simple if possible.

Question: What is the correct way to work with EF and SQL Server Compact database contexts in a multi-threaded graphical application? Is there a way to offload individual requests into another thread without making the entire application asynchronous, i.e. Is there an easy way to do this?

Answer 1

SQL Server CE supports multithreading. But its objects, such as SqlCeConnection or SqlCeTransaction, are not thread safe. Each thread must use a separate connection. The DataContext instance for the Entity Framework is designed to perform one unit of work (business transaction). A recommendation is a context for one form.

You can either reverse engineer your application or save / transfer data using DTOs . Or you can use the Attach / Detach Entity Framework features ( here or here ). Or combine both.