6 ms·

I've been writing C# professionally for ~5 years at this point. While I was initially quite infatuated with LINQ2SQL and EF, I have gone through the same situa

I've been writing C# professionally for ~5 years at this point. While I was initially quite infatuated with LINQ2SQL and EF, I have gone through the same situation as this fellow. I just write SQL in-line using Dapper for parameterization/data mapping, and use stored procs when I need some of the more arcane features of SQL (merges, CTE, etc).

mattmanser 9y ago
I've been writing C# professionally for ~12 years at this point. I'm extremely comfortable with SQL, the first startup I worked for for 3 or 4 years in the mid-2000s did amazing things with it and was extremely anti-ORM. We did things like write SQL that would automatically get translated into XML, which we'd combine with xslt to create dynamic pages. Yes, I've hit major problems with the EF (including one on Friday which was causing huge CPU and Mem spikes at the busiest time of our year). Yes, it's a PITA to debug the queries. Yes, it can do stupid things. Yes, some programmers can massively over-complicate it. But you can prise the EF from my cold, dead hands before I give it up. ORMs rock. It's such a huge time saver as long as you KISS and accept you have to drop down to SQL sometimes. And screw switching to Core until they've sorted out lazy loading. Lazy Loading can also screw you, but again, it's just wonderful when you use it right. I've also turned into a huge fan of Code-First and Migrations, having been against them at first.
- ZenoArrow 9y ago
  > "And screw switching to Core until they've sorted out lazy loading. Lazy Loading can also screw you, but again, it's just wonderful when you use it right." Are you referring to EF Core? I was considering learning it. What's this lazy loading issue? It's not one I've heard of before.
  - mattmanser 9y ago
    As far I understand it, it's half finished and progress has been super-slow: 2016 - https://weblogs.asp.net/ricardoperes/missing-features-in-entity-framework-core https://weblogs.asp.net/ricardoperes/missing-features-in-ent... 2018 - https://github.com/aspnet/EntityFrameworkCore/wiki/Roadmap https://github.com/aspnet/EntityFrameworkCore/wiki/Roadmap Basic functionality like group by, lazy loading, etc. is missing. By the look of it you can't even load custom types from hand-crafted queries, which is pretty ridiculous. Haven't really been keeping that up-to-date with it. I personally feel the whole Core thing has been a massive cluster-fuck for their existing customers.
- pjmlp 9y ago
  We aren't even considering Core it reaches feature parity with EF 6. Not only lazy loading, ODP.NET and designer support are also a must have.
