Hacker News Reader

Comment by magicalhippo | Hacker News Reader

> ORM? EF Core is pretty good.

We're moving to .Net, and I was surprised by how poor the built-in DB stuff is. It's like either assembly or Python, but nothing in the middle.

That said I've also been impressed about how nice it is to get stuff going. I used C# back in the .Net 1.1 days and yeah massive difference in ergonomics.

View on HN

EF Core when it first came out was pretty rough but they have been adding a ton, especially in .NET 7 and coming up with 8: https://learn.microsoft.com/en-us/ef/core/what-is-new/ef-cor... Most of the pain points for me are solved now. Like someone else mentioned, Dapper can fill the gaps.

>It's like either assembly or Python, but nothing in the middle.

Dapper seems to be in the middle and it is pretty popular

Yeah, but from what I saw it doesn't help much with master-detail setups? Like, inserting or updating an order with order lines etc. We rely heavily on those.

It is pure SQL, so it should, I think?

Use transactions => fails rollback. No need to correct failures.

Important concepts: .NoTracking and setting the EntityState correctly ( mark as deleted, updated, ... )

Additionally, sometimes it's easier to update things granularly, instead of all at once.

I suppose the annoyance comes from updating in bulk on a POST and trying to map everything?

Maybe also check PetaPoco. But at this point you're getting closer and closer to code-first EF Core anyway. :)

https://github.com/CollaboratingPlatypus/PetaPoco

I haven't actually tried either Dapper or PetaPoco, only perused their documentation. But I was sold on LinqToDb after seeing how it supported CTE and seeing our close code to generate updates [1] and joins [2] ended up looking like the actual intended SQL.

  [1]: https://linq2db.github.io/#update

  [2]: https://linq2db.github.io/articles/sql/Join-Operators.html

"seeing how close"

It can. Basically you separate the query response by the parent and child in the row using `SplitOn` and it can materialize it.

I use the ORM with Servicestack, OrmLite, and it seems to handle those 1 to many references well.

https://docs.servicestack.net/ormlite/

>We're moving to .Net, and I was surprised by how poor the built-in DB stuff is

Right now EF Core is probably the best ORM that has ever existed. What exactly is missing?

Although for performance you would probably reach for something like Dapper but that is not an ORM.

Well that was my point, either you're writing a lot of code yourself ("assembly"), or you use EF ("Python").

We're not used to something like EF, perhaps it would work for us. But debugging generated queries due to performance issues is something we'd like to avoid. For now the decision was made to not use EF.

You can easily echo them to the console or debug window.

To be honest you should keep all ORM queries fairly simple if you can. Where clauses fine. Inserts, updates, deletes, ORMs save so much code, and so much pain when you add new properties/remove them.

But if a query is more than a few includes or joins you should be handcrafting it with FromSQL() or loading it piecemeal using Load().

And don't even think about using it to make complicated reports, that is not a good idea. Make a stored procedure or view.

And that's especially true if you are using anything other than SQL Server. I've seen abysmal performance myself on MySQL/Maria on moderately complex EF queries. I've not really looked since EF 6, but it used to love making nested selects instead of JOINs, which were fine in SQL Server but terrible performance-wise in MySQL. Postgre I've never used with EF in anger so can't comment.

You can use EF with Hot chocolate to make a GraphQL endpoint really easily, but I'd imagine that's an easy way to saddle yourself with serious performance problems unless you limit the levels it can go. I'd be interested to hear if anyone's using it and how they find it?

What I personally dislike about it is, for the easy stuff, I'm not sure it really saves that much code over a micro-ORM like Dapper, and for the hard stuff, well, everyone's a Linq wizard already so they're tempted to use it, especially if not in the habit of writing much SQL. With today's tools you can even get Intellisense on inlined SQL queries.

Also the lazy-loading and the in-memory provider for tests are both kind of misfeatures.

I think you might find Linq2Db[0] to be the right fit for you then, which brands itself on being typesafe SQL in C#.

[0]: https://github.com/linq2db/linq2db

EF handles migration and you can re-use the DbConnection and execute plain SQL.

If you don't want to debug difficult queries, then extend it to use Dapper and use the best of both worlds.

You don’t have to use migrations, it’s an optional feature. It works equally well to just generate entities from an existing database, that is migrated/set-up in any other way (as long as the schema is not crazily complex).

Not my project/not affiliate but Evolve is wonderful for handling db migrations

Haven't heard of Evolve before, but used DbUp in the past with success too: https://dbup.readthedocs.io/en/latest/

