When I started playing with my Garden App back in 2018, I figured out how to get SQLite to work with Entity Framework. It was slightly annoying but I got it to work. After the whole Xamarin fail, the project got put to the side for most of a year. Then I ran into a problem that made me realize that I should go back into the future with .NET and go ADO rather than Entity Framework for SQLite. That’s ADO.NET for the kids who did not catch the reference.
The Problem
When I got back, I ran into issues that the code could not cast with the Entity Framework objects. Apparently the versions of SQLite and the Entity Framework where out of sync. As a result of this version mismatch was two things named the same thing. Because these two things had the same name, it could not figure out how to cast them. So it blew up.
This was quite a surprise for me as I salvaged my useful bits from the Xamarin code base for use in my Garden App API. In trying to maintain it, I ran into a massive issue of annoyance. Because of the size of this issue and the fact it does not seem to be going away, I have no choice but to switch my database connection methodologies.
The Solution to Entity Frameworks meanness when dealing with SQLite, ADO.NET
While this really feels janky, after all Microsoft is encouraging everyone to Entity Framework, we really need to take a step back and do it the old fashion way and go home to ADO.NET. Until Entity Framework makes its SQLite instance constantly stable version to version, I just don’t think we can rely on Entity Framework.
The moment that they do then I am back on board the Entity Framework train.
As a result of my SQLite maintenance programming shock, I feel like the best solution to this broken Entity Framework reality is that I will need to switch to using ADO.NET.
Thankfully Microsoft has a good library for just that using SQLite. In the next several posts we will talk about working with this ADO.NET library to help speed along your journey to using it in your SQLite projects.