ASP.NET MVC lookup tables in Linq to SQL

Question

This really is an architectural question. I feel like I'm going about this the wrong way and wanted some input on best practices.

Let's say I have a Transactions table and a TransactionTypes table. Views will submit the appropriate transaction data which is processed in my controller. The problem is that the logic in the controller may be a bit complex and the TransactionType is not provided by the view inputs, but computed in the controller. (Which may be part of my problem).

For example, let's say that the View submits a ViewModel that would map to a TransactionType of "Withdrawal". However, the controller detects that it needs to change this to an Overdraft" as funds aren't sufficient. What I don't want to do is this:

transaction.TypeId =
    DataContext.TransactionTypes.Single(x => x.type == "Overdraft").id;

... as I'll be embedding string literals in my code. Right?

OK, so I could map the values to strong types that would allow me to do this:

class TranTypes
{
   public const long Deposit = 1;
   public const long Withdrawal = 2;
   public const long Overdraft = 3;
}

...

transaction.TypeId =
    DataContext.TransactionTypes.Single(x => x.id == TranTypes.Overdraft);

Now, if my lookups change in the DB, I have one place that I can update the mappings and my controllers still have insight into the model.

But this feels awkward too.

I feel like what I really want is for the Linq To SQL auto-code generation to be able to generate the association so I can just refer to strongly-typed names (Deposit, Withdrawal, and Draft) and be assured that it will always return the current values for these in the database. Changes made to the lookup table during runtime would result in problems, but it still seems so much cleaner.

What should I be digesting to understand how best to structure this?

Thanks in advance for enlarging my brain. :-)

Answer 1

One simple approach I've always liked is the Enum approach.

public enum TransactionType {
 Overdraft
}

transaction.TypeId =
    DataContext.TransactionTypes.Single(x => x.type == TransactionType.Overdraft.ToString()).id;

It's pretty simple, but I like it.

A more sophisticated approach (not sure if this works with Linq to SQL, but more sophisticated ORMs support it (like EF, DO .NET, LLBLGen, etc.) is to use inheritance in your data model, with discriminators.

That is, have a subclass of TransactionType called OverdraftTransactionType with a discriminator (the key) that identifies different types of TransactionTypes from each other.

Random link: http://weblogs.asp.net/zeeshanhirani/archive/2008/08/16/single-table-inheritance-in-entity-framework.aspx

Answer 2

Dont worry about whether you have an embedded string or a strongy typed value - either is perfectly acceptable - which ever makes sense fror your database design.

What you should do, however, is write a single routine in a repository or helper class that you can then call from whatever controller or action requires it - if anything changes there is only one place to make the change.