Unit Testing a LINQ to SQL or EF Query

I was writing a slightly non-trivial method to query a database to find a record matching a certain time range. It quickly became clear that it would be nice to write some automated unit tests against it. Integration tests would be less than ideal because of the execution time and complexity. I ended up with a way to test the code without jumping through too many hoops.

IEnumerable vs IQueryable

First, you need to understand the purpose of IEnumerable and IQueryable. IEnumerable defines a stream of objects that can be retrieved sequentially. It's implemented for nearly every type of list, and it's an integral part of LINQ. There are now methods included such as "Where" and "Select" that let us filter, sort, and manipulate lists of data in interesting yet simple ways. This can also be referred to as LINQ to objects.

IQueryable inherits from IEnumerable, and is designed to be translatable into a query. IQueryable is typically used to build an expression tree that represents the requested operations. The operations are not actually executed until the expression tree is evaluated and used.

As an example, let's say I have a database with a table and entities called DateRange. Suppose I cast the DateRange entityset (which implements IQueryable) as IEnumerable. When I call LINQ expressions on that IEnumerable, the underlying query is run immediately, which effectively causes all of the data from that table to be retrieved. If I use IQueryable without casting, my operations get turned into SQL that gets executed when I actually try to iterate through the data (probably using ToList() or foreach). It's obviously preferable to have the query run in SQL since it can more efficiently filter the data.

The Problem

