Earlier quoted context omitted.
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…
>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 th…
- A venue associated with it
- A user who booked it
- A menu
-- With courses (starter, main, dessert)
--- of Menu items (steak)
---- with Menu item option groups (think, 'pick one of', 'choose at most 3', etc.)
----- of Menu item options ('rare', 'medium', or 'extra chips', 'onion rings')
- Guests
-- Guest.User
-- Guest selections of menu items
--- Guest selection options
- And Many more! (payments, events, offers, postal addresses, etc.)
As for the query, you can load them in chunks (which we do), but the way the EF works you're limited on how you can do that.
If you try loading that all in one go, you get a very, very slow query. It's beyond the limits of the execution planner to do it well.
Because of the nature of ORMs, the EF can also make decisions which result in horrible sub-selects, or terrible joins of sub-tables where the execution planner can't use the right indexes, especially when you're trying to do groups, counts, sums, etc.
This makes it often better to load things separately and to selectively use the lazy loader to do certain things.
Other scenarios include where say you have a complex object that you've only partially filled in, but in 1 in 5 cases you want to send an email using that object.
Now you could write your email function to load all the data again, or you could let lazy loading do its thing and, overall, save time and decrease db load, because you've already got 80% of the data, it just needs to fill in the missing 20% with some simple queries.
Answer to earlier question: I use to hand-code my db upgrades as my opinion is that having correctly structured data is king and I understood relational db design. But it turns out EF Migrations are wonderful when you know how to use them. I still check every single one to make sure they're doing exactly what I wanted and expected though, and take them up and down manually. Good way of catching mistakes.