Thoughts on ORMs, 2016 Edition

This is a bit of a follow up to my 3 year old post about LINQ 2 SQL and my even older 2011 Thoughts on ORM post. I’ve changed my position several times over the last three years as I learned about issues with different approaches. TL;DR is that for reads, I’d prefer handwritten SQL and Dapper for .Net as a mapper to avoid having to deal with DataReaders. For Inserts and Updates, I’m a bit torn.

I still think that L2S is a vastly better ORM than Entity Framework if you’re OK with solely targeting MS SQL Server, but once you go into even slightly more complex scenarios, you’ll run into issues like the dreaded SELECT N+1 far too easily. This is especially true if you pass around entities through layers of code, because now virtually any part of your code (incl. your View Models or serialization infrastructure) might make a ton of additional SQL calls.

The main problem here isn’t so much detecting the issue (Tools like MiniProfiler or L2S Prof make that easy) – it’s that fixing the issue can result in a massive code refactor. You’d have to break up your EntitySets and potentially create new business objects, which then require a bunch of further refactorings.

My strategy has been this for the past two years:

  1. All Database Code lives in a Repository Class
  2. The Repository exposes Business-objects
  3. After the Repository returns a value, no further DB Queries happen without the repository being called again

All Database Code lives in a Repository Class

I have one or more classes that end in Repository and it’s these classes that implement the database logic. If I need to call the database from my MVC Controller, I need to call a repository. That way, any database queries live in one place, and I can optimize the heck out of calls as long as the inputs and outputs stay the same. I can also add a caching layer right there.

The Repository exposes Business-objects

If I have a specialized business type (say AccountWithBalances), then that’s what the repository exposes. I can write a complex SQL Query that joins a bunch of tables (to get the account balances) and optimize it as much as I want. There are scenarios where I might have multiple repositories (e.g., AccountRepository and TransactionsRepository), in which case I need to make a judgement call: Should I add a “Cross-Entity” method in one of the repositories, or should I go one level higher into a service-layer (which could be the MVC Controller) to orchestrate?

After the Repository returns a value, no further DB Queries happen without the repository being called again

But regardless how I decide on where the AccountWithBalances Getter-method should live, the one thing that I’m not going to do is exposing a hot database object. Sure, it sounds convenient to get an Account and then just do var balance = acc.Transactions.Sum(t => t.Amount); but that will lead to 1am debugging sessions because your application broke down once more than two people hit it at the same time.

At the end, long term maintainability suffers greatly otherwise. It seems that it’s more productive (and it is for simple apps), but once you get a grip on T-SQL you’re writing your SQL Queries anyway, and now you don’t have to worry about inefficient SQL because you can look at the Query Execution Plan and tweak. You’re also not blocked from using optimized features like MERGE, hierarchyid or Windowing Functions. It’s like going from MS SQL Server Lite to the real thing.

So raw Nah. The problem with that is that after you wrote an awesome query, you now have to deal with SqlDataReaders which is not pleasant. Now, if you were using LINQ 2 SQL, it would map to business objects for you, but it’s slow. And I mean prohibitively so. I’ve had an app that queried the database in 200ms, but then took 18 seconds to map that to .net objects. That’s why I started using Dapper (Disclaimer: I work for Stack Overflow, but I used Dapper before I did). It doesn’t generate SQL, but it handles parameters for me and it does the mapping, pretty fast actually.

If you know T-SQL, this is the way I’d recommend going because the long-term maintainability is worth it. And if you don’t know T-SQL, I recommend taking a week or so to learn the basics, because long-term it’s in your best interest to know SQL.

But what about Insert, Update and Delete?

Another thing that requires you to use raw SQL is Deletes. E.g., “delete all accounts who haven’t visited the site in 30 days” can be expressed in SQL, but with an ORM you can fall into the trap of first fetching all those rows and then deleting them one by one, which is just nasty.

