Friday, November 30, 2007

Linq to Sql and Enums - Unexpected Behavior - Part 2

In my previous post, I mentioned the first set of unexpected behavior I ran into with Linq to Sql and Enums. Here's the next one:

So the Asp.net RoleProvider wants an array of strings the represents the names of all the Roles a given user is in. So here's the Linq code that I wrote to do that:

return (from u2r in lMainDataContext.User2Roles
where u2r.User.EmailAddress == username
select u2r.RoleTypeEnum.ToString()).ToArray();

The problem, the array that was returned contained the value "2".

Why, because Linq to Sql tries to convert this query to Sql and execute it using the database engine.

The problem is that RoleTypeEnum is an int as far as the database engine is concerned and so it returns a string representation of that int, instead of "Partner" which is what I was expecting.

Splitting the query into two forces Linq to Sql to map the int to an enum:

RoleType[] lMatchingRoles = (from u2r in lMainDataContext.User2Roles
where u2r.User.EmailAddress == username
select u2r.RoleTypeEnum).ToArray();

return lMatchingRoles.Select(r => r.ToString()).ToArray();

Another place where you realize that SQL Server doesn't really know about enums.

Linq to Sql and Enums - Unexpected Behavior - Part 1

It's been a long time since my first post. A lot has happened since then. The biggest change is that I quit my job almost 3 months ago and I'm now working full time on my very own startup. I'll blog more about that later. Anyhoo, this explains why I'm still up at 7:27 in the morning.

But I just ran into two sets of unexpected behavior using Linq to SQl and enums and I wanted to shared them with you.

So we have the following enum:

public enum RoleType
{
Admin = 1,
Partner = 2
}

which maps to a column in the db. We're using Linq to Sql's nifty Enum mapping capabilities that allow us to work with the enum in code while beneath the covers, it automatically translates between the enum in code to the int in the db.

Our db column looks like this:



Notice that the int that stores the enum value can't be null.

Of course, now Linq to Sql has a problem. The User2Role entity that this column belongs to has a property named RoleTypeEnum thats a non nullable int in the db but is a RoleType enum in code.

So when you create a new Role2Enum entity, the RoleTypeEnum defaults to 0. And if you don't replace that 0 before committing to the database, well guess what, the db wont complain because as far as it's concerned, the value of RoleTypeEnum is not null even though its an invalid RoleType of value 0.

More fun ensues when you try to get the invalid RoleType:

RoleType lRole = (from u2r in lMainDataContext.User2Roles
where u2r.User.EmailAddress == username
select u2r.RoleTypeEnum).Single();

The value of lRole is 0, which breaks all sorts of stuff.

This explains why when analyzing my code, the code analyzer suggests that I should add a new member None with value 0 to the RoleType enum.

Here's the next one