C#'s LINQ (query syntax, not methods) got it right

var result = from s in stringList where s.Contains("Tutorials") select s;

The work was already done for them, as LINQ is just do-notation from Haskell.

No, Linq is *not* in any way like Haskell's `do`.

Linq is a (reasonably) compromised, non-referentially-transparent, implementation of a restricted form of relational-algebra with side-effects permitted, so I'm not comfortable describing Linq as "monadic".

Whereas Haskell's `do` is strictly monadic.

or just `var result = stringList.Where( s => s.Contains("Tutorials") )`

I can't stand the non-extension-method Linq syntax: the _only_ place where it offers a readability improvement over ext-methods is using `join` - but I hardly ever do that in Linq anyway.

Also, in both my code and yours, `result` will be a lazy-evaluated `IEnumerable<String>` which may be undesirable - which means it's probably a good idea to use `.ToList()` to materialize it - which means having to use ext-methods anyway - and mixing both syntaxes in the same expression is aesthetically atrocious.

>I can't stand the non-extension-method Linq syntax: the _only_ place where it offers a readability improvement over ext-methods is using `join`

Hah! I tell the juniors this all the time :D. This seems to be basically the consensus among the C# community these days as far as I can tell as well.

I guess I'm not in the C# community. `let` is pretty annoying to reproduce with extension methods. Also, when you use multiple `from` clauses, you get access to all scopes, whereas with `SelectMany` you only get the parameters of your current lambda.

> you get access to all scopes

Read: "you create new heap-allocated closures which wreck your Linq expression's runtime performance"

Or:

"you create Linq queries that cannot be translated into SQL"

It was all in memory. To the extent that it's a performance tradeoff, it might be one that's worth making depending on the use.

Aye - but the annoying thing is that it was never necessary: with a few subtle changes to Linq it’s possible to have allocation-free closures by passing state via hidden parameters on the stack - but just like every language out there we’re now hobbled by decisions made 15 years in the past.

——

On a related note, it’s interesting just how unpopular so many new C# language features are (just by looking at the numbers of Thumbs-down reactions on the GitHub Issues/PRs - stuff like top-level Main. It feels like C#’s LDT wants to be like Swift, but without Swift’s willingness to ditch ill-conceived features after a few years… but I think C# would be well-served by taking an axe to some language-features by now - like keyword-Linq and CLS-compliance (honestly, do any ISAs today still lack hardware support for unsigned ints?)

Interesting.

I think there are some otherwise-seldom-used linq overloads for doing just that. Maybe on Join() or GroupJoin() or something like that. Query expressions use them for compilation to keep closure use down, but I don't know too much about it.

Anyway, if top-level statements are wrong, I don't ever want to be right. I had no idea there was any controversy on that. It's trivial to add the boiler-plate back in if you want it.

The main thing that bugs me is that Expression<> is stuck with language features that existed in C#4, even when there are trivial lowerings. e.g. `is not null` could be `!= null`. (This example ignores operator implementations, but most IQueryables ignore more than that already) Even better, add AST node types for the new language features.

I'm not sure the Github issues are the best reflection of the popularity of those features.

Top level statements and the new HostBuilders are fantastic, they're much cleaner and easier to follow than the previous mess. Add Minimal APIs to the mix and C# is finally a viable choice for spinning up something quickly.

I completely agree with you, in the context of C# code. But also take into account not everybody in HN is a C# developer, and OP syntax works better as an example of his point. All non C# devs will understand his "sql like" example easier.

> the _only_ place where it offers a readability improvement over ext-methods is using `join`

No love for let?

    from item in items 
    let frob = Expenseive(item.P1) 
    where frob > 3 
    selec new { frob, item }

That can be done with ValueTuples instead of Anonymous Types: `items.Select( i => ( i, frob: Expensive( i.P1 ) ) ).Where( t => t.frob > 3 );`

...and ValueTuples are superior to Anonymous Types because you can actually return them from a function or use them as parameters - whereas Anonymous Types cannot cross method-call boundaries (excepting using generics for pass-through).

