Hacker News Reader

Comment by andrewl-hn | Hacker News Reader

I use CTEs, window functions, and groupings all the time when I write reporting queries. These things tend to be much more verbose in raw SQL, and ORMs / Query Builders either do not support some of these features at all or do very poor job (like, force me to put raw SQL substrings in my code), or force to write DSLs that are even more verbose than raw SQL. Look at corresponding PRQL samples, and you may see an appeal.

Also, I agree, no one should write SQL like this - screaming keywords, superficial newlines, etc. I don't think this style made sense ever, even in 1970s.

View on HN

While there's some stuff in C#/LINQ/EF that's more verbose (left joins are often a nightmare) or not-supported, I'll always say that I prefer writing queries in EF than in SQL, at least when dealing with SQL features that are supported by EF (which is a lot of them, it's a very expressive dialect).

But EF lets you start with FROM, lets you do whichever keywords you need in whichever order (instead of WHERE -> GROUP BY -> HAVING and you've got to CTE or Subquery if you want another GROUP BY). It also lets you access the members of a group because the objects are still treated as a graph instead of being pulverized into a flat table like SQL does. It also makes your FKs into first-class navigational properties of the table.

Like, if I have an addressID and I want to get its country code?

In MS SQL that's

    SELECT CountryCode
    FROM Country
    INNER JOIN ProvState ON ProvState.CountryID = Country.ID
    INNER JOIN Address ON Address.ProvStateID = ProvState.ID
    WHERE Address.ID = @AddressIDParam

In EF that's

    db.Addresses
      .Where(a => a.ID == addressIDParam)
      .Select(a => a.ProvState.Country.CountryCode)

EF has a hell of a lot of flaws, but linguistically I love it. Yes there's a lot of aliasing boilerplate in EF, but the ability to walk the foreign keys and the fact that you can put the select after the table name pays off so very well.

Also there's a dialect of LINQ that looks more like SQL but it's kind of weird and I don't love it so I prefer to use the lambda syntax above.

In that dialect, it's

    from a in db.Addresses
      where a == addressIDParam
      select a.ProvState.Country.CountryCode

which is even more terse and SQL-y although I find it a weird linguistic mash-up.

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.

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.

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!

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.

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.

I have some ideas how to implement a high performance ORM (yes that sounds strangey doesn't it?) that makes extensive use of CTEs, window functions and temporary tables. Writing reports in that ORM would be downright trivial.

From my perspective the JPA speciation is downright harmful. There can never be a good JPA implementation. I don't understand how there are no Java ORMs that actually learned anything from the mess that Hibernate etc are.

Take a look at how nasty even some of the most trivial concepts like calculated properties.are: https://blog.eyallupu.com/2009/07/hibernate-derived-properti...

In my hypothetical ORM design, calculated properties would be easy and fast by default.

Also, the CORBA style lazy loading based on proxies is ridiculous. As if people truly wanted a landmine like that to blow up on them.

At least we can all agree on leading commas though!

death before trailing commas

Well if it was after they wouldn't be trailing would they?

for the win

Reply on news.ycombinator.com