Using entity framework to connect to multiple similar tables in .net MVC.

Question

A relative newcomer to .net MVC2 and the entity framework, I am working on a project which requires a single web application, (C# .net 4), to connect to multiple different databases depending on the route of access, (ie subdomain).

No problem with this in principle and all the logic is written to transform the subdomain into an entity connection and pass this through to the Entity Model.

The problem comes with the fact that the different database whilst being largely similar in structure contain 3 or 4 unique tables bespoke to that instance.

To my mind there are two ways to solve this issue, neither of which i am sure will be possible.

1/ Use a separate entity model for each database.
-Attempts down this route have through up conflicts where table/sp names are the same across differnt db's, or implicit conversion errors when I try and put the different models in different namespaces.

or

2/ Overwrite the classes which refer to the changeable database objects based on the value of a base controller property.
-I have found nothing to suggest i can even do this.

My question is if either of theser routes can ever work in principle or if i should just give up on the EF and connect to the dtabases directlky using ADO. Perhaps there is another way to solve this problem i haven't thought of?

Thanks for any help...

Answer 1

Interesting question.

I'd recommend you have a seperate EDM for each database, and add another layer (class library) on top of model (a DAL repository). This layer would be a single point of call from your controller. Then in this layer you can direct a call to a particular data context and return the result. You might also like to create common POCO's for your two databases, then this combined with the use of interfaces will give you a strong design pattern.

This way your model knows nothing about which database its connecting to - this is the job of your DAL Repository.

When you create your EDMX (in the designer), be sure to specify the "Namespace" in the properties window to be unique, before you attempt to use the data context.

Of course as a last resort (if the above wont work for you), you could create seperate assemblies for each EDM, and direct a call to a specific assembly in your DAL.

Here's how i have mine setup (im simplistic, 'Foo'-masked terms)

I have a table called "tblFoo" on my EDMX.

MyApp.Model.Repository (Class Library)
  - `Foo.edmx` (`internal`, namespace `MyApp.Model.Repository`)
  - `ILinqRepository.cs` (defines methods such as `IList<Foo> GetFoo`)
  - `EntityFrameworkRepository.cs` (concrete implemenation of `ILinqRepository`)
      projects a collection of `tblFoo` into type `Foo`
MyApp.Model.Service (Class Library)
  - `IDataService.cs` (similar signatures to `ILinqRepository`)
  - `LinqDataService.cs` (concrete implementation of `ILinqRepository`)
       'greedy' constructor accepts interface `ILinqRepository`
       and invokes methods on it
    ie:


public ILinqRepository _repository
public LinqDataService(ILinqRepository repository)
{
    _repository = repository;
}

public List<Foo> GetFoo()
{
    return _repository.GetFoo();
}
MyApp.Model.Entities (Class Library)
   - POCO classes such as "`Foo`".
MyApp.WebApplication (MVC2)

So, i use IOC (StructureMap) to inject the concrete implementations of the interfaces into the Controllers/ServiceModel.

This way there is a 1-1 "stack-like" approach:

Controller -> Model.Service -> Model.Repository -> EDMX

In your case, you could add another EDMX in your repository (say the table is called tblFoo2). Then both your repository methods would project from tblFoo -> Foo or tblFoo2 -> Foo.

Your controllers have a reference to the POCO's, not the tables (as they are internal).

You can easily add more and more functionality to your repository without touching any of the other layers.

Now i know this may be overkill for your scenario (you may not like to use IoC - just invoke on concrete classes), but this is what works for me.

Hope this helps!