LINQ + type tables best practices

Question

Whats the best design pattern to use for LINQ and type tables that exist in SQL.

I have tables in SQL that constrain values to type values, and I want to be able to use this in my C# code as strongly typed values.

My current approach for a 'PackageStatus' type is as follows:

SQL Table

PackageStatusType (int)
desc (varchar)

C# Class - using LINQ

public class PackageStatusType
{
    static PackageStatusType()
    {

        var lookup = (from p in DataProvider.ShipperDB.PackageStatus
                      select p).ToDictionary(p => p.Desc);


        Unknown = lookup["Unknown"];
        LabelGenerated = lookup["Label generated"];
        ReadyForCollection = lookup["Ready for pickup"];
        PickedUp = lookup["Picked up"];
        InTransit = lookup["In Transit"];
        DeliveryAttempted = lookup["Delivery attempted"];
        DeliveredByHand = lookup["By hand"];
        DeliveryFailed = lookup["Delivery failed"];
        Delivered = lookup["Delivered"];
        Voided = lookup["Voided"];
    }

    public static ShipperDB.Model.PackageStatus Unknown;
    public static ShipperDB.Model.PackageStatus LabelGenerated;
    public static ShipperDB.Model.PackageStatus ReadyForCollection;
    public static ShipperDB.Model.PackageStatus PickedUp;
    public static ShipperDB.Model.PackageStatus InTransit;
    public static ShipperDB.Model.PackageStatus DeliveryAttempted;
    public static ShipperDB.Model.PackageStatus DeliveryFailed;
    public static ShipperDB.Model.PackageStatus Delivered;
    public static ShipperDB.Model.PackageStatus DeliveredByHand;
    public static ShipperDB.Model.PackageStatus Voided;
}

I then can put PackageStatusType.Delivered in my C# code and it will correctly reference the right LINQ entity.

This works fine, but makes me wonder:

a) how can i make this more efficient b) why doesn't Microsoft seem to provide anything to create strongly typed type tables c) is my database design even a good one? d) what is everyone else doing!

thanks!

Answer 1

Linq to SQL allows you to map a string or int column in a database to an enumeration in your C# code. This allows you to let Linq to SQL to map these values for you when you select from the database. In this case, I would change my package status column to be either an int column with the values from the enumeration or a string that represents the values from the enumeration.

In your case, I would have a PackageStatus enumeration with the different values that you specified, and then using the ORM designer or SQLMetal, map that column to that enumeration. The only caveat is that the string values in the column in the database must match the values in the enumeration as Linq to SQL will use Enum.Parse() to map the string values from the database to the enumeration or make sure that the int values in the database match the values from the enumeration.

This is more efficient as you don't even need to map the lookup table at all in the code.

http://msdn.microsoft.com/en-us/library/bb386947.aspx#EnumMapping describes how this works.