How do I create a facade for identical LINQ 2 SQL tables?

Question

I'm working with a 14-year old schema where a parent table (P) contains a type (8 possibilities) and location (3 possibilities) field that together point to one of twenty-four specific tables (A1, A2, A3, B1, B2, B3...), all with identical schemas for their type.

I would like to create a generic way to access the specific tables but cannot seem to make it work.

Let's say the parent table is called Document and two types of specific are (NewLetter, CurrentLetter, and HistoricLetters) and (NewBill, CurrentBill, and HistoricBills).

 public interface ILetter
 {
     DateTime CreationDate { get; }
 }

 public interface IBill
 {
     DateTime BillDate { get; }
 }

 // The LINQ 2 SQL generated classes already implement the CreationDate property
 public partial class NewLetter : ILetter { }
 public partial class CurrentLetter : ILetter { }
 public partial class HistoricLetter : ILetter { }

Then in my code, I would like to be able to do this:

 switch (type)
 {
   case 1:
     Table<IBill> specificBill;
     switch (location)      
     {
        ...
     }

   case 2:
     Table<ILetter> specificTable;
     switch (location)
     {
       case 1: specificTable = dataContext.NewLetter as Table<ILetter>; break;
       case 2: specificTable = dataContext.CurrentLetter ...
       case 3: specificTable = dataContext.HistoricLetter ...
      }   
 }

Or something similar. Unfortunately I can't cast Table<NewLetter> to Table<ILetter> even if NewLetter implements ILetter. Is there any way around this? I guess I'm basically trying to create a view in C#, as I don't have the permissions to create one in the db itself.

Answer 1

Look at inheritance in Linq2SQL. Your tables will need some kind of discriminator column/property.

Answer 2

The DataContext has a generic method to retrieve the table you want:

var table = dataContext.GetTable<NewLetter>();

Maybe you could use that to rewrite your query:

var table = dataContext.GetTable<NewLetter>();
var letters = 
    from letter in table
    where ...
    select letter as ILetter;