C# Architecture – Best Practice for Modeling an Active Record Entity with Linq-To-SQL

Architectureasp.net-mvc-3clinq

Currently I'm starting a new system on my company, and we are using a good separation between models, views and controllers, basically using Asp.Net MVC 3 for the user UI, and a C# class library for the model.

The question is about "modelling a model".

We are using Linq-to-SQL as a Data Access Layer, and modelling entities over this DAL. Example:

    // DAL, an autogenerated .dbml file
    ...
    public System.Data.Linq.Table<TB_USER> TB_USERs {
       get {
          return this.GetTable<TB_USER>();
       }
    }
    ...

And we are mapping this table on an entity, like below:

    public class User {
       // Entity, mirroring a .dbml table
       public static IEnumerable<User> GetAll() {
           var db = new MyDataContext();
           var userList = (from u in db.TB_USERs select u).ToList();
           IEnumerable<User> retorno = lista.ConvertAll(u => (User)u);
           return retorno;
       }

       // Active Record ?
       public static User Save(User user) { ... }
    }

Is this kind of modelling correct ? It feels like I'm repeating myself by having 2 entities meaning the same thing (User and TB_USER), but TB_USER is the raw representation of the database table that persists the User entity.

And the GetAll method, a static method created on the entity with the sole purpose of retrieving all of them. That means that if I want to retrieve data using a filter, for example, I have to create another GetDataBy... method.

And what about the Save method ? I know it's supposed to save the state of one User, but what if I have to save some random User object along with other objects to make a transaction ?

Shouldn't this kind of transaction control be in the database ?

Best Answer

If you are going to stick with LINQ-to-SQL you probably want to use the linq-to-sql classes as your entities. That is rename TB_USER to User and you then wrap the interaction with LINQ-to-sql in repositories, i.e. a UserRepository with a GetById, GetByUserName, Save, and similar methods - depending on your specific needs. This keeps the data access in one place.

With LINQ-to-sql beware of the temptation to have LINQ expressions that go off and query the DB scattered across your code base. LINQ-to-sql seems to encourage that in my experience. But that leads to very tight coupling.

If you want a cleaner cut between your domain entities and the DB, I'd really recommend moving to another ORM. My personal preference with a SQL Server on the backend would be NHibernate.

Related Topic