Using LINQ to SQL with multiple databases

Question

I am working on a new project and hoping to use LINQ to SQL for the data access but have come across the following issue.

I need to have my application access 3 databases with similar but not the same table structure, for example

Database1 and Database 2 has a table called tblCustomer with 2 columns CustomerKey and CustomerName

Database2 has a table called tblCustomer with 3 columns CustomerKey, CustomerName and CustomerPostCode

I am looking for a solution that will allow me a query all three databases without the need for 3 GetCustomerList functions as Database1 and Database2 could use the same function as are the same structure, with an override function for database 3 to bring back the additional field.

Is there a way i can declare a base datacontext class to handle Database 1 and 2 with an inherited version for Database 3.

Thanks In Advance

Stuart Ferguson

Answer 1

I think it is possible to have three slightly different table definitions (in three database), with one single entity/domain object. For this to work, you will need to use POCO entities (thus no attributes decorated on the L2S objects) and supply the DataContext with a mapping source containing your database definition. In this case you will need, three separate database connections, each with its own DataContext with its own mapping.

While this might work, this solution might be brittle. Perhaps you'd be better of letting those different customer object implement the same interface or use an (non LINQ to SQL related) object (proxy) that is able to represent all of them.