How to retrieve dates as UTC in SQLite

SQLite is a nice in-process database engine: it’s very lightweight, doesn’t require any server or configuration, and runs on all platforms. There is even an official ADO.NET provider that’s very well made. However, if you store dates as UTC with this provider, you will probably encounter a serious issue: even though the date is properly stored as UTC (it’s stored in a form similar to ISO8601, with a ‘Z’ to indicate the UTC timezone), when you read it back from the database, you will get a DateTime converted to local time, with Kind = Unspecified. Here’s an example that exhibits the problem (using Dapper in LINQPad):

void Main()
{
    string connectionString = @"Data Source=D:\tmp\testSQLiteDate.db";
    using (var connection = new SQLiteConnection(connectionString))
    {
        connection.Open();
        connection.Execute("create table Foo(Id integer not null primary key, CreationDateUtc datetime not null)");
        
        var foo = new Foo{Id = 42, CreationDateUtc = DateTime.UtcNow};
        foo.Dump("Original object");
        connection.Execute("insert into Foo(Id, CreationDateUtc) values (@Id, @CreationDateUtc)", foo);
        var foo2 = connection.Query<Foo>("select * from Foo where Id = @Id", new{ Id = 42 }).SingleOrDefault();
        foo2.Dump("After reading from DB");
    }
}

class Foo
{
    public int Id { get; set; }
    public DateTime CreationDateUtc { get; set; }
    public DateTimeKind Kind { get { return CreationDateUtc.Kind; } }
}

Here’s the output:

image

As you can see, after reading it from the database, the date is no longer in UTC, and doesn’t even contain an indication that it’s a local date. This can cause all kinds of bugs if your code is comparing dates that are supposed to be UTC.

I initially tried to fix that in my code by manually converting the date, before I realized that the solution was much simpler (although not very well documented): there is a connection string setting to control how dates are handled. You just need to specify DateTimeKind=Utc in your connection string:

string connectionString = @"Data Source=D:\tmp\testSQLiteDate.db;DateTimeKind=Utc";

If you’re using SqliteConnectionStringBuilder to build the connection string, just set the DateTimeKind property to DateTimeKind.Utc.

As you can see, the date kind is now preserved:

image

 

Important caveat: this setting will apply to all dates read from this connection. If you have dates that were not stored as UTC, the provider will assume that they are UTC dates anyway, which will produce incorrect results. So, only do this if you store all dates as UTC.

Exception filters in C# 6: their biggest advantage is not what you think

Exception filters are one of the major new features of C# 6. They take advantage of a CLR feature that was there from the start, but wasn’t used in C# until now. They allow you to specify a condition on a catch block:

static void Main()
{
    try
    {
        Foo.DoSomethingThatMightFail(null);
    }
    catch (MyException ex) when (ex.Code == 42)
    {
        Console.WriteLine("Error 42 occurred");
    }
}

As you might expect, the catch block will be entered if and only if ex.Code == 42. If the condition is not verified, the exception will bubble up the stack until it’s caught somewhere else or terminates the process.

At first glance, this feature doesn’t seem to bring anything really new. After all, it has always been possible to do this:

static void Main()
{
    try
    {
        Foo.DoSomethingThatMightFail(null);
    }
    catch (MyException ex)
    {
        if (ex.Code == 42)
            Console.WriteLine("Error 42 occurred");
        else
            throw;
    }
}

Since this piece of code is equivalent to the previous one, exception filters are just syntactic sugar, aren’t they? I mean, they are equivalent, right?

WRONG!

Stack unwinding

There is actually a subtle but important difference: exception filters don’t unwind the stack. OK, but what does that mean?

When you enter a catch block, the stack is unwound: this means that the stack frames for the method calls “deeper” than the current method are dropped. This implies that all information about current execution state in those stack frames is lost, making it harder to identify the root cause of the exception.

Let’s assume that DoSomethingThatMightFail throws a MyException with the code 123, and the debugger is configured to break only on uncaught exceptions.

  • In the code that doesn’t use exception filters, the catch block is always entered (based on the type of the exception), and the stack is immediately unwound. Since the exception doesn’t satisfy the condition, it is rethrown. So the debugger will break on the throw; in the catch block; no information on the execution state of the DoSomethingThatMightFail method will be available. In other words, we won’t know what was going on in the method that threw the exception.
  • In the code with exception filters, on the other hand, the filter won’t match, so the catch block won’t be entered at all, and the stack won’t be unwound. The debugger will break in the DoSomethingThatMightFail method, making it easy to see what was going on when the exception was thrown.

