4 ms·

The downside though is you have to grab the sql it’s generating somehow to try to optimize it, figure out what crazy cross apply madness it’s going or to figure

The downside though is you have to grab the sql it’s generating somehow to try to optimize it, figure out what crazy cross apply madness it’s going or to figure out why it’s blowing sql servers recursion limit. I prefer to avoid linq syntax now. It’s a false economy.

Pxtl 3y ago
Oh I agree the actual mapping framework where it converts the EF/LINQ is deeply flawed. But that's the underlying implementation. I just mean on a linguistic level, I tremendously prefer EF/LINQ/C# to SQL.
Turskarama 3y ago
In my experience, it quite rarely uses cross apply, and typically only for functions where you're doing complicated join filtering and trying to extract full EF entities. If you don't use linq you can't get EF entities anyway, and if you don't need EF entities you can can still use linq to get a projection and avoid the cross apply! The worst case scenario often ends up being writing a linq query to get your primary keys, then loading entities just using the list of keys. I've used linq for years and it makes the basic and intermediate stuff way simpler, and the complex stuff no more complex. In hundreds of thousands of lines of code, we have exactly TWO queries where it ended up simpler to hand write SQL, and EF did not prevent us from doing that!
aayjaychan 3y ago
I drew a different conclusion from similar experience. I avoid navigation properties and other advanced mapping features, so an entity maps flatly to one table. The LINQ queries will be more verbose as you'll need to write the join and group clauses explicitly, but I find it much easier to predict the performance of queries since the generated SQL will look almost exactly the same as the LINQ syntax. It's also less likely to accidentally pull in half the database with `.Include()` this way.
WorldMaker 3y ago
EF for some time (since the "Core" reboot) has made things like "crazy cross apply" "opt-in only" and instead you get runtime exceptions for LINQ query shapes it thinks can only be done that way. Some of those crazy things early days EF silently supported still aren't even possible in today's EF, for mostly better (but sometimes rarely worse), let alone opt-in behaviors. I think more than ever current EF generates SQL that straightforward looks like you would expect given the LINQ you've written, with only surprises being runtime exceptions before it ever tries to confuse an SQL database with a horribly deoptimized query. Today's EF also has more tools than ever to optimize its queries. It logs query text by default in some debug contexts to visible debug consoles, and to certain telemetry providers when errors occur in certain release/Production contexts. It's easy to opt-in to such logging in even more contexts should you desire that. (Thanks to modern .NET's ubiquitous System-owned ILogger infrastructure and the modern "Generic Hosts" approach.) For advanced scenarios, intercepting specific LINQ queries and enhancing/optimizing their output based on domain knowledge of the queries or the specific databases is easier than ever with simple dependency injection.

Pxtl 3y ago

Oh I agree the actual mapping framework where it converts the EF/LINQ is deeply flawed. But that's the underlying implementation. I just mean on a linguistic level, I tremendously prefer EF/LINQ/C# to SQL.

Turskarama 3y ago

In my experience, it quite rarely uses cross apply, and typically only for functions where you're doing complicated join filtering and trying to extract full EF entities. If you don't use linq you can't get EF entities anyway, and if you don't need EF entities you can can still use linq to get a projection and avoid the cross apply! The worst case scenario often ends up being writing a linq query to get your primary keys, then loading entities just using the list of keys. I've used linq for years and it makes the basic and intermediate stuff way simpler, and the complex stuff no more complex. In hundreds of thousands of lines of code, we have exactly TWO queries where it ended up simpler to hand write SQL, and EF did not prevent us from doing that!

aayjaychan 3y ago

I drew a different conclusion from similar experience. I avoid navigation properties and other advanced mapping features, so an entity maps flatly to one table. The LINQ queries will be more verbose as you'll need to write the join and group clauses explicitly, but I find it much easier to predict the performance of queries since the generated SQL will look almost exactly the same as the LINQ syntax. It's also less likely to accidentally pull in half the database with `.Include()` this way.

WorldMaker 3y ago

EF for some time (since the "Core" reboot) has made things like "crazy cross apply" "opt-in only" and instead you get runtime exceptions for LINQ query shapes it thinks can only be done that way. Some of those crazy things early days EF silently supported still aren't even possible in today's EF, for mostly better (but sometimes rarely worse), let alone opt-in behaviors. I think more than ever current EF generates SQL that straightforward looks like you would expect given the LINQ you've written, with only surprises being runtime exceptions before it ever tries to confuse an SQL database with a horribly deoptimized query. Today's EF also has more tools than ever to optimize its queries. It logs query text by default in some debug contexts to visible debug consoles, and to certain telemetry providers when errors occur in certain release/Production contexts. It's easy to opt-in to such logging in even more contexts should you desire that. (Thanks to modern .NET's ubiquitous System-owned ILogger infrastructure and the modern "Generic Hosts" approach.) For advanced scenarios, intercepting specific LINQ queries and enhancing/optimizing their output based on domain knowledge of the queries or the specific databases is easier than ever with simple dependency injection.