- Mouse47 9y ago
  >Lazy Loading can also screw you, but again, it's just wonderful when you use it right. That's funny - the first thing I do when starting a new project is turn off lazy loading globally. I find it hides poorly-performing code until it's causing problems; the equivalent code without lazy loading usually just throws an exception. Granted I've only been in the industry for 3 years, so /shrug Also, question: you mention code-first and migrations... do you think you'd rather use SQL for your schema definition/migrations if the tooling was better? I find SSDT doesn't quite cut it :(
  - mattmanser 9y ago
    We have far more problems with too many .Includes causing terribly performing queries with bad JOINs than lazy loading problems. Granted this code base is in a bit of a state and we have a complex order structure that can go like 10 layers deep, and ideally we're looking to go even deeper with complex pricing. You do an include with all of that and you're going to get a terrible query. It's generally very cheap to do a single item query with no joins (as in a nanoseconds db query, yes, nano), and it only has to do it once, if you re-use that object again anywhere else, it's already in the context so it doesn't have to go to the db. Even doing them hundreds of times can be super cheap[1]. Add to that it only has to load each item once you can make intelligent decisions about what to .Include and what to lazy load. [1] Caveat, Azure db connection latency often sucks so this isn't completely true, we had 5-6ms instead of the <1ms you'd expect. Presently at about 2-3ms. This is the fault of Azure and not lazy loading though. Causes a problem when you make hundreds. 100 lazy loading calls each taking 6ms would add 600ms, or 1/2 a second, to a request.
    - Mouse47 9y ago
      >We have far more problems with too many .Includes causing terribly performing queries with bad JOINs than lazy loading problems. Granted this code base is in a bit of a state and we have a complex order structure that can go like 10 layers deep, and ideally we're looking to go even deeper with complex pricing. You do an include with all of that and you're going to get a terrible query. Wait - are you "Include"ing things you don't need? If not...assuming a sane query plan, shouldn't the single query (e.g. "Include" version) outperform the deconstructed series-of-queries that brings back the same data? E.g., Included: context.Orders.Where(x=>x.OrderId = 5).Include(x=>x.OrderItems) which translates roughly to select * from Orders o join OrderItems i on o.OrderId = i.OrderId where o.OrderId = 5 vs. var order = context.Orders.Where(x=>x.OrderId = 5); var items = order.OrderItems; which translates roughly to select * from Orders o where o.OrderId = 5; select * from OrderItems where OrderId = 5; As far as I understand the former will outperform the latter, even if you don't take into account the additional connection overhead. If the second version was faster...wouldn't SQL just compile down to a series-of-queries automatically?

mattmanser 9y ago

I've been writing C# professionally for ~12 years at this point. I'm extremely comfortable with SQL, the first startup I worked for for 3 or 4 years in the mid-2000s did amazing things with it and was extremely anti-ORM. We did things like write SQL that would automatically get translated into XML, which we'd combine with xslt to create dynamic pages. Yes, I've hit major problems with the EF (including one on Friday which was causing huge CPU and Mem spikes at the busiest time of our year). Yes, it's a PITA to debug the queries. Yes, it can do stupid things. Yes, some programmers can massively over-complicate it. But you can prise the EF from my cold, dead hands before I give it up. ORMs rock. It's such a huge time saver as long as you KISS and accept you have to drop down to SQL sometimes. And screw switching to Core until they've sorted out lazy loading. Lazy Loading can also screw you, but again, it's just wonderful when you use it right. I've also turned into a huge fan of Code-First and Migrations, having been against them at first.

ZenoArrow 9y ago
> "And screw switching to Core until they've sorted out lazy loading. Lazy Loading can also screw you, but again, it's just wonderful when you use it right." Are you referring to EF Core? I was considering learning it. What's this lazy loading issue? It's not one I've heard of before.
- mattmanser 9y ago
  As far I understand it, it's half finished and progress has been super-slow: 2016 - https://weblogs.asp.net/ricardoperes/missing-features-in-entity-framework-core https://weblogs.asp.net/ricardoperes/missing-features-in-ent... 2018 - https://github.com/aspnet/EntityFrameworkCore/wiki/Roadmap https://github.com/aspnet/EntityFrameworkCore/wiki/Roadmap Basic functionality like group by, lazy loading, etc. is missing. By the look of it you can't even load custom types from hand-crafted queries, which is pretty ridiculous. Haven't really been keeping that up-to-date with it. I personally feel the whole Core thing has been a massive cluster-fuck for their existing customers.
pjmlp 9y ago
We aren't even considering Core it reaches feature parity with EF 6. Not only lazy loading, ODP.NET and designer support are also a must have.
Mouse47 9y ago
>Lazy Loading can also screw you, but again, it's just wonderful when you use it right. That's funny - the first thing I do when starting a new project is turn off lazy loading globally. I find it hides poorly-performing code until it's causing problems; the equivalent code without lazy loading usually just throws an exception. Granted I've only been in the industry for 3 years, so /shrug Also, question: you mention code-first and migrations... do you think you'd rather use SQL for your schema definition/migrations if the tooling was better? I find SSDT doesn't quite cut it :(
- mattmanser 9y ago
  We have far more problems with too many .Includes causing terribly performing queries with bad JOINs than lazy loading problems. Granted this code base is in a bit of a state and we have a complex order structure that can go like 10 layers deep, and ideally we're looking to go even deeper with complex pricing. You do an include with all of that and you're going to get a terrible query. It's generally very cheap to do a single item query with no joins (as in a nanoseconds db query, yes, nano), and it only has to do it once, if you re-use that object again anywhere else, it's already in the context so it doesn't have to go to the db. Even doing them hundreds of times can be super cheap[1]. Add to that it only has to load each item once you can make intelligent decisions about what to .Include and what to lazy load. [1] Caveat, Azure db connection latency often sucks so this isn't completely true, we had 5-6ms instead of the <1ms you'd expect. Presently at about 2-3ms. This is the fault of Azure and not lazy loading though. Causes a problem when you make hundreds. 100 lazy loading calls each taking 6ms would add 600ms, or 1/2 a second, to a request.
  - Mouse47 9y ago
    >We have far more problems with too many .Includes causing terribly performing queries with bad JOINs than lazy loading problems. Granted this code base is in a bit of a state and we have a complex order structure that can go like 10 layers deep, and ideally we're looking to go even deeper with complex pricing. You do an include with all of that and you're going to get a terrible query. Wait - are you "Include"ing things you don't need? If not...assuming a sane query plan, shouldn't the single query (e.g. "Include" version) outperform the deconstructed series-of-queries that brings back the same data? E.g., Included: context.Orders.Where(x=>x.OrderId = 5).Include(x=>x.OrderItems) which translates roughly to select * from Orders o join OrderItems i on o.OrderId = i.OrderId where o.OrderId = 5 vs. var order = context.Orders.Where(x=>x.OrderId = 5); var items = order.OrderItems; which translates roughly to select * from Orders o where o.OrderId = 5; select * from OrderItems where OrderId = 5; As far as I understand the former will outperform the latter, even if you don't take into account the additional connection overhead. If the second version was faster...wouldn't SQL just compile down to a series-of-queries automatically?