How to use Entity Framework for applications with many clients

Question

I have created an application that manages Hotels, Rooms, Reservations and customers. In the first version I used pure MySQL commands to communicate with the database and worked perfectly but it was quite hard to implement, mostly when I needed to change the name of a property or the order in the database and relative commanding in complex entities, and also was slow on taking the result. So i tried to implement it using Entity Framework. Since then i faced too many problems that I am trying to go around. First of all, my app is used by two clients and I need the data to be synced. EF is caching data for faster results even if they change at the DB. As far as I know EF doesn't check if the data is current and the solutions I have found for this were:

1. to reload or detach every single entity - something bad while having like 300 rooms or 2000 customers
2. to dispose and recreate the context - this is what I tried to do but it seems hard to implement because my app can have 3 or 4 windows in the same time and then I need to reload the ItemSource in every Combobox and list otherwise when I use it again exceptions are thrown like "context is disposed" or "entity changes are tracked by many trackers" or "foreign key is not exposed" etc. Also this way you lose the change tracker.

An other big problem is that when i don't use my app for a while and then access a property of an entity that tries to get by lazy loading it might crash if the connection or command timeout is reached. Maybe it can be solved by exception handling but I thought EF could manage those kind of things.

The thing is that I need the change tracker to update the entities, I also use MVVMlight and a generic repository I found in the internet with UnitOfWork. https://cpratt.co/truly-generic-repository/

My app is WPF 4.7.2

Is there any suggestion? Should I use some other framework for communicating with the DB? Should I use other kind of DB? Other programming language?

Another

Answer 1

I know you're not going to want to hear this but there is no "one size fits all" answer to this question. People dedicate their entire careers trying to solve problems like this.

I work in the airline industry where we have literally thousands of kiosks, terminals, mobile phone apps etc across the world all accessing the same database(s) simultaneously, with many of them needing real-time communication despite sitting behind some the tightest firewalls...and flakiest airport networks...you'll ever see. As a very rough guideline I tend to expose the database via a web server and have everything connect through that. For real-time stuff I use WebSockets (which can be simulated with long-polling when the network doesn't support it) but non-critical stuff can usually be handled with basic REST calls; both of these technologies have ample JavaScript support, which is important for when (not if) you eventually develop any kind of webapp components. Generally speaking I steer clear of SOAP; if you're already controlling both sides of a communication channel then there's no point adding the extra complexity unless you have reason to.

The advantage to doing things like this is you wind up with a single project containing all your models which all your projects share, this in turn results in excellent type-safety and code re-use. Serialization between your components is generally handled for you automatically by .NET, and it's very easy to subject your database layer to stress testing in a real-world environment. You also have a centralized point for logging and report generation, which your clients will want at some point (analytics is a whole field unto itself). On the downside the addition of a dedicated server adds extra complexity and potential points of failure, and if you have to host it locally then it will increase the complexity of your installation procedures and probably also your clients network config.

That's one answer, there are many more. And once you start getting into issues like server loading and redundancy it all starts getting very "interesting"...