Entity Framework – how about giving a shit about databases?

By | April 10, 2017

Entity Framework (and other ORMs) are in general great pieces of software that makes developers lives so much easier by letting us thinking about objects when we’re working with data persisted in some underlying database. This makes development much easier than writing raw SQL queries. But some of us tend to get too comfy and forgetting about things that lies beneath our beautiful, object oriented code. After all we have Entity Framework, we shouldn’t wrap our heads around SQL stuff. But how about actually giving a single fuck what our code does in table world of SQLs?

Lets start with little clarification. Since Entity Framework is probably most popular ORM in .NET it became title character but offten it’s appliable to other ORMs as well. Also – I really like ORMs, just as long as we’re keeping in mind that on the other side there is living, breathing and feeling database which we could hurt by being careless.

What am I? DBA or something?!

I’m not DBA, I’m developer. Why should I even consider database stuff when I have tool to do that for me? This question have one simple key word – tool. As I’ve mentioned, and probably repeat few times along the way, Entity Framework and other ORMs are great tools … if used wisely. Think of it like fancy toolbox full of screwdrivers, wrenches and hammer or two. When you want to unscrew screw from a piece of wood – you probably want to use screwdriver  and not a bigass axe.

In Entity Framework we have some classes that represent our database tables. That’s why we can do that in our C#:

Which in fact does that:

While SELECT * is not something I would want to see executed againt database, but for now lets agree we really want to get every single record in our table. But for what exactly we’re doing that? Well, we’ve got ourselves a list of contents, nothing fancy just some Title and Id. But there is one problem, we’re going to need values from two columns and we’re getting EVERYTHING. Let’s say our [Stuff] table have prices, descriptions, descriptions in other languages and 20 other columns, it does not matter. And we’ve just fetched all this stuff from database.

What we should do? How about using LINQ To Entities Select? Because if we’re aware that by using IQueryable we’re operating on SQL, we’ll know that this:

Is this:

And we’re fetching few times less data from database, believe me – your SQL is into you now. Creating some simple ViewModels or other DTOs to select into them is really simple and allows us to fetch just things we’ll need and will actually use. And you won’t operate on EF entities afterwards which’ll prevent acces to things like Lazy Loading.

GimmeGimmeGimmeGimme

If you’re at least a bit familiar with Entity Framework you probably know what Lazy Loading is. If you don’t – just read this. Long story short – Lazy Loading will fetch related entities from database when you request it but forgot to explicitly load it first. If you iterate with foreach on non-loaded navigation property containing collection of objects – Entity Framework will fetch those items from database. One by one. That could mean 5 requests to database or 500 of them. How should it be done? You could read about it in great post about Include here.

I’ve once heard that first thing that should be done after installing Entity Framework package should be turning off Lazy Loading. I strongly aggree with that. Lazy Loading seems to be cool and dev-friendly feature, but it allows us to be careless and just don’t care about that.

But it works!

Why should we concern ourselves with all this when everything works like a charm? Just ask yourself simple question – how big is your dev db? Dow big is your test db? And most of all – how big is prod db and how big it’ll be in next few years. Because you just won’t see big impact on 100MB database, you may fetch half of your db with your ORM and don’t even know it. But with hundreds gigs of data this “half of db” will be considerable ammount which could really hurt. And tracking SQL performance issues at this moment will be pain in the ass, especially if someone won’t even look into  SQL “because Entity Framework”.

“With great power comes great responsibility”

-Uncle Ben

Entity Framework and other ORMs are great and powerful tools. It was not my intention to cover any of them now because doing so would take enormous ammount of time, and there are some great articles about that. I’ve just wanted to remind you about those living, feeling and breathing databases somewhere, just don’t hurt them. Maybe sometimes just give a damn what’s happening there, maybe grab Adventure Works database write up some really bad LINQ queries and see what’s happening in SQL Profiler? Maybe take a look at query plans? If you’ve never done that, or even worse – don’t even know what query plan or SQL profiler is – just look those things up and work with them for a while, just to see what SQL queries could be generated by combination of ORM and careless coding.

 

Edit: I haven’t mentioned useful tools to peek at SQL generated by Entity Framework, I’m fixing that. Thx reddit ;).

  • First and easiest to use is code snippet that can be attached in context constructor globally or anywhere else locally
  • SQL Server Profiler along with VS debugger and breakpoints
  • Entity Framework Profiler
  • LINQPad – for experiments