Anonymous Types in C# were a massive mistake. They should be [Obsolete]'d, IMO.

The tuple might be gone by the time the scope ends. I'm aware it's possible to reproduce the behavior without let. The code just looks worse.

chill,

I've been just showing that *approach* of starting SQL query from "from" part is viable, because that's how Microsoft timplemented in one of two LINQ "API"s"

I'm not trying to convince anyone to use LINQ's Query Syntax.

I wonder if that could be addressed at the spec level allowing reverse order of these keywords. It doesn't seem complex on the surface and the the engines could slowly add support for it.

LINQ and proper reflection are what I miss most from C#. (I’m all Typescript at the moment.)

Gods, I tried to do some reflection stuff in a Node TS project I got put on about 18-24 months ago, and it was a total shit show. It was really disappointing to see how lacking the actual runtime capabilities of TS are.

There are no TS runtime capabilities. This isn't a thing.

There are some libraries that allow you to do some stuff. You can attach metadata to some objects about types in some contexts. But obviously it's bolted on because under the hood it's all Javascript.

What runtime capabilities? There shouldn’t be any?

What does "proper reflection" mean to you?

I recall Anders saying on some podcast they did that so they could provide auto-complete. Funny thing is that I used it for a couple years, but almost never use the linq syntax any more, and not sure why.

Probably because if you wanted to write something like SQL, you would write it in SQL, and if you want to write C#, you write C#

Linq query syntax is just dumb

I'm so jealous that Java does not have its own LINQ.

Tcl dicts do something very similar, and Tcl is very well integrated with SQLite.

Always start with the end in mind, first what your goal is then how to achieve it.

Also, i don't actually see the problem because you never write a query in a lineair way. Usually start with "select * from table limit 10", look at the columns and data available, and then start refining. By now, code completion works as the table is known. Wouldn't help much to write it table first.

Exactly, you could actually drop the `select` in this case and just say `from table limit 10` don't state what you don't need.

What you describe is learned behavior to get along with a design flaw. SQL won't change, so no reason to worry.

My point is: People keep creating new versions of it, because it is not as `easy` to work with as it could be.

That's valid DuckDB syntax

DuckDB has some really nice syntax updates that I hope get added to the ISO spec, like GROUP BY ALL and GROUP BY aliases.

I like their approach of adding thoughtful quality of life improvements instead of coming up with a new language.

https://duckdb.org/2022/05/04/friendlier-sql.html

Most of the improvements in the article, including unrestricted aliases, were added in ClickHouse and subsequently influenced DuckDB. They still have to implement many usability and language improvements from ClickHouse.

Didn't know that, thanks.

> GROUP BY aliases

We're migrating from Sybase SQLAnywhere to MSSQL, and not being able to use aliases in WHERE, GROUP BY and ORDER BY is such a pain in the behind.

Almost all our non-trivial queries have to be nested multiple levels due to this, which doesn't exactly help readability.

Ugh, I'll be having to do this same migration soon for one of my clients. Not looking forward to it. Good to know!

I would love if base SQL were improved. On the other hand, we are just slapping lipstick on a pig. It is already a gargantuan mess which needs a path to replacement.

Then again, Oracle just this year added support for booleans, so asking the incumbents to switch to a new query language seems an impossible ask.

Agreed... Their SQL is awesome and seemingly only getting better.

My favorite is window aliases (I'm sure it's found in other SQL engines too). It's not only cleaner and less likely to accidentally introduce a discrepancy, but apparently also results in better performance.

> The three window functions will also share the data layout, which will improve performance.

https://duckdb.org/docs/sql/window_functions#window-clauses

I'm surprised some of the other big-name OLAPs don't provide this. *cough* Snowflake *cough*. Snowflake's query optimizer doesn't even seem to recognize common window definitions across multiple window functions.

Because the DuckDB folks used the Postgres query parser.

It's been an alternative in Postgres for decades.