But where it gets really complicated is when it comes to foreign key relationships. This is actually a discussion we had internally at Stack Overflow, with good points on either side. Let’s say you have a Bank Account, and you add a new Transaction that also has a new Payee (the person charging you money, e.g., your Mortgage company). The schema would be designed to have a Payees table (Id, Name) and a Transactions table (Id, Date, PayeeId, Amount) with a Foreign Key between Transactions.PayeeId and Payees.Id. In our example, we would have to insert the new Payee first, get their Id and then create a Transaction with that Id. In SQL, I would probably write a Sproc for this so that I can use a Merge statement:

CREATE PROCEDURE dbo.InsertTransaction
	@PayeeName nvarchar(100),
	@Amount decimal(19,4)

    DECLARE @payeeIdTable TABLE (Id INTEGER)
    DECLARE @payeeId INT = NULL

    MERGE dbo.Payees AS TGT
        USING (VALUES (@PayeeName)) AS SRC (Name)
        ON (TGT.Name = SRC.Name)
        INSERT(Name) VALUES (SRC.Name)
        UPDATE SET @payeeId = TGT.Id
    OUTPUT Inserted.Id INTO @payeeIdTable

    IF @payeeId IS NULL
        SET @payeeId = (SELECT TOP 1 Id FROM @payeeIdTable)

    INSERT INTO dbo.Transactions (Date, PayeeId, Amount)
    VALUES (GETDATE(), @payeeId, @Amount)

The problem is that once you have several Foreign Key relationships, possibly even nested, this can quickly become really complex and hard to get right. In this case, there might be a good reason to use an ORM because built-in object tracking makes this a lot simpler. But this is the perspective of someone who never had performance problems doing INSERT or UPDATE, but plenty of problems doing SELECTs. If your application is INSERT-heavy, hand rolled SQL is the way to go – if only just because all these dynamic SQL queries generated by an ORM don’t play well with SQL Server’s Query Plan Cache (which is another reason to consider stored procedures (sprocs) for Write-heavy applications)

Concluding this, there are good reasons to use ORMs for developer productivity (especially if you don’t know T-SQL well) but for me, I’ll never touch an ORM for SELECTs again if I can avoid it.

When nslookup works but you can’t ping it, NetBIOS may be missing

I have a custom DNS Server (running Linux) and I also have a server running Linux (“MyServer”). The DNS Server has an entry in /etc/hosts for MyServer.

On my Windows machines, I can nslookup MyServer and get the IP back, but when I try to access the machine through ping or any of the services it offers, the name doesn’t resolve. Access via the IP Address works fine though.

What’s interesting is that if I add a dot at the end (ping MyServer.) then it suddenly works. What’s happening?!

What’s happening is that Windows doesn’t use DNS but NetBIOS for simple name resolution. nslookup talks to the DNS Server, but anything else doesn’t use DNS.

The trick was to install Samba on MyServer, because it includes a NetBIOS Server (nmbd). On Ubuntu 16.04, just running sudo apt-get install Samba installs and auto-starts the service, and from that moment on my Windows machines could access it without issue.

There are ways to not use NetBIOS, but I didn’t want to make changes on every Windows client (since I’m using a Domain), so this was the simplest solution I could find. I still needed entries in my DNS Server so that Mac OS X can resolve it.

Why Feature Toggles enable (more) frequent deployments

As Nick Craver explained in his blog posting about our deployment process, we deploy Stack Overflow to production 5-10 times a day. Apart from the surrounding tooling (automated builds, one-click deploys, etc.) one of the reasons that is possible is because the master-branch rarely ever stays stale – we don’t feature branch a lot. That makes for few merge-nightmares or scenarios where suddenly a huge feature gets dropped into the codebase all at once.

The thing that made the whole “commit early, commit often” principle click for me was how easy it is to add new feature toggles to Stack Overlow. Feature Toggles (or Feature Flags), as described by Martin Fowler make the application [use] these toggles in order to decide whether or not to show the new feature.

