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:
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?
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.
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"...