In Postgres, it's

   TABLE foo
   LIMIT 100;

No SELECT with columns and no FROM keyword.

> Usually start with "select * from table limit 10", look at the columns and data available, and then start refining.

An experienced person won't do that. For any moderately complex SQL query, before writing it I already have in mind the several jointures I'll need, since I usually know the tables and FK I'm working with. It's like following the edges of a graph, all in my head. But I don't know all the fields of these tables, so I rarely write their names from memory. So I have to write "SELECT 1 FROM …" and then go back to that "1" once my FROM is complete. That's not the end of the world, but it does smell.

> An experienced person won't do that.

I’m very experienced and do that kind of thing all the time.

Also, your “SELECT 1” technique seems like practically the same thing, except I guess you discover column names from autocomplete tooling rather than query output. (To my mind the difference is inconsequential.)

Me too and I don't even bother with LIMIT 10. However, the "proper" experienced way would be DESCRIBE I guess.

If you mean a developer with deep and recent experience on the exact tables your query will use, then yeah, this one won't do that.

But if you mean any other kind of experience or expertise, you are wrong. Those do not correlate with how a person assembles a query.

I consider myself experienced, started professionally with sql in 1994 with oracle 6, and it really depends on how familiar you are with the database. I often work with databases I’m not familiar with, so exploring the structure by looking at examples of the data is where I start.

> have in mind the several jointures

I typically keep all JOINs I've ever used on that schema in a single file, one per line.

Before writing a new query I can just copy paste some JOINs, simply skimming through table names like lego bricks.

That way it's surprisingly easy to beam from domain problem to a new query that uses 10 or 20 tables.

I've just realized that it might be all archaic now, in LLM era.

This is a great idea in practice, but it really points to how sql could improve.

You have a library of useful joins that you've checked for correctness. Saving the library as reusable code would be even more useful.

In most tooling, you can write SELECT FROM table, then go back to the select list and have autocomplete work.

The situation could certainly be better, but at least this works today.

Yeap, simply supporting FROM … SELECT … WHERE … would make sql much more autocompleteable?

This is also my primary issue with vi.

d3w (`d`elete `3 w`ords) cannot be highlighted / indicated in any way ahead of time. If the motion specifier came first, it could be.

`3w` is a command all its own. There’s an implicit move command baked in. Moving gets pretty clunky if you don’t have first order movement (adding a specifier or something to clear selection). You can mimic this by mapping a single button to <C-v> and disabling all movement commands in normal mode. It’s rough, but may be learnable.

I think something that might work better is adding a “commit” signal to operations. So you type `d3w` and the editor highlights the next three words with a strike through or red or whatnot. Then you can hit enter to commit the delete or escape to cancel it (cursor resets to start position, highlight goes away).

If you use visual mode, you can do `v3w` to highlight three words, and then you can manipulate that selection before you do something with it, like `d` for delete.

You can also use that with ex commands, like if you do `vap:s/foo/bar/g` you will replace `foo` with `bar` only within the block of code you visually highlighted with `ap` (which I remember as “a paragraph).

So if you want “delete three words”, do `d3w`. If you want “highlight three words” do `v3w` and then issue another command like `d` to do something with what you highlighted.

I think these two concepts could be reconciled by having selection be combined with moving. So 3w could select three words but also move the cursor to the end of selection, ready to process another normal mode command. The highlighting would need to be scaled back a little (perhaps a dark gray background), as you don't want your entire screen to light up when moving but I think it's doable.

That's exactly the model used by the Kakoune editor[0]. It definitely feels more intuitive to me, but I personally didn't stick with it due to vim's ubiquity.

[0] https://kakoune.org

> So 3w could select three words but also move the cursor to the end of selection

I mean, if you hit v first, that's exactly what it does.

Yes, but in terms of editing speed and comfort, one keystroke is the difference between life and death. As long as the motion can be described in a single command, visual mode is overkill.

Wouldn't what you're proposing complicate the rest of normal mode though?

