16 Sep 2011

When I logged into my blog to write a new post, I realised I had 2 posts still in draft... I have been neglecting my blog a hell of a lot... anyway, here's a post I really wanted to write as this issue got me a couple of times, but never again!

**[Background:** this application uses SQL Server 2005 and NHibernate as the ORM. Our tables all have IsDeleted (bool), LastUpdated (datetime) and LastUpdatedBy (varchar) for audit purposes**]**

I ran into an issue sometime ago when writing a Linq query whilst trying to return items from the db where the IsDeleted flag was set to false like this:

var users =
    HibernateSession
        .Query<SecurityUserIdentity>()
        .Where(s => s.IsDeleted == false)
        .OrderBy(s => s.DisplayName);

Running this, would result in the following Exception, "Unable to cast object of type 'System.Boolean' to type 'System.String'". Investigating the SQL produced from this Linq Query, it wraps the where clause for the IsDeleted property in a bloated case clause in the T-SQL but doesn't seem to translate the bool value correctly.

I found someone had the same issue as me over at StackOverFlow. He explained by striping the == false from the where clause and replacing it with ```!(s.IsDeleted)``` so that it looks like this:

var users =
    HibernateSession
        .Query<SecurityUserIdentity>()
        .Where(s => !(s.IsDeleted))
        .OrderBy(s => s.DisplayName);

Doing this worked and when tracing the T-SQL, I was able to verify that the boolean flag is being evaluated correctly.