Welcome to LINQ - introduced in 2007 (?).
LINQ isn’t exactly an ORM. You create strongly typed LINQ statements that are turned into expression trees that are then turned into a query by a query provider.
It's kinda gross because the developer needs to make a decision about what is authoritative source of truth, the programming model makes you feel like you can trust your code (probably the wrong choice), and all the footguns around distributed state kick in (possibly even worse if you have a frontend with two way data binding and data structures that last longer than an http request in your backend)
You can really use linq to query anything (APIs, for example), including databases without using EF. It's just not very common because building a custom query provider is a lot of work.
Being able to run queries over data without having to implement the various boilerplate is excellent.
Unfortunately basic mistakes can result in so many of those N+1 type queries if you're not careful. It can also result in reading entire table(s), possibly inside those nested N+1s.
It gets expensive when it's a remote DB server and/or dealing with a lot of records.
Profiling tools are essential.
It's why I'm not a fan of LINQ (and Entity Framework) for talking to DB severs without very strict controls.
Many times I've been called in to deal with a "slow" server, only to find out the issue is someone chained together a bunch of calls through EF and we have hundreds of thousands of queries that can be replaced by one or two.
All of those can use SQL as a worse query language than their native counterparts.
You can also create very bad SQL if you don’t know the underlying engine. For instance if you try to write SQL for a columnar database like you would for a traditional database, you are in for a world of hurt.
I would argue that when talking about writing SQL, most folks are talking about applications who's query planner talks some dialect of SQL. MS SQL Server, MariaDB, MySQL, Oracle, SQLite.
In those cases, it's not really an abstraction any more than writing assembly is an abstraction over (say) Intel's CPU microcode is.
The query planner takes your SQL and turns it into something else, sure, but you can't generally do that yourself. It's the lowest layer of abstraction that's reasonably available.
I don't know which "basic mistake" would do this. Maybe when using Lazy loading Proxies?
We use EF Core and it is quite easy to control eager loading or load-on-demand on a per-case basis.
As developers get more acclimated to the .NET ecosystem they usually pick up a good spidey sense for what query "shapes" are appropriate to express in LINQ and which are asking for trouble.
However, for every one of those awful LINQ queries in a codebase, I think there are 40 to 50 run-of-the-mill queries that make it worthwhile. The biggest win that it delivers over a simpler, "stringier" ORM is the ability to return structured data. I have no problem writing SQL, but processing the results back out of flat rows into objects gets extremely annoying.
Say I want to load some Foos with their Bars. If I use straight SQL with a row mapper, I define a type to represent each FooBar row, load a List<FooBarRow> into memory from my simple left join, then I'll have to do a GroupBy in memory on that List to get the actual structure that I want: a list of Foos where each one has its own list of Bars. Notice that I also have to handle the empty-list case specially.
var fooBarRows = await connection.QueryAsync<FooBarRow>("select f.FooId, f.Name as FooName, b.BarId, b.Name as BarName from Foos f left join Bars b on b.FooId = f.FooId where whatever");
// re-shape result set manually from flat query results
var foos = fooBarRows
.GroupBy(fbr => fbr.FooId)
.Select(g => new Foo
{
FooId = g.Key,
FooName = g.First().Name,
Bars = g.First().BarId == null ? new List<Bar>() : g.Select(b => new Bar { BarId = b.BarId.Value, BarName = b.BarName }).ToList()
});
var foos = await db.Foos.Where(whatever).Include(f => f.Bars).ToListAsync();
It is weird that people jump into strange extremas when you can just combine best of both worlds.
I'm pretty sure Dapper lets you do this fairly easily. You've got a couple options - you can either provide a mapping function, or you can have your query/sprocs return multiple result sets. Then you can assemble the result sets into the object structure.
It's not quite as magical as LINQ, but it's also not quite as annoying and fraught with so much marshalling code as your example.
https://learn.microsoft.com/en-us/ef/core/querying/client-ev...
The IQueryable operations composed before to the ToListAsync() will run as SQL on the server. In the case of my example it will look pretty similar in structure to the string SQL I wrote before it: a straightforward left join with a where clause. Performance will be similar too. It will not load the entire universe of Foos and Bars and then filter them down on the client.
One of the habits I have developed from working with EF for the past 10 years is to be explicit and as local as possible about forcing the query to evaluate. It lets me pretty reliably predict what the SQL is going to look like.
You can return IQueryables from methods and pass them around through many layers of your program, adding complexity to them as you go, and lazily getting the results at the last possible moment, five layers up the call stack from where the query first started.
It seems at first like that would be good, because that way the maximum amount of logic will run on the DB server, and letting the server do stuff is better, right? But that's also where you open yourself up to being very surprised about what your SQL looks like by the time it executes, with multiple layers of your program each tacking complexity onto the query. It can get ugly. Also you can have problems if you're hanging onto IQueryables that you still haven't executed after you've Dispose()d your DbContext. For this reason I try not to let IQueryables travel very far through my program before forcing evaluation.
You mean "LINQ" as an input for ORM? or LINQ in general?
either way this is crazy claim.
There's shitton of projects where queries aren't complex and LINQ will make your life saner instead of this stringly typed hell called SQL.
Just check what ORM is generating for your queries and if it is a mess then write it manually and you're good to go.
This way you get both benefits: sane type system and performance.
I get that there are big LINQ advocates. It's convenient. I'm sure there are projects where it is use spectacularly. I've never seen such a project.
Do you think that LINQ is only used for EF/LINQ2SQL?
LINQ is (at the most basic level) list comprehensions done better. It is functional programming for the imperative C# programmers. It has the potential to remove most/all loops and make the code more readable in the process.
The classic fallback.
>If you had, you would not make a comment where you seem to think that LINQ is used only for data access.
List comprehension is data access. Accessing sets in memory is data access.
>If you had, you would not make a comment where you seem to think that LINQ is used only for data access.
It has the potential, and almost the certainty, of allowing one to thoroughly shoot themselves in the foot. See without the "magic" of LINQ the grotesqueness of many patterns of data access (which, as previously mentioned, includes in memory structures. Pretty bizarre that anyone actually in this field thinks this only applies to databases, or that only DBs are "data") would lead one to rethink.
LINQ is almost always a bad indicator. It is actually a fantastic thing in one off scripts and hack type code, but when it appears in production code, I would say 90%+ of the time it is absolutely gross, but it hides how gross it actually is.
It is just a slightly slower than e.g fors, so unless this is hot path, then it is basically not relevant meanwhile it improves readability of the code
Yeah, you definitely have no idea what LINQ, or an list, even is.
It's also the first thing I recommend to anyone claiming that concatenating SQL strings together is a good use of your time in the 21st century. Makes writing things like complex HTML tables with configurable columns and tons of optional filters so much faster and more maintainable.
I don't see this as a given and I don't accept it in my own applications. If you need data, go to the data access layer. If it doesn't provide you what you need, build a new repository / provider / whatever your pattern is.
Whether it's an ORM or a home-built query compositor or whatever, one thing I know from experience is that once your application is "sufficiently complex" that you start to (incorrectly) believe you need this, your application has become too complex to use it reliably.
You absolutely will be mistakenly evaluating these builders in the wrong layers, iterating results without realising you're generating N+1 queries, etc.
You don't need an ORM.
One simple example is when you need to make atomic changes to two different types of entities. In the data layer, this is usually trivial — just run two queries within a transaction — but you need to expose a function that boils down to `updateBothAandB`. Rinse and repeat enough times and your data layer is a soup of business logic.
So no, bespoke data access layer code is not an ORM.
Whichever layer you put it in, you need a way to compose two query fragments. Otherwise you have to write N*M queries manually instead of N+M query fragments.
Either you use an ORM to help you with this or you don't, with all the usual tradeoffs about using a library or not. But you still have to solve the problem. (Or you do a lot of tedious copy-paste work - with all the usual tradeoffs of that)
* Accept that this will just be two trips to the DB.
* Write a new function func12 that writes a query to return all the needed data in one query and use that instead.
* Have your tool be able to automatically compose the queries.
If you do with the second option you have to do that with every combination of functions that end up being used together which is multiplicative in general.
it's pretty rare for queries to be dynamically composed from arbitrary sub-queries
if this is a problem you need to solve then ORMs certainly make more sense, but even in this case I find query builders to be more effective
the interface between the application and the DB is actually a string! it's not an abstract data type, it doesn't benefit from being modeled by types
in general, it should not be possible for user input to produce arbitrarily complex queries against your database
each input element in an HTML form should map to a well-defined parameter of a SQL query builder, like, you shouldn't be dynamically composing sub-queries based on the value of a text field, the value should add a where or join or whatever other clause to the single well-defined query
sometimes this isn't possible but these should be super rare exceptions
/users/:id should map to 1 endpoint that's parameterized on userid
/search?userid=:userid&tag=:tag should map to 1 endpoint that's parameterized on userid and tag(s)
endpoints should be simple to write
You’re going to have parameters that are compound. You’re going to end up filtering on objects 3 relations removed, or deal with nasty syncing of normalization. You’ll have endpoints with generic relations, like file uploads, where the parent isnt a foreign key.
It’s going to be a mess. They will NOT always be simple to write.
I'm talking static, not dynamic. You still need to compose two pieces together into a single query, and you can either use an ORM to help with that or not.
> the interface between the application and the DB is actually a string! it's not an abstract data type, it doesn't benefit from being modeled by types
No it isn't. You can't send an arbitrary string to the database and expect it to work. At the very least you benefit from having an interface that's structured enough to tell you whether your parentheses are balanced and your quotes are matched rather than having to figure that out at runtime.
when your app queries the db, the query is not composed from several pieces, it is well-defined in the relevant method
fn search(q string) -> result
return db.query(`SELECT id, text FROM table WHERE text LIKE $1;`, q)
the db accepts a string and parses it to an AST, it does not accept a typed value
this means the interface is the string
unbalanced parens and whatever other invalid syntax is obviously caught by tests
> this is a single query, not multiple
And when you want to query for multiple related things together, the whole point of having a relational database? For different purposes you need different views on your data, and those views are generally constructed out of a bunch of shared fragments; you can either figure out a way to share them, or copy-paste them everywhere you use them.
> the db accepts a string and parses it to an AST, it does not accept a typed value
> this means the interface is the string
The DB accepts a structured query, not a string. It might be represented as a string on the wire, but if that was what mattered then we'd use byte arrays for all our variables since everything's a byte array at runtime.
> unbalanced parens and whatever other invalid syntax is obviously caught by tests
Tests are a poor substitute for types.
every "view" on your DB should be modeled as a separate function
every possible "thing" that's input to a function which queries the database should be transformed into a part of the query string by that function
> The DB accepts a structured query, not a string. It might be represented as a string on the wire, but if that was what mattered then we'd use byte arrays for all our variables since everything's a byte array at runtime.
...no
the API literally receives a string and passes it directly to the DB's query parser
if the DB accepted structured queries, then the API would rely on something like protobuf to parse raw bytes to native types -- it doesn't
like `echo SELECT * FROM whatever; | psql` does not parse the `SELECT * FROM whatever;` string to a structured type, it sends the string directly to the database
OK, and when a significant amount of what those functions do is shared, how do you share it? (E.g. imagine we're building, IDK, some kind of CMS, and in one view we have authors (based on some criteria) and posts by those authors, and in another we have tags and posts under those tags, and in another we have date ranges and posts in that date range. How do you share the "fetching posts" bit of SQL between those three different (parameterized) queries?)
> if the DB accepted structured queries, then the API would rely on something like protobuf to parse raw bytes to native types -- it doesn't
> like `echo SELECT * FROM whatever; | psql` does not parse the `SELECT * FROM whatever;` string to a structured type, it sends the string directly to the database
In both cases the parsing happens on the server, not the client. "echo abcde | psql" and "curl -D abcde http://my-protobuf-service/" are both doing the same kind of thing - passing an unstructured string to a server which will fail to parse it and give some kind of error - and both equally useless.
your application has a fetch posts method, that method takes input including (optional) author(s), tag(s), etc., it builds a query that includes WHERE clauses for every provided parameter
the code that converts an author to a WHERE clause can be a function, the point is it outputs a string, or something that is input to a builder and results in a string
i'm not sure what a "fetching posts bit of SQL" is, a query selects specific rows, qualified by where clauses that filter the result set, joins that modify it, etc.
So you do string->string processing, and you explicitly won't treat the queries you're generating as structured values? Enjoy your rampant SQL injection vulnerabilities.
creating a query string that's parameterized on input usually means you model those input parameters as `?` or `$1` or whatever, and provide them explicitly as part of the query
nobody is doing printfs of values
Right, but is an ORM a good way to achieve this? I would posit no. The problem, correct me if I’m wrong, is one of an in memory representation of working data and a data access layer (how to get/update the data). An ORM provides a framework for both of these, but in my opinion it’s very inefficient to build, maintain, and optimise. You don’t need to turn to raw SQL as the alternative data access layer, but having one that is easier to customise is better in my opinion.
Not at all an expert, probably nonsense, please correct me.
ORM's such as Eloquent in Laravel also have some nice methods to resolve N+1 and perform lazy loading, but it's always tradeoff.
There is no other way — people who don’t know one should not touch the other. Otherwise they are either juniors, or crazies who just like to complain that “the plane is a bad vehicle because I can’t just sit inside and land it properly without years of training”.
E.g. you could write a query like this:
getUser:
SELECT * FROM users WHERE id = ?
class GetUserQuery {
static getUser(id: String): GetUserQueryResult
}Go: https://github.com/kyleconroy/sqlc
Rust: https://github.com/cornucopia-rs/cornucopia
This is my preferred method of interacting with databases now, if available.
Very flexible.
In short, I don't think ORMs should generate class libraries to be referenced by applications, they should help you get exactly what you need, when you need it, nothing more, nothing less, and no one expect the person that maintains that code should ever need to care how the data got there. If a column gets added to a table that has nothing to do with a specific application, that application shouldn't need to be updated.
We still used the library for inserts and updates.
Then your devs have way too much time on their hands. Find them some actual problems to solve.
We should just learn to recognize it for what it is, someone that's being controversial for attention, and move on. Let's save our attention for the ORM article and discussion that starts out along the lines of "ORMs can provide benefit, but it's important to recognize where, and not let the problems of their use outweigh their benefits. Here's what I've found."
I don't have a horse in this race, I could care less if you do or don't use an ORM. But, and maybe this is the cynic in me, there are practices in software development that absolutely, 100%, for certain, have no good reason and are perpetuated in part by this belief that there must have been a good reason for it to exist (Chesterton's fence and all that).
Null terminated C strings are a prime example. There is absolutely no good reason, other than the fact that the authors may have wanted to save 3 bytes, that C strings should be null terminated. Fortran was created in 1954, and passed the length of the string with the string itself. How many countless bugs and CVEs have risen due to errors in handling null terminated C strings (one example[0])? And for what? To save 3 bytes or just because of the authors decision at a whim's notice?
Likewise, decisions made at Javascript's inception have burdened it for its entire life. Decisions that were made at a whim's notice, like implicitly converting numbers to strings sometimes, and sometimes implicitly converting strings to numbers! (Tell me what `10 + "10"` is and what `"10" + 10` is without using the inspector). And the million and one ways to define something that's undefined.
Anyways, when somebody tells me there's absolutely no good reason for a development practice to exist, sometimes, that is the absolute truth. And I would rather have more people throwing away these crummy practices that lead to unnecessary headaches (or at least questioning them) then people continuing to laud the practice and perpetuate it ad infinitum.
[0]: https://defendtheweb.net/article/common-php-attacks-poison-n...
I see this as a problem that's horribly inflated by those who don't use JS on a daily basis.
Practitioners of the language largely don't care, because in actual code you rarely see cases where it would matter.
Now that we have template strings it's even less relevant.
Or, you know, they wanted to make the language track assembly as closely as possibly (which was possibly back at that time when processors were much simpler and instructions weren't constantly reordered), and if you've ever written assembly, you know you're not really working at a string level, you're working at a byte and character level. Sized strings are more complex than null terminated ones in that you either need to set a max size or you need to waste multiple bytes per string (which actually mattered on many systems C was used on when it was developed) or you have to use masks on those early bytes to or determine if the next byte is part of the string or a continuation of the size.
And honestly, when you're working on systems where ram (and maybe storage) is in kilobytes or less and speed is in kilohertz, the extra code to do that and the extra time to process them and the extra space to store sized strings is a lot less of an obvious choice to make.
Was C a good choice for the time and where it was used? Possibly. Is it a good choice these days? Probably not without a bunch of extra utils and compiler guards to beat back the worst problems. I do t blame C as much for that as I do the people that continue to use it without extra safeguards.
What was that you said about Chesterton's fence? That's one of those terms you should be careful about throwing around when supporting an absolutist position...
But that's just me guessing. There's no reason for us to do that when Dennis Ritchie wrote down the reason for this:
>> This change was made partially to avoid the limitation on the length of a string caused by holding the count in an 8- or 9-bit slot, and partly because maintaining the count seemed, in our experience, less convenient than using a terminator.[1]
So this was a change made primarily for convenience. And if the limitations of 255 characters was really a huge blocker, they could have easily created a spec like UTF8 to allow variable length encoding depending on the size of the string, which funnily enough Ken Thompson who also worked with Ritchie, later did invent. You mentioned that the processing time would have been an issue, but C strings require you to process the entire length of the string to determine the length, and Ritchie notes that as an additional tradeoff for this convention.
But that wasn't done. And I can't blame Ritchie for that either, because he didn't think this language would become what it is today! Later on in the paper he alludes to this:
>> C has become successful to an extent far surpassing any early expectations[1]
All throughout the paper you can see him referring to decisions that were made out of convenience, and not because he had done extensive analysis to determine whether the tradeoff for the convenience was worth it:
>> Two ideas are most characteristic of C among languages of its class: the relationship between arrays and pointers, and the way in which declaration syntax mimics expression syntax...In both cases, historical accidents or mistakes have exacerbated their difficulty.
>> C treats strings as arrays of characters conventionally terminated by a marker...and as a result the language is simpler to describe and to translate than one incorporating the string as a unique data type.
All that to say, yes of course there were reasons that decisions were made the way they were. But, and this is what I've noticed more and more in programming communities, these decisions are often made with little to no analysis and usually made out of a subjective preference, or to make the implementors life a tad easier. So, yea, I think it's right to call out a lot of "best practices" because history has shown that programmers really don't put too much thought into their decisions. And then you end up with gurus proclaiming that a decision made out of convenience was actually the best decision available and we should never change the way we do things because clearly this is the right way.
Fortran was not created for the same purpose. Fortran existing as an invalidation of C's choices is like Java existing being an invalidation of C++'s choices. There's a reason Fortran and Java are not common choices to write in OS kernel in, while C and C++ are/were. There's a reason why C and C++ aren't often used for web development, but Interpreted languages are. Different design choices fir different niches better or worse.
> So this was a change made primarily for convenience.
Convenience can mean a lot of things, and in this context and in the absence of contrary evidence I interpret that statement to be entirely inline with what I said above. It was inconvenient to have a more complex type to deal with, for multiple reasons. I'm not sure why you would think it different, it's not like I said it could not work the other way, just that there were things that went into the reasoning that made it less obvious than in today's world.
> You mentioned that the processing time would have been an issue, but C strings require you to process the entire length of the string to determine the length, and Ritchie notes that as an additional tradeoff for this convention.
Yes, tradeoff. If what you're doing with strings most the time is parsing them, knowing the length ahead of time may be of little benefit, since you're going to step through them character by character anyway. For many operations, knowing the length ahead of time is irrelevant.
> decisions that were made out of convenience, and not because he had done extensive analysis
I'm not sure anyone is arguing they used extensive analysis. I'm certainly not. But when the reality you live and work in is that you are running up against real hardware constraints routinely, that's bound to affect your ad-hoc reasoning about what choice to make when you don't do extensive analysis.
> All that to say, yes of course there were reasons that decisions were made the way they were.
Given that this started because you wanted to support abso9lutist statements with "there is absolutely no good reason, other than the fact that the authors may have wanted to save 3 bytes, that C strings should be null terminated." and your prime example has now been walked back to the fact that yes, there were some considerations beyond that, including making it a simple language to describe, I think you've proved my original point.
Who is to say that C's simple description and ease of implementation for additional architectures isn't a major factor in it's success and spread? And yes, while we've been paying the price for that for quite a while now, it may also have allowed for a level of software portability that really helped advance computers beyond where they would currently be otherwise.
I don't like C all that much and I don't use it for anything, but I'm also willing to note that it must have done quite a few things right to get to where it did, and I'm not willing to call out any aspect of it as completely without merit while still acknowledging the immense benefit the language brought as a whole, because at that point we're getting into conjecture about alternate histories.
Absolute statements are generally bad ideas, and I've just been reminded of why that is the case haha.
It’s funny - it’s almost like C never had a string type and null-termination is more a convention than a primitive data type.
There’s nothing that prevents us from having Pascal-like strings as much as we want, provided we know we’ll need to reimplement everything we need.
So I don't see how this has much bearing on the ultimate point I'm making, which is that yes, conventions can be bad haha.
If it's a third-party API (say, a weather forecast or market data) the answers will range from "no" to "yes, and it will cost you X".
your query builder isn't just string based to avoid a lot of potential bugs which are easy to introduce and miss in tests? It also has a simple way to (de)serialize row from/to POD structs? Now you already have a thin ORM.
If you find yourself needing to lazily compose queries, then by all means, reach for a query builder. But I would encourage you to first examine whether you've split code into services that really should live together and whether that code should live closer to the data layer before you build a whole query builder.
And don't reach for a query builder until you need it.
JooQ in Java is a DSL for writing SQL in Java, it works amazingly well. It’s not guaranteed every JooQ statement compiles to valid SQL but I have pretty good experiences writing crazy complex queries using things like string_agg in pgsql. Here the relational model is primary and the representation as Java objects is secondary, given the SQL is persistent and the Java objects ephemeral this makes sense.
My RSS reader uses Arangodb and Python and I’d like to stop a bit and develop a query builder inspired by OWL class axioms which if you look at them the right way look like building blocks for a Scratch-like SQL query builder. I want both ordinary queries (browse my favorites, say an article with the keyword “niantic” that appeared on Tildes) and rules (don’t want read articles from the Guardian that have “- live” in the title)
Debugging and optimizing SQL queries is a well understood art, barring some rare DB specific optimizations (which ORMs don't even touch)
Getting expert SQL advice and support when you need is very practical and the results show.
Putting an ORM as the middle man generating your queries, negates all those benefits for some pithy premature laziness.
Operating on raw SQL is something I'd strongly recommend.
ORM is a tool that can make this easier, it's also a tool that can make it easier to shoot yourself in the foot (ie by making it easy to create N+1 queries without knowing). Like all tools, there are tradeoffs that need to be accounted for together with the actual use case to make a decision. Operating on SQL statement strings is not something I'd recommend in any case.