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();
}

The “ToList” is forcing the IQueryable<Cat> query to execute and put the results in a list immediately. However, we know that IQueryable<T> inherits from IEnumerable<T>, 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;
}

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

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.

Conclusion

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.

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:

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.

Conclusion

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.