5 ms·

>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

• magicalhippo 4y ago
  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.
  • NicoJuicy 4y ago
    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.
    • andix 4y ago
      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).
      • other_herbert 4y ago
        Not my project/not affiliate but Evolve is wonderful for handling db migrations
        • NicoJuicy 4y ago
          Haven't heard of Evolve before, but used DbUp in the past with success too: https://dbup.readthedocs.io/en/latest/ https://dbup.readthedocs.io/en/latest/
  • mattmanser 4y ago
    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?
    • emodendroket 4y ago
      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.
  • bhubert 4y ago
    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 https://github.com/linq2db/linq2db
  • fabian2k 4y ago
    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.
  • andix 4y ago
    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.
    • magicalhippo 4y ago
      Well that sounds a lot better than what my coworkers told me. Will definitely check out EF Core.
      • WorldMaker 4y ago
        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.
• NicoJuicy 4y ago
  For performance, .NoTracking() and not calling .SaveChanges() on every loop already does wonders. I usually call .SaveChanges() when i % 20 == 0
  • magicalhippo 4y ago
    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?
    • fabian2k 4y ago
      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.
    • NicoJuicy 4y ago
      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.
• Guillaume86 4y ago
  > 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.
  • skeletonjelly 4y ago
    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
    • Rapzid 4y ago
      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.
    • Guillaume86 4y ago
      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.
  • WorldMaker 4y ago
    Are your models code first or reverse engineered? Are you relying on model conventions or spelling out everything in modelBuilder calls?
• Rapzid 4y ago
  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.
• kaba0 4y ago
  Can someone familiar with both EF Core and the Java ecosystem’s Hibernate/etc describe how are they different?