How do I execute LINQ queries against a database with varying schema?

Question

I need to execute read-only queries against a database that I don't control. My top choice would be Linq to SQL, however, the column names differ slightly between our Dev, QA, and Production environments.

For example, take a FolderName column. We might have:

Dev:  u34_FolderName
QA:   u74_FolderName
PROD: u56_FolderName

I want to do queries like this:

 var query = from c in DepartmentReviews
             where c.FolderName == "Test"

Can I use the Entity Framework to solve this problem?

What a potential solution look like? 3 assemblies, 1 for each of my environments? Can I create common interfaces or base classes for each of these and code against those?

Answer 1

You may need to use an XmlMappingSource instead of the default AttributeMappingSource. With an XmlMappingSource you can customise the column mappings in an xml file, independent of the main application. This should allow you to have different mappings from dev, qa and production.

Answer 2

Changing schemas are always a problem. Though I am not a big advocate of storing XMLs in a database, your problem sounds like it can make use of this approach.

This solution requires you to make one (and only one schema change).It may/may not be feasable in your application.

In your table you probably would have a column for unique identifier and a column for xml (SQL Server 2005/2008 naturally support it). You can serialize it as XML (you probably would end up using a generic XML Serializer- Serializer<T> whose type you would infer at run time using reflection). Deserialize it and you can get your object. You can read more about it here.

So your query would be like

var myXML = from c in ObjectContext.Table
             where c.FolderName == "Test" select MyXmlColumn;

var myType = InferTypeFromConfig();
var serializer = new XmlSerializer<myType>();
var object = (myType )serializer.Deserialize(TheXMLStreamCreatedFrom(myXml));

my $0.02

Answer 3

Have you considered making your own data access layer? If your queries are limited to a specific set of cases, it wouldn't be a fully-fledged framework. You would have a common vocabulary sit atop the three database schemas, and you would pick the correct raw column name at runtime. You'd just stitch up your queries the old fashioned way.

Although you would have to write your queries manually, you still retain a lot of the client-side power of LINQ if your native data structures are IEnumerable.

Answer 4

Using Entity Framework you could manipulate the EDMX file at build time to achieve the desired result for each environment, see http://msdn.microsoft.com/en-us/library/cc982042.aspx

You could for example create a project that does the pre-processing for the appropriate environment at build time and include this project file in MSBUILD as the first project that gets built.

Or you could generate multiple CSDL, MSL, SSDL files and switch between them at runtime instead of using the default behavior which is to load them from resources that are embedded during the build process.

Answer 5

In the EF 4 you can do this fairly easily with Code First, which is currently in CTP.