Most ORMs will download all the data and evaluate them on the client side, with code that is even more convoluted that the SQL one and thus with less performance.
Entity Framework and any other Linq to data provider translates the Linq expression to the native language of the source data and does it server side.
LINQ only allows for a fraction of what is possible with SQL, and good luck having the best queries generated out of it, if the RDMS doesn't happen to be SQL Server.
It all depends on the talent of the authors of the drivers.
I've seen that pattern in lots of homegrown applications, but I've never seen such a thing in a mainstream ORM. Care to provide examples ?
Nowadays I only bother with Dapper, MyBatis, using a mix of SQL and stored procedures.
EF only if the RDMS happens to be SQL Server.
That is only true in a one-to-many entity relationship (and even so, it is debatable). A one-to-one relationship can be modeled in the two objects, in one of them, or delegated to a third entity. A many-to-many entity relationship can also be handled, in OO, in various different ways. Idem for a ternary relationship or, basically any higher order relation between objects.
This is known, borrowing a term from electrical engineering, as an impedance mismatch between the two models, and it's not an easy problem by any measure.
What AI do you need? You map your tables to objects and relationships between objects via FK relationships.