I keep getting this question so often - "I want to do xyz. I have CRUD (Create, Read, Updated, Delete) methods on my entity class ...."
Stop right there. By now I have completely forgotten about "xyz" and I am rushing to climb on my soapbox!
While designing LINQ to SQL, we went to great lengths to avoid or at least minimize any coupling of an entity to data access operations (CRUD). This lets entities behave like data or business objects and makes them mobile from mid-tier to client. The whole point was to avoid having to dump the kitchen sink and its crud into the entity. Looks at wrapper methods for sprocs, override methods for CUD, Table<T> methods, SubmitChanges() or Attach(). All of them are _away_ from entities. This is not an accident! There are several reasons behind it:
- As mentioned above, this provides a better logical organization - the data and associated methods that manipulate the data in entity class and the persistence service in a separate class.
- Entities can be moved between tiers much more easily. Connecting to database from presentation tier is either unadvisable or simply not permissible. With the separation mentioned above, DataContext stays on the mid-tier while entities move around as needed. (We haven't done nearly enough in this area to make it even simpler but we have laid the foundation at least)
- (This is often negelected but important) The persistence operation is structured around a unit of work. If you modify multiple entities, you don't have to create another unit of work that calls the update methods on all the entities or even all entity classes. Just DataContext.SubmitChanges() is enough and it is done transactionally and with optimistic concurrency checks in that transaction.
To be blunt, if you are going to use LINQ to SQL, please don't fight the pattern it was designed for. The proverbial square peg of CRUD methods on entity is just not going to fit in the round hole of the DataContext as CRUD service provider design.
Now that this is out of the way, we can talk about the "I want to do xyz" part instead 🙂