Not sure I have seen a type or construct that could not be translated one way or another. The mismatch is the need for translation. Table per hierarchy, table per subclass etc.. References vs foreign keys. Instance identity vs primary key.
The ability to run procedural code on the db server allow for much better performance than sending data across the network to the app server for processing. Being able to share procedural code between app server and db allows for the choice to be made easily based on the best place to run it rather than whether it might have to be rewritten to move from one to the other. I suppose a plugin could be made for .Net in PG similar to plV8, SQL server has had .Net stored procedures some time with many issue and caveats so it doesn't get much use.
The problem with Linq and what makes it so nice to use is the ability to mix procedural code in the Linq statement ex: list.Where(x=>x.SomeMethod()) Very easy to make something that can't be translated and then starts streaming the entire intermediate result to the app server for processing SomeMethod that would be much more performant in the db tier local to the data.
The ability to run procedural code on the db server allow for much better performance than sending data across the network to the app server for processing. Being able to share procedural code between app server and db allows for the choice to be made easily based on the best place to run it rather than whether it might have to be rewritten to move from one to the other. I suppose a plugin could be made for .Net in PG similar to plV8, SQL server has had .Net stored procedures some time with many issue and caveats so it doesn't get much use.
The problem with Linq and what makes it so nice to use is the ability to mix procedural code in the Linq statement ex: list.Where(x=>x.SomeMethod()) Very easy to make something that can't be translated and then starts streaming the entire intermediate result to the app server for processing SomeMethod that would be much more performant in the db tier local to the data.