Proper & Efficient Structure for an entity layer?

Question

Context

Web application based on the .NET Framework written in C# and following the MVC design pattern.

Question

What is the most efficient way to structure an entity layer consisting on various data inputs?

Background

I am developing an application for a client that requires my reading and writing data from various sources and I would like to build a single entity layer/model to abstract the various data sources so that I only need to query the unified model from code. Here is an example of the disparate sources:

• MS SQL Data Warehouse (about 200 tables of enterprise-wide data
• MS Dynamics AX web services
• A new MS SQL database that is specific to the application being developed
• MS Active Directory for authenticating users and for getting user profile and group information.
• In some cases, SharePoint web services.

I have used ADO.NET a good bit (.edmx files and so forth) and fundamentally, I know how to do this but I am struggling a bit with a solid, clean approach. Do I create two separate .edmx files for the two SQL DBs and then write object classes for the Active Directory, SharePoint and AX stuff? Do I simply write one big model file? A whole separarate project just to make the abstraction?

Many thanks!

Answer 1

It sounds like you are trying to model a set of disparate data sources as a single model.

If that is indeed the case, my opinion is that creating a project to make the abstraction is the way to go.

I say that because you are dealing with different TYPES of sources. For example, if you just had two databases you could put a link between them, and abstract that way.

But, you have a data warehouse, a few web services, and Active Directory to deal with. I don't know if there would be one way to consolidate across all of those, and even if there were, it could be messy.

Putting the abstraction in a separate project also ensures that you only have to change ONE place when your entities change. It also ensures that you hide the details of what is going on behind the scenes from your front end. This can be important, particularly when you have to add something else (not if - with that many sources something else will surely come along later).

Answer 2

Having a separate assembly for the Data Access is a good idea in this case and it makes it easier to point a separate testing project at it to more cleanly test your data access layer. You can develop the entity models as plain classes and then use services to populate them. You may want to take a look at Entity Framework Code First for the database access as it will get away from the .edmx dependency and keep your entity classes cleaner.

There is a tutorial by Scott Guthrie on using Code First with existing databases.

Answer 3

I have previously used the repository model in a project that sounds very similar to this. I captured entities as plain classes and then for the entities that were stored in SQL I used Entity Framework Code First to generate the tables in SQL for me. Also, if your information will be stored in multiple databases you will just need a DBContext class for each datasource. Using the repository model will allow you to point to any source to populate each repository of entities.

As far as web services, I generally split these out into another project to make a physical division between a service layer and a data layer.

So in this scenario I would have 2 projects. One for the data layer (this would implement the repository pattern, include related entities, and handle CRUD to datastores using EF or ADO.Net), and one for the service layer (this will talk to AD service, SharePoint services, and any other services. It will also contain the related entities for these services).