The Stack Overflow code base contains a Site Settings class with (as of right now) 1302 individual settings. Some of these are slight behavior changes for different sites (all 150+ Q&A sites run off the same code base), but a lot of them are feature toggles. When the new IME Editor was built, I added another feature toggle to make it only active on a few sites. That way, any huge issue would’ve been localized to a few sites rather than breaking all Stack Exchange sites.

Feature toggles allow for a half-finished feature to live in master and to be deployed to production – in fact, I can intentionally do that if I want to test it with a limited group of users or have our community team try it before the feature gets released network-wide. (This is how the “cancel misclicked flags” feature was rolled out). But most importantly, it allows for changes to constantly go live. If there is any unintended side-effects, we notice it faster and have an easier time locating it as the relative changeset is small. Compare that to some massive merge that might introduce a whole bunch of issues all at once.

For feature toggles to work, it must be easy to add new ones. When you start out with a new project and want to add your first feature toggle, it may be tempting to just add that one new toggle, but if the code base grows bigger, having an easy mechanism really pays off. Let me show you how I add a new feature toggle to Stack Overflow:

public partial class SiteSettings
    // ... other properties ...

    [Description("Enable to allow users to retract their flags on Questions, Answers and Teams.")]
    public bool AllowRetractingFlags { get; private set; }

When I recompile and run the application, I can go to a developer page and view/edit the setting:


Anywhere in my code, I can gate code behind an if (SiteSettings.AllowRetractingFlags) check, and I can even use that in JavaScript if I decorate it with the [AvailableInJavascript] attribute (NĂ©stor Soriano added that feature recently, and I don’t want to miss it anymore).

Note what I did not have to do: I did not need to create any sort of admin UI, I did not need to write some CRUD logic to persist the setting in the database, I did not need to update some Javascript somewhere. All I had to do was to add a new property with some attributes to a class and recompile. What’s even better is that I can use other datatypes than bool – our code supports at least strings and ints as well, and it is possible to add custom logic to serialize/deserialize complex objects into a string. For example, my site setting can be a semi-colon separated list of ints that is entered as 1;4;63;543 on the site, but comes back as an int-array of [1,4,63,543] in both C# and JavaScript.

I wasn’t around when that feature was built and don’t know how much effort it took, but it was totally worth building it. If I don’t want a feature to be available, I just put it behind a setting without having to dedicate a bunch of time to wire up the management of the new setting.

Feature Toggles. Use them liberally, by making it easy to add new ones.

Dell XPS 15 9550 (2016), 6 weeks in

One of the perks of working for Stack Overflow is that you get to choose your own work computer. I decided to go with a 2016 Dell XPS 15 (9550) (not to be confused with the earlier XPS 15, which had the model number 9530) and settled on this configuration:

Dell XPS 15 9550

One of the big selling features of the XPS 15 is a gorgeous 4K (3840×2160) display – why didn’t I get that one? Simple: At 15″, I’d have to use Windows display scaling to make stuff not too tiny. Display scaling is a lot better in Windows 10 than it was in 7 or 8, but it’s still not great. I have used display scaling on Mac OS X, and on Windows it’s still just a giant crutch. So I decided on the normal 1080p display, and I like it a lot. It’s bright, it’s IPS and thus doesn’t suffer from colors being all weird when viewed from the side, and it doesn’t kill the battery nearly as much as the 4K screen.

At home, I’m also running two 2560×1440 displays (One Dell U2715H connected via a USB-C-to-DisplayPort adapter, and one Dell U2515H connected via HDMI).

The external display situation is a bit weird at the moment (Late July 2016). The XPS 15 has a Thunderbolt port, so supporting 2x 4K monitors at 60Hz each should be possible using the TB15 thunderbolt dock. The problem is that the Thunderbolt dock doesn’t work properly and is currently not sold (Dell might have a fix sometime in August). There have been 3 or 4 Thunderbolt BIOS updates over the last few weeks, but as it stands right now, unless you have a Thunderbolt display the port doesn’t do much.

