Generate a complex database model with MS Entity Framework

Question

I have a database with about 160 tables (it's a shock ! I thought it had about 50...).
We use a codebase with no DAL, and we want to start using MS Entity Framework (that's the latest, hottest thing in .net DAL, right ?) so we can use Linq and other state-of-the-art-3-years-ago stuff.

I tried to automatically generate the whole DB model, but it seems to be a nightmare to use and modify. For example, it has a few tables not automatically linked (maybe the DB model is lacking in a few areas).

I then tried to generate each "area" of the database separately, but two points bother me.

First of all, it needs a different connection string for every model. WTF. Plus, we have clients with different databases, and maintaining 10 connection strings across 30 clients will be a nightmare.

Second of all, it doesn't appears to be able to link to the classes in other models. Maybe I just didn't find it ? With around 10-15 "modules", most of wich linked together, it would defeat one of the purposes of a unified DAL. For example, we have a "file" table, linked to the contacts, the tech support, the products, the web, etc. We could still use the IDs, but it's kinda sad.

So, what would you do ? One huge model very complicated to create and maintain ; or a few smaller ones, easier to update, but a bit more complicated to use on a day-to-day basis ? What do you use, and what are the upsides and downsides ?

Answer 1

First, about the Connection Strings, yes, each model does need its own Connection String, because it's an EntityConnection which not only contains the information about how to connect to the database but also information about how to find the 3 part of your model at runtime (i.e. SSDL, CSDL and MSL). So when working with one database, even the database connection string part is the same, but the Metadata part is different for each model and that's why you need them all to be there.

Second, you got it right, having one big EDM containing all the entities is NOT a good practice and is not recommended. This post contains a detailed discussion about the reasons as well as a few guidelines about how to separate your database tables into different models.