Of course, when you’re debugging directly in Visual Studio, you can configure the debugger to break as soon as an exception is thrown, whether it’s caught or not. But you don’t always have that luxury; for instance, if you’re debugging an application in production, you often have just a crash dump to work with, so the fact that the stack wasn’t unwound becomes very useful, since it lets you see what was going on in the method that threw the exception.

Stack vs. stack trace

You may have noticed that I talked about the stack, not the stack trace. Even though it’s common to refer to “the stack” when we mean “the stack trace”, they’re not the same thing. The call stack is a piece of memory allocated to the thread, that contains information for each method call: return address, arguments, and local variables. The stack trace is just a string that contains the names of the methods currently on the call stack (and the location in those methods, if debug symbols are available). The Exception.StackTrace property contains the stack trace as it was when the exception was thrown, and is not affected when the stack is unwound; if you rethrow the same exception with throw;, it is left untouched. It is only overwritten if you rethrow the exception with throw ex;. The stack itself, on the other hand, is unwound when a catch block is entered, as discussed above.

Side effects

It’s interesting to note that an exception filter can contain any expression that returns a bool (well, almost… you can’t use await, for instance). It can be an inline condition, a property, a method call, etc. Technically, there’s nothing to prevent you from causing side effects in the exception filter. In most cases, I would strongly advise against doing that, as it can cause very confusing behavior; it can become really hard to understand the order in which things are executed. However, there is a common scenario that could benefit from side effects in exception filters: logging. You could easily create a method that logs the exception and returns false so that the catch block is not entered. This would allow logging exceptions on the fly without actually catching them, hence without unwinding the stack:

try
{
    DoSomethingThatMightFail(s);
}
catch (Exception ex) when (Log(ex, "An error occurred"))
{
    // this catch block will never be reached
}

...

static bool Log(Exception ex, string message, params object[] args)
{
    Debug.Print(message, args);
    return false;
}

Conclusion

As you can see, exception filters are not just syntactic sugar. Contrary to most C# 6 features, they’re not really a “coding” feature (in that they don’t make the code significantly clearer), but rather a “debugging” feature. Correctly understood and used, they can make it much easier to diagnose problems in your code.

Create an auto-mocking container with Unity and FakeItEasy

Unit testing can be tedious sometimes, especially when testing classes that have complex dependencies. Fortunately, some tools make it somewhat easier. I’ve been using FakeItEasy a lot recently; it’s a very easy to use mocking framework for .NET. It has a very lean and simple API based on generics and lambda expressions, and is a real pleasure to work with. It came as a breath of fresh air compared to the old RhinoMocks I had been using before.

But as nice as FakeItEasy is, the process of registering all the fake dependencies for the class you are testing is still a bit tedious. Wouldn’t it be nice if the IoC container could automatically create the fakes on demand ? So this code:

var container = new UnityContainer();

// Setup dependencies
var fooProvider = A.Fake<IFooProvider>();
container.RegisterInstance(fooProvider);
var barService = A.Fake<IBarService>();
container.RegisterInstance(barService);
var bazManager = A.Fake<IBazManager>();
container.RegisterInstance(bazManager);

var sut = container.Resolve<SystemUnderTest>();

Could be reduced to this:

var container = new UnityContainer();

// This will cause the container to provide fakes for all dependencies
container.AddNewExtension<AutoFakeExtension>();

var sut = container.Resolve<SystemUnderTest>();

Well, it’s actually pretty easy to do with Unity. Unity is usually not considered the “cool kid” in the small world of IoC containers, but it’s well supported, easy to use, and extensible. I came up with the following extension to enable the above scenario:

public class AutoFakeExtension : UnityContainerExtension
{
    protected override void Initialize()
    {
        Context.Strategies.AddNew<AutoFakeBuilderStrategy>(UnityBuildStage.PreCreation);
    }
    
    private class AutoFakeBuilderStrategy : BuilderStrategy
    {
        private static readonly MethodInfo _fakeGenericDefinition;
    
        static AutoFakeBuilderStrategy()
        {
            _fakeGenericDefinition = typeof(A).GetMethod("Fake", Type.EmptyTypes);
        }
        