EF Core generates pretty good queries for common cases. I'm not entirely sure about really complex analytical queries, but you might want drop down to SQL for those anyway.

There is one gigantic footgun in EF Core, that is the decision between single query and split query. If you choose single query in the wrong situation you can end up with truly pathological queries. I might blame EF Core here a bit for a dangerous default, but to be honest the other choice would be dangerous in a different way, so there is no obvious good default choice here. This is a part that you need to understand to use this ORM, and fortunately it generates warnings now and kind of forces you to choose the strategy.

The one other aspect that helps to generate good queries with EF Core is to use "Select()" for any case where you want to request fewer columns than available in your tables. I find it quite natural to write queries this way in any case.

You can use EF Core also with plain SQL queries, if you don’t like/trust the query builder. You can also disable change tracking completely, if you prefer to only INSERT/UPDATE directly with SQL statements. You get a lot of awesome features, but nobody forces you to use them.

Well that sounds a lot better than what my coworkers told me. Will definitely check out EF Core.

Yeah, EF's Interpolated Execute methods are a massive step up over raw ADO.NET ("assembly"), even if you don't use most of the other modeling tools and context tracking.

The Interpolated family of methods take nice, clean string interpolation like $"Select * from Table where Id = {Id}" and make sure that is properly parametric SQL queries (ie, avoiding things like SQL injection attacks).

It's a killer feature and I have some idea why it lives in the EF side of the house rather than being generally applied across all of ADO.NET, but it should still probably be a more reusable library of its own beyond just EF.

For performance, .NoTracking() and not calling .SaveChanges() on every loop already does wonders.

I usually call .SaveChanges() when i % 20 == 0

We'll need tracking (if I understand correctly, we need previous values). When calling SaveChanges often, how do you handle rollback in case something fails?

Either wrap everything into a transaction or just call SaveChangesAsync only once. The exact behaviour depends on the database here, under the hood this uses database transactions.

You can use .NoTracking() and an SQL query for bulk changes ( and minor changes). Eg. Updating one column.

Works faster than with .Tracking. The SQL script runs in your transaction, so rollback runs as usual.

The i%20==0 condition with .SaveChanges() is used with .Tracking(), yes.

> Right now EF Core is probably the best ORM that has ever existed. What exactly is missing?

Wish I could agree but they would have to fix the very slow time to first query when using big models (+500 tables in our case). Compiled models is not a solution for us since our model changes a lot and the compilation is just as slow. It's disappointing because it used to work fine under the ancient Linq2SQL library.

Can't you generate the compiled model as part of the CI/CD?

Also maybe you might find a benefit from splitting your context into multiples. I am considering this option for one of my code bases

This is what I plan to do and I might use something like Husky to ensure models are built before code is committed. It IS sort of a PITA and I wonder if they could introduce a system on top of content hashes or the like to verify optimized models match the source in dev and etc.

I can but the DX would still be miserable, starting a local instance would still incur the cost of building the model, and a dev need to do that a lot of times during a day.

I can't split the model without massive refactorings, and even then, some tables are common across all modules and would need to be duplicated. Your advice is unfortunately the standard answer in my case, I guess EF Core is just not for me, really disappointing but "c'est la vie".

edit: ha you probably mean commit it in source control so other devs can also use it? I guess it's a compromise, would still slow down our DbContext refresh command a lot.

Are your models code first or reverse engineered?

Are you relying on model conventions or spelling out everything in modelBuilder calls?

I really like EF Core but I feel it's just recently started to hit parity in a lot of areas with EF 6(though it has moved well past in other areas), and it's missing odd some stuff that's table stakes these days. Stuff I'd like to see:

* Support for "FOR UPDATE" and "SKIP LOCKED"; it's easy enough to tag the queries and modify the SQL in an interceptor though.. Yuck.

* Transaction attributes ala Spring; Though I can make do without them it's nice being able to specify what sorta transaction party a method is interested in.

* Better support for "bulk updates". SQL Alchemy, as rough an onboarding experience as it is, has really good support for executing db-side update logic.

That said, I largely agree with you and nobody should overlook LinqPad. Anyone interested in babysitting the generated SQL should be using it; too bad it's not on Linux :| :( ;(

The optimizations they cover in the docs should all be done by default IMHO; optimizing models, polling db contexts, and etc. I also open and close a db connection at app start which further reduced the first request latency after putting a process in rotation.

