Chapter 15 – LINQ to SQL and ADO.NET Entity Framework

When Jeremy first asked me if I’d be interested in reviewing the LinqToSql/EF chapter of the Deep Dives book, I immediately accepted.  Basically, for the past two and a half years, I’ve worked extensively with LinqToSql, and learned a handful of pros (and cons) about the technology from both a developer standpoint, and also from an interim database administrator standpoint.  It was almost like getting to play my own devil’s advocate, and that can often yield the greatest decisions and results as a developer.  I’ve spent much of the past year discussing (often passionately) these technologies with DBAs, and trying to convince them that LinqToSql – when used thoughtfully – can serve as a dependable, easy-to-maintain, and even extremely performant tool for data reads and writes.  Allow me to reiterate – when used thoughtfully.

Chapter 15: LINQ to SQL and ADO.NET Entity Framework does a great job of outlining five or six very valid concerns from a DBA standpoint, and offers recommendations for alleviating these concerns.  In reading the chapter, I would certainly spend a little bit of extra focus on the following sections:

–          Whole objects/anonymous types (pp.213)

–          Updating and deleting via objects (pp.214)

–          Deferred loading and unnecessary roundtrips (I/O) (pp.216)

These concerns (in my expertise) are most often misused and abused by developers.  The different LinqToSql implementations  that I’ve encountered across development teams runs the gamut of highly documented to highly off-road and risky.  This is where LinqToSql and EF’s greatest strength becomes it’s complementing greatest weakness.  It is amazingly flexible and offers the developer great power.  But as Spiderman has taught us, “with great power comes great responsibility”.  And in crunch-time, with deadlines and midnight coding sessions comes the absence of thoughtful development and attention to responsibility.

So, how do we address this?  To start, I’m not going to try and solve the problem of bad decisions stemming from deadlines – to do so would be like attempting to solve world hunger.  Deadlines and deliverable dates will always be a pivotal part of development, and we will never eliminate midnight coding sessions, and the quality of output that results.  What we can do is enforce some best practices with LinqToSql and EF that decrease (and in many cases, eliminate) the developer’s ability to run amazingly slow queries, pollute query plan cache and overrun the system with unnecessary roundtrips.

My recommendation, in accordance with the chapter, is data access using stored procedures (in-lieu of whole objects and dynamic queries), within LinqToSql and EF.  Yes, believe me, it’s much easier to pull a fully loaded object from the database and query it from every direction under the sun.  However, there is very little need for this type of flexibility and overwhelming excess of data.  Think of your typical table.  How many columns does it contain?  Eight, ten, twelve? 

And how many of those columns will ever be used by your application?  I can’t think of the last time my application needed data from the DateModified column.  In essence, we know what our application’s scope is, we know what our consumer requires at a field level, so let’s allow the database to do the legwork for us and keep our initial dataset lean and mean.  LinqToSql and EF both allow us to push the results into a strongly typed collection which we can use LINQ to filter, sort, and read – so let’s keep the processing power at the database, and depend upon stored procedures to formulate our initial rowsets.

By using stored procedures in accordance with the LINQ language, you alleviate nearly every concern that’s mentioned in this chapter.  Updating and deleting becomes entirely database driven – which is both safe, easy to maintain, and performance savvy.  Projection issues (and the SELECT * FROM notion) becomes a non-issue, because you are passing exactly the necessary parameters, and telling the framework exactly which anonymous type or custom class to return the data within.  All of your covered indexes are respected, and your queries run exactly as your query plan (and your DBA) expect.

Finally, and most importantly, by using stored procedures, you allow for easy tuning of your queries without requiring a recompile of your code.  As a developer myself, I know that my queries are all perfect right off-the-bat.  Wink, wink.  But what if (God forbid) I wanted a DBA to perform a code review of my T-SQL?  If it’s a stored procedure being called from LinqToSql/EF, the DBA can pop out to the database and review my proc without me having to walk them through code on my machine, in Visual Studio.  That should be reason enough to convince you to root your data access in stored procedures.

NOTE: This review was written prior to the release of the .NET Framework 4.0.  Although several of the potential issues outlined in this chapter will be addressed in the .NET Framework 4.0, my recommendation for using stored procedures is still very valid.

Jason Marsell (Blog) is a software engineer from Denver, CO with over eleven years of architecture and development experience.  He is a Microsoft Certified Solution Developer, and the co-founder of Blue Orbit Technology Consulting.  He specializes in .NET programming, database design, web application development, web services (WCF/ASMX) and client-server applications.


0 Responses to “Chapter 15 – LINQ to SQL and ADO.NET Entity Framework”

  1. Leave a Comment

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )


Connecting to %s


Enter your email address to subscribe to this blog and receive notifications of new posts by email.

Join 11 other followers