        public override void PreBuildUp(IBuilderContext context)
        {
            if (context.Existing == null)
            {
                var type = context.BuildKey.Type;
                if (type.IsInterface || type.IsAbstract)
                {
                    var fakeMethod = _fakeGenericDefinition.MakeGenericMethod(type);
                    var fake = fakeMethod.Invoke(null, new object[0]);
                    context.PersistentPolicies.Set<ILifetimePolicy>(new ContainerControlledLifetimeManager(), context.BuildKey);
                    context.Existing = fake;
                    context.BuildComplete = true;
                }
            }
            base.PreBuildUp(context);
        }
    }
}

A few comments on this code:

  • The logic is a bit crude (it generates fakes only for interfaces and abstract classes), but can easily be adjusted if necessary.
  • The ugly reflection hack is due to the fact that FakeItEasy doesn’t have an non-generic overload of A.Fake that accepts a Type as a parameter. Well, nobody’s perfect…
  • The lifetime is set to “container controlled”, because if you need to configure method calls, you will need to access the same instance that is injected into the system under test:
var fooProvider = container.Resolve<IFooProvider>();
A.CallTo(() => fooProvider.GetFoo(42)).Returns(new Foo { Id = 42, Name = “test” });

Note that if you register a dependency explicitly, it will take precedence and no fake will be created. So you can use this extension and still be able to manually specify a dependency:

var container = new UnityContainer();
container.AddNewExtension<AutoFakeExtension>();
container.RegisterType<IFooProvider, TestFooProvider>();
var sut = container.Resolve<SystemUnderTest>();

Of course, this extension could easily be modified to use a different mocking framework. I guess the same principle could be applied to other IoC containers as well, as long as they have suitable extension points.

What about AutoFixture?

Before you ask: yes, I know about AutoFixture. It’s a pretty good library, and I actually tried to use it as well, with some success. The resulting code is very similar to the examples above. The main reason why I didn’t keep using it is that AutoFixture is not really an IoC container (though it does some of the things an IoC container does), and I prefer to use the same IoC mechanism in my unit tests and in the actual application. Also, I’m not very comfortable with the way it handles properties when creating an instance of the SUT. By default, it sets all public writable properties to dummy instances of their type; this is fine if those properties are used for dependency injection, but IMO it doesn’t make sense for other properties. I know I can suppress this behavior for specific properties, but I have to do it manually on a case by case basis; it doesn’t take IoC container specific attributes like [Dependency] into account. So eventually I found it easier to use my custom Unity extension.

Async and cancellation support for wait handles

The .NET framework comes with a number of low-level synchronization primitives. The most commonly used are collectively known as “wait handles”, and inherit the WaitHandle class: Semaphore, Mutex, AutoResetEvent and ManualResetEvent. These classes have been there since at least .NET 2.0 (1.1 for some of them), but they haven’t evolved much since they were introduced, which means they don’t support common features that were introduced later. In particular, they don’t provide support for waiting asynchronously, nor do they support cancelling the wait. Fortunately, it’s actually quite easy to add these features via extension methods.

Cancellation

Let’s start with the easiest one: cancellation. There are cases where it would be useful to pass a CancellationToken to WaitHandle.WaitOne, but none of the overloads supports it. Note that some more recent variants of the synchronization primitives, such as SemaphoreSlim and ManualResetEventSlim, do support cancellation; however, they’re not necessarily suitable for all use cases, because they’re designed for when the wait times are expected to be very short.

A CancellationToken exposes a WaitHandle, which is signaled when cancellation is requested. We can take advantage of this to implement a cancellable wait on a wait handle:

    public static bool WaitOne(this WaitHandle handle, int millisecondsTimeout, CancellationToken cancellationToken)
    {
        int n = WaitHandle.WaitAny(new[] { handle, cancellationToken.WaitHandle }, millisecondsTimeout);
        switch (n)
        {
            case WaitHandle.WaitTimeout:
                return false;
            case 0:
                return true;
            default:
                cancellationToken.ThrowIfCancellationRequested();
                return false; // never reached
        }
    }

