Passing an enum value as a stored procedure (data function) parameter in LINQ to SQL

Question

As mentioned in this question, "LINQ to SQL allows table mappings to automatically convert back and forth to Enums by specifying the type for the column - this works for strings or integers." However, I am having trouble with this when using stored procedures (instead of auto-generated SQL) to create and update entities.

In my database I have a table Users with a column Role of type int. I also have a stored procedure CreateUser which accepts a parameter @role of type int. I want user roles to be represented by an enum Role in my C# code. I can change the type of the Role property on the autogenerated User class from int to Role, but then the project refuses to compile because the type of the Role property doesn't match the type of the data function role parameter (corresponding to the @role parameter in my stored procedure). The data function parameter types are autogenerated by LINQ to SQL and I can't find any way to change them.

I'm considering leaving the Role property as an int, renaming it to something like RoleValue, making it private, and adding another public property of type Role which performs the int-enum conversion. But it seems like there ought to be a better way.

Answer 1

You can overload the CreateUser method..

public Enum Role
{
    Guy = 0,
    Girl = 1
}

public partial LINQtoSQLClass
{
    public ResultObject CreateUser(Role thisRole)
    {
        return CreateUser(Convert.ToInt32(thisRole);
    }
}

It's also common to use T4 Templates to generate the Enumberable class using real-time data from the backend database. That way it matches up with the Ints and you won't have to update it manually.

Answer 2

The data function parameter types are autogenerated by LINQ to SQL and I can't find any way to change them.

open your DBML file, click on the Role field. the properties window will have a place to specify the server type, as well as the .NET type. here's an example of how it works:

http://stackoverflow.com/questions/211875/linq-to-sql-mapping-enum-from-string

the answers indicate it works with VARCHARs and TINYINTs so it should work fine with INT as well. the hardest part is probably remembering to put in the right namespace. good luck!