Don't get me wrong; I love my LINQ. But I have mixed feelings about LINQ2SQL, or anything that promises to make my life easier by allowing me to write fewer SQL statements.
I've been burned too many by “innocuous” LINQ2SQL queries that later ballooned into resource hogs after deployment. Often, it's a sleeper—some query that's been running just fine for years and then, BAM! You get jolted by a spiked CPU like a shovel to the face because an email campaign hit some remote part of the site that hadn't been pored over.
It's that friend you learn to limit. He may be great in certain situations, like running the basic CRUD (Create, Read, Update, Delete) routines on all those cumbersome admin screens, but once you take him into the real world, once you expose him to all your other friends on your high traffic ecommerce site, once you give him a broader audience, you run the risk that he'll show his true colors, and you may not like what they see.
This is my relationship with any ORM that promises to lift the burden of having to write straight SQL. It's fine in the right circumstances and saves loads of time writing basic operations. But once you cook up a slightly more complex query and roll it into a public website with tens of thousands of hits an hour, it's just not enough. Trusting the black box of ORM SQL generation is often a risky endeavor.
I'd rather be in direct control of what SQL gets executed when writing finely tuned database access. Thus, I've come to love what Dapper has to offer.
Dapper, by the folks over at stackoverflow.com, is an extremely lightweight data access layer optimized for pulling raw data into your own POCOs (Plain Old C# Objects). It's that perfect fit between the nauseatingly redundant world of SqlCommands and DataReaders, and the overzealous, overbearing friend you find in LINQ2SQL.
I no longer have to guess what kind of query an ORM will generate or worry that LINQ2SQL is going to fly off the handle and take up all my CPU trying to compile the same dastardly query over and over again.
For example, let's say I've got this domain object:
All I have to do to yank the data from the database is this:
The result is, of course:
SQL: I Squeal for SQL!
No longer must I suffer the fate of black box SQL generation when all I want is a clean, easy, and fast way to get my SQL or stored procedure results directly into my domain objects.
I can instead write the SQL myself and get it into my POCO of choice with less effort than it takes to bash my head on the keyboard.
I'm sold on Dapper for many of my high-performing pages. As we maintain our sites and find the occasional bloated LINQ2SQL resource hog, we're swapping out the queries to straight SQL, stored procedures, and Dapper. It has really sped things up.
Go ahead, give it a shot yourself. It's available on the NuGet Gallery, and only imports a single C# file; no extra assemblies required. They've got plenty of examples at the project site. I'm wondering how I ever lived without it.