Comment by aksss
4 hours ago
I'm sure you know more about this than me, but EF works fine with SQLite for a project I work on. There are limitations, but I think there's a difference between the idea of EF deliberately not implementing some features for SQLite and SQLite itself not having support for some EF features (which is the reality I'm familiar with).
SQL Server is the flagship database project on the Microsoft side and that team surely has some imperative to facilitate the EF vision. It simply must be less of a priority for the PostgreSQL and SQLite maintainers to do so. Is that really a valid dig on the .net ecosystem, though?
Incidentally, I'm seriously considering dropping EF. The ability to apply migrations is nice. I find the means of authoring them to be arcane and capricious. Not having to write every simple query is nice, but it gets really hard to do complex or high performance stuff. After a decade of using this thing, I find myself fighting the Entity tracker more and more and now I'm seriously questioning if it's really all worth it.
I use EF now - but only for the string interpolation in Database.SqlQuery and Database.ExecuteSql. We don't use POCOs but instead use XML and JSON with Linq.
It's really the navigations that make EF a problem. I've built a typed graph database on top of Sqlite that works really well specifically because it's in Sqlite, so the overhead of the 1+N query problem with recursively descending navigations isn't a big problem. But it requires lazy loading and that requires all the navigation to happen inside a single service scope and that makes sequencing some things get difficult (can't just pass the results around willy nilly).