Hacker News Reader

Comment by plaguuuuuu | Hacker News Reader

People always say this and my experience in C# has been the opposite. I've never wanted to reach for raw SQL and hardly ever do, only if its because there's no extension to do SKIP..LOCKED or something

var name = dbContext.Users .Where(u => u.Id == 123) .Select(u => u.Name) .First();

View on HN

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.

In EF Core this will almost certainly generate a query like "SELECT name FROM users WHERE id = 123 LIMIT 1;". The LINQ expression is transformed into SQL, and any expression that can't be transformed will cause an error to be thrown.

Certain methods like First() in this case materialize expressions. Only those trigger a DB query.

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.

EF Core does not lazy load by default. You have to explicitly include relations if you want them on an entity. You can enable lazy loading, but in the default configuration you usually don't have the N+1 problem.

Isn't Linq a lazy-loading interface by default? The example they gave was a Linq query.

I'm not a C# user in my professional life so I'm happy to be corrected, BTW, just citing the source of my misinterpretation

Yes, but in this context this just means that until you call ToList()/ToListAsync() you have an IQueryable. That represents a query, but isn't executed yet. Only at the point where you call a method like ToList (in this case First() is the relevant one) is the actual DB query performed.

The LINQ query in the comment above would only execute a single query like "SELECT name FROM users WHERE id = 123 LIMIT 1;" and would not even fetch the full entity, only the name.

Right, which was my point. They used Linq to get around the limitations of an ORM more-so than using an ORM itself (by fetching an entity and accessing its 'field'). They're evading the N+1 problem by writing a query by hand still, just not in SQL itself.

The bit about lazy loading was a bit of a side-note more than my main point.

But using LINQ to query stuff is a core part of this ORM. And even if you access the full entity, it won't do an N+1 in the default configuration. You have to explicitly call Include() on any relation you want to fetch and it'll either fetch all of them with one query or do one query per relation type. There's a different footgun here with AsSingleQuery() and AsSplitQuery(), but that's a separate topic.

Remember kids, writing queries in an ORM is a betrayal to the ORM gods.

You are supposed to manually count the question marks in your raw SQL like the JDBC gods intended.

    String sql = "INSERT INTO users (name, email, age, status, country) VALUES (?, ?, ?, ?, ?)";
    PreparedStatement pstmt = conn.prepareStatement(sql);
    pstmt.setString(1, "Alice");
    pstmt.setString(2, "alice@example.com");
    pstmt.setInt(3, 30);
    pstmt.setString(4, "ACTIVE");
    pstmt.setString(5, "US");

There is absolutely zero chance an ORM can ever get close to the performance of this query, so write it by hand!

After all, the primary promise of ORMs has been that you do not have to write queries, it's right in the name! Object Relational Mapper!

> You are supposed to manually count the question marks in your raw SQL like the JDBC gods intended.

    String sql = "INSERT INTO users (name, email, age, status, country) " +
                 "VALUES            (?,    ?,     ?,   ?,      ?      )";

But I get your point.

Reply on news.ycombinator.com