Or is this just reversing the order so it's {motion}{verb} instead of {verb}{motion}?

v3wd?

If you want to highlight something, use `v` for "visual" mode

You might like kakoune (https://github.com/mawww/kakoune), which does exactly that: first you select the range (which can even be disjoint, e.g. all words matching a regex), then you operate on it. By default, the selected range is the character under cursor, and multiple cursors work out of the box.

It's also generally lean and follows the Unix philosophy, e.g. by using shell script, pipes, and built-in Unix utilities to do complex operations, rather than inventing a new language (vimscript) for it.

(Not affiliated with the creator, but kakoune has been my daily driver for years now.)

Helix[1] is another editor which heavily borrows from kakoune’s “selection then action” paradigm. The editor is very good, but still in heavy development, so it lacks plugins and has the occasional rough edge. [1] https://helix-editor.com

I know you specifically mentioned 'vi', but I'm going to blindly assume you meant 'vim'. In which case, as others already pointed out, you can use visual model to get exactly that behavior.

A good mindset to have in regards to Vim is, "Vim can do anything, even make you coffee". The trick with Vim is actually figuring out /how/ to do it.

I highly recommend you start with the VimCasts[1] video series. They're short, 5 minute, videos. With each covering a specific functionality of Vim. They straight to the point, and the author provides samples code for all videos.

[1] http://vimcasts.org/episodes/page/8/

While I agree with your point, this isn't really as big of a stopper for tooling as you make it seem. Many programming languages use something akin to import foo from bar and their tooling is just fine.

FROM users SELECT name, id, location WHERE name LIKE 'a%';

You know I think you're right.

Eh. I’m not convinced by this. I may know what I want to query as often or more than knowing where it comes from.

I suppose it could be nice if the user could specify clauses in an arbitrary order but it’d certainly add complexity.

I don’t find it difficult to jump around a bit from clause to clause while writing a query. In fact, it’s incredibly rare to write a query straight through and have it do what you want it to do.

Go further: `FROM users WHERE name LIKE 'a%' SELECT name, id, location` - after all, you can WHERE on things that aren't projected by SELECT

A human would grab the paper with the right table on it then search for the row he wants with his finger and finally gets the phone number from the row.

There was an article on HN about this maybe 6 months ago. I’m in agreement.

O well, if we are going to be like that?:

FROM users WHERE name LIKE 'a%' SELECT name, id, location

(I should refresh the page before posting)

I might be wrong, but there’s nothing to stop a front end UI from accepting this syntax and then writing out canonical SQL to do the actual query, is there?

Exactly, it puzzles me that tooling doesn't do that...

This is a strength, not a weakness. It forces you to only select what you actually need, and state where it comes from afterwards, to only limit your query to exactly what you need.

This is paramount for performance.

If autocompletion is your issue, just get a better client, it's perfectly possible to autocomplete field names even before specifying database or table name.

I think you're missing what they're trying to say. You can still select what you actually need in a different order, but changing the order gives more immediate feedback from autocomplete. The order has pretty much zero impact on the performance of a query. A parser would still have to read out the whole query, and it's not expensive to unravel into a more efficient implementation if it would really help.

The problem is that SQL forces you to think about what to select before you even say where you're selecting from. There's nothing a client can do to recommend columns if it doesn't know where you're selecting from. It's pretty cumbersome to have to SELECT * FROM x and then go back and erase the * to actually get auto-completions.

It basically forces you to tell it what you want before you even know what the options are.

You are missing what I said.

I know that from the interpreter's standpoint, it doesn't matter which one is written first.

What I meant is that as a human, if you have to think first of the columns you want to bring in, it will guide you towards the joins that you need and only those, rather than thinking "let me join all those tables because I need _some_ data from the entities inside".

My point about "autocompletion is still position" was not connected to the first part of my comment.

Even if I know the exact query I want to write, your suggestion does nothing to improve autocomplete for typing it in.

Yes because that's not the main point of my comment. Disregard completely the part about autocomplete if you want.

I just added it as a separate point, to say "you can have autocomplete no matter the order in which you write your query"...

The query is just a requirements statement.

After parsing and analysis steps, the system is going to do what it does with the statement, no?

The syntax is for the user, not the system.

Sure, but the parent is speaking about how it is difficult for tooling to present with options when it doesn't know which table to select from.

Ain't broke.

GP clearly shows why it is. I think SQL is great but their point is spot on.

The wrong order in the statements is a minor inconvenience but the widespread use and having an industry standard is 100x as valuable.

Agreed. The last thing I need is more flexibility in how people write queries. It would be shortly followed by numerous aggressive query formatting tools. Things would get messy fast.

That's just his opinion. SQL does it's job extremely well, is relatively easy to parse for its inherent complexity. That somebody would like FROM before SELECT or WHERE after GROUP BY is of no consequence.

Almost exactly what I was going to say, then saw your comments. To me that the biggest problem. What I have been doing to workaround this is to just write select *, then finish the from join etc, go back and fix the result columns.

I get around this when hand coding by doing a quick 'SELECT * FROM', adding my joins, then going back and filling in the fields using intellisense. Intellisense doesn't know what columns are available until the FROM clause is handled.

To me, this is a slight annoyance with an easy workaround. If the SQL spec gets updated to allow switching the clauses around, I'll be pleased. But I'm not about to change languages over it.

That's exactly what I do too. 'SELECT * FROM', then write the rest of the query, then double back and replace the * at the end.

Is it ideal? No. But it's muscle memory now, so...

¯\_(ツ)_/¯

Oh, that's a great idea. What editor/ui do you use? Since I've got divorced from Jetbrains I miss Datagrip ...

Why the divorce? DataGrip has been the best database-gui that I've ever used.

I had the all-tools subscription for years (working mainly on Spring Boot and Angular projects) and IntelliJ and Webstorm were fantastic. Then I started Vue projects and later on Svelte and the Webstorm support for both (and Tailwind btw.) has been a disaster. Esp. how they handled the countless issues created by me and many others. Many other small annoyances like no support for international keyboards with dead keys on Linux and esp. how support handled it.

So, I had to move to VS Code for Vue and Svelte and as I didn't want to use 2 different IDEs I canceled my JB subscription. So far VS Code is pretty good for Goland and Rust as well, but as you said, I struggle to find something as good as DataGrip for SQL. Maybe I should just get a DataGrip license (single tool).

Edit: I forgot another issue ... DataGrip didn't support the latest SQLite version for a very long time, as the maintainer for the open source Java client wasn't working on it. When I dared to ask the JB support a second time for the status after a while, I got a stroppy reply from the responsible Jetbrains engineer, as how I dare asking twice and there's nothing Jebrains could do when the single person maintainer of an open source lib is doing other stuff with his life. Crazy.

vscode. mssql plugin because that's what I'm dealing with.

I agree with you and think this backwards model leads to developers having a poor mental model of what they are doing.

Step 1: build the dataset you want (FROM and JOIN) with all columns.

Step 2: filter out the rows you don't want (WHERE).

Step 3: choose which columns/values you want (SELECT).

Maybe it's just me but this model makes so much more sense to me! I'm sure but every developer has the same way of thinking, but it sure does seem more logical to me.

This is definitely something that makes tooling harder. But I think it is because tooling is though up wrongly.

Consider `SELECT 0 AS some_num`. This does not have a FROM clause.

While this example seems contrived, there are several examples of queries where the FROM clause is not just a listing of tables. Especially when moving into larger projects in SQL.

If I could change one thing with sql, this would be it.

It makes more sense to have the select last.

Just start with `SELECT *` and sculpt w/ autocomplete afterwards as you like.

Since you express that opinion, I trust you're already aware of this, but just in case: https://prql-lang.org/

>eliminates all tooling support.

That's an exaggeration. Usually I just start with Select * and build all the necessary joins. For that the tooling support works without problems and when I select the columns in the end it works too.