How does the (u => u.Id == 123) lambda parse/execute? The bad way would be to load all users from the DB and filter on the server, the good way is to send a WHERE clause to the DB in the first place.

I ask because `Where(u => ComplicatedBooleanFunction(u))` can't in general be transformed into something you can do in SQL, especially if it's stateful, but maybe there's a trick if the function is simple enough?

Some ORMs get around this problem with methods like .WhereEqual(User::id, 123) so you don't need to parse anything to know the semantics.

PS. I have in the past got praised at work for massively speeding up database access, when I fixed something like `List<User> userList = UserDAO.fetchAll(); return userList.length();`. You need a minimal understanding of databases to use an ORM correctly. The DAO even had a `.count()` method, but you need to know a little about databases to know to look for that in the first place.

I mean, you're definitely still writing a query in this case, just using a lazy-loading C# interface.

An ORM in the traditional sense abstracts the details of the query itself fully away in favor of an object-oriented interface.

Is usually the right answer!

> I'd be willing to rewrite queries in some other language that transpiles to SQL if it allows me to do all the queries I want and gives me full compile time type support for db access in return.

Full typed coverage for db is what I'm doing in Typegres [1] -- including all dialect built-in functions/operators.

And regarding policy, instead of RLS it's all based on ocap: reachability is permission. So: `api.user.posts()` automatically injects a `where` clause on the `users` table and it's composable wherever a SQL set expression is allowed: `api.user.posts().join(...).groupBy(...)`. Since we're building up a SQL expression tree, we avoid the N+1 problem entirely.

[1] https://typegres.com/

I think N+1 problem in real applications are more than just a coding experience problem. N+1 problems frequently arise because of separation of concerns where the code doing the looping, and the code making the sub-queries are significantly separated from each other. In that case preventing the N+1 is not obvious, and fixing it can be very complex, and result in messy code.

In languages with strong meta-programming, like Ruby, it is possible to deal with N+1s more automatically, which allows you to prevent them systematically, and most importantly have an elegant solution to N+1s that span independent blocks of code.

A couple of articles about these techniques: https://www.aha.io/engineering/articles/90-percent-of-rails-... https://www.aha.io/engineering/articles/automatically-avoidi...

> not a perplexing multi-faceted, engineering problem to be solved

It's a common mistake, not a deep, interesting one.

It is just a coding mistake, except that fixing that mistake leaves you with clunkier abstractions.

If you have Foos, and users have permissions that control what they can do to a Foo, you'd like to have a function `GetPermissions : (UserId, FooId) -> Async<Permissions>`. If users can frob Foos you'd like to have a `FrobFoo : (FooId) -> Async<void>` function.

But as soon as you let users select multiple Foos, or god forbid, an entire folder containing Foos, and bulk-frob them now you have to write `FrobFoos : (List<FooId>) -> Async<void>`. And to avoid the implementation of that causing another 1+N checking permissions, you also need `GetPermissionsBulk : (UserId, List<FooId> -> Async<Dictionary<FooId, Permissions>>`. The singular forms of those functions, to avoid duplication, now become wrappers over the bulk forms.

The logic becomes harder to trace in the rewritten, bulk forms of the functions, but they are efficient.

Next the customer hits you with a request like "let's have a smart-frob function that works on all the selected foos. For foos that are red, it frobs them, if they are blue, it fizzles them". Now you have to bulk-load to select the redness or blueness of all your Foos, build two separate lists, red and blue, then call your bulk-frob and bulk-fizzle functions accordingly on the two lists. Again the machinery to turn the requirement into a batch-shaped thing is not a lot, but it does kind of obscure the original business requirement.

At various times in the life of the project you will have a feature that starts as a "always done on one Foo" thing because it's triggered by a button on the detail screen. Then somebody will possibly come along and want to do it in bulk later and you have to rewrite the implementation. Unless you have very strict code review that everything MUST be written in batch-style taking a list of IDs up to the API layer.

I wrote a library[1] many years ago to solve this problem and allow the straightforward, non-batch versions of the functions to be automatically batchable. The idea is kind of like what React did for frontend dev: React was not faster than mutating the page with jQuery soup, but it was much faster than replacing the entire DOM on every render, and it let you write your code as if that was what you were doing. That was a very simple mental model and much less buggy than jQuery soup.

The idea of my library was basically borrowed from other functional languages with a resumption monad, meaning that instead of an opaque async task to go do a thing, you have a "plan" which could either be a. done or b. waiting on some errand that requires firing off a query. If you have a list of plans like from a loop, you could step all of them to the next errand they are waiting on, then fire those off in a batch. So plans could be composed linearly or "batch-style" depending on your preference[2].

What makes it very powerful is the combination with an F# type provider that could analyze your SQL and automatically determine a caching profile for each query. It knows what tables the query reads from, what tables it writes to, whether it uses any impure functions like random(), etc. So within one transaction, it wouldn't re-run the same pure query again, it would pull the results from a local cache -- except if another command issued in that transaction updates those tables, the cache is automatically invalidated. This solves the other code smell that starts to accumulate as you try to write efficient database code in a complex app -- keeping materialized objects loaded in memory and passing them around to other functions so they don't have to re-query for them.

Anyway, it was a little too weird to catch on, and I was a little too burnt out to maintain it.

[1]https://github.com/fsprojects/Rezoom.SQL

[2]https://fsprojects.github.io/Rezoom.SQL/doc/Rezoom/README.ht...

This isn't algebraic. Going from one to many queries may be arithmetically the same as going from many to many plus one queries, but operationally, the implications are different.

I'm willing to look the other way when N=1, and 0..n is a small number of values on an indexed column. As others have pointed out, sometimes it's difficult to merge two queries like `get_item_list()` and `get_items()` located in different parts of the code, especially if they cross service boundaries. It's ugly, but may be a completely tolerable situation as long as timing profiles and usage patterns show that it's not going to blow things up. So there, 1+N => 1+1 which isn't inherently scary.

It's when 1+N turns into 147 queries, one for each line of a table displayed on a web page, that it really chafes.

Let me introduce you to our frenemy, IEEE-754.

Pure Datalog can't express peano up to infinity, its terms can't be functors as in Prolog. At best you could hardcode a successor relation up to a limit.

I really like how you phrase this and completely agree. Issues should be caught as early as possible in the dev process and what you describe is probably as good as it gets. I tend to look at such problems from an organizational perspective and a good APM is a fail safe that compensates for other failures.

I recall hibernate, it maybe rails, as those are my most used tech, that it can detect n+1, and then just query the whole relation.

Detecting is easy if you’re iterating over a proxy collection