Linq2sql on different SQL Server Schemas

Question

I have a application which is running in both production and development environments. I would like to utilize the databases better (and save money on my hosting bill) so i want to be able to make my Linq2Sql run on two different schemas (instead of two different databases) (there are ~15 tables in a schema). How to set this up in Linq2Sql?

Or should i go the distance and read up on Entity Framework instead (and is it possible to segment the tables based on schemas in this one?

Any other solutions to this problem are welcome?

Answer 1

This is actually easier to do in LINQ to SQL than it is in EF. Not that it is terribly easy, mind you. I wrote a blog post a couple of years ago on how to do it but the heart of it is to specify the mapping source in your context constructor.

XmlMappingSource source = XmlMappingSource.FromUrl("TestLINQ.map");
// Could also use XmlMappingSource.FromXml(string)
using (LINQ.TestLINQDataContext context = new LINQ.TestLINQDataContext(Properties.Settings.Default.TestConnectionString, source))
{

Using this method, you can alter your mapping source to point at the schema (or table name) that you want to.