The Four Deadly Sins of LINQ Data Access: Part 2–Too Many Columns

By February 28, 2014News


Last time I talked about the sin of Overly Chatty Queries and pointed out two other sins while showing an example of an Overly Chatty Query and how to fix it. One of those sins was the sin of Too Many Columns, and that’s what I’m going to discuss this time.


Here is the code snippet I showed last time which demonstrates how to eagerly load entities, ensuring you have all the data you need in a single query rather than requiring an additional query per loop iteration:

As I said back then, this query does repent of being Overly Chatty, but it is still selecting too many columns. The loop below only uses and needs Customer.ContactName, but the entire Customer table was included in the query results in order to fully populate Customer objects. For illustration, here is the generated SQL that results from executing these statements:

Including all those unused columns means more work for SQL Server, more data being transmitted over the network, and more memory usage in .NET, all of which add up to reduced performance and scalability.


The solution is quite simple: only request the columns you plan to use, like this:

This results in a much smaller SQL query:


“But”, some of you are probably thinking, “We use the Repository Pattern to separate our data access code and we don’t want to have a million different methods returning a million different combinations of columns, not to mention all of the extra classes you would have to define in order to be able to pass these custom projections back to the caller!” Fair enough. This is probably the kind of thing you were imagining:

I can see at least three objections to doing things this way:

  1. Proliferation of data classes, requiring more code to be written and maintained
  2. Proliferation of specialized methods in the repository
  3. Possible duplication of query logic within the repository, only with different outputs

But there is a better way, thanks to the magic of generic type parameters! In summary, you declare your repository methods with a generic type parameter and return a collection of that type. Then, you pass a function that accepts a IQueryable of the base type and returns an IQueryable of the generic type. Here, sometimes it’s clearer just to show the code. Here’s the new repository method:

And this is how you would call it:

This has the following benefits:

  1. No extra data classes
  2. No extra repository methods
  3. No duplication of repository query logic

Next time, I’ll circle back to the topic of Underly Chatty Calls. Yes, there is such a thing! Now, go forth and select Too Many Columns no more!


The following two tabs change content below.