How do I switch the table that is queried with linq-to-sql

Question

We have two tables with the same set of columns; depending on the “type” of object the value is stored in one of the two tables. I wish to use common code to access these two tables.

If I was using “raw sql” I could just use String.Format() to change the table name.

(Likewise for updates etc)

---

The two separate tables are needed as the data access patterns are very different for the common queries on the two tables and therefore different indexes are needed. “Views” and “instead of triggers” etc to make the tables look like a single table are not liked here. A lot of our customers use low end version of SqlServer so we cannot use partition tables.

---

Update:

Part of the issue I have is that I have lots of methods like

IQueryable<Event> AddFlexibleFieldsFilter(
   FlexibleFields flexibleFields, 
   IQueryable<Event> qry, 
   IQueryable<EventFlexibleField> fields
   )

And using subclass or interfaces don’t play well with generic types (we are still on .net 3.5)

Answer 1

Sadly, there's no easy way. It's possible to create two XML mappings and then load the appropriate mapping via an explicit DataContext constructor at runtime. Related links:

• http://stackoverflow.com/questions/582221/how-to-change-linq-o-r-m-table-name-source-during-runtime
• http://guy.dotnet-expertise.com/PermaLink,guid,789dd70b-73b9-4216-a479-77e19d403e38.aspx

This will certainly dirty-up your Linq-to-Sql logic as you'll need to determine which type you're working with before you create your DataContext.

I might consider two data access classes mapped to either table with a master class that fetches its data from the appropriate source given a type. Then a developer can use the master class without worrying about where the data is coming from.