Can someone familiar with both EF Core and the Java ecosystem’s Hibernate/etc describe how are they different?

I'd kill for anything close to EF for Node. Selecting complex structures from a database in any of the existing JS ORMs is just painful.

I've enjoyed the ORM in adonisjs: https://docs.adonisjs.com/guides/models/relationships#preloa...

Having used it on an actual product and dealing with some of the pain points, it's my go-to since the typescript version (v5) came out.

The ORM uses Knex.js internally, which is very simple to drop into if you just want a query builder. Having Knex be accessible also makes it simple to just write your query in plain sql as well, or as the Lucid ORM has available, just fragments of your query (say the join statement) as raw sql: https://docs.adonisjs.com/reference/database/query-builder#w...

Along with debugging, printing out the sql, and support via the Adonisjs REPL "Ace", it makes for a very nice experience.

These are more akin to Dapper which is a thin layer on top of SQL. Currently I use Prisma for JS, it's a bit better, but if you haven't used EF then you probably don't know what you're missing.

I've used EF quite a bit in the past. While there may be some features missing, Lucid an ActiveRecord implementation, which I would figure would fall into the "ORM" category.

Which features would you say are the key ones that make Lucid seem more like a query mapper (Dapper, Knex) than an ORM (ActiveRecord, EF)?

Specifically, in my Adonis projects, I'm mostly working with the Model objects through the ORM methods, and only dropping to Knex/SQL when necessary (complex CTE, etc). Since it's such a Model-centric seeming way of development, it naturally seems like an Object-Relational Mapping to me.

Context tracking - selecting multiple entities, updating and pushing them back to the db.

Selecting complex dtos, this isn’t query building. A lot of magic turns this into sql.

    TopPaidMayors = Cities
        .where(c => c.state.govoner.party ==‘dem’)
        .select(c => 
            c.name, 
            highestPaid = c.mayors
                .orderByDesc(m => salary)
                .take(10))
        .orderBy(c => highestPaid.First().salary)

Well you put a fairly normal select query there, which is can be accomplished in Lucid as well. Additionally change tracking exists since it's an ActiveRecord implementation.

It's not all magic as well. Looking into the internals of EF, ActiveRecord, Hibernate, or other ORMs reveal patterns that once familiarized can help reason about the behavior of complex queries. I only state this to try to work against the commonly found wisdom of "big frameworks are magic" that tends to scare away learning developers from hoping to understand them.

There are intersections and disjunctions of feature sets between the various ORMs, with some features for EF still only available via extensions (or nonexistent). I don't think this makes the Lucid ORM any less of an ORM.

Again, I like EF Core. I simply think that as far as node-based ORMs go, that Lucid is the one I've had the best experience with, so wanted to highlight it.

Query builders are a thin wrapper on raw sql. Real ORMs that understand the relations between objects are not.

There are a ton of joins and sub-selects in that query. 8 very succinct lines. Can you do anything close in Lucid? I don't think so.

Well I've put sources and links to multiple pages about the ActiveRecord implementation. The Lucid documentation for the ORM "query building" (which it's the same in EF, LINQ has "Integrated Query" in the name) does track entities and subentities, which is how you can query things update them, then later call `.save()` to persist them back to the database.

However, you seem set on making this a combative conversation rather than a collaborative discussion, so I'll end my participation here.

I had hoped you'd put some actual code in either of your last two replies.

Mikro ORM is the best I had come across while avoiding Prisma.

Prisma is a bit higher level, Mikro is more a query builder. EF though is way beyond both.

What would you like build-in db stuff to be like?

Well I mean DataTable and friends can handle master-detail for example, but you gotta do a lot of plumbing to set it all up, especially with autoincs involved. Was kinda expecting it to be less work.

Ideally I'd like to supply some selects, fill up some DataTables with master-detail data, manipulate it and commit changes.

But yeah, maybe I gotta check out the latest EF stuff and see if I can't convince the others...

DataTables are a construct from .NET Framework 1.1. You owe yourself (and would be doing your employer a massive favour) to check out EF Core.

Well that'd explain why they feel clunky :D

Will def look at it more carefully.

EF Core: 1. load order and related line items 2. edit anything 3. call SaveChanges()

Definitely check it out if you haven't recently!

Just do yourself a favor and don’t use datatables directly. If you want to be as close to the database as possible, check out Dapper. It EF core should cover nearly all Dapper functionality and much more.

I am just old fashioned, as much stored procedures as possible, why waste network traffic.

Reply on news.ycombinator.com