Delayed execution vs ToList() in LINQ Database Queries

LINQ to SQL and Entity framework allow us to build a query, which gets translated into an expression tree, and executed once the full query is built. The beauty is that we can build up a query using multiple expressions and Lambdas, without actually querying the data. Since these types of queries are delay loaded, why not avoid executing them until the last possible moment? Read on to see why this is usually a bad idea.

First, let's take a look the code for a repository method that builds a query, executes the query, and returns the results in a list:

public IEnumerable<Cat> FindAllCats()
    var query = from c in db.Cats
        select c;

    return query.ToList();

Execute in Repository

The "ToList" is forcing the IQueryable query to execute and put the results in a list immediately. However, we know that IQueryable inherits from IEnumerable, so what happens if we avoid the list creation completely?

public IEnumerable<Cat> FindAllCats()
    var query = from c in db.Cats
        select c;

    return query;

Execute in UI

In this scenario, our method is returning the same interface, but the underlying type is now a LINQ database iterator instead of a List.

Delaying execution can lead to multiple executions

If the code is not explicitly putting the results into a list, we're actually passing back a form of an iterator. This works great if we only need to execute the query once. However, if we iterate through the list more than once, we actually end up executing our query multiple times. This can obviously lead to poor performance.

If you're writing fast queries, you may not even notice if they're being called too many times. However, there may be a worse problem lurking in your code. Each time you iterate through the enumerator, you're getting a different set of objects. The same query is being made with the same results, but the objects are re-built each time. This leads to objects that are equivalent, but not the same. For example, you may get back Cat objects with the names "Bill" and "Ted", but if you actually check them for equality using "==", they will not be the same object instance. (Correction: Scott points out in the comments that this isn't necessarily the case. Keep in mind that it can still occur if projecting types and not working with the original objects.)

Delaying execution may mean you no longer have a database connection when attempting to execute the query

If you delegate the task of initiating your query to another layer, you better be sure that the database connection is still around, and is in a queryable state. If you're using the standard repository pattern and a short-lived database connection pattern, you may quickly run into problems when you try to iterate through the results of the enumerator you receive from your repository layer.


If you're thinking about moving the execution of your queries to another layer, make sure you understand the consequences. You'll need to weigh those consequences against the tiny benefit that you'll receive from the delayed execution. There may be cases where delaying the execution or possibly avoiding it completely will improve your application, but those are probably very rare cases.

Like this post? Please share it!

See a mistake? Edit this post!

Common Pitfalls when working with DateTime’s

In .NET, the DateTime structure provides us wonderful functionality, but this seemingly simple structure can cause a lot of headaches if you don't fully understand how to use it properly.


Understand the terminology

First, UTC, GMT, and even Zulu time are all the same thing. They're basically a universal time clock that is not subject to changes in time zones or time changes. Each tick of the universal clock represents a moment in our perception of time.

Use UTC as long as possible

UTC is very useful when developing software because it removes the need to know where the time was from, or where it's going to be used. We don't even care when it was from, or when we're displaying it. You can think of your local clock as a view of the time right now, where you are. It has already taken into account the time zone and daylight savings time.

These properties of your local clock suggest that we should always convert from the local clock to universal time as early as possible when accepting user input, and convert it back to the users time only when displaying it. This is a simple, easy to use pattern that may be enough to avoid some of the potential problems that other projects face. This pattern will give you the ability to cope with time changes and time zones much more easily.

Converting between local time and UTC is pretty easy. ToLocalTime will convert from universal time to local time. ToUniversalTime will convert to UTC. Just be aware that these methods have a certain amount of logic in them that only has the rules that were in effect when they were written. They are not perfect for all scenarios. You'll also want to take a look at the Kind property, which affects which conversions you can perform, as well as providing a nice way to keep track of whether or not he time has been adjusted to UTC.

Daylight Savings Time & Time Changes

Every year in many parts of the world, the time changes. Apparently the idea is to save gobs of money by using the sunlight more efficiently instead of using artificial lights. Unfortunately, this really sucks for software developers.

I used to write software for manufacturing facilities that would run during a time change. If you have software that records and time-sensitive data during a time change, your software had better be prepared to handle it the fact that one hour is skipped, and another is repeated. Storing the data in UTC solves part of the problem. Unfortunately, when you try to display the data you'll have an hour of missing data, and a hour with overlapping data. You may have to design your user interface to deal with this.

Fixed-time Appointments

Unfortunately, UTC doesn't solve all of our time offset problems. Let's say that you have an appointment that you're scheduling for a future date that occurs when DST is in effect, but it's not in effect right now. You choose 5:00am for your appointment time. Your application happily converts the time to UTC, and the reverse process expectedly yields the same result. The problem is, the time offset when the appointment occurs will be different than it is now. Daylight savings for the central time zone for example, switches between and offset of -5 and -6. This diagram attempts to visualize:

DST DateTime Diagram

What we want to store is the fact that our appointment occurs at 5:00am local time. If we simply store the information as UTC, we're losing this additional information. When we switch to non-DST time and use our current time adjustment of -6 hours, our appointment now occurs at 4:00am.

If you're writing an application that stores fixed-time appointments as well as appointments that are designed to have even intervals (exactly 1 month apart, etc) or occur in a different time zone or DST, you'll need to store an additional flag with the event so you can make the determination if it needs to be adjusted.


Times can be complicated depending on the requirements of your project. It would be unwise to work these problems out toward the end of a project, because the consistency of usage can't be guaranteed. Do yourself a favor and plan ahead for these issues, and it will be much easier.

Like this post? Please share it!

See a mistake? Edit this post!

Tip for Technical Presentations – Detailed Notes

Justin Etheredge over at CodeThinked is asking for tips for technical presentations. I can certainly relate to his experiences. Technical presentations, or any type of presentation for that matter, can be intimidating, difficult, and scary.


In my college days, I had to give a presentation in one of my information systems classes. I was pretty arrogant back then (ok, I still am), so I thought it would be a piece of cake to just wing it. I could save time and look like a cool speaker all at the time time. The result was me getting in front of the class, having my face turn red, and basically say "uh" and "um" for 10 minutes. It was so bad that people actually began to laugh. It pains me to even write about it.

A semester later, I had to give another presentation to an even bigger class. I was so nervous I can't even describe it. I spent hours upon hours making creating a "script" that I could simply read. The result was that I actually didn't even use the script. I actually knew what the hell I was talking about and ended up using the script as a reference. I did a great job speaking!

So how do I prepare for presentations these days? I have detailed notes, formatted specifically for the presentation. On my slides, I always keep the number of bullet points per slide low, usually 4 or less. In my notes, I organize them by bullet point so that I can walk though them while I go over each bullet point. I try to balance simplify with detail. The notes have to be simple enough to read quickly, but detailed enough that I can just read them out loud if I lose focus and forget the purpose. Bolding keywords and phrases can help quickly identify the key concepts while still having a readable version if needed.

Slide with Notes

I've seen many presentations where the speaker gets nervous and forgets what a slide or bullet point means. The usual remedy they employ is just reading the slide verbatim. Don't let this happen to you. It is almost certain that you'll forget a critical piece of information during your presentation. The trick is to be ready for it.

In my most recent presentation (on Unit Testing), I actually wrote a paper on the topic I was presenting on. This allowed me to get all of my thoughts in order, and get feedback from others. Once I actually had to put together the PowerPoint, that was the easy part. I was basically creating an outline from an existing document, and the details became my notes.

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