As I mentioned earlier, I recently starting writing a data access method that started to contain some non-trivial logic. Whenever I see logic, I want to be able to unit test it! I ended up pulling out the logic into its own static method. This method takes in IEnumerable, which can also accept IQueryable (because of the inheritance, you'll recall). Then, I simply use the AsQueryable method in IEnumerable. This ends up building the needed expression tree that can be translated into a SQL query, but it also lets me test against an in memory collection.

public static DateRange FindRelativeToDate(IEnumerable<DateRange> enumerable, DateTime reference, int periodOffset)
    //Build as an expression tree, if possible, otherwise enumerate
    var queryable = enumerable.AsQueryable();

    //Now put in all the logic

    var reference2 = reference.AddDays(-1);

    var initialRange = from bp in queryable
         where reference >= bp.Start && reference2 < bp.End
         select bp;

    var currentDateRange = initialRange.First();

    if(periodOffset == 0)
        return currentDateRange;

    var newRange = from bp in queryable
        select bp;

    if (periodOffset > 0)
        newRange = newRange.Where(x => x.Start >= currentDateRange.Start);
        newRange = newRange.OrderBy(x => x.Start);
        newRange = newRange.Where(x => x.End <= currentDateRange.End);
        newRange = newRange.OrderByDescending(x => x.Start);

    return newRange.Skip(Math.Abs(periodOffset)).Take(1).FirstOrDefault();

In my unit test class, I can define a list of sample data. I took real data from the database to make the tests as close to reality as possible:

private static readonly List<DateRange> _DateRanges = new List<DateRange>
    new DateRange {Start = new DateTime(2009, 1, 1), End = new DateTime(2009, 1, 30)},
                    new DateRange {Start = new DateTime(2009, 1, 31), End = new DateTime(2009, 3, 01)},
                    new DateRange {Start = new DateTime(2009, 3, 2), End = new DateTime(2009, 3, 31)},
                    new DateRange {Start = new DateTime(2009, 4, 1), End = new DateTime(2009, 4, 30)},

Now, I can easily test the static class I wrote (this is 1 of 9+ real tests):

public void FindRelativeToDate_MiddleOfDateRange_ContainingDateRange()
    var result = DateRangeRepository.FindRelativeToDate(_DateRanges, new DateTime(2009, 3, 15), 0);
    Assert.AreEqual(_DateRanges[2], result);

The only thing that is left to do is wire up the repository method so that it calls my static method. This is a thin wrapper layer that will actually get used in production. If you run profiler, you'll see that the query expression is being evaluated and converted into an efficient SQL expression.

public DateRange FindRelativeToDate(DateTime reference, int periodOffset)
    var ctx = dbEntities;
    return FindRelativeToDate(ctx.DateRangeSet, reference, periodOffset);


Unfortunately, this method of testing a repository doesn't scale easily. If you start working with multiple entity sets that are combined with join operations, this technique is next to impossible to use. You'll see the most benefit when working with a single entity type, and need to test logic in your repository method.

One thing you need to be aware of, is that LINQ to SQL and Entity Framework don't implement every IQueryable/IEnumerable method. This means that you could potentially make calls on the in-memory collection that will then fail when you use the actual database. Fortunately, these problems can usually be detected fairly quickly.

Like this post? Please share it!

See a mistake? Edit this post!

Maintaining Consistent Line Lengths

Today's tip comes from the "Anally Retentive" department. In the .NET CLR team likes to keep their lines of code under 110 characters long. I'm assuming that they're trying to maintain consistency and readability. I often try to maintain an imaginary line length limit, but I doubt I'm very consistent.

Vertical line in Visual Studio

Fortunately, Visual Studio provides a hidden feature that lets you draw a vertical line in the text editor to show you where a certain line length would end. Fire up your registry editor and find this key:

HKEY_CURRENT_USER\Software\Microsoft\VisualStudio9.0\Text Editor

If you're using a version of Visual Studio before 2008, you'll need to decrement the 9.0 version number in the path above.

Then, add the following value (as a string or REG_SZ) with the name of "Guides":

RGB(192,192,192) 110

The first part is the color, and the second part is the line length. Personally, I use a line length of 110 to stay consistent with how Microsoft has chosen to do it. I like the color listed above because it's faint, but visible. Since the line is almost impossible to see in the screenshot above, here is an un-scaled screenshot of the line itself:

Vertical Line

To further enforce the 110 character limit, you could also resize the code portion of your Visual Studio window so that it's near the line. This will make the line itself a little less annoying, while allowing you to use the rest of the window for other information. For example, take a look at how much room I have on a 1920x1200 screen when I horizontally resize my code window:

Utilizing a large monitor in Visual Studio

Obviously this tip isn't for everyone. You may be working with legacy code with long lines, or you might work on a team that doesn't mind long lines. The great news is that Visual Studio is pretty accommodating to however you like to work.

Like this post? Please share it!

See a mistake? Edit this post!

Ode to the iPhone & iPod Touch

I know I'm nearly two years late to the game, but I finally went out an purchased an iPod Touch, and I have a feeling an iPhone will be mine in the near future. I've been blown away by how far ahead of it's time this thing is. More importantly, it amazing how much of an application ecosystem has developed in such a short time.

Kyocera 6035

Back in the day, I bought one of the first Smartphone's to be sold in the United States, the Kyocera 6035. It was basically a Palm device glued to a phone. It was pretty cool since I could use the same device to play Monopoly and make phone calls. After getting rid of this phone, it would be years before I would get another so called "Smartphone".

Years later, I started getting into the Windows Mobile world (Pocket PC at the time). The Windows mobile platform is compelling because as a developer I can write applications using the .NET Compact Framework without a huge learning curve. It's also one of the few platforms that works/worked great with Microsoft Exchange. My current carrier of choice, Sprint, also tends to have a great suite of Windows Mobile phones. I also like the fact that any hackable feature is just a registry edit away.

HTC Touch Diamond

My current phone is an HTC Touch Diamond. On paper, this is currently one of the most amazing phones ever created. It's one of the smallest Smartphone's you can buy, yet it has a fast processor, VGA screen, excellent GPS, light sensor, stylus sensor, accelerometers, resistive & capacitive features, etc. However, in reality, this phone drives me crazy. Opera is a decent browser except that it takes too long to open, and doesn't render as fast as it should on 3g. The push email features are pretty good except that the UI is a joke. Scrolling is not as smooth as it should be. Integration between apps is non-existent. The resistive screen isn't optimal for finger use. The experience is just laughable. The list goes on. I assumed these were all unavoidable simply due to the fact that it's a mobile platform.

Recently, I decided to try the iPod touch. It's my understanding that it's somewhat of a gateway drug to the iPhone. Essentially, it's the same thing but without a phone, a real GPS, and a microphone.

After using this device for a while, I am consistently surprised how streamlined and painless it is to use. Nearly every function works without even thinking about it. Every motion is perfectly smooth. No configuration is too difficult.

At first I was skeptical about the main interface, which consists of one or more screens full of icons. There is really no organization, no folders. The beauty of this design is in its simplicity. You're never more than one press away from the information you're looking for. Weather, click. Headlines, click. Calendar, click. Email, click. Touch Flo 3d on my HTC phone is essentially lipstick on a pig. It looks cool, and kind of works well if you're completely sober.

Now, let's get to the real reason that the iPhone is an unstoppable force. They have an insane application ecosystem. Most of the applications are not worth the bytes they're made of. However, a few of them are so simple, so elegant, and so efficient that they change the platform. For example, if I want to see what movies are in the theater, I can use the movie app. If I want TV listings, I use the TV app. If I want to find local events or lookup a number, I use the yellow pages app.

iPhone applications usually have similar functionality to what you get in your browser on your desktop or laptop computer, but they're typically designed to do one thing, and do it well. If you were to download an application to your computer specifically for getting movie times, I'm sure the experience would be similar, but on the desktop platform it's not quite worth it. I find myself using my iPod touch instead of my laptop to get a lot of quick information. I've also been opened up to a world of information that I normally would not have seen. For example, I have an application that shows me the local events in the area. I could have Googled for the same information, but this puts it all just a press away.

If you are someone that hasn't given the iPhone platform a try, do yourself a favor and go spend $230 on the iPod Touch. Then, visit the app store and download some freebies. If you're waiting for Windows Mobile or Android to catch up and build up the same application ecosystem, don't hold your breath.

I've been so excited by this platform that I ordered myself a Mac Mini that should hopefully be showing up tomorrow :-). Stay tuned as I talk about the experience of a c# developer writing an iPhone app in Objective-C!

Like this post? Please share it!

See a mistake? Edit this post!

Jason Young I'm Jason Young, software engineer. This blog contains my opinions, of which my employer - Microsoft - may not share.

@ytechieGitHubLinkedInStack OverflowPersonal VLOG