ASP.NET MVC (MVC2) Best practices when Inserting/Updating Data using Linq to SQL and Repository layers

Question

I'm in a bit of a conundrum here and I'm hoping for some of you Guru's to help fill in the blanks.

The situation I'm currently facing is with regards to my "Users" table and my "OpenID" table. My app allows for a user to have multiple OpenID's, so I keep track of them in a separate table.

Users
ID
Username

OpenID
ID
UserID
Claimedidentifier

I have a CRUD repository for each table, and I also have a Service that interfaces with the Repository (one service per repo).

The question I have is with regards to inserting a new user (since updating will follow the same principal). Here are the options that I have in my head.

1. Have the UserService insert a new user, retrieve the user's ID and then insert a new OpenID using the UserID
2. Have the UserService send the new user along with the ClaimedIdentifier to the UserRepository, and have the repository insert both the User and the OpenID (this doesn't fit the CRUD methodology very well)
3. Create a View of both the User table an the OpenID table, create a UsersOpenIDRepository and a UsersOpenIDService, and then insert to the View.

Any other thoughts or suggestions beyond what I can think of will be greatly appreciated.

Please note, I am not using NHibernate whereby I can model my domain however I see fit. I am sticking to Linq to SQL on this project,

Answer 1

In my experience Linq2SQL does not fit the CRUD methodology very well anyway. Making it fit means jumping through too many hoops to be really worth it. The problem you are describing isn't even the one only one - it gets even worse when updating entities.

Therefore I opted for solution 2 (inserting both entities in the usersrepostory) in my current project.

I also gave up on update methods for the repositories. Unstead my repositories simply have a SubmitChanges method which must be called after all updates are performed on loaded entities. All repositories created in the same web request share the same DataContext, so it doesn't really matter which repository I call Submitchanges on. It's not CRUD, but it lends itself much better to the LINQ2SQL way of performing database updates.

If you really absolutely want pure CRUD, you might want to check out EF with the POCO entity generation template.