We use WaitHandle.WaitAny to wait for either the original wait handle or the cancellation token’s wait handle to be signaled. WaitAny returns the index of the first wait handle that was signaled, or WaitHandle.WaitTimeout if a timeout occurred before any of the wait handles was signaled. So we can have 3 possible outcomes:

  • a timeout occurred: we return false (like the standard WaitOne method);
  • the original wait handle is signaled first: we return true (like the standard WaitOne method);
  • the cancellation token’s wait handle is signaled first: we throw an OperationCancelledException.

    For completeness, let’s add some overloads for common use cases:

        public static bool WaitOne(this WaitHandle handle, TimeSpan timeout, CancellationToken cancellationToken)
        {
            return handle.WaitOne((int)timeout.TotalMilliseconds, cancellationToken);
        }
        
        public static bool WaitOne(this WaitHandle handle, CancellationToken cancellationToken)
        {
            return handle.WaitOne(Timeout.Infinite, cancellationToken);
        }
    

    And that’s it, we now have a cancellable WaitOne method!

    Asynchronous wait

    Now, what about asynchronous wait? That’s a bit harder. What we want here is a WaitOneAsync method that returns a Task<bool> (and since we’re at it, we might as well include cancellation support). The typical approach to create a Task wrapper for a non-task-based asynchronous operation is to use a TaskCompletionSource<T>, so that’s what we’ll do. When the wait handle is signaled, we’ll set the task’s result to true; if a timeout occurs, we’ll set it to false; and if the cancellation token is signaled, we’ll mark the task as cancelled.

    I struggled a bit to find a way to execute a delegate when a wait handle is signaled, but I eventually found the ThreadPool.RegisterWaitForSingleObject method, which exists for this exact purpose. I’m not sure why it’s in the ThreadPool class; I think it would have made more sense to put it in the WaitHandle class, but I assume there’s a good reason.

    So here’s what we’ll do:

  • create a TaskCompletionSource<bool>;
  • register a delegate to set the result to true when the wait handle is signaled, or false if a timeout occurs, using ThreadPool.RegisterWaitForSingleObject;
  • register a delegate to mark the task as cancelled when the cancellation token is signaled, using CancellationToken.Register;
  • unregister both delegates after the task completes

    Here’s the implementation:

        public static async Task<bool> WaitOneAsync(this WaitHandle handle, int millisecondsTimeout, CancellationToken cancellationToken)
        {
            RegisteredWaitHandle registeredHandle = null;
            CancellationTokenRegistration tokenRegistration = default(CancellationTokenRegistration);
            try
            {
                var tcs = new TaskCompletionSource<bool>();
                registeredHandle = ThreadPool.RegisterWaitForSingleObject(
                    handle,
                    (state, timedOut) => ((TaskCompletionSource<bool>)state).TrySetResult(!timedOut),
                    tcs,
                    millisecondsTimeout,
                    true);
                tokenRegistration = cancellationToken.Register(
                    state => ((TaskCompletionSource<bool>)state).TrySetCanceled(),
                    tcs);
                return await tcs.Task;
            }
            finally
            {
                if (registeredHandle != null)
                    registeredHandle.Unregister(null);
                tokenRegistration.Dispose();
            }
        }
        
        public static Task<bool> WaitOneAsync(this WaitHandle handle, TimeSpan timeout, CancellationToken cancellationToken)
        {
            return handle.WaitOneAsync((int)timeout.TotalMilliseconds, cancellationToken);
        }
        
        public static Task<bool> WaitOneAsync(this WaitHandle handle, CancellationToken cancellationToken)
        {
            return handle.WaitOneAsync(Timeout.Infinite, cancellationToken);
        }
    

    Note that the lambda expressions could have used the tcs variable directly; this would make the code more readable, but it would cause a closure to be created, so as a small performance optimization, tcs is passed as the state parameter.

    We can now use the WaitOneAsync method like this:

    var mre = new ManualResetEvent(false);
    …
    if (await mre.WaitOneAsync(2000, cancellationToken))
    {
        …
    }
    

    Important note: this method will not work for a Mutex, because it relies on RegisterWaitForSingleObject, which is documented to work only on wait handles other than Mutex.

    Conclusion

    We saw that with just a few extension methods, we made the standard synchronization primitives much more usable in typical modern code involving asynchrony and cancellation. However, I can hardly finish this post without mentioning Stephen Cleary’s AsyncEx library; it’s a rich toolbox which offers async-friendly versions of most standard primitives, some of which will let you achieve the same result as the code above. I encourage you to have a look at it, there’s plenty of good stuff in it.