It can be used as a normal USB-C port and drive a single 4K screen at 60 Hz with the DisplayPort adapter cable, and that works fine. Dell does have a USB-C dock (Dell WD15) which has HDMI and Display Port, but unlike the built-in HDMI port, it cannot drive a 2560×1440 screen over HDMI.

That basically means that unless you’re only using 1080p screens, there isn’t a good dock out there and you’re better off connecting external screens directly to the laptop. I have tried daisy chaining the two 2560×1440 displays (2nd display into 1st using display port cable, 1st into USB-C port using DP-USBC cable) and that worked fine

The keyboard is surprisingly good. It’s still a laptop keyboard, but it’s normal sized keys with enough travel to not feel strange. Because it’s a 15″ laptop and the keyboard is towards the screen though, my arm is resting on the bottom edge of the laptop which isn’t the most comfortable position. It’s one of the sacrifices to be made. The covering is some rubbery material that feels good, but finger-stains are readily visible.

The touchpad is pretty good, as close to a Macbook touchpad as I’ve encountered so far, although it doesn’t have the glass cover that makes the Macbook feel frictionless. It was definitely one of the reasons I wanted a Dell XPS laptop, because the touchpad is one of the main reasons to buy an Apple laptop, and I feel that there’s no need to regret not getting one.

The battery runtime is pretty good. There are two Battery choices: a 56 WHr and an 84 WHr. The battery situation is a bit interesting: Basically the lower-end models come with a 2.5″ S-ATA hard drive and a 32 GB M.2 SSD:


The higher-end models come with only a M.2 SSD and either a 56 or 84 WHr battery. In case of the 84 WHr battery, it takes up the space that the 2.5″ hard drive would take, and so it’s not possible to add an additional 2.5″ hard drive to an XPS 15 with a 84 WHr battery (in case you were thinking of adding a second hard drive for data).

In theory, it is possible to add a 2.5″ drive to a model with the 56 WHr battery, but no mounting hardware is included and it seems Dell doesn’t sell it individually. So if you’re really thinking of putting in two hard drives (say, a dream configuration of a 1 TB Samsung SM961 and a 4 TB Samsung 850 EVO), you’d have to buy the XPS 15 in a configuration that includes a hard drive and swap them out.

Battery runtime with the 84 WHr battery is pretty good – Dell makes some lofty claims of 17 hour runtime that of course aren’t reached in real world use, but I get at least 6 hours out if my normal use (WiFi enabled, nothing connected to USB, display to about 60% brightness, Visual Studio, SQL Server, IIS, no video streaming). The i7 uses a bit more power than the Core i5-6300HQ that is also offered, but not much more since they are both Quad-Cores, the i7 basically just adding Hyper-Threading. (There is also a model with a Core i3-6100H CPU but honestly, I’d get at least the i5). The 4K Infinity Display apparently really drains the battery from what others have said

Overall, after using the laptop for about 5 weeks both as a stationary computer (external displays, keyboard and mouse) and as a portable, I’m highly satisfied with it. Thunderbolt woes aside, it’s insides are up-to-date with a Skylake-CPU, a PCI Express NVMe SSD, a really good IPS display, pretty much the best Wintel touchpad out there and USB-C. Literally the only other Windows laptop I would look at is the XPS 13 in for something a bit smaller. With laptops like these, comparing it to the Macbook Pro is always a hot topic, despite the (as of July 31) MBP’s really outdated hardware. For me, it boiled down to the question if I needed to run Mac OS X, and since I have two Macs already (Late 2010 Mac Pro, 2015 Retina Macbook) the answer was “no”, and thus the Dell XPS 15 won out. So far, I do not regret that decision.


(Note that BitLocker is enabled, which may skew results downward a bit)
(Note that BitLocker is enabled, which may skew results downward a bit)